reports.php

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

			echo "<td><font face='arial' size=2>" . $date_text . "</font></td>";
			echo "<td><font face='arial' size=2>" . $ip_text . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["Notes"] . "</font></td>";
			echo "</tr>";

			$found_flag = 1;
		}
	}
}


// generate an excel report for all employees and their assets
function reports_assets() {
	global $download;
	global $emp_db;
	
	if ($download == 1) {
		header("Content-type: application/force-download: name=assets.csv");
		header("Content-Disposition: filename=assets.csv");
	} else {
		echo "<p><font class='text12bold' face='arial'><b><a href='" . $PHP_SELF . "?action=reportsassets&download=1'>Autoopen this report in Excel (HTML Format with a .CSV extension)</a> or save this file and import it to any speadsheet program.</b></font><p>";
	}
	
	echo "<table border=1>";
	echo "<tr>";
	echo "<td><font face='arial' size=2><b>Name</b></font></td>";
	echo "<td><font face='arial' size=2><b>Description</b></font></td>";
	echo "<td><font face='arial' size=2><b>Asset Tag</b></font></td>";
	echo "<td><font face='arial' size=2><b>Serial</b></font></td>";
	echo "<td><font face='arial' size=2><b>Supplier</b></font></td>";
	echo "<td><font face='arial' size=2><b>Model</b></font></td>";
	echo "<td><font face='arial' size=2><b>Purchase Price</b></font></td>";
	echo "<td><font face='arial' size=2><b>Install Date</b></font></td>";
	echo "<td><font face='arial' size=2><b>IP</b></font></td>";
	echo "<td><font face='arial' size=2><b>Notes</b></font></td>";
	echo "</tr>";

	// general assets
	reports_assets_details("0","General Assets");

	// surplus
	reports_assets_details("-1","Surplus");

	// all employees
	$sql = "SELECT id, LastName, FirstName FROM " . $emp_db . "Employees ORDER BY LastName";
	if ($result = doSQL($sql)) {
		while ($query_data = mysql_fetch_array($result)) {
			reports_assets_details($query_data["id"], $query_data["LastName"] . ", " . $query_data["FirstName"]);
		}
	}
	echo "</table>";

}


// prints out the licenses for one product (linked with reports_licenses_detailed()
function reports_licenses_detailed_details($key, $manufacturer) {
	$sql2 = "SELECT qty, product, paymentmethod, price, purchasedate, oem, expiredate, licensekey FROM Licenses WHERE product='" . addslashes($key) . "'";
	if ($result2 = doSQL($sql2)) {
		$found_flag = 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></tr>";
		while ($query_data2 = mysql_fetch_array($result2)) {
			if ($query_data2["purchasedate"] > "0") $date_text = date("d-M-Y", $query_data2["purchasedate"]);
			else $date_text = "";
			if ($query_data2["price"] > "0") $price_text = "$" . $query_data2["price"] . ".00";
			else $price_text = "";
			if ($query_data2["oem"] == "1") $oem_text = "Yes";
			else $oem_text = "No";
			if ($query_data2["expiredate"] > "0") $expdate_text = date("d-M-Y", $query_data2["expiredate"]);
			else $expdate_text = "";

			if ($found_flag == 1) echo "<tr><td>&nbsp;</td>";
			else echo "<tr><td><font face='arial' size=2><b>" . $manufacturer . " " . $key . "</b></font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["qty"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $date_text . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["purchasemethod"] . "&nbsp;</font></td>";
			echo "<td><font face='arial' size=2>" . $price_text . "</font></td>";
			echo "<td><font face='arial' size=2>" . $oem_text . "</font></td>";
			echo "<td><font face='arial' size=2>" . $expdate_text . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data2["licensekey"] . "&nbsp;</font></td>";
			echo "</tr>";

			$found_flag = 1;
		}
	}
}

