Location: PHPKode > projects > PM Report for PostgreSQL > pm_report/index.php
<?
//session_start();
include "config.php";
// CHANGE TABLE
if($clear_table == "yes") {
	$SQL_TABLE = "";
}
// CHANGE DATABASE
if($clear_database == "yes") {
	$SQL_DATABASE = "";
	$SQL_TABLE = "";
}
// CHANGE ALL MYSQL INFORMATION
if($clear_all == "yes") {
	$SQL_SERVER = "";
	$SQL_USER = "";
	$SQL_PASSWORD = "";
	$SQL_DATABASE = "";
	$SQL_TABLE = "";
}
if(isset($SQL_SERVER)){$_SESSION[SQL_SERVER] = $SQL_SERVER;} 
if(isset($SQL_USER)){$_SESSION[SQL_USER] = $SQL_USER;} 
if(isset($SQL_PASSWORD)){$_SESSION[SQL_PASSWORD] = $SQL_PASSWORD;} 
if(isset($SQL_DATABASE)){$_SESSION[SQL_DATABASE] = $SQL_DATABASE;} 
if(isset($SQL_TABLE)){$_SESSION[SQL_TABLE] = $SQL_TABLE;} 
$SQL_DSN = "host=".$_SESSION[SQL_SERVER]." dbname=".$_SESSION[SQL_DATABASE]." user=".$_SESSION[SQL_USER]." password=".$_SESSION[SQL_PASSWORD];
?>
<html>
<head>
<title>PM Report</title>
<link rel=stylesheet type=text/css href=css/default.css>
<?
if($SQL_TABLE != "") {
	$connection = pg_connect($SQL_DSN) or die ("Unable to connect to PgSQL server.");
	$fields = pg_query($connection, "SELECT * FROM $_SESSION[SQL_TABLE] LIMIT 0");
	$columns = pg_num_fields($fields);
?>
<script language="JavaScript">
function checkAllFields(theForm){
	for (var j = 0; j <= "<? print $columns; ?>"; j++)  {
		box = eval("document.form.field_number" + j);
		if (box.checked == false) box.checked = true;
	}
}
function uncheckAllFields(theForm){
	for (var j = 0; j <= "<? print $columns; ?>"; j++)  {
		box = eval("document.form.field_number" + j);
		if (box.checked == true) box.checked = false;
	}
}
function query_help(){
//	alert("-This will override ALL of the selected criteria above\n\n-Only SELECT queries will work at this time\n\n-Seperate selected field names with a comma and NO SPACE\nExample:\nSELECT first_name,last_name FROM customer_file\nNOT\nSELECT first_name, last_name FROM customer_file\n\n-The order you list the selected fields is the order they will show up on the report\n\n-You could also use an * to select all the fields\n\nSample Query: SELECT * FROM customer_file WHERE name LIKE 'Busi%' LIMIT 10");
}
</script>
<?
}
?>
</head>
<body text="<? print $text_color; ?>">
<div align=center>
<br>
<img src="pmreport.gif">
<br>
<br>
<table border=0 cellspacing=0 cellpadding=3>
	<tr>
		<? //if($SQL_SERVER != "" OR $SQL_USER != "" OR $SQL_PASSWORD != "") { ?>
			<form action="<? print $PHP_SELF; ?>" method="post">
			<td align=center>
				<input type="hidden" name="clear_all" value="yes">
				<input type="submit" value="Reset All Information">
			</td>
			</form>
			<?
		//}
		//if($SQL_DATABASE != "") {
			?>
			<form action="<? print $PHP_SELF; ?>" method="post">
			<td align=center>
				<input type="hidden" name="clear_database" value="yes">
				<input type="submit" value="Change Database">
			</td>
			</form>
			<?
		//}
		//if($SQL_TABLE != "") {
			?>
			<form action="<? print $PHP_SELF; ?>" method="post">
			<td align=center>
				<input type="hidden" name="clear_table" value="yes">
				<input type="submit" value="Change Table">
			</td>
			</form>
		<? //} ?>
	</tr>
