Location: PHPKode > projects > PM Report for PostgreSQL > pm_report/generate.php
<?
//session_start();
include "config.php";
include "graphs.inc.php";
function remove_non_num($field_value) {
	$field_value = str_replace("$", "", $field_value);
	$field_value = str_replace(",", "", $field_value);
	$field_value = str_replace("%", "", $field_value);
	return $field_value;
}
?>
<html>
<head>
<title>PM Report ( http://www.busmgtsys.com )</title>
<link rel=stylesheet type=text/css href=css/default.css>
<style type="text/css">
td {font-family: Verdana, Arial, sans-serif; font-size: <? print $report_text_size; ?>px}
body {font-family: Verdana, Arial, sans-serif; font-size: <? print $report_title_size; ?>px}
</style>
</head>
<body text="<? print $text_color; ?>">
<? if($print_logo != "") { ?>
<div align=left>
<img src="<? print $print_logo; ?>">
</div>
<? } ?>
<div align="center">
<?
$SQL_DSN = "host=$_SESSION[SQL_SERVER] dbname=$_SESSION[SQL_DATABASE] user=$_SESSION[SQL_USER] password=$_SESSION[SQL_PASSWORD]";
$connection = pg_connect($SQL_DSN) or die ("Unable to connect to PgSQL server.");
$db = $connection;
if($manual_query == "") {
	if($print_title == "yes") {
		if($report_title == "") {
			$report_title = "$SQL_DATABASE -> $SQL_TABLE";
		}
		print ("<br><br><b>$report_title</b><p>");
	}
	$fields = pg_query($connection, "SELECT * FROM $SQL_TABLE LIMIT 0");
	$columns = pg_num_fields($fields);
	$file = fopen('EXPORT_REPORT.csv', 'w');  // erase text data if it exists!!
	$fp = fopen("EXPORT_REPORT.csv", "a+"); 
	$num = 0;
	$next_field_order = 0;

	$search_query_list = array();
	$select_fields_list = array();
	while ($next_field_order < "$columns") {
		for ($i = 0; $i < $columns; $i++) {
			if(${"field_order".$i} == "$next_field_order") {
				$next_field_order += 1;
				if(${"field_number".$i} != "" OR ${"search_type".$i} != "" AND ${"search_value".$i} != "") {
					$num += 1;
					${"field_".$num} = pg_field_name($fields, $i);
					${"search_type_".$num} = ${"search_type".$i};
					${"search_value_".$num} = ${"search_value".$i};
					$svtext = "search_value_";
					$fields_info = ($fields_info."&field_number".$i."=".${"field_".$num}."&search_type".$i."=".${"search_type_".$num}."&search_value".$i."=".${$svtext.$num});
					$space_check = explode(" ", ${"field_".$num});

					if(${"search_type_".$num} == "IS NULL" OR ${"search_type_".$num} == "IS NOT NULL") {
						array_push($search_query_list, ${"field_".$num}." ".${"search_type_".$num});
					}
					else if(${"search_type_".$num} == "" AND ${$svtext.$num} == "") {
						// do nothing
					}
					else if(${"search_type_".$num} == "LIKE" OR ${"search_type_".$num} == "NOT LIKE") {
						array_push($search_query_list, ${"field_".$num}." ".${"search_type_".$num}." '%${$svtext.$num}%'");
					}
					else {
						array_push($search_query_list, ${"field_".$num}." ".${"search_type_".$num}." '${$svtext.$num}'");
					}

					if(${"field_number".$i} != "") {
						array_push($select_fields_list, ${"field_".$num});
					}
				}
			}
		}
	}

	$search_query = implode(" AND ", $search_query_list);
	$selected_fields = implode(", ", $select_fields_list);

	if ($selected_fields == "") {
		$selected_fields = "*";
	}

	if($per_page == "") {$per_page = 0;}
	if($limit == "") {$limit = 1000;}
	if($group_by != "") {$group_by = "GROUP BY "."`$group_by`";}
	
	if($search_query == "" AND $display_all_recs != "yes") {
		print "<br><big><b>No Search Criteria Was Selected!</b></big>";
	} else {
		if($display_all_recs == "yes") {
			$query = "SELECT $selected_fields FROM $SQL_TABLE $group_by ORDER BY $sort_by $order_by LIMIT $limit OFFSET $per_page";
		} else {
			$query = "SELECT $selected_fields FROM $SQL_TABLE WHERE $search_query $group_by ORDER BY $sort_by $order_by LIMIT $limit OFFSET $per_page";
		}
		print $query;
		$sql = pg_query($query);
		if($export_only != "yes") {
			print ("<table border=0 cellspacing=0 cellpadding=3>
				<tr style=\"background: $header_row_bg_color;color: $header_row_fg_color;\">");
			if($line_numbers == "yes") {
				print ("<td valign=top align=center style=\"border-bottom:solid 1px $report_border_color;border-top:solid 1px $report_border_color;border-left:solid 1px $report_border_color;border-right:solid 1px $report_border_color;\"><b># &nbsp</b></td>");
			}
		}
		$num = 0;
		$next_field_order = 0;
		while ($next_field_order < "$columns") {
			for ($i = 0; $i < $columns; $i++) {
				if(${"field_order".$i} == "$next_field_order") {
					$next_field_order += 1;
					if(${"field_number".$i} != "" OR $selected_fields == "*") {
						$num += 1;
						${"field_".$num} = pg_field_name($fields, $i);
						$ftext = "field_";
						$field_name = ${$ftext.$num};
						$get_longest_length_for_pdf = pg_query("SELECT CHARACTER_LENGTH($field_name) FROM $SQL_TABLE ORDER BY CHARACTER_LENGTH($field_name) DESC LIMIT 1");
						if($glf = pg_fetch_array($get_longest_length_for_pdf)) {
							if($longest_values == "") {
								$longest_values = (15+$glf[0]);
							} else {
								$longest_values .= ";".(15+$glf[0]);
							}
						}
						if(${"graph".$i} != "") {
							if($graph_field == "") {
								$graph_field = $field_name;
							} else {
								$graph_field .= ",".$field_name;
							}
						}
						$field_description = explode("_",$field_name);
						for ($z = 0; $z < 11; $z++) {
							$w = $z+1;
							${"field_description".$w} = ucfirst($field_description[$z]);
						}
						if($field_description10 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6."&nbsp;".$field_description7."&nbsp;".$field_description8."&nbsp;".$field_description9."&nbsp;".$field_description10;
						} elseif($field_description9 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6."&nbsp;".$field_description7."&nbsp;".$field_description8."&nbsp;".$field_description9;
						} elseif($field_description8 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6."&nbsp;".$field_description7."&nbsp;".$field_description8;
						} elseif($field_description7 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6."&nbsp;".$field_description7;
						} elseif($field_description6 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6;
						} elseif($field_description5 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5;
						} elseif($field_description4 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4;
						} elseif($field_description3 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3;
						} elseif($field_description2 != "") {
							$final_descrip = $field_description1."&nbsp;".$field_description2;
						} else {
							$final_descrip = $field_description1;
						}
						if($export_only != "yes") {
							print ("<td valign=top align=center style=\"border-bottom:solid 1px $report_border_color;border-top:solid 1px $report_border_color;border-left:solid 1px $report_border_color;border-right:solid 1px $report_border_color;\"><b>$final_descrip</b></td>");
						}
						if($num == "1") {$column_headings = $fields_enclosed_by.$field_name.$fields_enclosed_by;} else {$column_headings = $column_headings.$fields_seperated_by.$fields_enclosed_by.$field_name.$fields_enclosed_by;}
					}
				}
			}
		}
		if($export_type == "text" AND $generate_pdf != "yes") {
			fwrite($fp,""."$column_headings\n");
		}
		print ("</tr>");
		$color = 0;
		$line_numbering = 0;
		while ($row = pg_fetch_array($sql)) {
			if($color == 0) {$alt_color = $alt_1_row_bg_color;$color = 1;} else {$alt_color = $alt_2_row_bg_color;$color = 0;}
			$line_numbering += 1;
			print ("<tr id=\"$line_numbering\" style=\"background:$alt_color;\" onMouseOut=\"this.style.color='$text_color';this.style.background='$alt_color';\" onMouseOver=\"this.style.color='$highlight_fg_color';this.style.background='$highlight_bg_color';\">");
	
			if($export_only != "yes") {
				if($line_numbers == "yes") {
					print ("<td valign=top align=center style=\"border-left:solid 1px $report_border_color;border-right:solid 1px $report_border_color;\"><b>$line_numbering &nbsp</b></td>");
				}
			}
			$num = 0;
			$next_field_order = 0;
			while ($next_field_order < "$columns") {
				for ($i = 0; $i < $columns; $i++) {
					if(${"field_order".$i} == "$next_field_order") {
						$next_field_order += 1;
						if(${"field_number".$i} != "" OR $selected_fields == "*") {
							$num += 1;
							${"field_".$num} = pg_field_name($fields, $i);
							$ftext = "field_";
							$row_id = ${$ftext.$num};
							if(${"number_format".$i} == "") {
								if($pdf_alignment == "") {
									$pdf_alignment = "L";
								} else {
									$pdf_alignment .= ";L";
								}
								$cell_data = $row[$row_id];
								$cell_data_tot = remove_non_num($row[$row_id]);
								$c_align = "left";
							} else {
								if($pdf_alignment == "") {
									$pdf_alignment = "R";
								} else {
									$pdf_alignment .= ";R";
								}
								$cell_data = remove_non_num($row[$row_id]);
								$cell_data = number_format($cell_data,2);
								$cell_data_tot = remove_non_num($row[$row_id]);
								$c_align = "right";
							}
							if($graph_data_type == "results") {
								if(${"graph".$i} != "") {
									if($graph_results_data == "") {
										$graph_results_data = $cell_data;
										$pdf_graph_results_data = $cell_data;
										$graph_results_numbers = $line_numbering." - ".$row_id;
									} else {
										$graph_results_data .= ",".$cell_data;
										$pdf_graph_results_data .= ",".$cell_data;
										$graph_num += 1;
										$graph_results_numbers .= ",".$line_numbering." - ".$row_id;
									}
									if(${$row_id."_graph"} == "") {
										${$row_id."_graph"} = $cell_data;
									} else {
										${$row_id."_graph"} .= ";".$cell_data;
									}
								}
							}
							if(${"total_col".$i} != ""){${$row_id."_total"} += $cell_data_tot;}
							if($export_only != "yes") {
								print ("<td valign=top align=$c_align style=\"border-left:solid 1px $report_border_color;border-right:solid 1px $report_border_color;\"><b>$cell_data &nbsp</b></td>");
							}
							if($export_type == "text") {
								if($num == "1") {$column_data = $fields_enclosed_by.$cell_data.$fields_enclosed_by;} else {$column_data = $column_data.$fields_seperated_by.$fields_enclosed_by.$cell_data.$fields_enclosed_by;}
							} elseif($export_type == "sql") {
								if($num == "1") {$column_data = "INSERT INTO '$SQL_TABLE' VALUES ('$cell_data'";} else {$column_data = $column_data.",'".$cell_data."'";}
							}
						}
					}
				}
			}
			if($export_type == "sql") {
				$column_data = $column_data.");";
			}
			fwrite($fp,""."$column_data\n");
			print ("</tr>");
		}
		if($any_totals == "yes") {
			if($export_only != "yes") {
				print ("<tr><td style=\"border:solid 2px $report_border_color;background: $header_row_bg_color;color: $header_row_fg_color;\" colspan=$columns align=center><b> &nbsp TOTALS &nbsp </b></td></tr>");
				
				print ("<tr>");
				
				if($line_numbers == "yes") {
					print ("<td valign=top align=center style=\"border-left:solid 1px $report_border_color;border-right:solid 1px $report_border_color;\"><b>&nbsp</b></td>");
				}
			}
			$num = 0;
			$next_field_order = 0;
			while ($next_field_order < "$columns") {
				for ($i = 0; $i < $columns; $i++) {
					if(${"field_order".$i} == "$next_field_order") {
						$next_field_order = $next_field_order+1;
						if(${"field_number".$i} != "" OR $selected_fields == "*") {
							$num = $num+1;
							${"field_".$num} = pg_field_name($fields, $i);
							$ftext = "field_";
							$row_id = ${$ftext.$num};
							$graph_total = ${$row_id."_total"};
							if($graph_total == "") {$graph_total = "0";}
							if(${"number_format".$i} == "") {
								$c_align = "left";
								$col_total = ${$row_id."_total"};
							} else {
								$c_align = "right";
								$col_total = number_format(${$row_id."_total"},2);
							}
							if($graph_data_type == "totals") {
								if(${"graph".$i} != "") {
									if($graph_data == "") {
										$graph_data = $graph_total;
										$pdf_graph_data = $graph_total;
									} else {
										$graph_data .= ",".$graph_total;
										$pdf_graph_data .= ",".$graph_total;
									}
								}
							}
							if($export_only != "yes") {
								print ("<td valign=top align=$c_align style=\"border-left:solid 1px $report_border_color;border-right:solid 1px $report_border_color;\"><b>$col_total &nbsp</b></td>");
							}
							if($num == "1") {
								$column_data = $col_total;
								$total_pdf_array = $col_total;
							} else {
								$column_data = $column_data.",".$col_total;
								$total_pdf_array .= ";".$col_total;
							}
						}
					}
				}
			}
		}
		if($export_only != "yes") {
			if($line_numbers == "yes") {
				$columns += 1;
			}
			print ("<tr><td style=\"border-top:solid 2px $report_border_color\" colspan=$columns align=center><b> &nbsp End Of Page &nbsp </b></td></tr>");
			print ("</table>");
		}
		fclose($fp); 
	}
	?>
	<br>
	<br>
	<a href="EXPORT_REPORT.csv">Export Data</a>
	<br>
	<table><tr><td>( Right click on "Export Data" and select "Save Target As..." to save the file to your computer. )</td></tr></table>
	<p>
	<?
	// PDF GRAPHS	
	if($create_graph == "yes" AND $graph_data_type != "results") {
		$pdf_graph_values = $pdf_graph_data;
	} else {
		$pdf_graph_values = $pdf_graph_results_data;
	}
	// HTML GRAPHS	
	$columns = pg_num_fields($fields);
	if($create_graph == "yes" AND $graph_type != "pie" AND $graph_type != "none") {
		$num = 0;
		$next_field_order = 0;
		while ($next_field_order < "$columns") {
			for ($i = 0; $i < $columns; $i++) {
				if(${"field_order".$i} == "$next_field_order") {
					$next_field_order += 1;
					if(${"field_number".$i} != "" OR $selected_fields == "*") {
						$num += 1;
						${"field_".$num} = pg_field_name($fields, $i);
						$ftext = "field_";
						$row_id = ${$ftext.$num};
						$graph_total = ${$row_id."_graph"};
						if($graph_data_type == "results") {
							if(${"graph".$i} != "") {
								if($graph_data == "") {
									$graph_data = $graph_total;
								} else {
									$graph_data .= ",".$graph_total;
								}
							}
						}
					}
				}
			}
		}
		$graph = new BAR_GRAPH("$graph_type");
		$graph->labels = "$graph_field";
		$graph->values = "$graph_data";
		$graph->barColor = "$barColor";
		$graph->barBGColor = "$barBGColor";
		$graph->labelColor = "$labelColor";
		$graph->labelBGColor = "$labelBGColor";
		$graph->showValues = $show_graph_values;
		echo $graph->create();
	} elseif($create_graph == "yes" AND $graph_type == "pie") {
		if($graph_data_type == "results") {
			$values = $graph_results_data;
			$desc = $graph_results_numbers;
		} else {
			$values = $graph_data;
			$desc = $graph_field;
		}
		$DescCount = explode(",", $desc);
		$desc_num = count($DescCount);
		$width = '600';
		$height = 100+(round($desc_num/5)*100);
		$title = $report_title;
		print "<img src=\"piemaker.php?width=".$width."&height=".$height."&values=".$values."&desc=".$desc."&title=".$title."\">";
	}
} else {
	$search_query = pg_query("$manual_query");
	$selected_fields_check = explode(" ", $manual_query);
	$fields = pg_query($connection, "SELECT * FROM $SQL_TABLE LIMIT 0");
	$columns = pg_num_fields($fields);
	if($selected_fields_check[1] == "*") {
		$selected_fields = "*";
		for ($i = 0; $i < $columns; $i++) {
			if($i == 0) {
				$names = pg_field_name($fields, $i);
			} else {
				$names .= ",".pg_field_name($fields, $i);
			}	
		}
		$field_names = explode(",", $names);
	} else {
		$display_all_rec = "no";	
		$field_names = explode(",", $selected_fields_check[1]);
	}
	print ("<table border=0 cellspacing=0 cellpadding=3>
		<tr style=\"background: $header_row_bg_color;color: $header_row_fg_color;\">");
	for ($i = 0; $i < $columns; $i++) {
		if($field_names[$i] != "" OR $selected_fields == "*") {
			$field_name = $field_names[$i];
			$field_description = explode("_",$field_name);
			for ($z = 0; $z < 11; $z++) {
				$w = $z+1;
				${"field_description".$w} = ucfirst($field_description[$z]);
			}
			if($field_description10 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6."&nbsp;".$field_description7."&nbsp;".$field_description8."&nbsp;".$field_description9."&nbsp;".$field_description10;
			} elseif($field_description9 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6."&nbsp;".$field_description7."&nbsp;".$field_description8."&nbsp;".$field_description9;
			} elseif($field_description8 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6."&nbsp;".$field_description7."&nbsp;".$field_description8;
			} elseif($field_description7 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6."&nbsp;".$field_description7;
			} elseif($field_description6 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5."&nbsp;".$field_description6;
			} elseif($field_description5 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4."&nbsp;".$field_description5;
			} elseif($field_description4 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3."&nbsp;".$field_description4;
			} elseif($field_description3 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2."&nbsp;".$field_description3;
			} elseif($field_description2 != "") {
				$final_descrip = $field_description1."&nbsp;".$field_description2;
			} else {
				$final_descrip = $field_description1;
			}
			print ("<td valign=top align=center style=\"border-left:solid 1px $report_border_color;border-right:solid 1px $report_border_color;\"><b>$final_descrip</b></td>");
		}
	}
	print ("</tr>");
	$color = 0;
	if ($row = pg_fetch_array($search_query)) {
		while ($row = pg_fetch_array($search_query)) {
			if($color == 0) {$alt_color = $alt_1_row_bg_color;$color = 1;} else {$alt_color = $alt_2_row_bg_color;$color = 0;}
			print ("<tr style=\"background:$alt_color;\" onMouseOut=\"this.style.color='$text_color';this.style.background='$alt_color';\" onMouseOver=\"this.style.color='$highlight_fg_color';this.style.background='$highlight_bg_color';\">");
			for ($i = 0; $i < $columns; $i++) {
				if($field_names[$i] != "" OR $selected_fields == "*") {
					$row_id = $field_names[$i];
					$c_align = "left";
					print ("<td valign=top align=$c_align style=\"border-left:solid 1px $report_border_color;border-right:solid 1px $report_border_color;\"><b>$row[$row_id] &nbsp</b></td>");
				}
			}
			print ("</tr>");
		}
	} else {
		print ("</table><p><b><font color=red>Something is wrong with your query or you tried something that is unsupported.</font></b><table>");
	}
	print ("<tr><td style=\"border-top:solid 2px $report_border_color\" colspan=$columns align=center><b> &nbsp End Of Page &nbsp </b></td></tr>");
	print ("</table>");
}
if($generate_pdf == "yes") {
	?>
	<form name="form" action="generate_pdf.php" target="_blank" method="post">
	<input type="hidden" name="title" value="<? print $report_title; ?>">
	<input type="hidden" name="pdf_layout" value="<? print $_POST[pdf_layout]; ?>">
	<input type="hidden" name="pdf_date" value="<? print $_POST[pdf_date]; ?>">
	<input type="hidden" name="header_data" value="<? print $column_headings; ?>">
	<input type="hidden" name="pdf_alignment" value="<? print $pdf_alignment; ?>">
	<input type="hidden" name="longest_values" value="<? print $longest_values; ?>">
	<input type="hidden" name="any_totals" value="<? print $any_totals; ?>">
	<input type="hidden" name="total_pdf_array" value="<? print $total_pdf_array; ?>">
	<input type="hidden" name="create_graph" value="<? print $create_graph; ?>">
	<input type="hidden" name="graph_type" value="<? print $pdf_graph_type; ?>">
	<input type="hidden" name="pdf_graph_values" value="<? print $pdf_graph_values; ?>">
	<input type="hidden" name="graph_data_type" value="<? print $graph_data_type; ?>">
	<input type="hidden" name="pdf_graph_title" value="<? print $pdf_graph_title; ?>">
	<input type="hidden" name="print_logo" value="<? print $print_logo; ?>">
	</form>
	<SCRIPT LANGUAGE="JavaScript">
	<!-- Begin
	document.form.submit();
	//  End -->
	</script>
	<?
}
?>
<p>
<a href="javascript:void(window.close())">Close Window</a>
<p>
<a href="http://www.busmgtsys.com">Visit the BMS website for updates!<br>www.busmgtsys.com</a>
</div>
</body>
</html>
Return current item: PM Report for PostgreSQL