OpenPro PHP Report Writer Module

<wrap leftalign>[[support:start|Back]]</wrap>

=====How To Use Openpro To Create A Php Report=====
OpenPro has the ability to create custom customer reports. This feature is excellent for consultants and administrators who need to provide custom reports. The process of setting up new customer reports can be outlined as follows:

  * Login to the system under administrator, Developement Report Template.
  * Add new report.
  * Fill out header information, add header
  * Fill out table information, add table.
  * Fill out line item detail information, add fields.
  * Compile report
  * Add report to the menu system.
  * Advance items, select records, how to change the reports, merge tables etc.

The tutorial that follows will walk you through the process step-by-step. If you do not have an OpenPro installation in your business, you are invited to follow this tutorial using our OpenPro live demo site on the Internet. If you have a log in account, please go to the demo site and log in before proceeding. If you do not have a demo account, please contact OpenPro and we will set one up for you.

Login to the system under administrator, go to Developement then Report Template.

{{support:php-report-writer:image-001.png}}

Click on the blue Add new Report button, or if you wanted to edit an existing report you can select the existing report to edit.

The next screen will be the header information. Reports are broken up into sections, you have header section level 0, detail section level 1, totals are level 2 and 3. Fill out the report name, title, restrict to company should be checked, bold your title, center your title, change your title color. In the bottom section of the page check show date and page numbers and select **ADD**.

{{support:php-report-writer:image-002.png}}

Table information needs to be added to let the report writer know where to get the fields from. The tables can also be linked together.

{{support:php-report-writer:image-003.png}}

Select the ''IC_ITEMS'' table to display the part number and name and price 1 fields. Click the **ADD** button.

{{support:php-report-writer:image-004.png}}

Fill out the table alias and report level 1 and click **ADD**.

{{support:php-report-writer:image-005.png}}

Then you receive the message data table successfully edited. To add the fields you want to see on the report, select the data table next to EDIT button, select ''IC_ITEMS'' and click **EDIT**.

{{support:php-report-writer:image-006.png}}

When you edit the table all the fields will then be added to the report.

{{support:php-report-writer:image-007.png}}

To the right of the screen select the fields you want to show up on the report, ''partnum'', ''name'' and ''price_1''. Select the field then click **ADD**, the*n next screen will popup.

{{support:php-report-writer:image-008.png}}

This page is the detailed line item field information. If you want to just take the default size color fonts etc, just click the **ADD** button. Select each field you want to see on the screen, they will show up on the order that you select them in from left to right.

{{support:php-report-writer:image-009.png}}

After you have added the fields to see the field in the EDIT button you will have to click **SAVE** and then edit the table again or refresh the screen.

{{support:php-report-writer:image-010.png}}

When all the fields are added to the report you then can compile the php code for the report. On the top right hand side, fill out the file name and click on compile report.

{{support:php-report-writer:image-011.png}}

Note the name can not have any special characters, or spaces in it and needs to be 10 characters or less.

Then you receive the message php report file successfully created.

{{support:php-report-writer:image-012.png}}

====Add the new report to the menu====
Go into menu edit mode under Admin.

{{support:php-report-writer:image-013.png}}

Copy an existing menu item (green button), then edit the copied version (blue button). In the script you need to put in the following:

''index.php?act=item55&rptfolder=true''

where ''item55'' is the name you gave the report when you compiled it.

{{support:php-report-writer:image-014.png}}

When you run the report from the menu the following displays. If you put selection criteria it will display on this page. Then click make report.

{{support:php-report-writer:image-015.png}}

Then the following report appears. To make changes to the report go into edit mode, or even bring up the php source code and make the changes manually. If you change the source code using php commands, the next time you compile this report it will overwrite all changes made to the system.

{{support:php-report-writer:image-016.png}}

Selecting certain records when creating a report. Then go back and edit the report.

{{support:php-report-writer:image-017.png}}

Pull up your Data table and click **EDIT** the screen below will appear.

{{support:php-report-writer:image-018.png}}

Then in the selection criteria, you can select certain fields to limit your report searching.

So then before you run the report it will ask for selection criteria.

{{support:php-report-writer:image-019.png}}

Click **Save**, re-compile the report as item 55 and run it again.

Now when running the report, it ask for the selection criteria, you can enter in something it will find all the records with that something in there.

