Location: PHPKode > projects > QuickTicket > quickticket/qti_search_csv.php
<?php



/**

* PHP versions 4 and 5

*

* LICENSE: This source file is subject to version 3.0 of the PHP license

* that is available through the world-wide-web at the following URI:

* http://www.php.net/license. If you did not receive a copy of

* the PHP License and are unable to obtain it through the web, please

* send a note to hide@address.com so we can mail you a copy immediately.

*

* @package    QuickTicket

* @author     Philippe Vandenberghe <hide@address.com>

* @copyright  2008-2012 The PHP Group

* @version    2.3 build:20091102

*/



session_start();

require_once('bin/qti_init.php');

$oVIP->output='csv';

if ( !$oVIP->CanView('V5') ) HtmlPage(11);



if ( !isset($_GET['a']) ) die('Missing parameter a');

$strAction = strip_tags($_GET['a']);



include('bin/qti_fn_sql.php');



function ToCsv($str)

{

  if ( $str=='' ) return '"";';

  if ( !is_string($str) ) $str = strval($str);

  $str = str_replace('&nbsp;',' ',$str);

  $str = QTconv($str,'-4');

  $str = str_replace( array('"',';',"\r\n"),array("'",',',' '),$str );

  return '"'.$str.'";';

}



// ---------

// INITIALISE

// ---------



$strCSV = '';

$arrMe[] = array();



$s = -1;

$intYear = 0;

$k1 = '';

$k2 = '';

$k3 = '';

$strOrder = 'lastpostdate';

$strDir = 'DESC';

$intLimit = 0;

$intLen = $_SESSION[QT]['topics_per_page'];

$strExtraparam ='';



// Read query arguments



if ( isset($_GET['s']) ) $s = intval(strip_tags($_GET['s']));

if ( isset($_GET['y']) ) $intYear = intval(strip_tags($_GET['y']));

if ( isset($_GET['order']) ) $strOrder = $_GET['order'];

if ( isset($_GET['dir']) ) $strDirec = $_GET['dir'];

if ( isset($_GET['k1']) ) $k1 = strip_tags($_GET['k1']);

if ( isset($_GET['k2']) ) $k2 = strip_tags($_GET['k2']);

if ( isset($_GET['k3']) ) $k3 = strip_tags($_GET['k3']);



  if ( isset($_GET['debug']) )

  {

  echo '$strAction=',$strAction,' | ';

  echo '$s=',$s,' | ';

  echo '$intYear=',$intYear,' | ';

  echo '$strOrder=',$strOrder,' | ';

  echo '$strDir=',$strDir,' | ';

  echo '$k1=',$k1,' | ';

  echo '$k2=',$k2,' | ';

  echo '$k3=',$k3,' | ';

  }



// Check query arguments (no long argument)



if ( strlen($strOrder)>20 ) die('Invalid argument #order');

if ( strlen($strDir)>4 ) die('Invalid argument #dir');

if ( strlen($k3)>24 ) die('Invalid argument #name');



// Read report arguments



$size = strip_tags($_GET['size']);

$intCount = intval($_GET['n']);



// Check report arguments



if ( empty($size) || $intCount <= $_SESSION[QT]['topics_per_page'] ) $size='all';

if ( strlen($size)>6 || strlen($size)<2 ) die('Invalid argument');

if ( substr($size,0,1)!='p' && substr($size,0,1)!='m' && $size!='all') die('Invalid argument');

if ( substr($size,0,1)=='p' )

{

  $i = intval(substr($size,1));

  if ( empty($i) ) die('Invalid argument');

  if ( ($i-1) > $intCount/$_SESSION[QT]['topics_per_page'] ) die('Invalid argument');

}

if ( substr($size,0,1)=='m' )

{

  if ( $size!='m1' && $size!='m2' && $size!='m5' && $size!='m10' ) die('Invalid argument');

}

if ( $intCount>1000 && $size=='all' ) die('Invalid argument');

if ( $intCount<=1000 && substr($size,0,1)=='m' ) die('Invalid argument');

