Location: PHPKode > scripts > xDIF > xDif.class.php
<?php
/*
 * xDif Class
 * @author David Doran
 * @visit http://www.daviddoran.org
 *
 * This class is used to convert an array to the
 * DIF (Data Interchange Format) which is recognized
 * by many spreadsheet apps such as Microsoft Office.
 *
 * Supplied with headers(ARRAY) and rows(ARRAY) it can
 * create DIF output.
 * Also, supplied with a Database Connection and Query Result
 * it will create a DIF file from a query result for MySQL, PostgreSQL, Mssql and Ibase/Firebird.
 * {{Currently only MySQL has been tested}}
 */
class xDif
{
        /**
         * Class properties
         * type_* variables are for data column types
         */
        var $type_num = 'NUM';
        var $type_na = 'NA';
        var $type_error = 'ERROR';
        var $type_true = 'TRUE';
        var $type_false = 'FALSE';
        var $type_string = 'STRING';

        /**
         * xDif Function Declaration
         * @param array  $headers   Spreadsheet Column Headers
         * @param array  $input     Spreadsheet data rows
         * @param string $title     Optional title for spreadsheet
         * $param string $linebreak Linebreak setting, eg "\n" or "\r" or "\r\n"
         * @return void
         * @access public
         */
        function xDif($headers=null,$input=null,$title=null,$linebreak=null)
        {
                if(!is_null($headers) && !is_null($input))
                {
                        $this->xDif = $this->arrayToDif($headers,$input,$title,$linebreak);
                }
        }

        /**
         * arrayToDif; Main function for converting
         * @param array  $headers   Spreadsheet Column Headers
         * @param array  $input     Spreadsheet data rows
         * @param string $title     Optional title for spreadsheet
         * $param string $linebreak Linebreak setting, eg "\n" or "\r" or "\r\n"
         * @return string
         * @access public
         */
        function arrayToDif($headers,$input,$title="xDifClassOutput",$linebreak="\r\n")
        {
                $this->newline = $linebreak;
                $rows = count($input)+1;
                $cols = count($headers);

                $BUFFER = '';
                $BUFFER .= $this->nl( $this->__begin() );
                $BUFFER .= $this->nl( $this->__headerVersion() );
                $BUFFER .= $this->nl( $this->__quote($title) );
                $BUFFER .= $this->nl( $this->__vectorHeader() );
                $BUFFER .= $this->nl( '0,'.$rows );
                $BUFFER .= $this->nl( $this->__quote('') );
                $BUFFER .= $this->nl( $this->__tupleHeader() );
                $BUFFER .= $this->nl( '0,'.$cols );
                $BUFFER .= $this->nl( $this->__quote(''));
                $BUFFER .= $this->nl( $this->__dataHeader() );
                $BUFFER .= $this->nl( '0,0' );
                $BUFFER .= $this->nl( $this->__quote('') );
                 $BUFFER .= $this->__tupleList($headers,$input);
                $BUFFER .= $this->nl( '-1,0' );
                $BUFFER .= $this->nl( $this->__eod() );
                return( $BUFFER );
        }

        /**
         * mysqlToDif; Main function for converting Mysql to DIF
         * @param resource  $conn   Database Connection
         * @param resource  $res    Mysql Query Result
         * @param string $title     Optional title for spreadsheet
         * $param string $linebreak Linebreak setting, eg "\n" or "\r" or "\r\n"
         * @return string
         * @access public
         */
         function mysqlToDif(&$conn,&$res,$title=null,$linebreak="\r\n")
         {
                 $headers = array();
                 $input = array();
                 while($row = mysql_fetch_assoc($res))
                 {
                         $headers = array_keys($row);
                         $input[] = $row;
                 }
                 return($this->arrayToDif($headers,$input,$title,$linebreak));
         }

        /**
         * ibaseToDif; Main function for converting Ibase to DIF
         * @param resource  $conn   Database Connection
         * @param resource  $res    Mysql Query Result
         * @param string $title     Optional title for spreadsheet
         * $param string $linebreak Linebreak setting, eg "\n" or "\r" or "\r\n"
         * @return string
         * @access public
         */
         function ibaseToDif(&$conn,&$res,$title=null,$linebreak="\r\n")
         {
                 $headers = array();
                 $input = array();
                 while($row = ibase_fetch_assoc($res))
                 {
                         $headers = array_keys($row);
                         $input[] = $row;
                 }
                 return($this->arrayToDif($headers,$input,$title,$linebreak));
         }

        /**
         * mssqlToDif; Main function for converting MSsql to DIF
         * @param resource  $conn   Database Connection
         * @param resource  $res    Mysql Query Result
         * @param string $title     Optional title for spreadsheet
         * $param string $linebreak Linebreak setting, eg "\n" or "\r" or "\r\n"
         * @return string
         * @access public
         */
         function mssqlToDif(&$conn,&$res,$title=null,$linebreak="\r\n")
         {
                 $headers = array();
                 $input = array();
                 while($row = mssql_fetch_assoc($res))
                 {
                         $headers = array_keys($row);
                         $input[] = $row;
                 }
                 return($this->arrayToDif($headers,$input,$title,$linebreak));
         }

