Changing order in the summary.php

[MartinLev]

Hi again!
 

Another easy one for expert!

 

I can change the sort order of the summary.php page by changing the « Ascent » parameter to « Descent ». The problem is that records are sort correctly by date (most recent to last), but inside a date section, the are sort by least recent first. In other word, all records that share the same modification date appear in the reverse order.

 

I guess I should implement a timestamp field in my DB. But I need help to sort with two fields at the same time in order to get this working properly... Any clue on the php code I should add in the summary.php file?

 

Martin

[webko]

Post the php code please... I don't have a copy of summary.php

[MartinLev]

Mmm...

 

After thinking a lot, I figure it out : FM is sorting the records by date (to create the « block » in the summary report), but don't sort the records that share the same date (because it doesn,t have any clue to know which record was entered first in the database).

 

I tried to use « Timestamp dateand time » to track the record to the second (and to know in which ordre they have been modified), but now, I can't sort by timestamp because the code provided by the PHP assistant will created a block for each record (because they all have a diffrent modification timestamp).

 

So : How can sort by date (as per the code of summary.php) and after resort them by timestamp?

 

Here the code of summary.php

 

<?php

/**
    * FileMaker PHP Site Assistant Generated File
    */
require_once 'fmview.php';
require_once 'FileMaker.php';

// Summary Report

interface Summary {
    public function update(FileMaker_Record $eachRecord);
    public function getData();
};
abstract class PrintSummary {
    public function getGroupLabel() {
        return "Group Summary";
    }
    public function getGrandLabel() {
        return "Grand Summary";
    }
    public function printGroupSummary($colSpanVal) {
        echo "<tr><td class='group_total_title'>" . $this->getGroupLabel() . ":</td><td class='group_total' colspan=".($colSpanVal-1).">" . $this->getData() . "</td></tr>";
    }
    public function printGrandSummary($colSpanVal) {
        echo "<tr><td class='grand_total_title'>". $this->getGrandLabel() . ":</td><td class='grand_total' colspan=".($colSpanVal-1).">" . $this->getData() . "</td></tr>";
    }
};
class Total extends PrintSummary implements Summary{
    private $_summaryField;
    private $_total;

    public function getGroupLabel() {
        return "Group Total";
    }
    public function getGrandLabel() {
        return "Grand Total";
    }
    function __construct($summaryField) {
        $this->_summaryField = $summaryField;
    }
    public function update(FileMaker_Record $eachRecord) {
        $this->_total += $eachRecord->getField($this->_summaryField);
    }
    public function getData() {
        return $this->_total;
    }
};

class Count extends PrintSummary implements Summary{
    private $_summaryField;
    private $_count;

    public function getGroupLabel() {
        return "Decompte";
    }
    public function getGrandLabel() {
        return "Total des fiches";
    }
    function __construct($summaryField) {
        $this->_summaryField = $summaryField;
        $this->_count = 0;
    }
    public function update(FileMaker_Record $eachRecord) {
        $this->_count++;
    }
    public function getData() {
        return $this->_count;
    }
};

class Median extends PrintSummary implements Summary{
    private $_summaryField;
    private $_values = array();
    private $_count;
        
    public function getGroupLabel() {
        return "Group Median";
    }
    public function getGrandLabel() {
        return "Overall Median";
    }
    function __construct($summaryField) {
        $this->_summaryField = $summaryField;
        $this->_count = 0;
    }
    
    public function update(FileMaker_Record $eachRecord) {
        $currentValue = $eachRecord->getField($this->_summaryField);
        array_push($this->_values, $currentValue);
        $this->_count++;
    }
    
    public function getData() {
        sort($this->_values);
        if ($this->_count%2 == 0) {
            $lowmedian = $this->_values[$this->_count/2-1];
            $highmedian = $this->_values[$this->_count/2];
            $median = ($lowmedian + $highmedian)/2;
        } else {
            $median = $this->_values[($this->_count-1)/2];
        }
        
        return $median;
    }
};
class Average extends PrintSummary implements Summary{
    private $_summaryField;
    private $_count;
    private $_total;

    public function getGroupLabel() {
        return "Group Average";
    }
    public function getGrandLabel() {
        return "Overall Average";
    }
    function __construct($summaryField) {
        $this->_summaryField = $summaryField;
        $this->_count = 0;
    }
    public function update(FileMaker_Record $eachRecord) {
        $currentValue = $eachRecord->getField($this->_summaryField);
        $this->_total += $currentValue;
        $this->_count ++;
    }
    public function getData() {
        return $this->_total / $this->_count;
    }
};

