Location: PHPKode > projects > QuickTalk Forum > quicktalk/qtf_find_inc.php
<?php

// QuickTalk 2.5 build:20100924

// This is included in qtf_find, qtf_find_csv and qtf_find_prt

$s = -1;
$intYear = 0;
$k1 = '';
$k2 = '';
$k3 = '';
$strOrder = 'lastpostdate';
$strDir = 'DESC';
$intLimit = 0;
$intPage = 1;

// security check 1

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 = strip_tags($_GET['order']);
if ( isset($_GET['dir']) ) $strDir = strip_tags($_GET['dir']);
if ( isset($_GET['page']) ) $intPage = intval(strip_tags($_GET['page']));
if ( isset($_GET['view']) ) $_SESSION[QT]['viewmode'] = strip_tags($_GET['view']);

if ( isset($_GET['k1']) ) $k1 = urldecode(strip_tags($_GET['k1']));
if ( isset($_GET['k2']) ) $k2 = urldecode(strip_tags($_GET['k2']));
if ( isset($_GET['k3']) ) $k3 = urldecode(strip_tags($_GET['k3']));
if ( !empty($k1) ) $k1 = str_replace('"','',$k1);

if ( isset($_GET['debug']) )
{
echo '$a=',$a,' | ';
echo '$s=',$s,' | ';
echo '$intYear=',$intYear,' | ';
echo '$strOrder=',$strOrder,' | ';
echo '$strDir=',$strDir,' | ';
echo '$intPage=',$intPage,' | ';
echo '$view=',$_SESSION[QT]['viewmode'],' | ';
echo '$k1=',$k1,' | ';
echo '$k2=',$k2,' | ';
echo '$k3=',$k3,' | ';
}

// security check 2 (no long argument)

if ( strlen($strOrder)>12 ) die('Invalid argument #order');
if ( strlen($strDir)>4 ) die('Invalid argument #dir');
if ( strlen($k3)>24 ) die('Invalid argument #name');

$strOptions = '';
foreach(array('s','k1','k2','k3') as $key) $strOptions .= '&amp;'.$key.'='.urlencode($$key);
if ( $a=='tst' ) $strOptions .= '&amp;y='.$intYear;

switch($a)
{
case 'ref':

  $oVIP->selfname = $L['Search_by_ref'];
  if ( empty($k1) ) $error = $L['Ref'].' '.Error(1);
  if ( strstr($k1,'.') ) $error = $L['Ref'].' '.Error(1);
  if ( strstr($k1,',') ) $error = $L['Ref'].' '.Error(1);
  if ( !is_numeric($k1) ) $error = $L['Ref'].' '.Error(1);
  $k1 = intval($k1);
  break;
  
case 'kw':

  $oVIP->selfname = $L['Search_by_key'];
  if ( empty($k1) ) $error = $L['Keywords'].S.Error(1);
  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 = explode(' ',$k1);
  if ( $k2=='1' )
  {
    if ( count($arrKeys)<1 ) $k2='0';
    if ( count($arrKeys)>5 ) $error = $L['Too_many_keys'];
  }
  break;

case 'user':

  if ( $k1=='' ) $error = 'Userid '.Error(1);
  $k1 = intval($k1);
  if ( $k1<0 ) $error = 'Userid '.Error(1);
  if ( !empty($k2) ) $k2 = urldecode($k2);
  break;

case 'tst': // time status tags

  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 intialise

$intLimit = ($intPage-1)*$_SESSION[QT]['topics_per_page'];

if ( QTF_LIST_ME && $oVIP->numpost>0 )
{
$oDB2 = new cDB($qtf_dbsystem,$qtf_host,$qtf_database,$qtf_user,$qtf_pwd,$qtf_port,$qtf_dsn);
}

$strField = 't.*, s.title as sectiontitle,s.prefix,p.icon,p.title,p.textmsg,s.numfield ';

// QUERY DEFINITION

// Section option (and check if ref exists)

$bAddRef=false;
$strSection = 't.forum='.$s;
if ( $s<0 )
{
  $strSection = 't.forum>=0 AND s.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($a)
{
case 'ref':

  $strFrom  = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' s ON t.forum=s.id INNER JOIN '.TABPOST.' p ON t.firstpostid=p.id ';
  $strWhere = 'WHERE '.$strSection.' AND t.numid='.$k1;
  $strCount = 'SELECT count(*) as countid '.$strFrom.$strWhere;
  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.' s ON t.forum = s.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';
  $strWhere = 'WHERE '.$strSection.' AND '.( $k3=='1' ? $qryTitle : "($qryTitle OR $qryMessage)");
  $strCount = 'SELECT count(*) as countid '.$strFrom.$strWhere;
  break;

case 'news':

  $strFrom = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' s ON t.forum=s.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';
  $strWhere = 'WHERE '.$strSection.' AND t.type="A"';
  $strCount = 'SELECT count(*) as countid FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' s ON t.forum=s.id 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

  switch(substr($oDB->type,0,5))
  {
  case 'mysql': $strWhere = 'LEFT(t.lastpostdate,8)>"'.$strDate.'"'; break;
  case 'mssql': $strWhere = 'LEFT(t.lastpostdate,8)>"'.$strDate.'"'; break;
  case 'pg':    $strWhere = 'SUBSTRING(t.lastpostdate,1,8)>"'.$strDate.'"'; break;
  case 'ibase': $strWhere = 'SUBSTRING(t.lastpostdate FROM 1 FOR 8)>"'.$strDate.'"'; break;
  case 'sqlit': $strWhere = 'SUBSTR(t.lastpostdate,1,8)>"'.$strDate.'"'; break;
  case 'acces': $strWhere = 'LEFT(t.lastpostdate,8)>"'.$strDate.'"'; break;
  case 'db2':   $strWhere = 'SUBSTR(t.lastpostdate,1,8)>"'.$strDate.'"'; break;
  case 'oci':   $strWhere = 'SUBSTR(t.lastpostdate,1,8)>"'.$strDate.'"'; break;
  default: die('Unknown db type '.$oDB->type);
  }
  $strWhere = 'WHERE '.$strSection.' AND '.$strWhere;
  $strFrom = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' s ON t.forum = s.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';
  $strCount = 'SELECT count(*) as countid FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' s ON t.forum=s.id '.$strWhere;
  break;

case 'user':

  $strFrom = 'FROM '.TABTOPIC.' t INNER JOIN '.TABSECTION.' s ON t.forum = s.id INNER JOIN '.TABPOST.' p ON t.firstpostid = p.id ';
  $strWhere = 'WHERE t.firstpostuser='.$k1;
  $strCount = 'SELECT count(*) as countid FROM '.TABTOPIC.' WHERE  firstpostuser='.$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' )
  {
    // this year
    switch(substr($oDB->type,0,5))
    {
    case 'mysql': $strWhere = 'LEFT(t.firstpostdate,4)="'.$intYear.'"'; break;
    case 'mssql': $strWhere = 'LEFT(t.firstpostdate,4)="'.$intYear.'"'; break;
    case 'pg':    $strWhere = 'SUBSTRING(t.firstpostdate,1,4)="'.$intYear.'"'; break;
    case 'ibase': $strWhere = 'SUBSTRING(t.firstpostdate FROM 1 FOR 4)="'.$intYear.'"'; break;
    case 'sqlit': $strWhere = 'SUBSTR(t.firstpostdate,1,4)="'.$intYear.'"'; break;
    case 'acces': $strWhere = 'LEFT(t.firstpostdate,4)="'.$intYear.'"'; break;
    case 'db2':   $strWhere = 'SUBSTR(t.firstpostdate,1,4)="'.$intYear.'"'; break;
    case 'oci':   $strWhere = 'SUBSTR(t.firstpostdate,1,4)="'.$intYear.'"'; break;
    default: die('Unknown db type '.$oDB->type);
    }
  }
  elseif ( $k1=='w' )
  {
    // this week
    switch(substr($oDB->type,0,5))
    {
    case 'mysql': $strWhere = 'LEFT(t.firstpostdate,8)>"'.DateAdd(date('Ymd'),-8,'day').'"'; break;
    case 'mssql': $strWhere = 'LEFT(t.firstpostdate,8)>"'.DateAdd(date('Ymd'),-8,'day').'"'; break;
    case 'pg':    $strWhere = 'SUBSTRING(t.firstpostdate,1,8)>"'.DateAdd(date('Ymd'),-8,'day').'"'; break;
    case 'ibase': $strWhere = 'SUBSTRING(t.firstpostdate FROM 1 FOR 8)>"'.DateAdd(date('Ymd'),-8,'day').'"'; break;
    case 'sqlit': $strWhere = 'SUBSTR(t.firstpostdate,1,8)>"'.DateAdd(date('Ymd'),-8,'day').'"'; break;
    case 'db2':   $strWhere = 'SUBSTR(t.firstpostdate,1,8)>"'.DateAdd(date('Ymd'),-8,'day').'"'; break;
    case 'oci':   $strWhere = 'SUBSTR(t.firstpostdate,1,8)>"'.DateAdd(date('Ymd'),-8,'day').'"'; break;
    default: die('Unknown db type '.$oDB->type);
    }
  }
  else
  {
    // the month
    $intMonth = intval($k1);
    // check if month from previous year
    if ( $intYear==date('Y') && $intMonth>date('n') ) $intYear = $intYear-1;

    switch(substr($oDB->type,0,5))
    {
    case 'mysql': $strWhere = 'LEFT(t.firstpostdate,6)="'.($intYear*100+$intMonth).'"'; break;
    case 'mssql': $strWhere = 'LEFT(t.firstpostdate,6)="'.($intYear*100+$intMonth).'"'; break;
    case 'pg':    $strWhere = 'SUBSTRING(t.firstpostdate,1,6)="'.($intYear*100+$intMonth).'"'; break;
    case 'ibase': $strWhere = 'SUBSTRING(t.firstpostdate FROM 1 FOR 6)="'.($intYear*100+$intMonth).'"'; break;
    case 'sqlit': $strWhere = 'SUBSTR(t.firstpostdate,1,6)="'.($intYear*100+$intMonth).'"'; break;
    case 'db2':   $strWhere = 'SUBSTR(t.firstpostdate,1,6)="'.($intYear*100+$intMonth).'"'; break;
    case 'oci':   $strWhere = 'SUBSTR(t.firstpostdate,1,6)="'.($intYear*100+$intMonth).'"'; break;
    default: die('Unknown db type '.$oDB->type);
    }
  }
  $strWhere = 'WHERE '.$strSection.' AND '.$strWhere;

  // criteria status

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

  // 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.' s ON t.forum=s.id INNER JOIN '.TABPOST.' p ON t.firstpostid=p.id ';
  $strCount = 'SELECT count(*) as countid FROM '.TABTOPIC.' t  INNER JOIN '.TABSECTION.' s ON t.forum=s.id '.$strWhere;
  break;
}

?>
Return current item: QuickTalk Forum