        /**
         * pgToDif; Main function for converting PostgreSQL to DIF
         * @param resource  $conn   Database Connection
         * @param resource  $res    Mysql Query Result
         * @param string $title     Optional title for spreadsheet
         * $param string $linebreak Linebreak setting, eg "\n" or "\r" or "\r\n"
         * @return string
         * @access public
         */
         function pgToDif(&$conn,&$res,$title=null,$linebreak="\r\n")
         {
                 $headers = array();
                 $input = array();
                 while($row = pg_fetch_assoc($res))
                 {
                         $headers = array_keys($row);
                         $input[] = $row;
                 }
                 return($this->arrayToDif($headers,$input,$title,$linebreak));
         }

        /**
         * nl Function Declaration
         * @param string  $in   String to add a newline to
         * @return string
         * @access public
         */
        function nl($in)
        {
                return($in.$this->newline);
        }

        /**
         * __begin Function Declaration
         * @return string
         * @access private
         */
        function __begin()
        {
                return('TABLE');
        }

        /**
         * __headerVersion Function Declaration
         * @return string
         * @access private
         */
        function __headerVersion()
        {
                return('0,1');
        }

        /**
         * __quote Function Declaration
         * @param string $strToQuote
         * @return string
         * @access private
         */
        function __quote($strToQuote)
        {
                return('"'.str_replace('"','""',$strToQuote).'"');
        }

        /**
         * __vectorHeader Function Declaration
         * @return string
         * @access private
         */
        function __vectorHeader()
        {
                return('VECTORS');
        }

        /**
         * __tupleHeader Function Declaration
         * @return string
         * @access private
         */
        function __tupleHeader()
        {
                return('TUPLES');
        }

        /**
         * __dataHeader Function Declaration
         * @return string
         * @access private
         */
        function __dataHeader()
        {
                return('DATA');
        }

        /**
         * __tupleList Function Declaration
         * @param array $headers
         * @param array $input
         * @return string
         * @access private
         */
        function __tupleList($headers,$input)
        {
                $Tlist = '';
                $Tlist .= $this->__tuple($headers);
                foreach($input as $tuple)
                {
                        $Tlist .= $this->__tuple($tuple);
                }
                return($Tlist);
        }

        /**
         * __tuple Function Declaration
         * @param string $row
         * @return string
         * @access private
         */
        function __tuple($row)
        {
                $Tuple = '';
                $Tuple .= $this->nl( '-1,0' );
                $Tuple .= $this->nl( 'BOT' );
                foreach($row as $field)
                {
                        $Tuple .= $this->__tupleValue($field);
                }
                return($Tuple);
        }

        /**
         * __ofType Function Declaration
         * @param mixed $field
         * @return string
         * @access private
         */
        function __ofType($field)
        {
                if(is_int($field) || is_float($field))
                {
                        return($this->type_num);
                }
                if(is_null($field))
                {
                        return($this->type_na);
                }
                if($field===TRUE)
                {
                        return($this->type_true);
                }
                if($field===FALSE)
                {
                        return($this->type_false);
                }
                if($this->__isError($field))
                {
                        return($this->type_error);
                }
                else
                {
                        return($this->type_string);
                }
        }

        /**
         * __isError Function Declaration
         * @param string $data
         * @return boolean
         * @access private
         */
        function __isError($data)
        {
                //Replace with Error Check, return TRUE
                return(FALSE);
        }

        /**
         * __tupleValue Function Declaration
         * @return string
         * @access private
         */
        function __tupleValue($data)
        {
                $Tvalue = '';
                $type = $this->__ofType($data);
                switch($type)
                {
                        case $this->type_num:
                        {
                                $Tvalue .= $this->nl( '0,'.$data );
                                $Tvalue .= $this->nl( 'V' );
                                break;
                        }
                        case $this->type_na:
                        {
                                $Tvalue .= $this->nl( '0,0' );
                                $Tvalue .= $this->nl( 'NA' );
                                break;
                        }
                        case $this->type_true:
                        {
                                $Tvalue .= $this->nl( '0,1' );
                                $Tvalue .= $this->nl( 'TRUE' );
                                break;
                        }
                        case $this->type_false:
                        {
                                $Tvalue .= $this->nl( '0,0' );
                                $Tvalue .= $this->nl( 'FALSE' );
                                break;
                        }
                        case $this->type_error:
                        {
                                $Tvalue .= $this->nl( '0,0' );
                                $Tvalue .= $this->nl( 'ERROR' );
                                break;
                        }
                        case $this->type_string:
                        {
                                $Tvalue .= $this->nl( '1,0' );
                                $Tvalue .= $this->nl( $this->__quote($data) );
                                break;
                        }
                }
                return($Tvalue);
        }

        /**
         * toFile Function Declaration
         * Output a string to a file, for outputting DIF file.
         * @return string
         * @access private
         */
        function toFile($outputFile='difOUTPUT.out.txt',$string)
        {
                @ $fHandle = fopen($outputFile,'w');
                if($fHandle)
                {
                        $fWriteInt = fwrite($fHandle,$string);
                        if($fWriteInt!=FALSE)
                        {
                                return(true);
                        }
                        else
                        {
                                return(false);
                        }
                        return(true);
                }
                else
                {
                        return(false);
                }
        }

        /**
         * __eod Function Declaration
         * @return string
         * @access private
         */
        function __eod()
        {
                return('EOD');
        }
}
Return current item: xDIF