if ( $intCount>1000 && substr($size,0,1)=='p' ) die('Invalid argument');



// Apply report argument (page)



if ( substr($size,0,1)=='p' ) $_GET['page'] = substr($size,1);



// Apply query arguments



switch($strAction)

{

case 'ref':



  $oVIP->selfname = $L['Search_by_ref'];

  if ( empty($k1) ) $error = $L['Ref'].' '.$L['E_invalid'];

  if ( strstr($k1,'.') ) $error = $L['Ref'].' '.$L['E_invalid'];

  if ( strstr($k1,',') ) $error = $L['Ref'].' '.$L['E_invalid'];

  if ( !is_numeric($k1) ) $error = $L['Ref'].' '.$L['E_invalid'];

  $k1 = intval($k1);

  break;

  

case 'kw':



  $oVIP->selfname = $L['Search_by_key'];

  if ( empty($k1) ) $error = $L['Keywords'].' '.$L['E_invalid'];

  if ( strlen($k1)>64 ) die('Invalid argument #k1');

  if ( strlen($k2)>2 ) die('Invalid argument #k2'); // sp: single phrase

  if ( strlen($k3)>2 ) die('Invalid argument #k3'); // to: title only

  $arrKeys = split(' ',$k1);

  if ( $k2!='0' && $k2!='1' ) $error = $L['Keywords'].' k2 > '.$L['E_invalid'];

  if ( $k3!='0' && $k3!='1' ) $error = $L['Keywords'].' k3 > '.$L['E_invalid'];

  if ( $k2=='1' )

  {

    if ( count($arrKeys)<1 ) $k2='0';

    if ( count($arrKeys)>5 ) $error = $L['Too_many_keys'];

  }

  break;



case 'last':



  $oVIP->selfname = $L['Search_result'];

  break;



case 'news':



  $oVIP->selfname = $L['Search_result'];

  break;



case 'user':



  $oVIP->selfname = $L['Search_result'];

  if ( $k1=='' ) $error = 'Userid '.$L['E_invalid'];

  $k1 = intval($k1);

  if ( $k1<0 ) $error = 'Userid '.$L['E_invalid'];

  if ( !empty($k2) ) $k2 = urldecode($k2);

  break;



case 'actor':



  $oVIP->selfname = $L['Search_result'];

  if ( $k1=='' ) $error = 'Userid '.$L['E_invalid'];

  $k1 = intval($k1);

  if ( $k1<0 ) $error = 'Userid '.$L['E_invalid'];

  if ( !empty($k2) ) $k2 = urldecode($k2);

  break;



case 'tst': // time status tags



  $oVIP->selfname = $L['Advanced_search'];

  $k3 = strip_tags($_GET['k3']); if ( substr($k3,-1,1)==';' ) $k3 = substr($k3,0,-1);

  if ( empty($k3) ) { $arrTags=array(); } else { $arrTags = explode(';',$k3); }

  if ( strlen($k1)>2 ) die('Invalid argument #time');

  if ( $intYear<1970 || $intYear>2070 ) die('Invalid argument #y');



  break;



}



// stop if error



if ( !empty($error) ) $oVIP->EndMessage(NULL,$error,$_SESSION[QT]['skin_dir'],0);



// end initialise



if ( isset($_GET['page']) ) $intLimit = (intval($_GET['page'])-1)*$intLen;



// apply argument



if ( $size=='all') { $intLimit=0; $intLen=$intCount; }

if ( $size=='m1' ) { $intLimit=0; $intLen=999; }

if ( $size=='m2' ) { $intLimit=1000; $intLen=1000; }

if ( $size=='m5' ) { $intLimit=0; $intLen=4999; }

if ( $size=='m10') { $intLimit=5000; $intLen=5000; }



$oVIP->selfurl = 'qti_search_re.php';

$oVIP->exiturl = 'qti_search.php?'.QTargimplode(QTargexplode());

$oVIP->exitname = $L['Search'];



// --------

// QUERY

// --------



$strField = 't.*,f.prefix,p.icon,p.title,p.textmsg,f.numfield ';



