reports.php

来自「这是一个用于资产管理的源码工具包」· PHP 代码 · 共 558 行 · 第 1/2 页

<?
/*****************************************************************************
*	SimpleAssets - an online web based asset management application.
*	Copyright (C) 2002 Jeff Gordon ([e-mail]). All rights reserved.
*	Public Works and Government Services Canada (PWGSC)
*   Architecture and Standards Directorate
*
*	Released July 2002
*
*  	This program is free software licensed under the 
* 	GNU General Public License (GPL).
*
*	This file is part of SimpleAssets.
*
*	SimpleAssets is free software; you can redistribute it and/or modify
*	it under the terms of the GNU General Public License as published by
*	the Free Software Foundation; either version 2 of the License, or
*	(at your option) any later version.
*
*	SimpleAssets is distributed in the hope that it will be useful,
*	but WITHOUT ANY WARRANTY; without even the implied warranty of
*	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*	GNU General Public License for more details.
*
*	You should have received a copy of the GNU General Public License
*	along with SimpleAssets; if not, write to the Free Software
*	Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*******************************************************************************/

// contains functions:
// reports_summary()
// reports_ip()
// reports_verify()
// reports_sign_out()
// reports_assets_details($key, $full_name)
// reports_assets()
// reports_licenses_detailed_details($key, $manufacturer)
// reports_licenses()
// reports_licenses_detailed()
// reports_employees()

// shows a menu of all available reports
function reports_summary() {
	global $emp_db;
	global $print_screen;
	global $hrcolor;
	global $my_access_level;
	menu_header("","Reports","reports.jpg");
	echo "<blockquote><font class='text12'>";
	echo "<p><a href='" . $PHP_SELF . "?action=reportsassets' class='text12bold'>Download Asset Report (in Spreadsheet Format)</a>";
	echo "<br>This report lists all assets and their current assignments in one spreadsheet.";
	echo "<p><a href='" . $PHP_SELF . "?action=reportslicensessummary' class='text12bold'>Download Summary License Report (in Spreadsheet Format)</a>";
	echo "<br>This report lists a summary of licensing information in one spreadsheet.";
	echo "<p><a href='" . $PHP_SELF . "?action=reportslicensesdetailed' class='text12bold'>Download Detailed License Report (in Spreadsheet Format)</a>";
	echo "<br>This report lists all detailed licensing information in one spreadsheet.";
	if ($my_access_level > 1) {
		echo "<p><a href='" . $PHP_SELF . "?action=reportsemployees' class='text12bold'>Download Employees Report (in Spreadsheet Format)</a>";
		echo "<br>This report lists all the employee information in one spreadsheet.";
	}
	echo "<p><a href='" . $PHP_SELF . "?action=reportssignout' class='text12bold'>Transfer Report</a>";
	echo "<br>This report lists all transfers based on the date you select.";
	echo "<p><a href='" . $PHP_SELF . "?action=reportssignout&temp=1' class='text12bold'>Sign Out Report</a>";
	echo "<br>This report lists all sign outs based on the date you select.";
	echo "<p><a href='" . $PHP_SELF . "?action=reportsverify' class='text12bold'>Verification Report</a>";
	echo "<br>This report lists when each employee last verified their own information.";
	if ($my_access_level > 1) {
		echo "<p><a href='" . $PHP_SELF . "?action=reportsip' class='text12bold'>IP Report</a>";
		echo "<br>This report lists all the IPs of the directorate and which employee they belong.";
	}
	echo "</font></blockquote>";
}

// shows a list of all IPs and which employee they are assigned to
function reports_ip() {
	global $print_screen;
	global $hrcolor;
	global $emp_db;
	menu_header("<a href='" . $PHP_SELF . "?action=reports' class='text10bold'>Reports</a>","IP Report","reports.jpg");

	$color = "#eeffee";
	$sql = "SELECT IP.ip, IP.employeeid, " . $emp_db . "Employees.LastName, " . $emp_db . "Employees.FirstName FROM IP LEFT JOIN " . $emp_db . "Employees ON IP.employeeid = " . $emp_db . "Employees.Id ORDER BY " . $emp_db . "Employees.LastName";
	if (($result = doSql($sql)) && (mysql_num_rows($result))) {
		echo "<table width=100%><tr bgcolor='#ffffff'><td class='text12bold' width=20%>IP</td><td class='text12bold' width=80%>Employee</td></tr></table>";
		while ($query_data = mysql_fetch_array($result)) {
			echo "<table width=100% bgcolor='" . $color . "' class='reportsborder'>";
			echo "<tr>";
			echo "<td class='text12bold' width=20%>" . $query_data["ip"] . "</td>";
			echo "<td width=80%><a href='" . $PHP_SELF . "?action=employeeview&key=" . $query_data["employeeid"] . "' class='text12bold'>" . $query_data["LastName"] . ", " . $query_data["FirstName"] . "</a></td>";
			echo "</tr>";
			echo "</table>";
			if ($color == "#eeffee") $color = "#ffffff";
			else $color = "#eeffee";
		}
	} else {
		echo "<blockquote><font class='text12'>No IPs were found.</font></blockquote>";
	}
	echo "</table>";

}