</table>
<br>
<?
if($_SESSION[SQL_SERVER] == "" OR $_SESSION[SQL_USER] == "" OR $_SESSION[SQL_PASSWORD] == "") {
	?>
	<table>
		<form action="<? print $PHP_SELF; ?>" method="post">
		<tr>
			<td align=right><b>PgSQL Server Name:</b></td>
			<td><input type="text" name="SQL_SERVER" value=""></td>
		</tr>
		<tr>
			<td align=right><b>PgSQL User Name:</b></td>
			<td><input type="text" name="SQL_USER" value=""></td>
		</tr>
		<tr>
			<td align=right><b>PgSQL Password:</b></td>
			<td><input type="password" name="SQL_PASSWORD" value=""></td>
		</tr>
		<tr>
			<td colspan="2" align="right">
				<input type="submit" value="Submit">
			</td>
		</tr>
		</form>
	</table>
	<?
	exit;
} else {
}
if($_SESSION[SQL_DATABASE] == "") {
	?>
	<table>
		<form action="<? print $PHP_SELF; ?>" method="post">
		<tr>
			<td align=right><b>PgSQL Database:</b></td>
			<td><input type="text" name="SQL_DATABASE" value="eds"></td>
		</tr>
                <tr>
                        <td colspan="2" align="right">
                                <input type="hidden" name="clear_table" value="yes">
                                <input type="submit" value="Submit">
                        </td>
                </tr>
		</form>
	</table>
	<?
	exit;
} else {
	$connection = pg_connect($SQL_DSN) or die ("Unable to connect to PgSQL server.");
	$db = $connection;
}
if($_SESSION[SQL_TABLE] == "") {
	?>
	<table>
		<form action="<? print $PHP_SELF; ?>" method="post">
		<tr>
			<td align=right><b>Database Table:</b></td>
			<td>
				<select type="text" name="SQL_TABLE">
				<?
				$result = pg_query($connection, "select relname from pg_tables inner join pg_class on pg_tables.tablename=pg_class.relname where schemaname='public' and array_to_string(relacl, '|') similar to '%".$_SESSION[SQL_USER]."=[awdRxt]*r[awdRxt]*/%'");
				if (!$result) {
					print "DB Error, could not list tables\n";
					print 'PgSQL Error: ' . pg_last_error();
					exit;
				}
    
				while ($row = pg_fetch_row($result)) {
					print "<option value=\"$row[0]\">$row[0]</option>";
				}

				pg_free_result($result);
				?>
				</select>
			</td>
		</tr>
		<tr>
                        <td colspan="2" align="right">
                                <input type="submit" value="Submit">
                        </td>
                </tr>
		</form>
	</table>
	<?
	exit;
}
?>
<table border=0 cellspacing=0 cellpadding=3>
	<form action="generate.php" target="_blank" method="post" name="form">
	<tr style="background: lightyellow">
		<th style="border-top: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Display Fields<br /><span style="font-size: smaller">(Check fields to show on report.)</span></th>
		<th colspan="2" style="border: 2px solid <? print $header_row_bg_color; ?>;border-bottom: 0px none" align=center valign=bottom>Row selection criteria<br /><span style="font-size: smaller">(To add filter, select operation and enter value.)</span></th>
		<th colspan="4" style="border-top: 2px solid <? print $header_row_bg_color; ?>;border-right: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Report Options<br /><span style="font-size: smaller">(Check all that apply.)</span></th>
	</tr>
	<tr style="background:<? print $header_row_bg_color; ?>;color:<? print $header_row_fg_color; ?>;">
		<th style="border-top: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Field Names</th>
		<th style="border-top: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Operator</th>
		<th style="border-top: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Value</th>
		<th style="border-top: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Number Format</th>
		<th style="border-top: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Total</th>
		<th style="border-top: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Graph</th>
		<th style="border-top: 2px solid <? print $header_row_bg_color; ?>;border-right: 2px solid <? print $header_row_bg_color; ?>;" align=center valign=bottom>Field Order</th>
	</tr>
	<?
	$fields = pg_query($connection, "SELECT * FROM $_SESSION[SQL_TABLE] LIMIT 0");
	$columns = pg_num_fields($fields);
	for ($i = 0; $i < $columns; $i++) {
	$field_name = pg_field_name($fields, $i);
		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;\">
				<td style=\"border-left: 2px solid $header_row_bg_color;\">
					<input type=\"checkbox\" name=\"field_number$i\" value=\"$field_name\"> &nbsp <b>$field_name</b>
				</td>
				<td>
					<select name=\"search_type$i\">
					<option></option>
					<option value=\"LIKE\">LIKE</option>
					<option value=\"NOT LIKE\">NOT LIKE</option>
					<option value=\"=\">=</option>
					<option value=\"!=\">!=</option>
					<option value=\"<\"><</option>
					<option value=\">\">></option>
					<option value=\"<=\"><=</option>
					<option value=\">=\">>=</option>
					<option value=\"IS NULL\">IS NULL</option>
					<option value=\"IS NOT NULL\">IS NOT NULL</option>
					</select>
				</td>
				<td>
					<input type=\"text\" name=\"search_value$i\">
				</td>
				<td align=center>
					<input type=\"checkbox\" name=\"number_format$i\">
				</td>
				<td align=center>
					<input type=\"checkbox\" name=\"total_col$i\">
				</td>
				<td align=center>
					<input type=\"checkbox\" name=\"graph$i\">
				</td>
				<td align=center style=\"border-right: 2px solid $header_row_bg_color;\">
					<input type=\"text\" style=\"text-align:center;\" name=\"field_order$i\" size=\"4\" maxlength=\"4\" value=\"$i\">
				</td>
			</tr>");
	}
	print ("<tr>
			<td style=\"border-left: 2px solid $header_row_bg_color;border-right: 2px solid $header_row_bg_color;\" colspan=7>
				<input type=\"button\" onClick=\"javascript:checkAllFields(this.form);\" value=\"Check All\">
				<input type=\"button\" onClick=\"javascript:uncheckAllFields(this.form);\" value=\"Uncheck All\">
			</td>
		</tr>");
	print ("<tr>
			<td style=\"border-left: 2px solid $header_row_bg_color;border-right: 2px solid $header_row_bg_color;\" colspan=7>
				<input type=\"checkbox\" name=\"display_all_recs\" value=\"yes\"> <b>Display All Records</b>
			</td>
		</tr>");
	?>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;border-top: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Sort By: &nbsp</b></td>
		<td style="border-top: 2px solid <? print $header_row_bg_color; ?>;">
			<select name="sort_by">
			<?
			$fields = pg_query($connection, "SELECT * FROM $_SESSION[SQL_TABLE] LIMIT 0");
			$columns = pg_num_fields($fields);
			for ($i = 0; $i < $columns; $i++) {
				$field_name = pg_field_name($fields, $i);
				print ("<option value=\"$field_name\">$field_name</option>");
			}
			?>
			</select>
		</td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;border-top: 2px solid <? print $header_row_bg_color; ?>;" colspan=5>
			<select name="order_by">
			<option value="ASC">ASC</option>
			<option value="DESC">DESC</option>
			</select>
		</td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Group By: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6>
			<select name="group_by">
			<option value=""></option>
			<?
			$fields = pg_query($connection, "SELECT * FROM $_SESSION[SQL_TABLE] LIMIT 0");
			$columns = pg_num_fields($fields);
			for ($i = 0; $i < $columns; $i++) {
				$field_name = pg_field_name($fields, $i);
				print ("<option value=\"$field_name\">$field_name</option>");
			}
			?>
			</select>
		</td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Starting At Record: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="limit" name="per_page" value="0"></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Number Of Results To Show: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="limit" name="limit" value="1000"></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Any Totals?: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="any_totals" value="no" CHECKED><b>NO</b> &nbsp <input type="radio" name="any_totals" value="yes"><b>YES</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Number Lines?: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="line_numbers" value="no" CHECKED><b>NO</b> &nbsp <input type="radio" name="line_numbers" value="yes"><b>YES</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color;?>;" align=right><b>Title: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="text" name="report_title"></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Print Title?: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="print_title" value="yes" CHECKED><b>YES</b> &nbsp <input type="radio" name="print_title" value="no"><b>NO</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Logo: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="text" name="print_logo"></td>
	</tr>
	<tr>
		<td colspan=7 style="border-right: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=center><b><small>Enter the URL of your logo to display it on the report.</small></b></td>
	</tr>

	<!--tr style="background:<? print $header_row_bg_color; ?>;color:<? print $header_row_fg_color; ?>;">
		<td colspan=7 style="border-right: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=center><b>Graph Options &nbsp</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Create Graph?: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="create_graph" value="no" CHECKED><b>NO</b> &nbsp <input type="radio" name="create_graph" value="yes"><b>YES</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>HTML Graph Type: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="graph_type" value="none" CHECKED><b>None</b> &nbsp <input type="radio" name="graph_type" value="hbar"><b>Horizontal</b> &nbsp <input type="radio" name="graph_type" value="vbar"><b>Vertical</b> &nbsp <input type="radio" name="graph_type" value="pie"><b>Pie</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>PDF Graph Type: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="pdf_graph_type" value="none" CHECKED><b>None</b> &nbsp <input type="radio" name="pdf_graph_type" value="bar_graph"><b>Bar</b> &nbsp <input type="radio" name="pdf_graph_type" value="line_graph"><b>Line</b> &nbsp <input type="radio" name="pdf_graph_type" value="box_graph"><b>Boxes</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Graph Totals or Results: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="graph_data_type" value="totals" CHECKED><b>Totals</b> &nbsp <input type="radio" name="graph_data_type" value="results"><b>Results</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Show Values: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="show_graph_values" value="1" CHECKED><b>YES</b> &nbsp <input type="radio" name="show_graph_values" value="0"><b>NO</b></td>
	</tr-->
	<tr style="background:<? print $header_row_bg_color; ?>;color:<? print $header_row_fg_color; ?>;">
		<td colspan=7 style="border-right: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=center><b>Export Options &nbsp</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Export Only?: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="export_only" value="no" CHECKED><b>NO</b> &nbsp <input type="radio" name="export_only" value="yes"><b>YES</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Export Type: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="export_type"	value="text" CHECKED><b>Text</b> &nbsp <input type="radio" name="export_type" value="sql"><b>SQL</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Fields Seperated By: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="text" name="fields_seperated_by" value=";" size=2 maxlength=2></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Fields Enclosed By: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="text" name="fields_enclosed_by" value="" size=2 maxlength=2></td>
	</tr>
	<tr style="background:<? print $header_row_bg_color; ?>;color:<? print $header_row_fg_color; ?>;">
		<td colspan=7 style="border-right: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=center><b>PDF Options &nbsp</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Generate PDF?: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="generate_pdf" value="no" CHECKED><b>NO</b> &nbsp <input type="radio" name="generate_pdf" value="yes"><b>YES</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>PDF Layout: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="pdf_layout" value="P" CHECKED><b>Portrait</b> &nbsp <input type="radio" name="pdf_layout" value="L"><b>Landscape</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>Print Date: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="radio" name="pdf_date" value="no" CHECKED><b>No</b> &nbsp <input type="radio" name="pdf_date" value="yes"><b>Yes</b></td>
	</tr>
	<tr>
		<td style="border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>PDF Graph Title: &nbsp</b></td>
		<td style="border-right: 2px solid <? print $header_row_bg_color; ?>;" colspan=6><input type="text" name="pdf_graph_title"></td>
	</tr>
	<tr>
		<td colspan=7 style="border-right: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=center><b>DO NOT change any of the "Export Options" (except "Export Only?") when creating a PDF.<br>You can generate larger PDF reports by selecting "Yes" next to "Export Only?".</b></td>
	</tr>
	<tr style="background:<? print $header_row_bg_color; ?>;color:<? print $header_row_fg_color; ?>;">
		<td colspan=7 style="border-right: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=center><b>Enter Your Own Query &nbsp</b></td>
	</tr>
	<tr>
		<td style="border-bottom: 2px solid <? print $header_row_bg_color; ?>;border-left: 2px solid <? print $header_row_bg_color; ?>;" align=right><b>SQL Query: &nbsp<br><button style="background:#FFFFFF;color:blue;border:none;font-size:10;text-align:right;" onClick="query_help();">Click Here For Help &nbsp</button></small></b></td>
		<td colspan=6 style="border-bottom: 2px solid <? print $header_row_bg_color; ?>;border-right: 2px solid <? print $header_row_bg_color; ?>;">
			<textarea name="manual_query" cols=50 rows=4></textarea>
		</td>
	</tr>
	<tr>
		<td colspan=7 align=center>
			&nbsp
		</td>
	<tr>
		<td colspan=7 align=center>
			<input type=hidden name="SQL_SERVER" value="<? print $_SESSION[SQL_SERVER]; ?>">
			<input type=hidden name="SQL_USER" value="<? print $_SESSION[SQL_USER]; ?>">
			<input type=hidden name="SQL_PASSWORD" value="<? print $_SESSION[SQL_PASSWORD]; ?>">
			<input type=hidden name="SQL_DATABASE" value="<? print $_SESSION[SQL_DATABASE]; ?>">
			<input type=hidden name="SQL_TABLE" value="<? print $_SESSION[SQL_TABLE]; ?>">
			<input type="submit" value="Generate Report">
		</td>
	</tr>
	</form>
</table>
</div>
</body>
</html>
Return current item: PM Report for PostgreSQL