e-Zvit
Reports processing server.


Introduction

"e-Zvit" reporting server is an information system that allows you to organize process of sending in, saving and analizing of reporting forms. Any tall structure of reporting organizations can use "e-Zvit" system for working with forms made in Microsoft Excel(with the XLS extension). The system processes reporting forms those was sent by e-mail, uploaded as a file or filled in on-line.

Make your reporting form as a Microsoft Excel(or OpenOffice Calc) electronic worksheet. Enter your variables to all fields those reporting users are to fill. Names of the variables must be entered in braces -{}- and consists from numerals and latin letters. For example, enter {A35} to the A35 cell. Click the icon "Add" in the "Our company forms" section and upload your new form.

Icons
Opens a new window for adding new data to the "Dictionaries" section
Find information at this page.
Add new data to the section.
Send checked reports in.
Making the resulting report.
Printing

Data sorting

Data in the tables can be sorted. You need only click the necessary table heading.

Data editing

To edit some information in your database (for example, news, articles, a name in the list etc), click it's heading. Then make your changes and save them.

Reports

There you can find the reporting forms you are to fill. Using the system you can send in your reports in three ways:

1) Download the file of your report. Open it in an electronic worksheet program and fill it in. Then upload the file with data to the "Sent reports" section.

2) Also you can fill your report in on-line in the "Fill report" section. In the same way you can edit sent reports.

3) The reports can be sent in by e-mail.
Fill the reporting form. Then click the "Add" icon in the "Sent Reports" section and upload the file. The file name must look as:
file name_your login.xls
For example, the name of form is ../archive/blanks/zvit_2004-10-28.xls, your login is "director", so the file of report is
zvit_2004-10-28_director.xls

There you can upload repoting forms those are to fill and send in by the subordinate organizations .
To add new form:

  1. click the icon
  2. in the appeared window enter the name of your report, upload the file with the XLS extension and fill another fields.
  3. select organizations those are to send the report in.
  4. save the data.

There you can fill in the reports you are to send in.
Enter values to the empty fields. Press the button "Examin report" to see how the complete report looks like. If you gues that your report filled correctly and can be sent at the moment, press the button "Send report".

Reporting forms of your company and all ones those were sent you from subordinate organizations.
There you can edit sent reports on conditions that they weren't locked.

Locked reports

The value of a global variable is the result of SQL-query making, so it applies some restrictions on editing of "donor" reports which was used for tallying up a total. A report is available for editing if it's data haven't been used before. If data from a report form was used for another one the first form should be locked. If a report, contained data from another reports, is deleted, all equipped forms will be automatically unlocked.

There you can find the list of subordinate organizations those delay sending in of their reports or sent it at the wrong time.


Report analyzis

The analyzis can be made on the basis of reports from subordinate organizations.
Getting results:

Let's make the resulting form "report 1 - results", that contains data from "report 1" forms.

Let the basic reporting form is an execel file (picture.1). And we'll get results in the form of picture 2.

Report "report 1 " picture 1
Resulting report "report 1 - results" picture 2

We want to get the sum of all values from the "B1" ({field1}) cell of the basic reports. The sum will be dysplayed in the "B1" ({results_sum})cell of the resulting report. The "B2" ({results_count}) cell contains a value of a number of the basic reports.

Let's create the "results_sum" and "results_count" global variables. Having examined the structute of the database you can make SQL query to the main database.

results_sum select sum(f1.field_value) from form_values f1 where f1.field='field1'
results_count select count(f1.field_value) from form_values f1 where f1.field='field1'

These queries select from the database the sum of the "B1" ({field1}) cell values, those the "report 1" reporting forms contains.

Now you can get the resulting report. Enter the "Results" section.
With the help of the system filter , select sent repots those you're going to use,
to get your resulting report.

The global variables (GV) are defined in the corresponding section. Their names must be unique.
There are some predefined global variables in the system. Another GP take on values those are the result of a SQL query to the database. For example,

Constants

select '112233'
returns the string 112233

select DATE_FORMAT(CURRENT_DATE(),'%d/%m/%Y')
returns the current date in the format'%d/%m/%Y'

To create a global variable.
1.click the icon
2. put to the apeared window name of your new variable and your comments;
3. Choose by turns necessary variables(from received reports) from the list in the bottom part of the window and group functions for them and put them to the "SQL query" field.
4. Define type of the database connection.