// shows a report of when all employees last verified their information
function reports_verify() {
	global $print_screen;
	global $hrcolor;
	global $emailrequest;
	global $length;
	global $HTTP_HOST;
	global $SCRIPT_NAME;
	global $my_access_level;
	global $emp_db;
	
	// top header bar
	menu_header("<a href='" . $PHP_SELF . "?action=reports' class='text10bold'>Reports</a>","Verification Report","reports.jpg");
	
	// do the actual e-mailing
	if (($emailrequest == "1") && ($my_access_level > 1)) {
		echo "<font class='text12bold'>E-Mail Verification Requests have been sent out.</font><p>";
		$sql = "SELECT " . $emp_db . "Employees.Id, " . $emp_db . "Employees.FirstName, " . $emp_db . "Employees.EMail, " . $emp_db . "Employees.Verified FROM " . $emp_db . "Employees WHERE ((" . time() . " - " . $emp_db . "Employees.Verified) > " . ($length * 86400) . ")";
		if (($result = doSql($sql)) && (mysql_num_rows($result))) {
			while ($query_data = mysql_fetch_array($result)) {
				if ($query_data["EMail"] != "") {
					if ($query_data["Verified"] == "0") $date_text = "Your assets have never been verified.";
					else $date_text = "Your assets were last verified on " . date("F d, Y", $query_data["Verified"]) . " at " . date("h:i a", $query_data["Verified"]) . ".";
					if (strlen($query_data["EMail"]) > 0) {
						mail($query_data["EMail"], "SimpleAssets Verfication Required", $query_data["FirstName"] . ",\n\n" . $date_text . " The administrator has requested that you verify your assets as soon as possible by visiting this url:\nhttp://" . $HTTP_HOST . $SCRIPT_NAME . "?action=employeeview&key=" . $query_data["Id"] . " \n\nThank you for your co-operation,\nThe SimpleAssets Administrator","From: SimpleAssets Administrator\nX-Priority: 1\n");
					}
				}
			}
		}
	}

	// show all the statuses
	$color = "#eeffee";
	$sql = "SELECT " . $emp_db . "Employees.Id, " . $emp_db . "Employees.Verified, " . $emp_db . "Employees.LastName, " . $emp_db . "Employees.FirstName FROM " . $emp_db . "Employees ORDER BY " . $emp_db . "Employees.LastName";
	if (($result = doSql($sql)) && (mysql_num_rows($result))) {
		echo "<table width=100%><tr bgcolor='#ffffff'><td class='text12bold' width=50%>Employee</td><td class='text12bold' width=50%>Last Verified</td></tr></table>";
		while ($query_data = mysql_fetch_array($result)) {
			if ($query_data["Verified"] == "0") $date_text = "Not Verified";
			else $date_text = date("F d, Y", $query_data["Verified"]) . " at " . date("h:i a", $query_data["Verified"]);
			echo "<table width=100% bgcolor='" . $color . "' class='reportsborder'>";
			echo "<tr>";
			echo "<td width=50%><a href='" . $PHP_SELF . "?action=employeeview&key=" . $query_data["Id"] . "' class='text12bold'>" . $query_data["LastName"] . ", " . $query_data["FirstName"] . "</a></td>";
			echo "<td class='text12' width=50%>" . $date_text . "</td>";
			echo "</tr>";
			echo "</table>";
			if ($color == "#eeffee") $color = "#ffffff";
			else $color = "#eeffee";
		}
		// show the e-mail form
		if ($my_access_level > 1) {
			echo "<form action='" . $PHP_SELF . "' method='get'>";
			echo "<input type='hidden' name='action' value='reportsverify'>";
			echo "<input type='hidden' name='emailrequest' value='1'>";
			echo "<font class='text12'><b>E-Mail Verification Request</b> to employees who last verified: ";
			echo "<select name='length'>";
			echo "<option value='0'>anytime</option>";
			echo "<option value='5'>more than 5 days ago</option>";
			echo "<option value='15'>more than 15 days ago</option>";
			echo "<option value='30' selected>more than 30 days ago</option>";
			echo "<option value='60'>more than 60 days ago</option>";
			echo "<option value='90'>more than 90 days ago</option>";
			echo "</select>";
			echo "&nbsp;<input type='image' name='submit' src='images/go_white.jpg' width=20 height=20 border=0>";
			echo "<br><i>Note: E-mailing may take several minutes to process, please be patient.</i></font>";
			echo "</form>";	
		}
	} else {
		echo "<blockquote><font class='text12'>No employees were found.</font></blockquote>";
	}
}

