global $adb; global $current_user; $theme_path = "themes/" . $theme . "/"; $image_path = $theme_path . "images/"; $sGroup = "<GROUP RESET_SUPPRESS_ON_PAGEBREAK='TRUE' REPRINT_HEADER_ON_PAGEBREAK='TRUE'>" . "<HEADER>" . "<ROW><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>签约日期</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>客户</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>合同订单编号</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>订单金额</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>负责人</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>审批人</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>产品名称</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>产品类别</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>型号</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>产品编号</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>数量</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>单位</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>成本价</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>单价</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>售价</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>差价</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>金额</COL><COL CELLCLASS='HEADER' TEXTCLASS='BOLD'>利润</COL></ROW>" . "</HEADER>" . "<FIELDS>" . "<ROW>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>duedate</COL>" . "<COL TYPE='EXPRESSION' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'><LINK TYPE='EXPRESSION' TARGET='blank' TITLE='Click'>\"" . $site_URL . "getCustomerInfo.php?record=\".\$this->getValue('accountid')</LINK>\$this->getValue('accountname')</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>subject</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>total</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>user_name</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>appuser_name</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>productname</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>catalogname</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>serialno</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>productcode</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>quantity</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>usageunit</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>cost_price</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>unit_price</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>listprice</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD'>profit_price</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD' NUMBERFORMATEX='2'>producttotal</COL>" . "<COL TYPE='FIELD' CELLCLASSEVEN='EVEN' CELLCLASSODD='ODD' NUMBERFORMATEX='2'>profit</COL>" . "</ROW>" . "</FIELDS>" . "<FOOTER>" . "</FOOTER>" . "</GROUP>"; $sDoc = "<DOCUMENT>" . "<FOOTER>" . "<ROW>" . "<COL CELLCLASS='FOOTER' TEXTCLASS='BOLD' ALIGN='RIGHT' COLSPAN='14'></COL>" . "<COL CELLCLASS='FOOTER' TEXTCLASS='BOLD' ALIGN='RIGHT'>总金额</COL>" . "<COL TYPE='EXPRESSION' CELLCLASS='FOOTER' TEXTCLASS='BOLD' NUMBERFORMATEX='2'>\$this->getSum('producttotal')</COL>" . "<COL CELLCLASS='FOOTER' TEXTCLASS='BOLD' ALIGN='RIGHT'>总利润</COL>" . "<COL TYPE='EXPRESSION' CELLCLASS='FOOTER' TEXTCLASS='BOLD' NUMBERFORMATEX='2'>\$this->getSum('profit')</COL>" . "</ROW>" . "</FOOTER>" . "</DOCUMENT>"; echo '<HTML> <HEAD> <meta http-equiv="Content-Type" content="text/html; charset=utf8"> <TITLE>销售明细汇总</TITLE> <LINK REL="stylesheet" TYPE="text/css" HREF="include/phpreports/sales.css"> <link href="themes/images/report.css" rel="stylesheet" type="text/css"/></head>'; echo '<body BGCOLOR="#FFFFFF" marginheight="0" marginwidth="0" leftmargin="0" topmargin="0" style="text-align:center;" align="center">'; $query = "SELECT ec_account.accountname,ec_account.accountid,ec_salesorder.subject,ec_salesorder.duedate,ec_salesorder.total,ec_users.user_name,ec_salesorder.smownerid,ec_inventoryproductrel.listprice,ec_inventoryproductrel.quantity,ec_inventoryproductrel.listprice*ec_inventoryproductrel.quantity as producttotal,ec_products.productname,ec_products.productcode,ec_products.serialno,ec_products.usageunit,ec_products.unit_price,ec_products.cost_price,(ec_inventoryproductrel.listprice - ec_products.cost_price) AS profit_price,(ec_inventoryproductrel.listprice - ec_products.cost_price)*ec_inventoryproductrel.quantity as profit,ec_users2.user_name as appuser_name,ec_catalog.catalogname FROM ec_salesorder left join ec_users on ec_users.id=ec_salesorder.smownerid left join ec_account on ec_account.accountid=ec_salesorder.accountid left join ec_inventoryproductrel on ec_inventoryproductrel.id=ec_salesorder.salesorderid left join ec_products on ec_products.productid=ec_inventoryproductrel.productid left join ec_catalog on ec_catalog.catalogid=ec_products.catalogid left join ec_users ec_users2 on ec_users2.id=ec_salesorder.approvedby where ec_salesorder.deleted=0 and ec_salesorder.approved=1 "; $query = $listviewreport->addSecurityParameter($query); $query = $listviewreport->getModifiedListQuery($query); $query .= " ORDER BY ec_salesorder.duedate"; // echo $query; $oRpt = new PHPReportMaker(); $oRpt->setDatabaseInterface($dbconfig['db_type']); $oRpt->setDatabase($dbconfig['db_name']); $oRpt->setUser($dbconfig['db_username']); $oRpt->setPassword($dbconfig['db_password']); $oRpt->setConnection($dbconfig['db_hostname']); $oRpt->setSQL($query); $oRpt->setBody(false); $oRpt->setPageSize(50000); $oRpt->createFromTemplate("销售明细汇总", "include/phpreports/template.xml", null, $sDoc, $sGroup); $reportData = $oRpt->run(); echo '</body></html>'; }
protected function btnGenerate_Click() { $this->blnGenerate = true; // Expand the Asset object to include the AssetModel, Category, Manufacturer, and Location Objects $objExpansionMap[AssetTransaction::ExpandAsset][Asset::ExpandAssetModel][AssetModel::ExpandCategory] = true; $objExpansionMap[AssetTransaction::ExpandAsset][Asset::ExpandAssetModel][AssetModel::ExpandManufacturer] = true; $objExpansionMap[AssetTransaction::ExpandSourceLocation] = true; $objExpansionMap[AssetTransaction::ExpandDestinationLocation] = true; $objExpansionMap[AssetTransaction::ExpandTransaction][Transaction::ExpandTransactionType] = true; $objExpansionMap[AssetTransaction::ExpandTransaction][Transaction::ExpandCreatedByObject] = true; $objExpansionMap[AssetTransaction::ExpandTransaction][Transaction::ExpandModifiedByObject] = true; $arrTransactionTypes = array(); // Create an array of checked transaction types if ($this->chkMove->Checked) { $arrTransactionTypes[] = 1; } if ($this->chkCheckIn->Checked) { $arrTransactionTypes[] = 2; } if ($this->chkCheckOut->Checked) { $arrTransactionTypes[] = 3; } if ($this->chkReserve->Checked) { $arrTransactionTypes[] = 8; } if ($this->chkUnreserve->Checked) { $arrTransactionTypes[] = 9; } // Archived assets will be included in the Asset Transaction Report //$arrTransactionTypes[] = 10; // If checked at least one transaction type if (count($arrTransactionTypes)) { $this->lblReport->Warning = ""; // Total Transactions Count $intTotalTransactionCount = AssetTransaction::CountTransactionsBySearch($this->txtShortDescription->Text, $this->txtAssetCode->Text, $this->txtAssetModelCode->Text, $this->lstUser->SelectedValue, $this->lstCheckedOutBy->SelectedValue, $this->lstReservedBy->SelectedValue, $this->lstCategory->SelectedValue, $this->lstManufacturer->SelectedValue, $this->lstTransactionDate->SelectedValue, $this->dtpTransactionDateFirst->DateTime, $this->dtpTransactionDateLast->DateTime, $arrTransactionTypes, $objExpansionMap); // Total Transactions Count > 0 to avoid bug with NoDataMsg if ($intTotalTransactionCount) { // begins the report process $oRpt = new PHPReportMaker(); // Create the constant to use in xml template $oRpt->putEnvObj("TotalTransactions", $intTotalTransactionCount); //some data to show in the report $sSql = AssetTransaction::LoadArrayBySearch(true, $this->txtShortDescription->Text, $this->txtAssetCode->Text, $this->txtAssetModelCode->Text, $this->lstUser->SelectedValue, $this->lstCheckedOutBy->SelectedValue, $this->lstReservedBy->SelectedValue, $this->lstCategory->SelectedValue, $this->lstManufacturer->SelectedValue, $this->lstSortByDate->SelectedValue, $this->lstTransactionDate->SelectedValue, $this->dtpTransactionDateFirst->DateTime, $this->dtpTransactionDateLast->DateTime, $arrTransactionTypes, $objExpansionMap); $strXmlColNameByCustomField = ""; $strXmlFieldByCustomField = ""; $intCustomFieldCount = 0; if ($this->chkCustomFieldArray) { 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>"; $intCustomFieldCount++; } } } $oGroups = "\n <GROUP NAME='transaction_id' EXPRESSION='transaction_id' PAGEBREAK='FALSE'>\n <HEADER>\n <ROW>\n <COL ALIGN='LEFT' TYPE='EXPRESSION' COLSPAN='" . (4 + $intCustomFieldCount) . "' CELLCLASS='report_section_heading'>\$this->getValue('asset_transaction__transaction_id__transaction_type_id__short_description').' by '.(\$this->getValue('asset_transaction__transaction_id__modified_by')?\$this->getValue('asset_transaction__transaction_id__modified_by__first_name').' '.\$this->getValue('asset_transaction__transaction_id__modified_by__last_name').' on '.\$this->getValue('asset_transaction__transaction_id__modified_date'):\$this->getValue('asset_transaction__transaction_id__created_by__first_name').' '.\$this->getValue('asset_transaction__transaction_id__created_by__last_name').' on '.\$this->getValue('asset_transaction__transaction_id__creation_date'))</COL>\n </ROW>\n\t\t\t\t<ROW>\n <COL ALIGN='LEFT' TYPE='EXPRESSION' COLSPAN='" . (4 + $intCustomFieldCount) . "' CELLCLASS='report_cell'>\$this->getValue('asset_transaction__transaction_id__note')</COL>\n </ROW>\n <ROW>\n <COL CELLCLASS='report_column_header'>Asset Code:</COL>\n <COL CELLCLASS='report_column_header'>Asset Model:</COL>\n <COL CELLCLASS='report_column_header'>From:</COL>\n <COL CELLCLASS='report_column_header'>To:</COL>\n {$strXmlColNameByCustomField}\n </ROW>\n </HEADER>\n <FIELDS>\n <ROW>\n <COL TYPE='FIELD' CELLCLASS='report_cell'><LINK TYPE='EXPRESSION'>'" . __SUBDIRECTORY__ . "/assets/asset_edit.php?intAssetId='.\$this->getValue('asset_transaction__asset_id__asset_id')</LINK>asset_transaction__asset_id__asset_code</COL>\n <COL TYPE='FIELD' CELLCLASS='report_cell'><LINK TYPE='EXPRESSION'>'" . __SUBDIRECTORY__ . "/assets/asset_model_edit.php?intAssetModelId='.\$this->getValue('asset_transaction__asset_id__asset_model_id__asset_model_id')</LINK>asset_transaction__asset_id__asset_model_id__short_description</COL>\n <COL TYPE='FIELD' CELLCLASS='report_cell'>asset_transaction__source_location_id__short_description</COL>\n <COL TYPE='FIELD' CELLCLASS='report_cell'>asset_transaction__destination_location_id__short_description</COL>\n {$strXmlFieldByCustomField}\n </ROW>\n </FIELDS>\n </GROUP>"; $arrDBInfo = unserialize(DB_CONNECTION_1); $oRpt->setSQL($sSql); $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); if ($this->lstGenerateOptions->SelectedValue == "print") { // Start the output buffer ob_start(); $this->lblReport->Text = ""; $oDocs = "<CSS>../css/tracmor.css</CSS>"; $fOut = fopen(".." . __TRACMOR_TMP__ . "/" . $_SESSION['intUserAccountId'] . "_asset_transaction_report.htm", "w"); $oRpt->createFromTemplate('Asset Transaction Report', __DOCROOT__ . __SUBDIRECTORY__ . '/reports/asset_transaction_report.xml', null, $oDocs, $oGroups); $oRpt->run(); fwrite($fOut, ob_get_contents()); ob_end_clean(); fclose($fOut); // Open generated Report in new window QApplication::ExecuteJavaScript("window.open('.." . __TRACMOR_TMP__ . "/" . $_SESSION['intUserAccountId'] . "_asset_transaction_report.htm','AssetTransactionReport','resizeable=yes,menubar=yes,scrollbars=yes,left=0,top=0,width=800,height=600');history.go(-1);"); exit; } else { if ($this->lstGenerateOptions->SelectedValue == "csv") { $this->RenderCsvBegin(false); session_cache_limiter('must-revalidate'); // force a "no cache" effect header("Pragma: hack"); // IE chokes on "no cache", so set to something, anything, else. $ExpStr = "Expires: " . gmdate("D, d M Y H:i:s", time()) . " GMT"; header($ExpStr); header('Content-Type: text/csv'); header('Content-Disposition: csv; filename=export.csv'); ob_end_clean(); $oRpt->createFromTemplate('Asset Transaction Report', __DOCROOT__ . __SUBDIRECTORY__ . '/reports/asset_transaction_report.xml', null, null, $oGroups); $oOut = $oRpt->createOutputPlugin("csv"); $oRpt->setOutputPlugin($oOut); $oRpt->run(); ob_get_contents(); @ob_flush(); flush(); $this->RenderCsvEnd(false); exit; } else { // Start the output buffer ob_start(); // The head of the final html will be write by the Qform $oRpt->setBody(false); $oRpt->createFromTemplate('Asset Transaction Report', __DOCROOT__ . __SUBDIRECTORY__ . '/reports/asset_transaction_report.xml', null, null, $oGroups); $oRpt->run(); // Put the output buffer content in the Qlabel $this->lblReport->Text = ob_get_contents(); // Clean the output buffer ob_end_clean(); } } // Begin rendering the QForm //$this->RenderBegin(false); //ob_end_clean(); // Process the report //$oOut = $oRpt->createOutputPlugin("csv"); //$oRpt->setOutputPlugin($oOut); //$this->RenderEnd(false); //exit(); } else { $this->lblReport->Text = ""; $this->lblReport->Warning = "No data was found, check your query."; } } else { $this->lblReport->Text = ""; $this->lblReport->Warning = "You must check at least one transaction type."; } $this->blnGenerate = false; }