Xataface Forums

I have been looking for some time and am delighted to find Dataface. My question is twofold: (1) I have users who need to generate weekly reports and (2) a manager who needs to print the output of those reports to some format for their weekly briefing. There are five categories -> Lab Reports, Test Results, etc. which has subcategories. The users are divided into twelve unique areas -> County1, County2, etc. which have no subcategories.

Each user would login to the application and complete their weekly report -> County1 would complete Lab Reports, Test Results, etc. there would be totals for all twelve users for the week in each of these categories. Example: County1 Lab Reports + County2 Lab Reports, etc.

Question: (1) Any thoughts on how to go about setting up my table structure and (2) How does one go about printing or outputting the information by week into a viable report (cvs, pdf, etc.).

TIA for any assistance,

Steve

My advice would depend greatly on what it means to "generate weekly reports".Ê Does this mean that they are entering data that is considered a report.Ê Or does it mean that they are performing some function on existing data.

I would design the table structure to model the data that is being stored/entered in the system.Ê Reports can be generated from that data, but the format of the reports shouldn't really have an impact on the data model.Ê Sometimes it is beneficial to create separate tables to store reports, if the report is a tabular format.

As for outputting the data in csv, pdf, etc.. - dataface has an action for csv output (see icon in upper right of any result list).Ê For PDF there are quite a few PHP libraries around for generating PDF files.Ê You could create a custom action, and use one of these libraries to output a PDF file.

-Steve

Thanks for the reply but I would appreciate any additional clarification. I have a spreadsheet with 16 columns and 35 rows. Each value will be a number with the last column being a total of the previous 15 columns. This spreadsheet is completed weekly by one of 15 nurses (columns) to report the number of clients for services.

I first constructed a mock-up database and front end which can be viewed at: http://167.154.2.203/dataface/CHN/ . Before I go any further I wonder if it would be better to try a submission form to accomplish the same task. One issue is that the users (nurses) may want to go back in and make a change to one of their entries.

The final goal is to have the manager go into the application at the end of the week and generate a report on the number of clients served which is the total of the column 16.

Any thoughts on which way might best accomplish the end result.

TIA,

Steve

Hi Steve,

It is still a little difficult to offer good advice because the question is a little open-ended.Ê The way I look at this problem is in 2 parts:

1. Data entry

2. Generating reports

On which of these two areas are you looking for advice.Ê Looks like you have already created your table structure, and I'm assuming that you know the data that you are modeling better than I, so there isn't alot of advice I can give on the first section, other than answering more specific technical questions on how to achieve objectives (relating to data entry).

As for generating reports.Ê The Dataface search/find features allow you to search for records based on various criteria but it doesn't show you any totals.

If you simply want to get the totals for some columns of a table, you could just add a block after the result list, generate some totals there and display them.

e.gÊ Here is an untested (i.e. it will probably give you errors at first that you'll have to iron out) function that is meant to display totals for all integer and float columns in the current table's found set.Ê This function is meant to be added to a delegate class.Ê The title of the function indicates that its output is to be placed in a block named after_result_list.Ê The result list (the list view) template has a block by this name so the contents will be placed after the list in list view.
function block__after_result_list(){
ÊÊÊ $app =& Dataface_Application::getInstance();
ÊÊÊ $query = & $app->getQuery();
ÊÊÊ $table =& Dataface_Table::loadTable($query['-table']);
ÊÊÊ
ÊÊÊ
ÊÊÊ $columns = array();
ÊÊÊ foreach ( array_keys($table->fields(false,true)) as $field ){
ÊÊÊ ÊÊÊ if ( $table->isInt($field) or $table->isFloat($field) ){
ÊÊÊ ÊÊÊ ÊÊÊ $columns[] = "sum(`$field`) as `$field`";
ÊÊÊ ÊÊÊ }
ÊÊÊ }
ÊÊÊ
ÊÊÊ $qb = new Dataface_QueryBuilder($query['-table'], $query);
ÊÊÊ $orig_select = $qb->select();
ÊÊÊ
ÊÊÊ $sql = "select ".implode(', ', $columns)." ".substr($orig_select, strpos($orig_select, 'from'));
ÊÊÊ
ÊÊÊ $res = df_query($sql);
ÊÊÊ echo "

Totals:

ÊÊÊ
ÊÊÊ ÊÊÊ ";
ÊÊÊ foreach ($columns as $column){
ÊÊÊ ÊÊÊ $field = $table->getField($column);
ÊÊÊ ÊÊÊ echo "";
ÊÊÊ }
ÊÊÊ echo "
ÊÊÊ ";
ÊÊÊ
ÊÊÊ while ($row = mysql_fetch_assoc($res) ){
ÊÊÊ ÊÊÊ echo "";
ÊÊÊ ÊÊÊ foreach ($columns as $column){
ÊÊÊ ÊÊÊ ÊÊÊ echo "";
ÊÊÊ ÊÊÊ }
ÊÊÊ ÊÊÊ echo "";
ÊÊÊ }
ÊÊÊ echo "

".$field['widget']['label']."
".htmlspecialchars($row[$column])."

";
ÊÊÊ
ÊÊÊ @mysql_free_result($res);
}
If you need more advanced reporting, you may want to create a custom action.Ê See the getting started tutorial for information on creating custom actions.
-Steve