class Report {
    private $_groupField;
    private $_summaryField;
    private $_summaryFunction;
    private $_numberTableColumns;
    private $_firstGroup = true;
    private $_totalName = "Total";
    private $_averageName = "Average";
    private $_medianName = "Median";
    private $_countName = "Count";

    function makeSummary() {
        switch ($this->_summaryFunction) {
            case $this->_totalName:
                return new Total($this->_summaryField);
                break;

            case $this->_averageName:
                return new Average($this->_summaryField);
                break;

            case $this->_medianName:
                return new Median($this->_summaryField);
                break;

            case $this->_countName:
                return new Count($this->_summaryField);
                break;
                
            default:
                return new Count($this->_summaryField);
                break;
        }
    }

    function Report($groupField, $summaryField, $summaryFunction, $numberTableColumns) {
        $this->_groupField = $groupField;
        $this->_summaryField = $summaryField;
        $this->_summaryFunction = $summaryFunction;
        $this->_numberTableColumns = $numberTableColumns;
    }

    private function startGroup($groupData, $colspanVal) {
        $displayDateFormat = '%d/%m/%Y';
        $displayTimeFormat = '%I:%M:%S %P';
        $displayDateTimeFormat = '%d/%m/%Y %I:%M:%S %P';
    
        $this->_firstGroup = false;
        $fieldObj = $groupData->getLayout()->getField($this->_groupField);
        $resultType = $fieldObj->getResult();
        $fieldVal = $groupData->getField($this->_groupField);
        
        
        if ($resultType == "date")
            echo "<tr><td class='group_header' colspan=".$colspanVal.">" . displayDate($fieldVal, $displayDateFormat) . "</td></tr>";
        else if ($resultType == "time")
            echo "<tr><td class='group_header' colspan=".$colspanVal.">" . displayTime($fieldVal, $displayTimeFormat) . "</td></tr>";
        else if ($resultType == "timestamp")
            echo "<tr><td class='group_header' colspan=".$colspanVal.">" . displayTimeStamp($fieldVal, $displayDateTimeFormat) . "</td></tr>";
        else
            echo "<tr><td class='group_header' colspan=".$colspanVal.">" . str_replace(' ','&nbsp; ',$fieldVal) . "</td></tr>";
    }

    private function finishGroup($groupSummary, $colspanVal) {
        if ($this->_firstGroup === false) {
            $groupSummary->printGroupSummary($colspanVal);
        }
    }

    public function printReport($records, $fields, $layout) {

        $lastSeenGroup = null;
        $grandSummary = $this->makeSummary();
        $groupSummary = $this->makeSummary();
        foreach ($records as $eachRecord) {
            $grandSummary->update($eachRecord);
            if ($eachRecord->getField($this->_groupField) != $lastSeenGroup) {
                $lastSeenGroup = $eachRecord->getField($this->_groupField);
                $this->finishGroup($groupSummary,$this->_numberTableColumns);
                $groupSummary = $this->makeSummary();
                $this->startGroup($eachRecord, $this->_numberTableColumns);
            }
            $groupSummary->update($eachRecord);
            echo "<tr class='table_row'>";
            echo "<td class='browse_cell'></td>";
            foreach ($fields as $eachFieldName) {
                if (($eachFieldName != $this->_groupField) && ($eachFieldName != $this->_summaryField)) {
                    
                    if (isPortalField($eachRecord, $eachFieldName)) {
                        $colonPos = strpos($eachFieldName, "::");
                        $tableName = substr($eachFieldName, 0, $colonPos);
                        $relatedRecords = $eachRecord->getRelatedSet($tableName);
                        $portal = $layout->getRelatedSet($tableName);
                        getTableRows($portal, $relatedRecords, $eachFieldName);
                    }
                    else{
                        formatFieldData($eachRecord, $layout, $eachFieldName);
                    }
                }        
            }
            //Assumed that a summary field cannot be a portal field.
            formatFieldData($eachRecord, $layout, $this->_summaryField);
            echo "</tr>";
        }
        $this->finishGroup($groupSummary,$this->_numberTableColumns);
        $grandSummary->printGrandSummary($this->_numberTableColumns);
    }
};    // Report

function getTableRows($layout, $records, $fieldName) {
    
    if ($records != null && is_array($records) && (FileMaker::isError($records) === false)){
        $record = $records[0];
        formatFieldData($record, $layout, $fieldName);
    }
    else{
        echo "<td class='browse_cell'></td>";
    }
    
}