{{support:php-report-writer:image-020.png}}

Below is an example all the records with ''fg'' in the part number.

{{support:php-report-writer:image-021.png}}

You can have up to 4 fields to search for during the report process.

{{support:php-report-writer:image-022.png}}

During the selection process, you can have the following choices, Greater than, Equal, Less Than, Match% (the beginning of a field), and %Match% will be any thing in that field with this information.

Match examples:

  * Match% will pick up all parts start with FG.
   Match% will pick up all parts with FG anywhere in the part number.

When running the report it will ask for the multiple questions.

{{support:php-report-writer:image-023.png}}

====How to link multiple tables together====
To do this you have to know the basics of how to link tables to create a report.

  - select the table in value tables (ic inventory) for this example.
  - Click the **ADD** button.
  - Select the Table, **EDIT** the new table and fill out the Table Alias, and the Report level (same as the first table). Table Alias needs to be different than the first table, then click the **ADD** button.

{{support:php-report-writer:image-023.png}}

Then go to table links. This is where you can link table 1 to table 2. In this example we are linking ''Ic Inventory (id)'' with the ''Ic_Inventory Item''. Then click the **ADD** button.

{{support:php-report-writer:image-025.png}}

Once the link has been added you can select some fields from the secondary table, We selected the serial number.

{{support:php-report-writer:image-026.png}}

Then save it and recompile the item 55 report.

When you run the report it will then show serial number details for each item.

{{support:php-report-writer:image-027.png}}

{{support:php-report-writer:image-028.png}}

You can clean up the report by changing the report headers fields when you edit each field and other changes.

Other things you can do is modify the code. Here is an example of the code generated from the report writer.

