On MS Access applications, usually I use subreport objects with SQL queries of their own, linked to the main report which includes the subreport. Classic example of such master-detail reports is invoices:
+ master (main report) section includes invoice date, invoice number and details of the customer account such as name and address lines.
+ detail (subreport) section includes the products and services charged, product code, desription, quantity, price, and line total etc.
Another example could be statement of share transactions of an employee within a date interval.
+ employee record (name, surname, NI number etc) on main section
+ share transaction list in a subreport underneath the employee record
The problem: When the subreport has no data, MS Access does not show it at all. I may want to see the column headings, at least, or a message with it.
The Solution: We can modify the SQL query (record source) of the subreport and make sure that it has data every time it is run.
Let’s say the SQL query of the main report is something like this:
SELECT e.emp_surname , e.emp_name, e.payroll_number FROM tbl_employee e WHERE e.payroll_number = GetCurrentEmployeeNumber()
And the original SQL query for our subreport which brings the share transactions of employees is:
SELECT t.trans_date, t.quantity, t.price, t.description, t.payroll_number FROM tbl_transaction t INNER JOIN tbl_employee e ON t.payroll_number = e. payroll_number WHERE e.payroll_number = GetCurrentEmployeeNumber() AND t.trans_date BETWEEN GetStartDate() AND GetEndDate()
We can convert it into a UNION query which will always produce data:
SELECT t.trans_date, t.quantity, t.price, t.description, t.payroll_number FROM tbl_transaction t INNER JOIN tbl_employee e ON t.payroll_number = e. payroll_number WHERE e.payroll_number = GetCurrentEmployeeNumber() AND t.trans_date BETWEEN GetStartDate() AND GetEndDate() UNION SELECT TOP 1 NULL AS trans_date, 0 AS quantity, 0 AS price, "N/A" AS description, e.payroll_number FROM tbl_employee e2 WHERE e2.payroll_number = GetCurrentEmployeeNumber() AND e2.payroll_number NOT IN ( SELECT t2.payroll_number FROM tbl_transaction AS t2 WHERE t.trans_date BETWEEN GetStartDate() AND GetEndDate() GROUP BY t2.payroll_number HAVING COUNT(*)>0 );
This way, when there is at least one share transaction the subreport will be visible as usual, listing the transactions of the employee. If there are no transactions, the second part of the union query will have the information we want to show.
Note: In my SQL queries, I referred to my public functions placed inside a VBA module; GetCurrentEmployeeNumber(), GetStartDate(), GetEndDate(). That’s my way of collecting and holding the user inputs as criteria affecting my query results, using global variables; basically, setting them and getting them when required.
PHP has always been my favourite scripting language to develop applications for the web. I have always envied Flash applications! PHP applications, to me, are a fine mixture of PHP + SQL + HTML + CSS + JavaScript. You can always add the fancy TLAs in your applications such as XML, if you want to.
With FLEX, Adobe has opened doors to developers like me, who have been kept apart for years, who are unwilling to commit time to learn the complexities of Flash development.
So, with the popularity of web applications and XML-like languages popping everyday, Adobe introduced (in 2004):
FLEX = Flash + MXML + CSS + ActionScript.
The first unknown in our formula: MXML is XML-based language which resembles HTML + CSS + JavaScript. In a traditional web application, the engine is the browser. Here it is the Flash player.
Our PHP + HTML + CSS + JavaScript codes/files of our application are stored on the web server. But MXML code has no use until it is compiled into SWF files Flash can run. For example, SWF file can be embedded inside a HTML page and sent to the visitor’s browser where Flash player runs it. Java is required by the MXML compiler!?
Straightaway, I remember OpenLaszlo! From 2000 onwards, they had this idea of compiling codes of web developers into Flash applications. Poor guys: because of fundamental marketing flaw and approach of presentation, Adobe is now collecting the fruits instead of them. Sometimes, even the choice of the name of a product has major effects on its lifespan.
Now let’s get hands dirty! I wanted to convert the user interface of my PHP Stock profiler into Flex.
For simplicity, I will keep all the files inside a folder on my web server; here is the folder structure of
/phpShareFlex/
|–/flex/
|—-services-config.xml
|—-phpShareFlex.mxml
|—-build.bat
|–/service/
|—-/class.MyShareService.php
|—-/index.php
|–/zf/library/Zend/
|–/tmp/
|–AC_OETags.js
|–phpShareFlex.swf
|–index.php
Since I am a PHP coder, let’s write the code for our service first:
/phpShareFlex/service/class.MyShareService.php
<?php /** * Class to provide share services */ class MyShareService{ /** * Get options for index combobox * @return array */ public function getIndexes(){ $indexes = array(); $indexes[] = array('label' => 'FTSE 100', 'data' => '^FTSE'); $indexes[] = array('label' => 'FTSE 250', 'data' => '^FTMC'); return $indexes; } /** * Get options for top combobox * @return array */ public function getTop(){ $top = array(); for($i=1; $i<=10; $i++) $top[] = array('label' => 'Top ' .($i*10), 'data' => ($i*10)); return $top; } /** * Get array of share data * @param string $index * @param array $methods_arr * @param int $top Number of shares * @return array */ public function getShares($index, $methods_arr, $top=10){ $shares = array(); $shares[] = array('code' => 'AAA', 'price' => rand(1,100), 'change' => rand(-10,10), 'volume' => rand(100,1000)); $shares[] = array('code' => 'BBB', 'price' => rand(1,100), 'change' => rand(-10,10), 'volume' => rand(100,1000)); $shares[] = array('code' => 'CCC', 'price' => rand(1,100), 'change' => rand(-10,10), 'volume' => rand(100,1000)); return $shares; } }//end class ?>
Note: phpDoc blocks are required by Zend AMF, and the system does not like the output arrays with string keys, so use integer indexes.
Now, parent page to publish it:
/phpShareFlex/service/index.php
<?php error_reporting(E_ALL); date_default_timezone_set('Europe/London'); $myLog=''; function myErrorHandler($errno, $errstr, $errfile, $errline){ global $myLog; $myLog .= "Error $errno on line $errline in file $errfile\n" . "----- $errstr\n"; return TRUE; } $old_error_handler = set_error_handler("myErrorHandler"); $paths = array( realpath(dirname(__FILE__) . '/../zf/library'), '.' ); set_include_path(implode(PATH_SEPARATOR, $paths)); require_once "../zf/library/Zend/Loader.php"; Zend_Loader::loadClass('Zend_Amf_Server'); require "class.MyShareService.php";//our class to do the job $server = new Zend_Amf_Server(); $server->setProduction(FALSE); $server->setClass( "MyShareService" ); if($myLog!=''){ $filename = dirname(__FILE__).'/tmp/service.debug.'.date('YmdHis').'.txt'; file_put_contents($filename, $myLog); } $handle = $server->handle(); echo ($handle); ?>
Note: The path of Zend Framework library is required to be able to use the classes. One of the silly tricky points was to get set_include_path right! Sometimes such things take hours to find out! You must have noticed my error logging function because the communication happens in the background and developing and debugging this side might be painful; just check the output files inside /tmp/ folder.
Next is the MXML application to use our PHP Zend AMF service. Let’s define how it can communicate with our service.
/phpShareFlex/flex/services-config.xml
<?xml version="1.0" encoding="UTF-8"?> <services-config> <services> <service id="zend-service" class="flex.messaging.services.RemotingService" messageTypes="flex.messaging.messages.RemotingMessage"> <destination id="zend"> <channels> <channel ref="myZendChannel"/> </channels> <properties> <source>*</source> </properties> </destination> </service> </services> <channels> <channel-definition id="myZendChannel" class="mx.messaging.channels.AMFChannel"> <endpoint uri="http://www.muratyaman.co.uk/phpShareFlex/service/index.php" class="flex.messaging.endpoints.AMFEndpoint"/> </channel-definition> </channels> </services-config>
Note: Destination id will be required by the RemoteObject in our MXML application. The type of channel we used is AMFChannel.
/phpShareFlex/flex/phpShareFlex.mxml
<?xml version="1.0" encoding="utf-8"?> <mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" pageTitle="phpShareFlex" layout="absolute" creationComplete="Application_creationComplete()" > <mx:Script> <![CDATA[ import mx.collections.ArrayCollection; import mx.collections.SortField; import mx.collections.Sort; import mx.rpc.events.ResultEvent; import mx.rpc.events.FaultEvent; import mx.utils.ObjectUtil; private function logMsg(msg:String):void{ txtLog.text += msg; } private function svcShareService_getShares_fault( event:FaultEvent ) : void { logMsg('svcShareService_getShares_fault() ' + faultInfo(event) + '\n'); } private function Application_creationComplete() : void { try{ logMsg('BEGIN Application_creationComplete()' + '\n'); logMsg('Calling svcShareService.getIndexes()' + '\n'); svcShareService.getIndexes(); logMsg('Calling svcShareService.getTop()' + '\n'); svcShareService.getTop(); logMsg('END Application_creationComplete()' + '\n'); }catch(e:Error){ logMsg('ERROR Application_creationComplete():\n ' + e.message + '\n'); } } private function svcShareService_fault(event:FaultEvent):void{ logMsg('svcShareService_fault() ' + faultInfo(event) + '\n'); trace('svcShareService_fault'); } private function call_svcShareService_getShares() : void { logMsg('BEGIN call_svcShareService_getShares()' + '\n'); var myIndex:Object = cboIndexes.selectedItem; var myIndexCode:String = myIndex.data; var myTop:Object = cboTop.selectedItem; var myTopNum:Number = myTop.data; var myMethodsArr:Array; myMethodsArr = new Array(); if(chkLP.selected) myMethodsArr.push("LP"); if(chkLL.selected) myMethodsArr.push("LL"); if(chkLV.selected) myMethodsArr.push("LV"); logMsg('Calling svcShareService.getShares()' + '\n'); svcShareService.getShares(myIndexCode, myMethodsArr, myTopNum); logMsg('END call_svcShareService_getShares()' + '\n'); } private function svcShareService_getIndexes_result( event:ResultEvent ) : void { logMsg('svcShareService_getIndexes_result()' + '\n'); cboIndexes.dataProvider = event.result; } private function svcShareService_getIndexes_fault(event:FaultEvent):void { logMsg('svcShareService_getIndexes_fault() ' + faultInfo(event) + '\n'); } private function svcShareService_getTop_result( event:ResultEvent ) : void { logMsg('svcShareService_getTop_result()' + '\n'); cboTop.dataProvider = event.result; } private function svcShareService_getTop_fault(event:FaultEvent):void { logMsg('svcShareService_getTop_fault() ' + faultInfo(event) + '\n'); } private function btnGetShares_click(event:Event):void{ logMsg('btnGetShares_click()' + '\n'); call_svcShareService_getShares(); } private function svcShareService_getShares_result( event:ResultEvent ) : void { logMsg('svcShareService_getShares_result()' + '\n'); dgShares.dataProvider = event.result; } private function faultInfo(event:FaultEvent):String{ var s:String = event.fault.faultString + ' Msg: ' + event.fault.message + ' Status: ' + event.statusCode; return s; } private function sortNumber(d1:Number, d2:Number):int{ if(d1 < d2) { return -1; } else if(d1 == d2) { return 0; } return 1; } private function myParseFloat(strNumber:String):Number{ var s:String = strNumber; s = s.split(",").join(""); s = s.split("%").join(""); var f:Number = parseFloat(s); return f; } private function sortDate(obj1:Object, obj2:Object):int{ return sortNumber( (new Date(Date.parse(obj1.date))).getTime(), (new Date(Date.parse(obj2.date))).getTime()); } private function sortNumber_price(obj1:Object, obj2:Object):int{ return sortNumber( myParseFloat(obj1.price), myParseFloat(obj2.price)); } private function sortNumber_change(obj1:Object, obj2:Object):int{ return sortNumber( myParseFloat(obj1.change), myParseFloat(obj2.change)); } private function sortNumber_volume(obj1:Object, obj2:Object):int{ return sortNumber( myParseFloat(obj1.volume), myParseFloat(obj2.volume)); } private function shareChart(sign:String):void{ var url:String = 'http://uk.ichart.yahoo.com/z?s=' + sign + '&t=3m&q=b&l=on&z=m&p=e5,e20&a=ss,vm'; imgShareChart.source = url; } private function dgShares_itemClick(event:Event):void{ logMsg('dgShares_itemClick()' + '\n'); var code:String = dgShares.selectedItem.code; lblShareCode.text = code; shareChart(code); } private function lnkCompanyInfo_click(event:Event):void{ logMsg('lnkCompanyInfo_click()' + '\n'); var url:String = 'http://uk.finance.yahoo.com/q/pr?s=' + lblShareCode.text;//company profile //ExternalInterface.call( "myNewWindow("+url+")" ); } ]]> </mx:Script> <mx:RemoteObject id="svcShareService" source="MyShareService" destination="zend" showBusyCursor="true" fault="svcShareService_fault(event)"> <mx:method name="getIndexes" result="svcShareService_getIndexes_result(event)" fault="svcShareService_getIndexes_fault(event)" /> <mx:method name="getTop" result="svcShareService_getTop_result(event)" fault="svcShareService_getTop_fault(event)" /> <mx:method name="getShares" result="svcShareService_getShares_result(event)" fault="svcShareService_getShares_fault(event)" /> </mx:RemoteObject> <mx:Panel width="95%" height="95%" layout="vertical" title="phpShareFlex" horizontalAlign="center" horizontalCenter="0" verticalCenter="0" paddingBottom="5" paddingLeft="5" paddingRight="5" paddingTop="5"> <mx:VDividedBox width="100%" height="100%"> <mx:HDividedBox width="100%" height="90%"> <mx:Canvas label="Criteria" width="15%" height="100%"> <mx:VBox width="100%" height="90%"> <mx:ComboBox id="cboTop" /> <mx:Label text="shares from" /> <mx:ComboBox id="cboIndexes" /> <mx:Label text="Using" /> <mx:CheckBox id="chkLV" label="Last Volume" selected="true" /> <mx:CheckBox id="chkLP" label="Last Profit %" selected="true" /> <mx:CheckBox id="chkLL" label="Last Loss %" selected="false" /> <mx:Button id="btnGetShares" label="Get Shares" click="btnGetShares_click(event)" /> </mx:VBox> </mx:Canvas> <mx:Canvas label="Shares" width="55%" height="100%"> <mx:VBox width="100%" height="100%"> <mx:DataGrid id="dgShares" width="100%" height="100%" itemClick="dgShares_itemClick(event)" > <mx:columns> <mx:DataGridColumn headerText="Code" dataField="code" /> <mx:DataGridColumn headerText="Price" dataField="price" sortCompareFunction="sortNumber_price" /> <mx:DataGridColumn headerText="Change %" dataField="change" sortCompareFunction="sortNumber_change" /> <mx:DataGridColumn headerText="Volume" dataField="volume" sortCompareFunction="sortNumber_volume" /> </mx:columns> </mx:DataGrid> </mx:VBox> </mx:Canvas> <mx:Canvas label="Information from Yahoo!" width="30%" height="100%"> <mx:VBox width="100%"> <mx:Label text="Chart of Last 3 Months" /> <mx:HBox width="100%"> <mx:Label text="Selected share:" /> <mx:Label id="lblShareCode" text="" /> <mx:LinkButton id="lnkCompanyInfo" label="More info" color="#0000FF" fontWeight="bold" click="lnkCompanyInfo_click(event)"/> </mx:HBox> <mx:Image id="imgShareChart" width="100%" /> </mx:VBox> </mx:Canvas> </mx:HDividedBox> <mx:VBox width="100%" height="10%"> <mx:TextArea id="txtLog" width="100%" height="100%" text="" /> </mx:VBox> </mx:VDividedBox> </mx:Panel> </mx:Application>
Note: Like in other programming languages, there are various ways of doing the same thing: for example, I tried to stick to writing event handling functions first and then attaching them in object attributes like the click event of a button, rather than doing the job inside the MXML tags. I believe it is easier to understand: separation of code section and user interface section, if you see what I mean 
To compile this MXML application code, I have a copy of it and the services-config file on my PC and my Flex SDK is under:
c:\MyApps\FlexSDK\
So, the build.bat file is simply:
C:\MyApps\FlexSDK\bin\mxmlc.exe -services services-config.xml phpShareFlex.mxml
Note: The services switch tells the compiler custom service definitions. Once the phpShareFlex.swf file is created, you can upload it to your web server; mine is under:
www.muratyaman.co.uk/phpShareFlex/phpShareFlex.swf
Now, let’s present this to the world wide web either in a HTML page or PHP page; mine is:
www.muratyaman.co.uk/phpShareFlex/index.php
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>phpShareFlex</title>
<script src="AC_OETags.js" language="javascript"></script>
<script language="JavaScript" type="text/javascript">
<!--
//to be called by flex objects like link buttons
function myNewWindow(url){
window.open(url);
return true;
}
// -->
</script>
<style>
body { margin: 0px; overflow:hidden }
</style>
</head>
<body scroll='no'>
<script language="JavaScript" type="text/javascript">
<!--
AC_FL_RunContent(
"src", "phpShareFlex",
"width", "100%",
"height", "100%",
"align", "middle",
"id", "phpShareFlex",
"quality", "high",
"bgcolor", "#869ca7",
"name", "phpShareFlex",
"allowScriptAccess","sameDomain",
"type", "application/x-shockwave-flash",
"pluginspage", "http://www.adobe.com/go/getflashplayer"
);
// -->
</script>
<noscript>
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000"
id="phpShareFlex" width="100%" height="100%"
codebase="http://fpdownload.macromedia.com/get/flashplayer/current/swflash.cab">
<param name="movie" value="phpShareFlex.swf" />
<param name="quality" value="high" />
<param name="bgcolor" value="#869ca7" />
<param name="allowScriptAccess" value="sameDomain" />
<embed src="phpShareFlex.swf" quality="high" bgcolor="#869ca7"
width="100%" height="100%" name="phpShareFlex" align="middle"
play="true"
loop="false"
quality="high"
allowScriptAccess="sameDomain"
type="application/x-shockwave-flash"
pluginspage="http://www.adobe.com/go/getflashplayer">
</embed>
</object>
</noscript>
</body>
</html>Note: Main issue could be the cross site security issue (errors like “Send failed”) when RemoteObject is calling our Zend AMF service, so stick both on to the same domain/website. The second issue during development is that you have to clear the browser’s cache to see the latest version of your FLEX application.
In order not to increase the complexity of this sample tutorial about Flex and PHP Zend/AMF, I have not included the actual code that gathers the share data from Yahoo Finance, which lets you download a CSV file, given sign of a quote or share with date intervals. I have already done it in phpStockProfiler including more functions to save it locally, converting into arrays and working on the data.
Ideally, I want to improve this application to achieve the following diagram:
Main improvement is the processing of external data source (public CSV data from Yahoo Finance) and optimizing data flow by using a database. Next one could be a kind of queuing mechanism for concurrent requests and not to do the same calculations twice, at least for the current day, maybe. Any suggestions are welcome.
Happy coding!
…when we can “ask” or “google” or “yahoo!”
Today, I thought let me check an “online SQL tool” (web-based database administration software) on the net, and found out that Microsoft has one. OK, let me download it and check it. I visited their website, a link diverted me to it. And within it, it diverted me to an automated search, because the link was broken, or thinking the visitor typed an incorrect URL.
Search for SQL web data administrator
And click on the first “genuine” looking result, which was actually the link I followed to come to Microsoft’s site:
Oooops! a broken link!
How can Microsoft expect the people use their “bing” while they can not even index their own mess?!
As usual, they change search settings of Internet Explorer, automatically add “bing” thing to the search provider list, through Windows Update. By the way, is it (Windows Update, nowadays they call it Microsoft Update as well) a feature to fix their bugs/faults or a trojan that does whatever Microsoft wants to do on our PCs?
All right, let’s just forget about the whole “bing”!
Also known as content indexing service, this process is part of Windows operating system, and it is supposed to index the files on the drive(s), which should provide faster file search and access using the indexing service catalog.
Instead of speeding up your machine, it sometimes slows it down, which is probably because some dummy Microsoft programmers coded it and in some cases it is confused!
It should simply run when the machine is idle; and when is that on a server ?!
Maybe, it is programmed to work based on mouse and keyboard events, and when there is none, it thinks it is idle?!
Servers are always busy. That is the reason they exist. For example, since 2000, many companies have websites with database applications, open to customers and visitors from all parts of the world. I could otherwise say, during the day it is busy selling products, during the night it is busy backing up itself!
Nowadays, people use laptops to connect to their PCs or servers at work from the sofa at home! Loads of virtual/terminal sessions on a server kills it! You would not use a Windows Server to host 100 concurrent Remote Desktop sessions, would you?
Anyway, I am wondering when Microsoft will stop assuming their users are stupid, and let them choose which services/applications to enable.
For example, I am using an old PC with a bare Windows XP Pro (without antivirus), which uses around 80MB of memory when “nothing” is running on it, ready to use.
Also, I am using another x64 PC with Vista Home Ed (with antivirus) and it consumes around 900MB already when it is just started.
I recommend to use Linux or BSD variants for servers.
Future is open (..source)!
In this occasion, we have a PHP/Firebird application. We had to backup the database and the code (which includes PHP, HTML, CSS, Javascript files as well as user documents uploaded into a folder).
We have created a folder /mybackup/ and created a bash script backup.sh to do the following, using nano command line editor. We will always have 2 compressed gz files, remove the old ones. Backup the database, compress it. Backup the code folder, compress it. Rewind the tape. Then, copy into the tape. We are expecting the client to eject the tape and insert another one everyday, Monday to Friday.
#!/bin/bash rm -f *.gz /opt/firebird/bin/gbak -t -user sysdba -password "mypassword" localhost:mydb /mybackup/mydb.fbk gzip mydb.fbk tar -cpf mycode.tar /var/www/html gzip mycode.tar mt -f /dev/st0 rewind tar -cpf /dev/st0 *.gz
(mydb is an alias pointing to a FDB database file defined in Firebird aliases.conf)
Using Webmin, my favourite online system administration tool for Linux, I’ve created a cron job to run /mybackup/backup.sh at required intervals.
Make sure that the user account that runs the commands has the execute and write permissions where needed.
Install mt, if your system has not got it already.
CentOS package manager