// generate a summary excel report for all licenses
function reports_licenses_detailed() {
	global $download;
	
	if ($download == 1) {
		header("Content-type: application/force-download: name=licenses.csv");
		header("Content-Disposition: filename=licenses.csv");
	} else {
		echo "<p><font class='text12bold' face='arial'><b><a href='" . $PHP_SELF . "?action=reportslicensesdetailed&download=1'>Autoopen this report in Excel (HTML Format with a .CSV extension)</a> or save this file and import it to any speadsheet program.</b></font><p>";
	}
	
	echo "<table border=1>";
	echo "<tr>";
	echo "<td>&nbsp;</td>";
	echo "<td><font face='arial' size=2><b># of Copies</b></font></td>";
	echo "<td><font face='arial' size=2><b>Purchase Date</b></font></td>";
	echo "<td><font face='arial' size=2><b>Purchase Method</b></font></td>";
	echo "<td><font face='arial' size=2><b>Purchase Price</b></font></td>";
	echo "<td><font face='arial' size=2><b>OEM?</b></font></td>";
	echo "<td><font face='arial' size=2><b>Expiry Date</b></font></td>";
	echo "<td><font face='arial' size=2><b>License Key</b></font></td>";
	echo "</tr>";

	$sql = "SELECT manufacturer, product FROM Licenses GROUP BY product ORDER BY manufacturer, product";
	if ($result = doSQL($sql)) {
		while ($query_data = mysql_fetch_array($result)) {
			reports_licenses_detailed_details($query_data["product"], $query_data["manufacturer"]);
		}
	}
	echo "</table>";
}

// generate an detailed excel report for all licenses
function reports_licenses_summary() {
	global $download;
	
	if ($download == 1) {
		header("Content-type: application/force-download: name=licenses.csv");
		header("Content-Disposition: filename=licenses.csv");
	} else {
		echo "<p><font class='text12bold' face='arial'><b><a href='" . $PHP_SELF . "?action=reportslicensessummary&download=1'>Autoopen this report in Excel (HTML Format with a .CSV extension)</a> or save this file and import it to any speadsheet program.</b></font><p>";
	}
	
	echo "<table border=1>";
	echo "<tr>";
	echo "<td>&nbsp;</td>";
	echo "<td><font face='arial' size=2><b>Qty Used</b></font></td>";
	echo "<td><font face='arial' size=2><b>Qty Left</b></font></td>";
	echo "<td><font face='arial' size=2><b>Qty Total</b></font></td>";
	echo "</tr>";

	$sql = "SELECT manufacturer, product, SUM(qty) as sum_qty FROM Licenses GROUP BY product ORDER BY manufacturer, product";
	if ($result = doSQL($sql)) {
		while ($query_data = mysql_fetch_array($result)) {
			$sql2 = "SELECT count(assetid) as countid FROM LicenseOwners WHERE licenseid='" . $query_data["product"] . "' GROUP BY licenseid";
			if (($result2 = doSql($sql2)) && (mysql_num_rows($result2)) && ($query_data2 = mysql_fetch_array($result2))) {
				$used_license = $query_data2["countid"];
			} else {
				$used_license = 0;
			}
			$tot_license = $query_data["sum_qty"];
			$left_license = (($tot_license) - ($used_license));
		
			echo "<tr><td><font face='arial' size=2><b>" . $query_data["manufacturer"] . " " . $query_data["product"] . "</b></font></td>";
			echo "<td><font face='arial' size=2>" . $used_license . "</font></td>";
			echo "<td><font face='arial' size=2>" . $left_license . "</font></td>";
			echo "<td><font face='arial' size=2>" . $tot_license . "</font></td>";
			echo "</tr>";
		}
	}
	echo "</table>";
}