// Section option (and check if ref exists)



$bAddRef=false;

$strSection = 't.forum = '.$s;

if ( $s<0 )

{

  $strSection = 't.forum>=0 AND f.type<>"1"';

  if ( $oVIP->IsStaff() ) $strSection = 't.forum>=0';

  if ( count(GetSections($oVIP->role,-1,-1,'s.numfield<>"N"'))>0 ) $bAddRef=TRUE;

}

else

{

  if ( count(GetSections('A',-1,-1,'s.numfield<>"N" AND s.id='.$s))>0 ) $bAddRef=TRUE;

}



// Query definition



switch($strAction)

{

case 'ref':



  $strFrom  = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' f ON t.forum = f.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';

  $strWhere = 'WHERE '.$strSection.' AND t.numid='.$k1;

  break;



case 'kw':



  // full word criteria

  $qryTitle = 'UPPER(p.title) LIKE "%'.strtoupper($k1).'%"'; 

  switch ($oDB->type)

  {

  case 'mssql':$qryMessage = 'UPPER(CAST(p.textmsg AS VARCHAR(2000))) LIKE "%'.strtoupper($k1).'%"'; break;

  case 'db2':  $qryMessage = 'UPPER(p.textmsg2) LIKE "%'.strtoupper($k1).'%"'; break;

  default:     $qryMessage = 'UPPER(p.textmsg) LIKE "%'.strtoupper($k1).'%"'; break;

  }

  

  // split criteria

  if ( $k2=='1' )

  {

    $qryTitle = '(';

    $qryMessage = '(';

    $qryOr = '';

    foreach($arrKeys as $intKey=>$strValue)

    {

      $qryTitle .= $qryOr.'UPPER(p.title) LIKE "%'.strtoupper($strValue).'%"';

  

      switch ($oDB->type)

      {

      case 'mssql':$qryMessage .= $qryOr.'UPPER(CAST(p.textmsg AS VARCHAR(2000))) LIKE "%'.strtoupper($strValue).'%"'; break;

      case 'db2':  $qryMessage .= $qryOr.'UPPER(p.textmsg2) LIKE "%'.strtoupper($strValue).'%"'; break;

      default:     $qryMessage .= $qryOr.'UPPER(p.textmsg) LIKE "%'.strtoupper($strValue).'%"'; break;

      }

      $qryOr = ' OR ';

    }

    $qryTitle   .= ') ';

    $qryMessage .= ') ';

  }



  $strFrom  = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' f ON t.forum = f.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';

  $strWhere = 'WHERE '.$strSection.' AND '.( $k3=='1' ? $qryTitle : "($qryTitle OR $qryMessage)");

  break;



case 'news':



  $strFrom = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' f ON t.forum = f.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';

  $strWhere = 'WHERE '.$strSection.' AND t.type="A"';

  break;



case 'last':



  // get the lastpost date



  $oDB->Query('SELECT max(t.lastpostdate) as f1 FROM '.TABTOPIC.' t ');

  $row = $oDB->Getrow();

  if ( empty($row['f1']) ) $row['f1'] = date('Ymd');

  $strDate = DateAdd($row['f1'],-7,'day');

  

  // query post of this day



  $strWhere = 'WHERE '.$strSection.' AND '.SqlDateCondition($strDate,'t.lastpostdate',8,'>');

  $strFrom = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' f ON t.forum = f.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';

  break;



case 'user':



  $strFrom = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' f ON t.forum = f.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';

  $strWhere = 'WHERE t.firstpostuser='.$k1;

  break;



case 'actor':



  $strFrom = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' f ON t.forum = f.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';

  $strWhere = 'WHERE t.actorid='.$k1;

  break;



case 'tst':



  // date this week (by default)



  if ( $k1=='m' ) $k1=date('n');

  if ( $k1=='-1' )

  {

    $strWhere = 't.firstpostdate>"0"';

  }

  elseif ( $k1=='y' )

  {

    $strWhere = SqlDateCondition($intYear,'t.firstpostdate'); // this year

  }

  elseif ( $k1=='w' )

  {

    $strWhere = SqlDateCondition(DateAdd(date('Ymd'),-8,'day'),'t.firstpostdate',8,'>'); // this week

  }

  else

  {

    $intMonth = intval($k1); // the month

    if ( $intYear==date('Y') && $intMonth>date('n') ) $intYear = $intYear-1; // check if month from previous year

    $strWhere = SqlDateCondition(($intYear*100+$intMonth),'t.firstpostdate',6);

  }



  $strWhere = 'WHERE '.$strSection.' AND '.$strWhere;



  // criteria status



  if ( $k2!='-1' ) $strWhere .= ' AND t.status="'.$k2.'"';



  // criteria tag



  $str = '';

  foreach($arrTags as $strTag)

  {

    if ( !empty($str) ) $str .= ' OR ';

    $str .= 't.tags LIKE "%'.$strTag.'%"';

  }

  if ( !empty($str) ) $strWhere .= ' AND ('.$str.')';



  $strFrom  = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' f ON t.forum = f.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';

  break;



}