++++Click to open code block|
<code>
<?php
/**
* SM report writer.php - OpenPro report writer generates php reporting code
* handles multiple company, listing of data, selection criteria,
* Debug capabilities, sorting
* error handling, and more.
*
* @author OpenPro Programmer 121
* @package OpenPro ERP
* @copyright OpenPro, Inc. www.openpro.com
*/
/**
* Define global fields section
*/
function pagemake() {
global $page ;
global $pagecount ;
global $th ;
global $thend ;
global $th2 ;
global $thend2 ;
global $th3 ;
global $thend3 ;
if ( $pagecount >= 54 ) {
$page++ ;
$pagecount = 0 ;
if ( strlen($thend3) > 0 ) { print $thend3 ; }
if ( strlen($thend2) > 0 ) { print $thend2 ; }
if ( strlen($thend) > 0 ) { print $thend ; }
$sidedisp = 'Page ' . $page ;
$sidedisp = strftime('%m/%d/%Y %H:%M') . ' ' . $sidedisp ;
if ( strlen($sidedisp) > 0 ) { print '<table cellspacing=0
cellpadding=2 width="100%"><tr><td align=right>' . $sidedisp .
'</td></tr></table>' ; $pagecount++ ; }
print '<table cellspacing=0 cellpadding=2 width="100%"><tr><td
style="text-align: center; font-weight: bold; padding: 1px; " >Item 55
report</td></tr></table>' ; $pagecount++ ;
if ( strlen($th) > 0 ) { print $th ; }
if ( strlen($th2) > 0 ) { print $th2 ; }
if ( strlen($th3) > 0 ) { print $th3 ; }
}
}
if ( $search == 1 ) {
$pagecount = 0 ;
$thend = '' ;$thend2 = '' ;$thend3 = '' ;
$th = '' ;$th2 = '' ;$th3 = '' ;
$page = 0 ;
$pagecount = 1000 ;
pagemake() ;
/**
* Build database SQL section
*/
/**

* First reporting level
*/
/**
* rest of the sql levels
*/
$arr1=array() ;
$where= " where ic.companyid = '" . $company__id . "' and ic.id =
inv.item and inv.companyid = '" . $company__id . "' " ;
if (strlen($iccriteria_field1) > 0 ) {
if(strlen($where) < 1) { $where = " where " ; }
else { $where .= " and " ; }
$where .= " ic.partnum like '%" . $iccriteria_field1 . "%' " ;
}
if (strlen($iccriteria_field2) > 0 ) {
if(strlen($where) < 1) { $where = " where " ; }
else { $where .= " and " ; }
$where .= " ic.status_flag like '%" . $iccriteria_field2 . "%' "
;
}
if (strlen($iccriteria_field3) > 0 ) {
if(strlen($where) < 1) { $where = " where " ; }
else { $where .= " and " ; }
$where .= " ic.companyid = '" . $iccriteria_field3 . "' " ;
}
$sql="select ic.partnum partnum, ic.name name, ic.price_1 price_1,
ic.cost_1 cost_1, inv.serial serial from ic_items ic , ic_inventory inv " .
$where ;
$sql=$sql . " order by ic.partnum" ;
$sqlobj=&db_query($sql) ;
if(db_errno()) { kill (getErrorMessage(10).":<br>". $sql); }
unset($coltot) ; $coltot =array() ; unset($lintot) ; $lintot = array() ;
if (db_num_rows($sqlobj) > 0 ) { print '<div style="text-align:
center;"><table class="tableheader" ><tr class=""><td style="text-align: left;
font-weight: bold; padding: 1px; " >partnum</td><td style="text-align: left;
font-weight: bold; padding: 1px; " >name</td><td style="text-align: left; fontweight: bold; padding: 1px; " >price_1</td><td style="text-align: left; fontweight: bold; padding: 1px; " >cost_1</td><td style="text-align: left; fontweight: bold; padding: 1px; " >serial</td></tr>' ; $pagecount++ ; pagemake() ;
$th='<div style="text-align: center;"><table class="tableheader"
style="page-break-before: always; "><tr class=""><td style="text-align: left;
font-weight: bold; padding: 1px; " >partnum</td><td style="text-align: left;
font-weight: bold; padding: 1px; " >name</td><td style="text-align: left; fontweight: bold; padding: 1px; " >price_1</td><td style="text-align: left; fontweight: bold; padding: 1px; " >cost_1</td><td style="text-align: left; fontweight: bold; padding: 1px; " >serial</td></tr>' ;
$thend='</table></div>' ;
}
$grandtot = 0 ;
while ( $arr1=&db_fetch_array($sqlobj) ) {
// first level
print '<tr><td style="text-align: left; color: #000000; padding:
1px; " >' . $arr1[partnum] . '</td><td style="text-align: left; color: #000000;
padding: 1px; " >' . $arr1[name] . '</td><td style="text-align: left; color:
#000000; padding: 1px; " >' . sprintf('%8.0f',$arr1[price_1]) . '</td><td
style="text-align: left; color: #000000; padding: 1px; " >' .
sprintf('%8.0f',$arr1[cost_1]) . '</td><td style="text-align: left; color:
#000000; padding: 1px; " >' . $arr1[serial] . '</td></tr>' ; $pagecount++ ;
pagemake() ;
$linetot = 0 ;
} // end rec lev 1
print $thend ;
}
/**
* Selection process constraints

*/
if ( $search != 1 ) {
print '<table align="center"><form method="post" name="form1"><tr><td
colspan=3><table width="100%"><tr><td style="text-align: center; font-weight:
bold; padding: 1px; " >Item 55 report</td></tr></table></td></tr>' ;
print '<tr><td colspan=3 class="regtitle" align="center" ><b>Report
Selection Parameter(s)</b></td></tr>' ;
print '<tr>' ;
print '<td align="center"><b>ic_items<br>partnum<br>%Match%<br>' ;
print '<input type="text" name="iccriteria_field1"
onchange="(document.form1.iccriteria_field1);" size=10 maxlength=15
class=small></td>' ;
print '<td align="center"><b>ic_items<br>status_flag<br>%Match%<br>' ;
print '<input type="text" name="iccriteria_field2"
onchange="(document.form1.iccriteria_field2);" size=10 maxlength=15
class=small></td>' ;
print '<td align="center"><b>ic_items<br>companyid<br>Equal<br>' ;
print '<input type="text" name="iccriteria_field3"
onchange="(document.form1.iccriteria_field3);" size=10 maxlength=15
class=small></td>' ;
print '</tr>' ;
print '<input type="hidden" name="search" value="1">' ;
print '<tr><td colspan=3 class="regtitle" align="center" ><input
type="submit" value="Make Report"></form></td></tr></table>' ;
}
</code>

++++

[Last review date June 2017]

----

~~socialite~~