[News] [FAQ] [Examples] [Presentations] [Opinions] [About me] [Links] [Perm] [Statistics] [Search]

About example "Statistics of Visiting"

Contents

• Purpose
• Software
• As it's written
• Structure of datas
• XSL-transformations
• Notes to a Script-code
• Characteristic of datas
• Defects of display
• To look work of an example
• The literature

Purpose
Go top

In VFP 7.0 the support XML has appeared... and by reading about MS XML a little I wanted to something apply it. Besides I have not authorities on my Web Server to any kind of programming, while has seen in particular, that XML allows to use datas of the server and transform their by means of any clients. So here, these two circumstances have inspired me to write an example of "viewer" Statistics of Visiting mine site for visitors.

In the given note I try briefly illustrate: that from this was received and as it eventually works, in hope, that for the visitor, completely unfamiliar with this subject, these my words will useful.

Software
Go top

First of all it is necessary to tell, that in an example is used the software which is possible is not installed at you. In the following table the explanations are made:

Tab. 1 The used software.

As it's written
Structure of datas
Go top

The source data takes from Web Server (usually time in a month) and be located in the VFP-table with the following structure:

MyStat (date d, visitos i, pages i)

As it is necessary to the visitor grant choice of a month in two language variants, the VFP-table of the list of months also is filled in:

Month_e(Month_r) (month_id n(2,0), month_nm c(9))

So to tell, source data above. Now, as it is desirable to have final outcomes, both for one years, and on months in each to a year, the following of the request performing small transformations and appropriate summation are composed:

lvStat:
SELECT YEAR(Mystat.date) AS year, MONTH(Mystat.date) AS month,;
  DAY(Mystat.date) AS day, Mystat.visitors, Mystat.loads AS pages;
 FROM mystat!mystat;
 ORDER BY 1, 2, 3

lvStaty:
SELECT YEAR(Mystat.date) AS year, SUM(Mystat.visitors) AS visitors,;
 SUM(Mystat.pages) AS pages, COUNT(*) AS days;
 FROM mystat!mystat;
 GROUP BY 1;
 ORDER BY 1

lvStatm:
SELECT YEAR(Mystat.date) AS year, MONTH(Mystat.date) AS month,;
 SUM(Mystat.visitors) AS visitors, SUM(Mystat.pages) AS pages,;
 COUNT(*) AS days;
 FROM mystat!mystat;
 GROUP BY 1, 2;
 ORDER BY 1, 2

SQL-requests for build the used tables.

As result by using VFP 7.0 function CursorToXML(), we receive and put on ours Web Server the following files:

Tab. 2 The list of files, puted on the Web server.

So for example, for deriving month_e.xml by use opened file month_e.dbf it is enough to execute in the Command Window: ?CursorToXML("Month_e", "Month_e.xml", 1, 8+16+512, 16, "Month.xsd")

As I have understood, the small feature is those, that the method ADODB.Recordset.Save(<MyFile>, adPersistXML) saves the datas in so to speak attribute-centric XML format and on this, for statd.xml, statm.xml and state.xml I has not become to recede from this rule (i.e. used 3 - RAW as the third parameter to CursorToXML()) while for month_e.xml (month_r.xml) - has applied 1 - ELEMENTS to introduce though any variety :-)

It is clear, that datas of all three files: statd.xml, statm.xml and staty.xml should be agreed, i.e. the modifications in statd.xml should entail respective alterations in statm.xml and staty.xml. It would be possible to unit datas of these three files into one, by arranging in it the hierarchy: year/month/day and it, as I understand, will be well agreeed philosophy XML/XSL, however would reduce in necessity of writing of the utility - generator of such incorporated file. I have not become to do it, and has kept files directly in such kind, in which they are received by function CursorToXML().

Fragments of the contents of obtained files are shown below:

Fragments of files  month_r.xml, statd.xml, statm.xml, staty.xml, puted on the Web server.

The files with the xsd extension describe/inspect a structure of appropriate files and are not considered here. Let's consider problem of building xsl-files now.

XSL-transformations
Go top

Now consider the problem of a data conversion from XML-files above mentioned to a formats required for use directly in Web Browser on the client. So, it is necessary to build of datas of the file month_e.xml (month_r.xml) filling ComboBox (cobMonth). To simplify a similar problem I has written very simple month.xsl-file, ensuring a similitude:

The file month.xsl.

I.e. as a rooted element of the outcome is inserted <SELECT ID = "cobMonth" NAME="Month"> and further for each of month from VFPData (see filling of the file month_e.xml above) is formed an appropriate element <OPTION> and in his attribute VALUE with the significance of element id_month from the source file is added... Thus, the obtained outcome just also will be the html-code for the ComboBox, containing the list of months in one year.

Further, if to provide a possibility of choice by the visitor of a concrete year and month for construction of the diagram in a selected month for the indicated year, it is necessary to know how to select an appropriate subset of datas from the file statd.xml. Not having something practical examples of use XSL language in these purposes, I can not tell that it was easily for me :-) However, I wrote the statd.xsl file realizing a similar two-parameter filtration, using xsl:for-each together with xsl:if:

The example of statd.xsl file.

Example for choice of datas from statd.xml-file in the 10-th month for the 2001-st year is shown above. Pay attention, that the received outcome has an almost initial structure, with the elemination of absence in him of attributes: year and month (the fragment of the statd.xml file see above). In other words transformed datas will be:

• firstly - in the indicated range;
• secondly - to contain only attributes: day, visitors and pages, constructed on appropriate input datas.

At last, if to provide a possibility of a building of the diagrams for the indicated year on months, there will be a necessity of selection of a subset of datas from the statm.xml-file for the indicated year... and this a rather trivial problem is solved by the statm.xsl-file shown below:

The example of statm.xsl file.

In this case the example of the filtration of datas for the 2001-st year is shown... and also it is obvious, that from an initial structure (see statm.xml-file above) the attribute year (on which significance the sample was carried out) is excluded.

In this point, when as I hope in general clearly: how the transformation of the required subset of datas from xml-files is carried out by use appropriate xsl-files, we can consider some detailses of programming on JavaScrip on the party of the Internet-client.

Notes to a Script-code
Go top

First of all, we shall look at a below-mentioned code located in any html-file, loaded by the Internet-client:

The example samp1_e.html

I think, that the careless look enough to understand: unique the SPAN element on the page after successful loading of the month_e.xml-file and transformation it by using month.xsl will be replaced by an outcome of transformation (above the fragments of files month_r.xml and month.xsl were indicated)... and in our case we have

Fragment an outcome of transformation of the month_e.xml file with use month.xsl.

To look result of the example samp1_e.html click here.

Try still to do such experiment:

The example samp2.html executes parsing of tree-structure, obtained from statd.xml in an outcome of the transformation statd.xsl.

Above the fragments of files statd.xml and statd.xsl were indicated.
To look result of the example samp2.html click here.

The both examples above mentioned, load xml-files in IE from a JavaScript-code obviously, by using:

However, in MS Internet Explorer 5.0 it is a not unique possibility. So, is present other two variants:

• by use <XML ID="xmlDso" SRC="My.xml"></XML>
• through inserted <OBJECT> an element:

In both cases, in IE you receive XMLDSO object (i.e. Data Source Object (DSO) from the DHTML object model), the property XMLDocument which returns the reference on XMLDOMDocument (pay attention: just XMLDOMDocument, instead of the property name XMLDocument).

At last, the XSL-transformations to the xml-file can be applied and obviously, by indicating stylesheet, i.e. by inserting as the second line in the xml-file the appropriate reference:

<?xml:stylesheet type="text/xsl" href="My.xsl" ?>

In the latter case, if the indicated xsl-file will realize transformation to a html-format, the appropriate xml-file can be open from MS IE immediately.

Here only now, by understanding as will work the code above (probably under the debugger), it will be completely easy understand to you the code of my example stat_e.html (see below), if there will be desire, certainly: -) Only in last, I have not use files: statd.xsl or statm.xsl shown above. Simply the function getXSL (sYear, sNumMoth) is written, which as an outcome returns a line of a XSL-code... and if value of the second parameter is line with not zero length - then statd.xml, otherwise - statm.xml. The line, obtained as result is XSL-code, and can be used because of the object XMLDOMDocument has the method loadXML() for loading from a string (not from the file). To help you easy view a source code, I have not located it in the js-file, as it usually make, i.e. all code you can look through using menu item View/As HTML for page stat_e.html

Characteristic of datas
Go top

I do not think, that you seriously are interested by the datas of statistics of visiting on my site :-) however in any case, it is required to make some explanations rather purely of datas:

• The datas are conducted from 07/15/2000 and include two daily attributes:
  • number of the visitors, in other words: how many different (on ip-addresses) the visitors has visited site,
  • amount of the loaded pages, i.e. how many time was requested from the server separate pages on site,
• The datas are partial, i.e. in an interval from 07/15/2000 to 02/16/2001 the amount of the loaded pages was not fixed :-( also it is accepted equal to zero ). Besides the different counters of visitors were used: in the indicated interval SpyLog (http://www.stylog.ru/[rus]) was used, while outside of, i.e. in the present moment, the counter of my Web server - http://narod.yandex.ru/[rus] is used.

Defects of display
Go top

I can not prevent emerging of default datas on loading Microsoft Chart ActiveX Control, so I beg your pardon and waiting through temporary emerging of the diagram with datas inserted into his by default. After complete pagein they will be updated on the valid applied datas.

The disposition of the diagram in control area seems peculiar a little as a size of area on page assigned under the diagram. Hm... such disposition was selected by me after long experiments with screen resolution 800x600 pic so that the signatures for a horizontal axes did not disappear.

To look work of an example
Go top

If you have the software indicated in section Software as installed, to look "Statistics of Visitings" example stat_e.html click here.

The example (shapetest.prg) shows, that VFPOLEDB VFP 8.0 (SP1VFP8) [vfpoledb.dll (8.0.0.3117)] supports work with MSDataShape Provider, i.e. allows to receive hierarchical recordsets. The result of query to \samples\data\testdata.dbc (Customer.dbf, Orders.dbf) is kept in a xml-format... And further will be transformed to a html-format using Microsoft � XML Core Services (MSXML) 4.0 (file trans.xslt contains XML -> HTML transformation of the received xml-file) vfpshape.zip (4,23KB) (VFP ver: 8 SP1VFP8)

The literature
Go top

As introduction in a theme I can advise those books:

• Microsoft XML (Step by Step). Michael J.Young, Microsoft, 2000.
• Web Database Development(Step by Step). Jim Buyens. Microsoft, 2001.
• <html><scipt language= JavaScript Essentials. Jason J. Manger. Osborne McGraw-Hill, 1997.
• HOWTO: Convert FoxPro Cursor into XML Data Format (Q191758)

In all respects to this web-site, visit page: � Michael Drozdov, 2000-2005. All rights reserved. Last change of page: 08.14.2005