// shows a report of all past, present and future sign outs
function reports_sign_out() {
	global $print_screen;
	global $hrcolor;
	global $my_access_level;
	global $month_start;
	global $day_start;
	global $year_start;
	global $month_end;
	global $day_end;
	global $year_end;
	global $action;	
	global $temp;
	global $emp_db;
	
	if ($temp == "1") {
		$header_text = "Sign Out";
		$is_report = false;
	} else {
		$temp = "0";
		$header_text = "Transfer";
		$is_report = true;
	}

	// prints header
	menu_header("<a href='" . $PHP_SELF . "?action=reports' class='text10bold'>Reports</a>",$header_text . " Report","reports.jpg");

	// set start and end dates
	$start_date = mktime(0,0,0,$month_start,$day_start,$year_start);
	$end_date = mktime(23,59,59,$month_end,$day_end,$year_end);
	
	// verify given dates
	if (($month_start != "") && ($day_start != "") && ($year_start != "") && ($month_end != "") && ($day_end != "") && ($year_end != "")) {
		if ((($end_date - $start_date) < 0) || (!checkdate($month_start,$day_start,$year_start)) || (!checkdate($month_end,$day_end,$year_end))) {
			echo "<center><font class='text13bold' color='#ff0033'>Invalid Date Selection.</font></center>";
			$invalid_date = true;
		} else {
			$invalid_date = false;
		}
	} else {
		$invalid_date = false;
	}

	// date form
	echo "<table cellspacing=0 cellpadding=0 border=0><tr><td><form action='" . $PHP_SELF . "' method='get'></td></tr></table>";
	echo "<input type='hidden' name='action' value='reportssignout'>";
	echo "<input type='hidden' name='temp' value='" . $temp . "'>";
	echo "<table bgcolor='#eeffee' width=100% cellpadding=10 class='reportsborder'><tr><td><center><font class='text13'>";
	echo "<b>From </b>";
	$new_start_date = dateDropdown("start",$month_start,$day_start,$year_start,-1,0,true,false);
	if (($start_date == "-1") || ($start_date == "")) $start_date = $new_start_date;
	echo "<b> To </b>";
	$new_end_date = dateDropdown("end",$month_end,$day_end,$year_end,0,0,false,false);
	if (($end_date == "-1") || ($start_date == "")) $end_date = $new_end_date;
	echo "<input type='image' name='submit' src='images/go_white.jpg' width=20 height=20 border=0>";
	echo "</font></center></td></tr></table>";

	// Get data for either Transfers (Permanent) or Sign Outs (Temporary)
	if ($temp == 1) $sql = "SELECT Assets.AssetTag, Assets.AssetType, Assets.AssetSupplier, Assets.AssetModel, Assets.AssetSerial, Assets.AssetPrice, Assets.os, Assets.Id AS Assets_ID, Assignments.Id AS Assignments_ID, Assignments.EmployeeId AS Employees_ID, Assignments.StartDate, Assignments.EndDate, Assignments.Completed, " . $emp_db . "Employees.LastName, " . $emp_db . "Employees.FirstName, Assignments.Temp FROM ((Assignments LEFT JOIN Assets ON Assets.Id = Assignments.AssetId) LEFT JOIN " . $emp_db . "Employees ON Assignments.EmployeeId = " . $emp_db . "Employees.Id) WHERE Assignments.Approve=0 AND Assignments.Temp=1 AND ((Assignments.StartDate <= " . $end_date . ") AND (Assignments.EndDate >= " . $start_date . " OR Assignments.EndDate=0)) ORDER BY Assignments.StartDate;";
	else $sql = "SELECT Assets.AssetTag, Assets.AssetType, Assets.AssetSupplier, Assets.AssetModel, Assets.AssetSerial, Assets.AssetPrice, Assets.os, Assets.Id AS Assets_ID, Assignments.Id AS Assignments_ID, Assignments.EmployeeId AS Employees_ID, Assignments.StartDate, Assignments.EndDate, Assignments.Completed, " . $emp_db . "Employees.LastName, " . $emp_db . "Employees.FirstName, Assignments.Temp, Assignments_Prev.EmployeeId AS Employees_Prev_ID, Employees_Prev.LastName AS Employees_Prev_LastName, Employees_Prev.FirstName AS Employees_Prev_FirstName, Employees_Prev.EMail AS Employees_Prev_EMail, Employees_Prev.Building AS Employees_Prev_Building, Employees_Prev.Floor AS Employees_Prev_Floor, Employees_Prev.Workstation AS Employees_Prev_Workstation FROM ((((Assignments LEFT JOIN Assets ON Assets.Id = Assignments.AssetId) LEFT JOIN " . $emp_db . "Employees ON Assignments.EmployeeId = " . $emp_db . "Employees.Id) LEFT JOIN Assignments AS Assignments_Prev ON Assignments_Prev.EndDate = (Assignments.StartDate - 1)) LEFT JOIN " . $emp_db . "Employees AS Employees_Prev ON Assignments_Prev.EmployeeId = Employees_Prev.Id) WHERE Assignments.Approve=0 AND Assignments.Temp=0 AND (((Assignments.StartDate <= " . $end_date . ") AND (Assignments.StartDate >= " . $start_date . "))) ORDER BY Assignments.StartDate;";

	// print out results
	echo "<table cellspacing=0 cellpadding=0 border=0><tr><td></form></td></tr></table>";
	echo "<br><font class='text12'>";
	if (($result = doSql($sql)) && (mysql_num_rows($result))) {
		$color = "#eeffee";
		while ($query_data = mysql_fetch_array($result)) {
			$sql2 = "SELECT " . $emp_db . "Employees.LastName, " . $emp_db . "Employees.FirstName, " . $emp_db . "Employees.Tel, " . $emp_db . "Employees.Organization, " . $emp_db . "Employees.Dept, " . $emp_db . "Employees.Building, " . $emp_db . "Employees.Floor, " . $emp_db . "Employees.Workstation, Assignments.StartDate, Assignments.EndDate, Assignments.Completed, Assignments.Temp, Assignments.EmployeeID AS Employees_ID FROM Assignments LEFT JOIN " . $emp_db . "Employees ON Assignments.EmployeeID = " . $emp_db . "Employees.Id WHERE Assignments.Id=" . $query_data["Assignments_ID"] . " ORDER BY Assignments.StartDate;";
			if (($result2 = doSql($sql2)) && (mysql_num_rows($result2))) $query_data2 = mysql_fetch_array($result2);
			asset_format($query_data,$query_data2,$color,"reportsborder",$is_report);
			if ($color == "#ffffff") $color = "#eeffee";
			else $color = "#eeffee";
		}
	} else {
		if ($temp == 1) echo "<blockquote>There are no sign outs active during the specific dates.</blockquote>";
		else echo "<blockquote>There are no transfers taking place during the specified dates.</blockquote>";
	}
	echo "</font>";
}