// --------

// HTML START

// --------



// ========

$arrFLD = array();

$oFLD = new cFLD('csvdate',$L['Date']); $arrFLD['csvdate']=$oFLD;

$oFLD = new cFLD('csvtime',$L['Time']); $arrFLD['csvtime']=$oFLD;

$oFLD = new cFLD('status','Status'); $arrFLD['status']=$oFLD;

if ( $bAddRef ) { $oFLD = new cFLD('numid',$L['Ref']); $arrFLD['numid']=$oFLD; }

$oFLD = new cFLD('smile','Ico'); $arrFLD['smile']=$oFLD;

$oFLD = new cFLD('title',$L['Topics']); $arrFLD['title']=$oFLD;

$oFLD = new cFLD('text','Text'); $arrFLD['text']=$oFLD;

$oFLD = new cFLD('sectiontitle',$L['Section']); $arrFLD['sectiontitle']=$oFLD;

$oFLD = new cFLD('firstpostname',$L['Topic_starter']); $arrFLD['firstpostname']=$oFLD;

$oFLD = new cFLD('lastpostdate',$L['Last_message']); $arrFLD['lastpostdate']=$oFLD;

if ( $strAction=='tst' && !empty($k3) )

{

$oFLD = new cFLD('tags',$L['Tags']); $arrFLD['tags']=$oFLD;

}

else

{

$oFLD = new cFLD('replies',$L['Replys']); $arrFLD['replies']=$oFLD;

}

$oFLD = new cFLD('uid','UID'); $arrFLD['uid']=$oFLD;

// ========

foreach($arrFLD as $strKey=>$oFLD) $strCSV .= ToCsv($oFLD->name);

$strCSV = substr($strCSV,0,-1)."\r\n";

// ========

$strFullOrder = 't.'.$strOrder.' '.$strDir; // first order

if ( $strOrder=='title' ) $strFullOrder = 'p.title'.' '.$strDir;

if ( $strOrder=='sectiontitle' ) $strFullOrder = 'f.title'.' '.$strDir;

if ( $strOrder!='lastpostdate' ) $strFullOrder .= ',t.lastpostdate DESC'; //second order

$oDB->Query( LimitSQL($strField.$strFrom.$strWhere,$strFullOrder,$intLimit,$intLen,$intCount) );

// ========

$intWhile=0;

$strAlt='r1';

while($row=$oDB->Getrow())

{

  TableRow($arrFLD,$row,true,null,$strAlt);

  if ( $strAlt=='r1' ) { $strAlt='r2'; } else { $strAlt='r1'; }

  $intWhile++; 

  //odbcbreak

  if ( $intWhile>=$intCount ) break;  

}

// ========



// OUPUT



if ( isset($_GET['debug']) ) { echo $strCSV; exit; }



if ( !headers_sent() )

{

  header('Content-Type: text/csv; charset='.QTI_HTML_CHAR);

  header('Content-Disposition: attachment; filename="qti_'.date('YmdHi').'.csv"');

}

echo $strCSV;



?>
Return current item: QuickTicket