<?php  
/***
 *  TABS Asterisk Billing System
 *  Copyright (C) 2008  Thusa Business Support (Pty) Ltd.
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 * 
 ***/

/**
* search.php
*
* Search utility page
*
*/ 

//{{{ initialisation
  require_once "include/initialisation.inc.php";
  $tpl->setTPL('search.tpl');
  $form = new HTML_QuickForm();
  $routeField = $ini['System']['routing_field'];
  $rowsperpage = $ini['System']['per_page'];
  $tpl->assign('pagerows',$rowsperpage);
//}}}


//{{{ create QF elements
/*DISABLED: BugID 99: Remove Network Route
  if($LU->checkRight(VIEW_ALL_EXT)||$LU->checkRight(GROUP_STATS)) {
    //we only want to create the extension element if the user has the rights
    $form->addElement('text','txtExtension',"Ext: ","autocomplete=off");
  }
*/
  $form->addElement('text','txtSource',"Source: ","autocomplete=off id=srcID");
  $form->addElement('text','txtDestination',"Dest: ","autocomplete=off id=dstID");
  $form->addElement('date','dateFrom',"Date From: ",array('format'=>"Ymd",'minYear'=>date("Y"),'maxYear'=>2001));
  $form->addElement('date','dateTo',"Date To: ",array('format'=>"Ymd",'minYear'=>date("Y"),'maxYear'=>2001));
  $form->addElement('text','perPage','Entries per Page: ');
  
  $form->addElement('submit','btnSubmit',"Submit Search Criteria!");
  $form->addElement('submit','csv_download','Download CSV Data File');
  
  $form->addRule('perPage','Please enter a number', 'numeric');

  //{{{ create defaults
    $defaults = array('dateFrom'=>array('Y'=>date("Y"),'m'=>date('m'),'d'=>date("d")),
                        'dateTo'=>array('Y'=>date("Y"),'m'=>date('m'),'d'=>date("d")),
                        'perPage'=>"10"
                      );
    $form->setDefaults($defaults);
  //}}}
  /* DISABLED: BugID 99:/{{{ create select elements
  $acc_codes = array();
  $acc_query="SELECT DISTINCT `$routeField` FROM `cdr`";
  $acc_res = $db->query($acc_query);
  
  if(handleError($acc_res))  {
    
    while($acc_res->fetchInto($acc_row))  {
      $acc_codes[$acc_row[$routeField]] = $acc_row["$routeField"];
    }
  }
  //$acc_codes = array("ISDN Out"=>'Telkom');
  $form->addElement('select','selAccount','Network Route: ',$acc_codes);
  //}}}*/
  
//}}}
/*/{{{ push autocomplete data to template
  $src_query = "SELECT DISTINCT `src` FROM `cdr` WHERE `src` <> '' ORDER BY `src`";
  $src_res = $db->query($src_query);
  if(handleError($src_res)) {
    $sources = "";
    while($src_res->fetchInto($src_row)) {
      $sources .= '"'.$src_row['src'].'", ';
    }
    //take out the last comma
    $sources = substr($sources,0,-2);
    
    $tpl->assign('sources',$sources);
  }
  
  $dst_query = "SELECT DISTINCT `dst` FROM `cdr` WHERE `dst` <> '' ORDER BY `dst`";
  $dst_res = $db->query($dst_query);
  if(handleError($dst_res)) {
    $dests = "";
    while($dst_res->fetchInto($dst_row)) {
      $dests .= '"'.$dst_row['dst'].'", ';
    }
    //take out the last comma
    $dests = substr($dests,0,-2);
    
    $tpl->assign('dest',$dests);
  }
  //make sure that autocomplete code gets run in the header
  $tpl->assign('onLoad', 'createAutoComplete();');
//}}}*/

if($form->validate()) {
  $result = $form->process('process_search',false);
  $tpl->assign('result',$result);
}
$tpl->display($form);

/**
* Process Search Results
*
* Processes the search criteria to get some search results from the database
* @param $args These are the posted variables from the database
*/
function process_search($args)  {
  global $db;
  global $ini;
  global $routeField;
  global $tpl;
  
  $ast_tbl = $ini['DB']['cdr_table'];
  
  //cache phone list and phone book
  $phone_list = array();
  $phone_book = array();
  cacheNumbers($db,$phone_list,$phone_book);
  
  if(!isset($args['perPage']))  {
    $tpl->assign('pagerows',$ini['System']['per_page']);
  } else {
    $tpl->assign('pagerows',intval($args['perPage']));
  }
  //base SQL Statement
  $sql = "SELECT `$routeField`,`calldate`,`src`,`dst`,`billsec`,`cost` FROM `$ast_tbl` WHERE `disposition`='ANSWERED'";
  
  if(isset($args['selAccount']))  {
    $sql.= " AND `$routeField`='".$args['selAccount']."'";
  }
  
  if((isset($args['dateTo']))||(isset($args['dateFrom'])))    {
    // {{{ add leading zeroes to day and month variables
      if ($args['dateFrom']['d']<10)    {
        $args['dateFrom']["d"]="0".$args['dateFrom']["d"];
      }
      if($args['dateFrom']["m"]<10) {
        $args['dateFrom']["m"]="0".$args['dateFrom']["m"];
      }
      if ($args['dateTo']['d']<10)    {
        $args['dateTo']["d"]="0".$args['dateTo']["d"];
      }
      if($args['dateTo']["m"]<10) {
        $args['dateTo']["m"]="0".$args['dateTo']["m"];
      }
    // }}}
    // {{{ if 1 date value is missing, create it
      if(!isset($args['dateFrom'])) {
        $args['dateFrom'] = date("Y")."-".date("m")."-".date('d');
      }
      if(!isset($args['dateTo'])) {
        $args['dateTo'] = date("Y")."-".date("m")."-".date('d');
      }
    // }}}
                        
    // {{{ create from and to dates so that comparison works
      $date_from = $args['dateFrom']['Y']."-".$args['dateFrom']['m']."-".$args['dateFrom']['d']." 00:00:00";
      $date_to = $args['dateTo']['Y']."-".$args['dateTo']['m']."-".$args['dateTo']['d']." 23:59:59";
    // }}}
    //{{{ PROBLEM - this code compares days value first
    //if($args['dateTo'] > $args['dateFrom'])  {
    //}}}
    
    if($date_to > $date_from)  {
      //echo "from = ".$date_from."     to = ".$date_to;
      $sql .= ' AND `calldate` > "'.$date_from.'"';
      $sql .= ' AND `calldate` < "'.$date_to.'"';
    }
  }//end if isset date vars
  
  if((isset($args['txtSource']))&&(!empty($args['txtSource']))) {
    $sql.= ' AND `src` = "'.$args['txtSource'].'"';
  }
  if((isset($args['txtDestination']))&&(!empty($args['txtDestination']))) {
    $sql.= ' AND `dst` = "'.$args['txtDestination'].'"';
  }
  //{{{ perform query
  //echo "SQL: $sql";
    $result = $db->query($sql);
    if(handleError($result)) {
      if(isset($args['csv_download'])) {
        $filename = "tabs.csv";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        echo("Date,Source,Destination,Billed Time(sec),Cost\r\n");
        while($result->fetchInto($res_row)) {
          $line = $res_row['calldate'].','.checkNumber($phone_list,$phone_book,$res_row['src']).','.checkNumber($phone_list,$phone_book,$res_row['dst']).','.$res_row['billsec'].',';
		  if(0>$res_row['cost'])	{
		    if(!$debug)  {
		      $line.="0\r\n";
		    } else {
          $line.=$res_row['cost']."\r\n";
        }
		  } else {
		    $line.=$res_row['cost']."\r\n";
		  }
		  echo $line;
        }
        die();
      } else {
        $result_arr = array();
        while($result->fetchInto($res_row)) {
          $res_row['src'] = checkNumber($phone_list,$phone_book,$res_row['src']);
          $res_row['dst'] = checkNumber($phone_list,$phone_book,$res_row['dst']);
          $result_arr[]=$res_row;
        }
        return $result_arr;
      }
    } else {
      return false;
    }
  
  //}}}
  
} 
?>