// prints out the assets for one employee (linked with reports_assets())
function reports_assets_details($key, $full_name) {
	global $emp_db;
	$sql2 = "SELECT Assets.Id AS Assets_ID, Assets.AssetTag, Assets.AssetSupplier, Assets.AssetModel, Assets.AssetType, Assets.AssetSerial, Assets.AssetPrice, Assets.Notes, Assignments.StartDate FROM Assets INNER JOIN Assignments ON Assignments.AssetId = Assets.Id WHERE Assignments.EmployeeId =" . $key . " AND Temp=0 AND Approve=0 AND StartDate < " . time() . " AND (EndDate > " . time() . " OR EndDate=0) ORDER BY Assets.AssetType";
	if ($result2 = doSQL($sql2)) {
		if (mysql_num_rows($result2) > 0) echo "<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr>";
		$found_flag = 0;
		while ($query_data2 = mysql_fetch_array($result2)) {
			if ($query_data2["StartDate"] > "0") $date_text = date("d-M-Y", $query_data2["StartDate"]);
			else $date_text = "&nbsp;";
			if ($query_data2["AssetPrice"] > "0") $price_text = "$" . $query_data2["AssetPrice"] . ".00";
			else $price_text = "&nbsp;";

			$sql3 = "SELECT ip FROM IP WHERE assetid=" . $query_data2["Assets_ID"];
			if (($result3 = doSQL($sql3)) && (mysql_num_rows($result3) > 0) && ($query_data3 = mysql_fetch_array($result3)) ) {
				$ip_text = $query_data3["ip"];
			} else {
				$ip_text = "&nbsp;";
			}
			
			if ($found_flag == 1) echo "<tr><td>&nbsp;</td>";
			else echo "<tr><td><font face='arial' size=2><b>" . $full_name . "</b></font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["AssetType"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["AssetTag"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["AssetSerial"] . "&nbsp;</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["AssetSupplier"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["AssetModel"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $price_text . "</font></td>";