// Add the custom field columns with Display set to false. These can be shown by using the column toggle menu.
$objCustomFieldArray = CustomField::LoadObjCustomFieldArray(1, false);
if ($objCustomFieldArray) {
foreach ($objCustomFieldArray as $objCustomField) {
//Only add the custom field column if the role has authorization to view it.
if($objCustomField->objRoleAuthView && $objCustomField->objRoleAuthView->AuthorizedFlag){
$this->dtgChildAssets->AddColumn(new QDataGridColumnExt($objCustomField->ShortDescription, '<?= $_ITEM->GetVirtualAttribute(\''.$objCustomField->CustomFieldId.'\') ?>', 'SortByCommand="__'.$objCustomField->CustomFieldId.' ASC"', 'ReverseSortByCommand="__'.$objCustomField->CustomFieldId.' DESC"','HtmlEntities="false"', 'CssClass="dtg_column"'));
}
}
}It included the custom fields in the header of the datagrid, but now data is inserted in the table.
End Edit......
If anyone plan to have a look at this, here is what I have so far.
To make this report a little more useful to others as well, I will try to add some checkboxes so that it is selectable what information gets included in the report (I realize that my particular scenario is not very useful to others).
Index: data_model/db_update-0.2.0.sql
===================================================================
--- data_model/db_update-0.2.0.sql (revision 646)
+++ data_model/db_update-0.2.0.sql (working copy)
@@ -197,6 +197,7 @@
ON Delete CASCADE ON Update NO ACTION;
INSERT INTO `shortcut` (`module_id`, `authorization_id`, `short_description`, `image_path`, `link`, `entity_qtype_id`, `create_flag`) VALUES (7, 1, 'Asset Transaction Report', 'asset.png','../reports/asset_transaction_report.php', 1, 0);
+INSERT INTO `shortcut` (`module_id`, `authorization_id`, `short_description`, `image_path`, `link`, `entity_qtype_id`, `create_flag`) VALUES (7, 1, 'Asset Child Report', 'asset.png','../reports/asset_child_report.php', 1, 0);
ALTER TABLE `asset`
ADD `parent_asset_id` INTEGER UNSIGNED DEFAULT NULL AFTER `asset_id`,
Index: reports/asset_child_report.tpl.php
===================================================================
--- reports/asset_child_report.tpl.php (revision 0)
+++ reports/asset_child_report.tpl.php (revision 0)
@@ -0,0 +1,71 @@
+<?php
+/*
+ * Copyright (c) 2009, Tracmor, LLC
+ *
+ * This file is part of Tracmor.
+ *
+ * Tracmor 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.
+ *
+ * Tracmor 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 Tracmor; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+ */
+
+ include('../includes/header.inc.php');
+ $this->RenderBegin();
+?>
+<!-- Begin Header Menu -->
+<?php
+ $this->ctlHeaderMenu->Render();
+?>
+<!-- End Header Menu -->
+
+<!-- Begin Shortcut Menu -->
+<?php
+ $this->ctlShortcutMenu->Render();
+?>
+<!-- End Shortcut Menu -->
+ </td>
+ <td><img src="../images/empty.gif" width="10"></td>
+ <td width="100%" valign="top">
+ <div class="title"> Asset Child Report</div>
+ <table style="border:1px solid #AAAAAA;background-color:#EEEEEE;font-family:verdana;font-size:10;color:#444444;height:40" width="100%">
+ <tr>
+ <td class="item_label">Parent Asset:</td>
+ <td colspan="5"><?php $this->txtParentAsset->Render("Width=100") ?><?php $this->lblIconParentAssetCode->Render() ?></td>
+ </tr>
+ <tr>
+ <td class="item_label">Asset Property:</span></td>
+ <td><?php $this->chkCode->Render(); ?></td>
+ <td><?php $this->chkModel->Render(); ?></td>
+ <td><?php $this->chkCategory->Render(); ?></td>
+ <td><?php $this->chkManufacturer->Render(); ?></td>
+ <td><?php $this->chkLocation->Render(); ?></td>
+ </tr>
+ <tr>
+ <td class="item_label">Custom Fields:</span></td>
+ <td colspan="5"><?php $this->pnlCustomFields->Render(); ?></td>
+ </tr>
+ <tr>
+ <td class="item_label">Create Report:</span></td>
+ <td colspan="5"><?php $this->lstGenerateOptions->Render("Width=100"); ?><?php $this->btnGenerate->Render() ?><?php $this->btnClear->Render() ?></td>
+ </tr>
+ </table>
+ <?php
+ if ($this->ctlAssetSearchTool) {
+ $this->ctlAssetSearchTool->Render();
+ }
+ ?>
+
+ <?php $this->lblReport->RenderWithError(); ?>
+ <br />
+<?php $this->RenderEnd() ?>
+<?php require_once('../includes/footer.inc.php'); ?>
Index: reports/asset_child_report.php
===================================================================
--- reports/asset_child_report.php (revision 0)
+++ reports/asset_child_report.php (revision 0)
@@ -0,0 +1,307 @@
+<?php
+/*
+ * Copyright (c) 2009, Tracmor, LLC
+ *
+ * This file is part of Tracmor.
+ *
+ * Tracmor 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.
+ *
+ * Tracmor 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 Tracmor; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+ */
+
+ require_once('../includes/prepend.inc.php');
+ QApplication::Authenticate(7);
+ ini_set("include_path",ini_get("include_path"). PATH_SEPARATOR . __INCLUDES__ . "/php/PHPReports/" . PATH_SEPARATOR);
+ require_once('PHPReportMaker.php');
+ $_SESSION["phpReportsLanguage"] = null;
+
+ class SerialNumberListForm extends QForm {
+
+ // Header Tabs
+ protected $ctlHeaderMenu;
+
+ // Shortcut Menu
+ protected $ctlShortcutMenu;
+
+ protected $ctlAssetSearchTool;
+
+ // Labels
+ protected $lblIconParentAssetCode;
+
+ // Basic Inputs
+ protected $txtParentAsset;
+ protected $lstGenerateOptions;
+ protected $chkCode;
+ protected $chkModel;
+ protected $chkCategory;
+ protected $chkManufacturer;
+ protected $chkLocation;
+ protected $pnlCustomFields;
+ protected $arrCustomFields;
+ protected $chkCustomFieldArray;
+ protected $lblReport;
+
+ // Buttons
+ protected $btnGenerate;
+ protected $blnGenerate;
+ protected $btnClear;
+
+ protected function Form_Create() {
+
+ $this->ctlHeaderMenu_Create();
+ $this->ctlShortcutMenu_Create();
+ $this->txtParentAsset_Create();
+ $this->chkAssetProperties_Create();
+ $this->lstGenerateOptions_Create();
+ $this->btnGenerate_Create();
+ $this->btnClear_Create();
+ $this->lblIconParentAssetCode_Create();
+ $this->ctlAssetSearchTool_Create();
+ $this->customFields_Create();
+
+ // The report code will be render in a Qlabel
+ $this->lblReport = new QLabel($this);
+ // If don't put this you will see HTML code instead of a report
+ $this->lblReport->HtmlEntities = false;
+ }
+
+
+ // Create and Setup the Header Composite Control
+ protected function ctlHeaderMenu_Create() {
+ $this->ctlHeaderMenu = new QHeaderMenu($this);
+ }
+
+ // Create and Setp the Shortcut Menu Composite Control
+ protected function ctlShortcutMenu_Create() {
+ $this->ctlShortcutMenu = new QShortcutMenu($this);
+ }
+
+ protected function txtParentAsset_Create() {
+ $this->txtParentAsset = new QTextBox($this);
+ $this->txtParentAsset->Name = 'Parent Asset';
+ $this->txtParentAsset->AddAction(new QEnterKeyEvent(), new QServerAction('btnGenerate_Click'));
+ $this->txtParentAsset->AddAction(new QEnterKeyEvent(), new QTerminateAction());
+ }
+
+ protected function lstGenerateOptions_Create() {
+ $this->lstGenerateOptions = new QListBox($this);
+ $this->lstGenerateOptions->AddItem('Report', null);
+ $this->lstGenerateOptions->AddItem('Print View', 'print');
+ $this->lstGenerateOptions->AddItem('CSV Export', 'csv');
+ }
+
+ // Uncheck all items but SelectAll checkbox
+ public function UncheckAllItems() {
+ foreach ($this->GetAllControls() as $objControl) {
+ if (substr($objControl->ControlId, 0, 11) == 'chkSelected') {
+ $objControl->Checked = false;
+ }
+ }
+ }
+
+ protected function chkAssetProperties_Create() {
+ $this->chkCode = new QCheckBox($this);
+ $this->chkCode->Text = 'Code';
+ $this->chkCode->Checked = false;
+ $this->chkModel = new QCheckBox($this);
+ $this->chkModel->Text = 'Model';
+ $this->chkModel->Checked = false;
+ $this->chkCategory = new QCheckBox($this);
+ $this->chkCategory->Text = 'Category';
+ $this->chkCategory->Checked = false;
+ $this->chkManufacturer = new QCheckBox($this);
+ $this->chkManufacturer->Text = 'Manufacturer';
+ $this->chkManufacturer->Checked = false;
+ $this->chkLocation = new QCheckBox($this);
+ $this->chkLocation->Text = 'Location';
+ $this->chkLocation->Checked = false;
+ }
+
+ protected function customFields_Create() {
+ // Create QPanel with AutoRenderChildren to add cutom fields dynamically
+ $this->pnlCustomFields = new QPanel($this);
+ $this->pnlCustomFields->AutoRenderChildren = true;
+ // Load all custom fields and their values into an array objCustomFieldArray->CustomFieldSelection->CustomFieldValue
+ $this->arrCustomFields = CustomField::LoadObjCustomFieldArray(1, false, null);
+ $i = 0;
+ foreach ($this->arrCustomFields as $objCustomField) {
+ $this->chkCustomFieldArray[$i] = new QCheckBox($this->pnlCustomFields);
+ $this->chkCustomFieldArray[$i]->Text = $objCustomField->ShortDescription;
+ $this->chkCustomFieldArray[$i]->ActionParameter = $objCustomField->CustomFieldId;
+ $i++;
+ }
+ }
+
+
+ /**************************
+ * CREATE BUTTON METHODS
+ **************************/
+
+ protected function btnGenerate_Create() {
+ $this->btnGenerate = new QButton($this);
+ $this->btnGenerate->Name = 'Generate';
+ $this->btnGenerate->Text = 'Generate';
+ $this->btnGenerate->AddAction(new QClickEvent(), new QServerAction('btnGenerate_Click'));
+ $this->btnGenerate->AddAction(new QEnterKeyEvent(), new QServerAction('btnGenerate_Click'));
+ $this->btnGenerate->AddAction(new QEnterKeyEvent(), new QTerminateAction());
+ }
+
+ protected function btnClear_Create() {
+ $this->btnClear = new QButton($this);
+ $this->btnClear->Name = 'clear';
+ $this->btnClear->Text = 'Clear';
+ $this->btnClear->AddAction(new QClickEvent(), new QServerAction('btnClear_Click'));
+ $this->btnClear->AddAction(new QEnterKeyEvent(), new QServerAction('btnClear_Click'));
+ $this->btnClear->AddAction(new QEnterKeyEvent(), new QTerminateAction());
+ }
+
+ // Create the clickable label
+ protected function lblIconParentAssetCode_Create() {
+ $this->lblIconParentAssetCode = new QLabel($this);
+ $this->lblIconParentAssetCode->HtmlEntities = false;
+ $this->lblIconParentAssetCode->Display = true;
+ $this->lblIconParentAssetCode->Text = '<img src="../images/icons/lookup.png" border="0" style="cursor:pointer;">';
+ $this->lblIconParentAssetCode->AddAction(new QClickEvent(), new QAjaxAction('lblIconParentAssetCode_Click'));
+ $this->lblIconParentAssetCode->AddAction(new QEnterKeyEvent(), new QAjaxAction('lblIconParentAssetCode_Click'));
+ $this->lblIconParentAssetCode->AddAction(new QEnterKeyEvent(), new QTerminateAction());
+ }
+
+ protected function ctlAssetSearchTool_Create() {
+ $this->ctlAssetSearchTool = new QAssetSearchToolComposite($this);
+ }
+
+ protected function btnClear_Click() {
+ // Reload the page fresh.
+ QApplication::Redirect('asset_child_report.php');
+ }
+
+ public function lblIconParentAssetCode_Click() {
+ // Uncheck all items but SelectAll checkbox
+ $this->UncheckAllItems();
+ $this->ctlAssetSearchTool->Refresh();
+ $this->ctlAssetSearchTool->btnAssetSearchToolAdd->Text = "Add Parent Asset";
+ $this->ctlAssetSearchTool->dlgAssetSearchTool->ShowDialogBox();
+ }
+
+
+ public function btnAssetSearchToolAdd_Click() {
+ $this->ctlAssetSearchTool->lblWarning->Text = "";
+ $intSelectedAssetId = $this->ctlAssetSearchTool->ctlAssetSearch->dtgAsset->GetSelected("AssetId");
+ if (count($intSelectedAssetId) > 1) {
+ $this->ctlAssetSearchTool->lblWarning->Text = "You must select only one asset.";
+ } elseif (count($intSelectedAssetId) != 1) {
+ $this->ctlAssetSearchTool->lblWarning->Text = "No selected assets.";
+ } else {
+ if (!($objParentAsset = Asset::LoadByAssetId($intSelectedAssetId[0]))) {
+ $this->ctlAssetSearchTool->lblWarning->Text = "That asset code does not exist. Please try another.";
+ } else {
+ $this->txtParentAsset->Text = $objParentAsset->AssetCode;
+ $this->ctlAssetSearchTool->dlgAssetSearchTool->HideDialogBox();
+ }
+ }
+ // Uncheck all items but SelectAll checkbox
+ $this->UncheckAllItems();
+ }
+
+ protected function btnGenerate_Click($objCaller = null) {
+ $objExpansionMap[Asset::ExpandAssetModel][AssetModel::ExpandCategory] = true;
+ $objExpansionMap[Asset::ExpandAssetModel][AssetModel::ExpandManufacturer] = true;
+ $objExpansionMap[Asset::ExpandLocation] = true;
+
+ $objAsset = Asset::LoadByAssetCode($this->txtParentAsset->Text);
+ if ($objAsset) {
+ $oRpt = new PHPReportMaker();
+ $objChildAssetArray = Asset::LoadChildLinkedArrayByParentAssetId($objAsset->AssetId);
+ if ($objChildAssetArray) {
+ $strXmlColNameByCustomField = "";
+ $strXmlFieldByCustomField = "";
+ if ($this->chkCode->Checked) {
+ $strXmlColNameByCustomField .= "<COL CELLCLASS='report_column_header'>".$this->chkCode->Text."</COL>";
+ $strXmlFieldByCustomField .= "<COL TYPE='FIELD' CELLCLASS='report_cell'>asset__asset_code</COL>";
+ }
+ if ($this->chkModel->Checked) {
+ $strXmlColNameByCustomField .= "<COL CELLCLASS='report_column_header'>".$this->chkModel->Text."</COL>";
+ $strXmlFieldByCustomField .= "<COL TYPE='FIELD' CELLCLASS='report_cell'>asset__asset_model_id__short_description</COL>";
+ }
+ if ($this->chkCategory->Checked) {
+ $strXmlColNameByCustomField .= "<COL CELLCLASS='report_column_header'>".$this->chkCategory->Text."</COL>";
+ $strXmlFieldByCustomField .= "<COL TYPE='FIELD' CELLCLASS='report_cell'>asset__asset_model_id__category_id__short_description</COL>";
+ }
+ if ($this->chkManufacturer->Checked) {
+ $strXmlColNameByCustomField .= "<COL CELLCLASS='report_column_header'>".$this->chkManufacturer->Text."</COL>";
+ $strXmlFieldByCustomField .= "<COL TYPE='FIELD' CELLCLASS='report_cell'>asset__asset_model_id__manufacturer_id__short_description</COL>";
+ }
+ if ($this->chkLocation->Checked) {
+ $strXmlColNameByCustomField .= "<COL CELLCLASS='report_column_header'>".$this->chkLocation->Text."</COL>";
+ $strXmlFieldByCustomField .= "<COL TYPE='FIELD' CELLCLASS='report_cell'>asset__location_id__short_description</COL>";
+ }
+ foreach ($this->chkCustomFieldArray as $chkCustomField) {
+ if ($chkCustomField->Checked) {
+ $strXmlColNameByCustomField .= "<COL CELLCLASS='report_column_header'>".$chkCustomField->Text."</COL>";
+ $strXmlFieldByCustomField .= "<COL TYPE='FIELD' CELLCLASS='report_cell'>__".$chkCustomField->ActionParameter."</COL>";
+ }
+ }
+ $oGroups = "
+ <GROUP NAME='child_id' EXPRESSION='child_id' PAGEBREAK='FALSE'>
+ <HEADER>
+ <ROW>
+ $strXmlColNameByCustomField
+ </ROW>
+ </HEADER>
+ <FIELDS>
+ <ROW>
+ $strXmlFieldByCustomField
+ </ROW>
+ </FIELDS>
+ </GROUP>";
+
+ $arrDBInfo = unserialize(DB_CONNECTION_1);
+ $oRpt->setSQL($objAsset);
+ $oRpt->setUser($arrDBInfo['username']);
+ $oRpt->setPassword($arrDBInfo['password']);
+ $oRpt->setConnection($arrDBInfo['server']);
+ $oRpt->setDatabaseInterface('mysql');
+ $oRpt->setDatabase($arrDBInfo['database']);
+ $oRpt->setNoDataMsg("No data was found, check your query");
+ $oRpt->setPageSize(200000000);
+
+ $oRpt->setBody(false);
+ $oRpt->createFromTemplate('Asset Child Report', __DOCROOT__ . __SUBDIRECTORY__ . '/reports/asset_child_report.xml',null,null,$oGroups);
+
+ /* TODO
+ $oRpt->run();
+ */
+
+ // Start Debug
+ $str = "";
+ $i=1;
+ foreach ($objChildAssetArray as $objChild) {
+ $str = $str . $objChild->__toString() . "; ";
+ $i++;
+ }
+ $this->lblReport->Warning = $str;
+ // End Debug
+ } else {
+ $this->lblReport->Warning = "Asset has no child assets.";
+ }
+ } else {
+ $this->lblReport->Warning = "Not a valid asset!";
+ }
+ }
+
+ }
+
+ // Go ahead and run this form object to generate the page
+ SerialNumberListForm::Run('SerialNumberListForm', 'asset_child_report.tpl.php');
+?>
+
Index: reports/asset_child_report.xml
===================================================================
--- reports/asset_child_report.xml (revision 0)
+++ reports/asset_child_report.xml (revision 0)
@@ -0,0 +1,29 @@
+<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
+<!DOCTYPE REPORT SYSTEM "PHPReport.dtd">
+<REPORT MARGINWIDTH="5" MARGINHEIGHT="5">
+ <TITLE><REPLACE_WITH_TITLE/></TITLE>
+ <BACKGROUND_COLOR>#FFFFFF</BACKGROUND_COLOR>
+ <NO_DATA_MSG>No data was found, check your query</NO_DATA_MSG>
+ <REPLACE_WITH_DOCUMENT_INFO/>
+ <PAGE BORDER="0" CELLSPACING="0" CELLPADDING="3">
+ <HEADER>
+ <ROW>
+ <COL CELLCLASS="HEADER" COLSPAN="50">
+ <XHTML>
+ <TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0">
+ <TR>
+ <TD CLASS="report_title" colspan="4">
+ Asset Child Report
+ </TD>
+ </TR>
+ </TABLE>
+ </XHTML>
+ </COL>
+ </ROW>
+ <REPLACE_WITH_PARAMETERS/>
+ </HEADER>
+ </PAGE>
+ <GROUPS>
+ <REPLACE_WITH_GROUP_INFO/>
+ </GROUPS>
+</REPORT>Edited:
- Added my latest changes to the source.
- Added dynamically output based on which checkbox is checked.
- What remains are to generate a proper SQL statement. If somebody can help with that it would be great. I have never done any SQL before.
I made an Asset Custom Field called "Serial Number". Now I need to be able to print a report with the Asset Model and Serial Number for all child items for any given asset. Would that be possible?
]]>