// generate an excel report for all employees and their assets
function reports_employees() {
	global $download;
	global $emp_db;
	
	if ($download == 1) {
		header("Content-type: application/force-download: name=employees.csv");
		header("Content-Disposition: filename=employees.csv");
	} else {
		echo "<p><font class='text12bold' face='arial'><b><a href='" . $PHP_SELF . "?action=reportsemployees&download=1'>Autoopen this report in Excel (HTML Format with a .CSV extension)</a> or save this file and import it to any speadsheet program.</b></font><p>";
	}
	
	echo "<table border=1>";
	echo "<tr>";
	echo "<td><font face='arial' size=2><b>Last Name</b></font></td>";
	echo "<td><font face='arial' size=2><b>First Name</b></font></td>";
	echo "<td><font face='arial' size=2><b>Login Name</b></font></td>";
	echo "<td><font face='arial' size=2><b>EMail</b></font></td>";
	echo "<td><font face='arial' size=2><b>Telephone</b></font></td>";
	echo "<td><font face='arial' size=2><b>Organization</b></font></td>";
	echo "<td><font face='arial' size=2><b>Department</b></font></td>";
	echo "<td><font face='arial' size=2><b>Building</b></font></td>";
	echo "<td><font face='arial' size=2><b>Floor</b></font></td>";
	echo "<td><font face='arial' size=2><b>Workstation</b></font></td>";
	echo "<td><font face='arial' size=2><b>Access Level</b></font></td>";
	echo "</tr>";

	// all employees
	$sql = "SELECT LastName, FirstName, LoginName, EMail, Tel, Organization, Dept, Building, Floor, Workstation, AccessLevel FROM " . $emp_db . "Employees ORDER BY LastName";
	if ($result = doSQL($sql)) {
		while ($query_data = mysql_fetch_array($result)) {
			if ($query_data["AccessLevel"] == "2") $accesslevel_text = "Admin";
			elseif ($query_data["AccessLevel"] == "2") $accesslevel_text = "User";
			else $accesslevel_text = "Non-User";
			echo "<tr>";
			echo "<td><font face='arial' size=2>" . $query_data["LastName"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["FirstName"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["LoginName"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["EMail"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["Tel"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["Organization"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["Dept"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["Building"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["Floor"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["Workstation"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $accesslevel_text . "</font></td>";
			echo "</tr>";
		}
	}
	echo "</table>";

}


// generate an excel report for all employees and their assets
function reports_individual($key) {
	global $download;
	global $emp_db;
	
	if ($download == 1) {
		header("Content-type: application/force-download: name=employees.csv");
		header("Content-Disposition: filename=employees.csv");
	} else {
		echo "<p><font class='text12bold' face='arial'><b><a href='" . $PHP_SELF . "?action=reportsindividual&key=" . $key . "&download=1'>Autoopen this report in Excel (HTML Format with a .CSV extension)</a> or save this file and import it to any speadsheet program.</b></font><p>";
	}
	

	switch ($key) {
		case "0":
			$full_name = "General Assets";
			break;
		case "-1":
			$full_name = "Surplus";
			break;
		case "-2":
			$full_name = "Retired";
			break;
		default:
			$sql = "SELECT " . $emp_db . "Employees.LastName, " . $emp_db . "Employees.FirstName FROM " . $emp_db . "Employees WHERE " . $emp_db . "Employees.Id=" . $key;
			if (($result = doSQL($sql)) && ($query_data = mysql_fetch_array($result))) {
				$full_name = $query_data["LastName"] . ", " . $query_data["FirstName"];
			}
	}
	echo "<table border=1>";
	echo "<tr>";
	echo "<td colspan=6><center><font face='arial' size=2><b>Asset Details for " . $full_name . "</b></font></center></td>";
	echo "</tr>";
	echo "<tr>";
	echo "<td><font face='arial' size=2><b>Date</b></font></td>";
	echo "<td><font face='arial' size=2><b>Asset Tag</b></font></td>";
	echo "<td><font face='arial' size=2><b>Type</b></font></td>";
	echo "<td><font face='arial' size=2><b>Supplier</b></font></td>";
	echo "<td><font face='arial' size=2><b>Model</b></font></td>";
	echo "<td><font face='arial' size=2><b>Serial</b></font></td>";
	echo "</tr>";

	// all employees
	$sql = "SELECT Assets.AssetTag, Assets.AssetModel, Assets.AssetSerial, Assets.AssetType, Assets.AssetSupplier, Assignments.StartDate FROM Assignments LEFT JOIN Assets ON Assignments.AssetId = Assets.Id WHERE Assignments.EmployeeId=" . $key . " ORDER BY Assignments.StartDate DESC, Assets.AssetType";
	if ($result = doSQL($sql)) {
		while ($query_data = mysql_fetch_array($result)) {
			if ($query_data["StartDate"] == "0") $startdate_out = "Unknown Date";
			else $startdate_out = date("M d, Y", $query_data["StartDate"]);
			echo "<tr>";
			echo "<td><font face='arial' size=2>" . $startdate_out . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["AssetTag"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["AssetType"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["AssetSupplier"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["AssetModel"] . "</font></td>";
			echo "<td><font face='arial' size=2>" . $query_data["AssetSerial"] . "</font></td>";
			echo "</tr>";
		}
	}
	echo "</table>";

}


?>