create_invalid_url_csv.php

<?php
require 'autoload.php';

$script = eZScript::instance( array( 'description' => ( "OpenPA Cambio password\n\n" ),
                                     'use-session' => false,
                                     'use-modules' => true,
                                     'use-extensions' => true ) );

$script->startup();

$options = $script->getOptions( '[where:][count][is-valid:][limit:][offset:][show]',
                                '',
                                array(
                                      'where' => 'sql where condition',
                                      'count' => 'count results',
                                      'is-valid' => 'interger valid match',
                                )
);
$script->initialize();
$script->setUseDebugAccumulators( true );

$cli = eZCLI::instance();

$limit = $options['limit'];
$offset = $options['offset'];

function createQuery( $whereCondition = false, $asCount = false, $isValid = null, $onlyPublished = false)
{        
    $conditions = array();
    if( $isValid === false ) $isValid = 0;
    if ( $isValid !== null )
    {
        $conditions['is_valid'] = $isValid;
    }
    if ( count( $conditions ) == 0 )
        $conditions = null;

    $conditionQuery = $whereCondition ? ' AND ' : '';
    if ( $isValid !== null )
    {
        $isValid = (int) $isValid;
        $conditionQuery .=  " ezurl.is_valid=$isValid ";
    }
    $db = eZDB::instance();
    $cObjAttrVersionColumn = eZPersistentObject::getShortAttributeName( $db, eZURLObjectLink::definition(), 'contentobject_attribute_version' );

    if ( $asCount )
    {
        if ( $onlyPublished )  // Only fetch published urls
        {
            $query =  "SELECT count( DISTINCT ezurl.id ) AS count
                                        FROM
                                             ezurl,
                                             ezurl_object_link,
                                             ezcontentobject_attribute,
                                             ezcontentobject_version
                                        WHERE 
                                             ezurl.id                                     = ezurl_object_link.url_id
                                         AND ezurl_object_link.contentobject_attribute_id = ezcontentobject_attribute.id
                                         AND ezurl_object_link.$cObjAttrVersionColumn     = ezcontentobject_attribute.version
                                         AND ezcontentobject_attribute.contentobject_id   = ezcontentobject_version.contentobject_id
                                         AND ezcontentobject_attribute.version            = ezcontentobject_version.version
                                         AND ezcontentobject_version.status               = " . eZContentObjectVersion::STATUS_PUBLISHED . "
                                         AND $whereCondition $conditionQuery";
        }
        else
        {
            $query = "SELECT count( DISTINCT ezurl.id ) AS count FROM ezurl WHERE $whereCondition $conditionQuery";
        }        
    }
    else
    {
        if ( $onlyPublished )  // Only fetch published urls
        {
            $query = "SELECT DISTINCT ezurl.*
                        FROM
                              ezurl,
                              ezurl_object_link,
                              ezcontentobject_attribute,
                              ezcontentobject_version
                        WHERE 
                              ezurl.id                                     = ezurl_object_link.url_id
                          AND ezurl_object_link.contentobject_attribute_id = ezcontentobject_attribute.id
                          AND ezurl_object_link.$cObjAttrVersionColumn     = ezcontentobject_attribute.version
                          AND ezcontentobject_attribute.contentobject_id   = ezcontentobject_version.contentobject_id
                          AND ezcontentobject_attribute.version            = ezcontentobject_version.version
                          AND ezcontentobject_version.status               = " . eZContentObjectVersion::STATUS_PUBLISHED . "
                          AND $whereCondition $conditionQuery";
        }
        else
        {
             $query = "SELECT DISTINCT ezurl.* FROM ezurl WHERE $whereCondition $conditionQuery";
        }
    }
    
    return $query;
}

$query = createQuery(
    $options['where'],
    $options['count'],
    boolval($options['is-valid'])
);

$cli->warning($query);

$db = eZDB::instance();
if ( !$offset && !$limit )
{
    $data = $db->arrayQuery( $query );
}
else
{
    $data = $db->arrayQuery( $query, array( 'offset' => $offset, 'limit'  => $limit ) );
}

if (count($data)){

    
    
    if (!$options['count']){
        
        $headers = array_keys($data[0]);
        array_unshift($data, $headers);
        
        if ( !$offset && !$limit ){
            $trans = eZCharTransform::instance();
            $filename = $options['where'] ? $trans->transformByGroup( $options['where'], 'identifier' ) : 'ezurl';
            $filename .= '.' . date('Ymd') . '.csv';
            
            $fp = fopen($filename, 'w');        
            foreach ($data as $value) {
                fputcsv($fp, $value);
            }
        }
        
        if ($options['show']){
            $output = new ezcConsoleOutput();
    
            $output->formats->headBorder->color = 'blue';
            $output->formats->normalBorder->color = 'gray';
            
            $output->formats->headContent->color = 'blue';
            $output->formats->headContent->style = array( 'bold' );
            
            $table = new ezcConsoleTable( $output, 100 );
            
            $table[0]->borderFormat = 'headBorder';
            $table[0]->format = 'headContent';
            $table[0]->align = ezcConsoleTable::ALIGN_CENTER;
            
            foreach ( $data as $row => $cells )
            {
                foreach ( $cells as $cell )
                {
                    $table[$row][]->content = $cell;
                }
            }
            
            $output->outputLine( 'eZ components team:' );
            $table->outputTable();
            $output->outputLine();
    
        }        
    }
    
}else{
    $cli->error('No results');
}



$script->shutdown();