//     Formats field data

function formatFieldData($records, $layout, $fieldName) {
    if (FileMaker::isError($records) === false) {
        $field = $layout->getField($fieldName);
        if (FileMaker::isError($field) === false) {
            $resultType = $field->getResult();
            $isRepeating = $field->getRepetitionCount() > 1;
            
            if($resultType == 'container'){
                echo "<td class='browse_cell center'>";
            }
            else if($resultType == 'number'){
                echo "<td class='browse_cell right'>";
            }
            else{
                echo "<td class='browse_cell left'>";
            }
            
            if ($isRepeating) {
                echo "<div class='repeating'>";
            }
            for ($i = 0; $i < $field->getRepetitionCount(); $i++) {
                if (is_array($records))
                    formatFieldRepetition($records[0], $field, $isRepeating, $i);
                else
                    formatFieldRepetition($records, $field, $isRepeating, $i);
            }
            if ($isRepeating) {
                echo "</div>";
            }
            
            echo "</td>";
        }
    }
}

function formatFieldRepetition($record, $field, $isRepeating, $repetition) {
    
    // formats for dates and times
    $displayDateFormat = '%d/%m/%Y';
    $displayTimeFormat = '%I:%M:%S %P';
    $displayDateTimeFormat = '%d/%m/%Y %I:%M:%S %P';
    
    $resultType = $field->getResult();
    $fieldName = $field->getName();
    switch($resultType){
        case 'container':
            $recimagedata = $record->getField($fieldName, $repetition);
            if ($recimagedata != NULL) {
                echo ("<img src='img.php?-url=" . urlencode($recimagedata) . "'/>");
            }
            break;
        case 'date':
            echo displayDate($record->getField($fieldName, $repetition), $displayDateFormat);
            break;
        case 'time':
            echo displayTime($record->getField($fieldName, $repetition), $displayTimeFormat);
            break;
        case 'timestamp':
            echo displayTimeStamp($record->getField($fieldName, $repetition), $displayDateTimeFormat);
            break;
        default:
            echo str_replace(' ', '&nbsp; ',$record->getField($fieldName, $repetition));
            break;
    }
}

?>
 

and the code of report.php

 

 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<?php

    /**
    * FileMaker PHP Site Assistant Generated File
    */

    require_once 'fmview.php';
    require_once 'FileMaker.php';
    require_once 'error.php';

    $cgi = new CGI();
    $cgi->storeFile();
    
    $databaseName = 'XXXXXXX';
    $layoutName = 'Sommaire Complet';

    $userName = $cgi->get('userName');
    $passWord = $cgi->get('passWord');

    $fm = & new FileMaker();
    $fm->setProperty('database', $databaseName);
    $fm->setProperty('username', $userName);
    $fm->setProperty('password', $passWord);
    
    ExitOnError($fm);
    $layout = $fm->getLayout($layoutName);
    ExitOnError($layout);

    // formats for dates and times
    $displayDateFormat = '%d/%m/%Y';
    $displayTimeFormat = '%I:%M:%S %P';
    $displayDateTimeFormat = '%d/%m/%Y %I:%M:%S %P';
    $submitDateOrder = 'mdy';
    require_once 'summary.php';

    $record = NULL;
    $findCom = NULL;
    
    // get the report attributes

    $groupField = 'Date de modification';
    $summaryFunction = 'Count';
    $summaryField = 'Demande de suivi';

    // handle the cgi
    $action = $cgi->get('-action');
    switch ($action) {
        case "findall": {
           $cgi->clear('skip');
           $findCom = & $fm->newFindAllCommand($layoutName);
           break;
        }
        case "find": {
        // clear the recid
           $cgi->clear('recid');

        // create a find command
           $findCommand = $fm->newFindCommand($layoutName);
           ExitOnError($findCommand);

        // get the posted record data from the findrecords page
           $findrequestdata = $cgi->get('storedfindrequest');
           if (isset($findrequestdata)) {
               $findCom = prepareFindRequest($findrequestdata, $findCommand, $cgi);

            // set the logical operator
               $logicalOperator = $cgi->get('-lop');
               if (isset($logicalOperator)) {
                       $findCom->setLogicalOperator($logicalOperator);
               }
           } else
               $findCom = $fm->newFindAllCommand($layoutName);
           break;
        }
        default: {
           $findCom = & $fm->newFindAllCommand($layoutName);
           break;
        }
    }
    ExitOnError($findCom);

    // read and set, or clear the sort criteria
    $sortfield = "Date de modification";
    if (isset($sortfield)) {
        $findCom->addSortRule($sortfield, 1, FILEMAKER_SORT_DESCEND);
    }

    // store the number of columns in this report table
    $numCols = 0;

    // get the skip and max values
    $skip = 0;

    // set skip and max values
    $findCom->setRange($skip, null);

    // perform the find
    $result = $findCom->execute();
    ExitOnError($result);

    // get the records
    $records = $result->getRecords();   
    $fields = array(
                    'ID',
                    'Date de modification',
                    'Type',
                    'Équipements',
                    'Description',
                    'Suivi',
                    'Demande de suivi',
                    'Techniciens::Initiales'
    );
    