There you can create new variables those values are some mathematical actions between all of the variables entered to the system (from the reporting forms for subordinate organizations, global variables and created at the "Expressions" section ones).
Example:
We have received three filled in reporting forms "Small test".
Also we have the resulting form where the value of {results_sum} global variable is the sum of all values from the "B1" ({field1}) cell of the basic reports and the {results_count} global variable defines a number of received the basic reports.
Let's create a new variable for calculation of average value from received data.

  1. Click the "Add" icon.
  2. Put to the apeared window name of your new variable and your comments.
  3. Choose necessary variables from the list in the bottom part of the window, put them to the "Expression" field and add necessary operators.
  4. As a result we have the following expression:
    "{results_sum}/{results_count}"
  5. To examine the value of expression press the button "Evaluate expression".

As the value of any global variable is the result of SQL query making, it applies some restrictions on editing of reports which data were used for creation of another ones. A report is avaliable for editing till it's data haven't been used. If data from a reporting form are used for another one the first form will be locked. If a report contained data from another reports, was deleted, all equipped forms will be automatically unlocked.

To analise the results received from the reporting organizations, create a form for results. The form for results doesn't differ from the basic reporting form. The only thing that must be considered it's using of global variables, which describes as SQL-query in the appropriate section.

Gainning results from the sent reports

To get results from the sent forms you need make a query to the table with the necessary data.
Table structure form_values

`id` int(10) NOT NULL auto_increment, - key. Number of an entry.
`id_form` int(10) NOT NULL default '0', - form identifier
`id_blank` int(10) NOT NULL default '0', - report identifier
`field` varchar(50) NOT NULL default '', - field name(in a reporting form)
`field_value` varchar(250) NOT NULL default '', - field value(in the resultig report)
PRIMARY KEY (`id`)
)
ò.î.
select sum(form_values.field_value) from form_values where
form_values.field='field1'
returns the sum of all "field1" values. The "Results" section is meant for filtration of your reports. In this section you can sort the reports by types,organizations or the period.

Remarks.

For correct using of the soft you should work out your system of variable-name assignment. For example, {zvit_2004-10-28_03_05} - to define the 05 cell in the third part of the zvit_2004-10-28 form. Names of variables can be the same. In this case you'll be abble to collect data from a few different reports.But you shouldn't abuse this property as you may get a false result.

"e-Zvit" system provides a possibility of connection with existing databases. You can connect to the following databases:
postgresql, mssql,interbase, informix, odbc, mysql, oracle, peardb, adodb
To have a possibility to connect another database, you should define configuration parameters(as $oArray elements) in the corresponding file of the /database catalogue.

Administration

For the avoidance of leakage, the "Administration" section must be run by trusted user.

"e-Zvit" access system makes in compliance with organization's position in the general hierarchical system of the companies taking part in "e-Zvit". It means that two users from the same organisation have the same rights. Information,got from some organization, is available only for the parent ones.

The tall structure of organizations taking part in the e-Zvit system is the tree-type. It means that an organization can have a number of subordinate ones but only a single parent company. Such a structure allows the reporting system to serve a few "trees" don't bind with the same root.

Analisis of reports sent by e-mail can be made by means of system demon Cron (or another sheduler making system tasks)
All PHP files those must be made at regular intervals, are gathered in /cron catalogue
See below files' description and periodicity.

File name Functions Periodicity
check_email.php 1) Checks the mailbox
2) Process sent reports
3) Make log
4) Clear the mailbox
twise in a day, for example at 11-00 and 15-00 a.m.
debtors.php 1) Checks report terms
2) Defines the "Next term" for the reports
3) Make lists of debtors
twice in a month, for example on 10 and 20 days
mail_remainder.php 1) Mailing dunnings 3 days before ending of the next report period. 2) Mailing the list of debtors to the higher organizations once in a day

In the "mailbox Log" section you can find statistical data about e-mails processing, mailing of dunnings, acceptance or refusal of the reports.

if you want to expand system capabilities (add new module, chanage text to a link etc.) you shold use the "Links/rigts" section.

1) If you want to make a simple text link (as "Reports"), click the "add" icon and fill in the fields in the appeared window.



System requires.
To install "e-Zvit" in your local network you need: Server: OS - Windows 98+/Linux/Unix web-server: Apache 1.0+ PHP
Mysql
Sendmail
xlhtml
xlhtml 0.4.9.0 converts excel files (.xls) to HTML and XML.
http://chicago.sourceforge.net/xlhtml/
Cron
Work station: web-browser Opera 5+,Mozilla 1.2.1
Special mailbox to accept the reports.
If all of the system requires are concidered, make following:

The system users can work in the interfaice in native language if the administrator add lokalization. To add a new language: