Sunday, October 18, 2009

Jasper Reports Create a Union Query in iReport

This is a simple, step by step guide to creating a union query using Jasper's iReport.

For my sample, I am using a single table, "BookList", that contains data for a local library.

In my sample table, regular book titles have an ISBN number, but the library also has unpublished material that have an internal reference number called "LocalFileNumber".

For my Union Query, I want to select from this table the Author, Title, ISBN if available, and LocalFileNumber where ISBN is null.


Here is my sample data:

-- --------------------------------------------------------

--
-- Table structure for table `BookList`
--

CREATE TABLE IF NOT EXISTS `BookList` (
`id` int(11) NOT NULL auto_increment,
`Author` varchar(50) default NULL,
`Title` varchar(50) default NULL,
`ISBN` varchar(50) default NULL,
`LocalFileNumber` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Data for table `BookList`
--

INSERT INTO `BookList` (`id`, `Author`, `Title`, `ISBN`, `LocalFileNumber`) VALUES
(1, 'Henry James', 'The Portrait of a Lady', '0141439637', NULL),
(2, 'T.S. Eliot', 'Collected Poems', '0151189781', NULL),
(3, 'William Gaddis', 'The Recognitions', '0844667404', NULL),
(4, 'John Smith', 'History of Our Library', NULL, 'JS12345678'),
(5, 'James Merrill', 'The Changing Light at Sandover', '0307263215', NULL),
(6, 'Wendy Jones', 'Library Policy Manual', NULL, 'WJ12345678');



So, we have 4 titles with ISBN numbers, and 2 titles that use the libraries internal reference number (highlighted in bold below):


mysql> select * from BookList;
+----+----------------+--------------------------------+------------+-----------
------+
| id | Author         | Title                          | ISBN       | LocalFileNumber |
+----+----------------+--------------------------------+------------+-----------
------+
|  1 | Henry James    | The Portrait of a Lady         | 0141439637 | NULL
      |
|  2 | T.S. Eliot     | Collected Poems                | 0151189781 | NULL
      |
|  3 | William Gaddis | The Recognitions               | 0844667404 | NULL
      |
4 | John Smith     | History of Our Library         | NULL       | JS12345678
      |
|  5 | James Merrill  | The Changing Light at Sandover | 0307263215 | NULL
      |
6 | Wendy Jones    | Library Policy Manual          | NULL       | WJ12345678
      |
+----+----------------+--------------------------------+------------+-----------
------+
6 rows in set (0.00 sec)


1. Start up iReport and create your connection.

2. At the Query section, add the 'BookList' table into the viewer and select Author, Title, and IBSN:





3.  Right click on WHERE and select Add a Condition:


 

4. The WHERE clause in our first select will be ISBN IS NOT NULL



5. Now that we have our first select with our WHERE clause, right click on ROOTQUERY and select UNION

 

6. Note that the UNION node now appears in the query explorer pane (just above the ORDER BY node).

 

7. Highlight the SELECT node under Union.

 

8. Double click on the BookList table to add it into the pane. This time we will select Author, Title, and LocalFileNumber.





9. Right click on the WHERE node for the query and select Add Condition:





9. For our condition on this select, we will now enter WHERE ISBN IS NULL




10. Expand all of the relevant nodes so we can see our query:




11. Now, at the bottom of the Query Builder, select the SQL tab:




12. From here, we can edit our query so that both ISBN from the first select and LocalFileNumber from the second select are selected as "BookCode" as shown below:





SELECT
     booklist.`Author` AS booklist_Author,
     booklist.`Title` AS booklist_Title,
     booklist.`ISBN` AS BookCode
FROM
     `booklist` booklist
WHERE
     ISBN IS NOT NULL
UNION
SELECT
     booklist.`Author` AS booklist_Author,
     booklist.`Title` AS booklist_Title,
     booklist.`LocalFileNumber` AS  BookCode
FROM
     `booklist` booklist
WHERE
     ISBN IS NULL


So we now have a query that will return Author, Title, and BookCode (ISBN or LocalFileNumber when ISBN is null).

One item you may note above is that we did not need to qualify our alias BookCode with quotation marks.

13. Click OK and review your query: 








PHP MySQL Union Query

Union Query with PHP and MySQL

For deailed explanation of MySQL sample data and logic, please see my Jasper Reports Query post

-- --------------------------------------------------------

--
-- Table structure for table `BookList`
--

CREATE TABLE IF NOT EXISTS `BookList` (
`id` int(11) NOT NULL auto_increment,
`Author` varchar(50) default NULL,
`Title` varchar(50) default NULL,
`ISBN` varchar(50) default NULL,
`LocalFileNumber` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Data for table `BookList`
--

INSERT INTO `BookList` (`id`, `Author`, `Title`, `ISBN`, `LocalFileNumber`) VALUES
(1, 'Henry James', 'The Portrait of a Lady', '0141439637', NULL),
(2, 'T.S. Eliot', 'Collected Poems', '0151189781', NULL),
(3, 'William Gaddis', 'The Recognitions', '0844667404', NULL),
(4, 'John Smith', 'History of Our Library', NULL, 'JS12345678'),
(5, 'James Merrill', 'The Changing Light at Sandover', '0307263215', NULL),
(6, 'Wendy Jones', 'Library Policy Manual', NULL, 'WJ12345678');




<?php
$myconn = mysql_connect("localhost","username","password");
if (!$myconn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("Books", $myconn);


$result = mysql_query
('select Author, Title, ISBN as "BookCode"
FROM BookList where ISBN is not null
UNION
select Author, Title, LocalFileNumber as "BookCode"
FROM BookList where ISBN is null')
or die(mysql_error());

while($row = mysql_fetch_array($result))
  {
  echo $row['Author'] . " " . $row['Title'] . " " .$row['BookCode'];
  echo "</br
>";
  }

mysql_close($myconn);
?>



(and with a bit formatting.........


<?php
$myconn = mysql_connect("localhost","username","password");
if (!$myconn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("Books", $myconn);

$result = mysql_query('select Author, Title, ISBN as "BookCode"
FROM BookList where ISBN is not null
UNION
select Author, Title, LocalFileNumber as "BookCode"
FROM BookList where ISBN is null')
or die(mysql_error());

echo "<table border='1'>
<tr>
<th>Author</th>
<th>Title</th>
<th>BookCode</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Author'] . "</td>";
  echo "<td>" . $row['Title'] . "</td>";
  echo "<td>" . $row['BookCode'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($myconn);
?>

Saturday, October 10, 2009

Install Group Office on CentOS 5.3

Install Group Office on CentOS 5.3

Start Here: http://www.group-office.com/wiki/Installation

The server is going to be used primarily for Group Office, so I stayed with the script and unpacked the tar file to /var/www/html/groupoffice-pro-3.2.37

Overall, this was fairly straight forward, with the exception of having to upgrade PHP and a some brief scrounging for some rpms.

The installation package includes a test file in the root directory, gotest.php, that provides a useful summary of required items that your server is lacking. So if you don't have php configured, that should be the first order of business.

One of the reasons I use CentOS whenever possible is it's stability and it's conservative approach.

Unfortunately, this means that php >=5.2, one of the prerequisites for Group Office, was not available from the repo.

Not wanting to recompile PHP, it meant having to avail myself of Jason Litka's repository.

[root@server4 ~]# rpm --import http://www.jasonlitka.com/media/RPM-GPG-KEY-jlitka
[root@server4 ~]# vi /etc/yum.repos.d/utterramblings.repo
[utterramblings]

Enter the following and then :wq
[utterramblings]
name=Jason's Utter Ramblings Repo
baseurl=http://www.jasonlitka.com/media/EL$releasever/$basearch/
enabled=1
gpgcheck=1
gpgkey=http://www.jasonlitka.com/media/RPM-GPG-KEY-jlitka

[root@server4 ~]# yum update php
[root@server4 ~]# yum update mysqlat

After upgarding php, simply consult the gotest.php script to find missing prerequisites. In my case:

[root@server4 ~]# yum install php-imap php-xml php-mbstring php-devel
[root@server4 ~]# service httpd restart

Install Zend Ion Cube

[root@server4 ~]# wget http://downloads2.ioncube.com/loader_downloads/ioncube_loaders_lin_x86.tar.gz
[root@server4 ~]# tar -zxvf ioncube_loaders*
[root@server4 ~]# mv ioncube /var/www/html/ioncube
[root@server4 ~]# service httpd restart

After installing ion cube above, add the Zend extension in bold below to your php.ini file under Language Options before the Zend references:

;;;;;;;;;;;;;;;;;;;;
; Language Options ;
;;;;;;;;;;;;;;;;;;;;

; Enable the PHP scripting language engine under Apache.
engine = On

zend_extension = /var/www/html/ioncube/ioncube_loader_lin_5.2.so


Restart Apache:
[root@server4 ~]# service httpd restart

Check your php version is now updated and ion cube installed:

[root@server4 ~]# php -v
PHP 5.2.11 (cli) (built: Sep 21 2009 14:49:09)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2009 Zend Technologies
with the ionCube PHP Loader v3.1.34, Copyright (c) 2002-2009, by ionCube Ltd.


Install libwbxml:

[root@server4 ~]# rpm -Uvh http://dl.atrpms.net/all/wbxml2-0.9.2-5.el4.at.i386.rpm

Install TNEF:

For whatever reason, I could not pull down tnef-1.3.3-1.2.el4.rf.i386.rpm from dag.wieers.com, even searching manually.

I grabbed it here: http://www.rpmfind.net/linux/rpm2html/search.php?query=tnef
saved it to temp and installed:

[root@server4 ~]# cd /var/tmp
[root@server4 ~]# rpm -Uvh tnef-1.3.3-1.2.el4.rf.i386.rpm

That was about it.

Once you cleared all of the gotest.php prerequisites, you can then start the installation via the home directory, which will walk you through the installation. Keep putty open as you will need to do some mkdir's as some chmod's.

Add your virtual host entry in your Apache httpd.conf file.


<VirtualHost *:80>
ServerAdmin david@mydomain.com
DocumentRoot /var/www/html/groupoffice-pro-3.2.37
ServerName mycal.mydomain.com
ErrorLog logs/mycal.mydomain.com-error_log
CustomLog logs/mycal.mydomain.com-access_log common
</VirtualHost>

Tune Apache according to requirements.

I originally tested using the Community Edition, groupoffice-com-2.18-stable-21, and the procedure is identical.

If you will be using the CE, the items you can skip are indicated in the gotest.php file.