?>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8">
        <title>
            Sommaire complet
        </title>
        <link rel="stylesheet" type="text/css" media="screen" href="viridis.css">
    </head>
    <body>
        <div id="header">
            <!--HEADER-->
            <div id="headerlogo">
                Log CCR
                <div id="headercaption">
                    <!--Caption for the Company-->
                </div>
            </div>
        </div>
        <div id="content">
            <!-- Navigation Menu -->
            <?php include_once 'navigation.php' ?><!-- PAGE BODY -->
            <table cellpadding="0" cellspacing="0" class="contentbg">
                <tr>
                    <td class="contentbgleft">
                    </td>
                    <td class="contentmidrecords">
                        <div id="contenttitlebg">
                            <h1>
                                Sommaire complet
                            </h1>
                        </div>
                        <table class="curvedbg">
                            <tr>
                                <td>
                                </td>
                            </tr>
                        </table>
                        <div class="scrolladd">
                            <table cellpadding="0" cellspacing="0" class="browse_records">
                                <thead>
                                    <tr>
                                        <th class="browse_header left">
                                            <?php $numCols++; echo str_replace(' ', '&nbsp; ',htmlentities('Date de modification',ENT_NOQUOTES,'UTF-8',false)); ?>
                                        </th>
                                        <th class="browse_header right">
                                            <?php $numCols++; echo str_replace(' ', '&nbsp; ',htmlentities('ID',ENT_NOQUOTES,'UTF-8',false)); ?>
                                        </th>
                                        <th class="browse_header left">
                                            <?php $numCols++; echo str_replace(' ', '&nbsp; ',htmlentities('Type',ENT_NOQUOTES,'UTF-8',false)); ?>
                                        </th>
                                        <th class="browse_header left">
                                            <?php $numCols++; echo str_replace(' ', '&nbsp; ',htmlentities('Équipements',ENT_NOQUOTES,'UTF-8',false)); ?>
                                        </th>
                                        <th class="browse_header left">
                                            <?php $numCols++; echo str_replace(' ', '&nbsp; ',htmlentities('Description',ENT_NOQUOTES,'UTF-8',false)); ?>
                                        </th>
                                        <th class="browse_header left">
                                            <?php $numCols++; echo str_replace(' ', '&nbsp; ',htmlentities('Suivi',ENT_NOQUOTES,'UTF-8',false)); ?>
                                        </th>
                                        <th class="browse_header left">
                                            <?php $numCols++; echo str_replace(' ', '&nbsp; ',htmlentities('Tech',ENT_NOQUOTES,'UTF-8',false)); ?>
                                        </th>
                                        <th class="browse_header left">
                                            <?php $numCols++; echo str_replace(' ', '&nbsp; ',htmlentities('Demande de suivi',ENT_NOQUOTES,'UTF-8',false)); ?>
                                        </th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                                            $report = new Report($groupField, $summaryField, $summaryFunction, $numCols);
                                                            $report->printReport($records, $fields, $layout);
                                                            ?>
                                </tbody>
                            </table>
                        </div>
                    </td>
                    <td class="contentbgright">&nbsp;
                        
                    </td>
                </tr>
                <tr>
                    <td class="contentbgfooterleft">&nbsp;
                        
                    </td>
                    <td class="contentfooter">&nbsp;
                        
                    </td>
                    <td class="contentbgfotterright">&nbsp;
                        
                    </td>
                </tr>
            </table>
        </div>
        <!-- Footer-->
        <table class="footerwidth" cellspacing="0" cellpadding="0">
            <tr>
                <td>
                    <?php include_once 'footer.php' ?>
                </td>
            </tr>
        </table>
    </body>
</html>