Logo Search packages:      
Sourcecode: dcl version File versions  Download package

class.boView.inc.php

<?php
/*
 * $Id: class.boView.inc.php,v 1.5.4.14 2003/12/10 05:54:06 mdean Exp $
 *
 * Double Choco Latte - Source Configuration Management System
 * Copyright (C) 1999  Michael L. Dean & Tim R. Norman
 *
 * 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., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 * Select License Info from the Help menu to view the terms and conditions of this license.
 */

class boView
{
      var $title;
      var $table;
      var $columns;
      var $columnhdrs;
      var $groups;
      var $order;
      var $filter;
      var $filternot;
      var $filterdate;
      var $filterlike;
      var $logicdate;
      var $logiclike;
      var $joins;
      var $urlpieces;
      var $style;
      var $startrow;
      var $numrows;
      var $m_oDB;

      function boView()
      {
            $this->Clear();
            $this->startrow = 0;
            $this->numrows = 0;
            $this->urlpieces = array(
                        'vt',
                        'vti',
                        'vc',
                        'vch',
                        'vg',
                        'vo',
                        'vf',
                        'vfn',
                        'vfd',
                        'vfl',
                        'vs'
                  );

            $this->m_oDB = null;
      }

      function ClearDef($def)
      {
            $code = sprintf('$this->%s = array();', $def);
            eval($code);
      }

      function Clear()
      {
            // Set some defaults
            $this->title = '';
            $this->table = 'workorders';
            $this->columns = array();
            $this->columnhdrs = array();
            $this->groups = array();
            $this->order = array();
            $this->filter = array();
            $this->filternot = array();
            $this->filterdate = array();
            $this->filterlike = array();
            $this->joins = array();
            $this->logicdate = 'OR';
            $this->logiclike = 'OR';
            $this->style = 'spreadsheet';
      }

      function GetURLArray($field, &$arr, $encode = true)
      {
            reset($arr);
            $retVal = '';

            if (count($arr) > 0)
            {
                  $retVal = $field . '=';
                  $bFirst = true;
                  while (list($key, $value) = each($arr))
                  {
                        if (is_array($value))
                        {
                              if ($bFirst == false)
                                    $retVal .= ',';

                              if (count($value) > 0)
                              {
                                    $retVal .= $key . ',' . count($value);
                                    while (list($key, $realVal) = each($value))
                                    {
                                          if ($encode)
                                                $retVal .= ',' . rawurlencode($realVal);
                                          else
                                                $retVal .= ',' . $realVal;
                                    }
                              }
                        }
                        else
                        {
                              if ($bFirst == false)
                                    $retVal .= ',';

                              if ($encode)
                                    $retVal .= rawurlencode($value);
                              else
                                    $retVal .= $value;
                        }

                        $bFirst = false;
                  }
            }

            return $retVal;
      }

      // Gets a URL to regenerate this view in another page
      function GetURL()
      {
            $retVal = 'vt=' . $this->table;
            $retVal .= '&vs=' . $this->style;
            if ($this->title != '')
                  $retVal .= '&vti=' . rawurlencode($this->title);
            if (count($this->columns) > 0)
                  $retVal .= '&';
            $retVal .= $this->GetURLArray('vc', $this->columns);
            if (count($this->columnhdrs) > 0)
                  $retVal .= '&';
            $retVal .= $this->GetURLArray('vch', $this->columnhdrs);
            if (count($this->columns) > 0)
                  $retVal .= '&';
            $retVal .= $this->GetURLArray('vg', $this->groups);
            if (count($this->order) > 0)
                  $retVal .= '&';
            $retVal .= $this->GetURLArray('vo', $this->order);
            if (count($this->filter) > 0)
                  $retVal .= '&';
            $retVal .= $this->GetURLArray('vf', $this->filter);
            if (count($this->filternot) > 0)
                  $retVal .= '&';
            $retVal .= $this->GetURLArray('vfn', $this->filternot);
            if (count($this->filterdate) > 0)
                  $retVal .= '&';
            $retVal .= $this->GetURLArray('vfd', $this->filterdate);
            if (count($this->filterlike) > 0)
                  $retVal .= '&';
            $retVal .= $this->GetURLArray('vfl', $this->filterlike);

            return $retVal;
      }

      function GetFormElement($var, $val)
      {
            return sprintf('<input type="hidden" name="%s" value="%s">', $var, $val) . phpCrLf;
      }

      function GetForm()
      {
            $retVal = $this->GetFormElement('vt', $this->table);
            $retVal .= $this->GetFormElement('vs', $this->style);
            if ($this->title != '')
                  $retVal .= $this->GetFormElement('vti', htmlspecialchars($this->title));

            $arrItems = array('vc' => 'columns', 'vch' => 'columnhdrs', 'vg' => 'groups', 'vo' => 'order', 
                        'vf' => 'filter', 'vfn' => 'filternot', 'vfd' => 'filterdate', 'vfl' => 'filterlike');
            while (list($attr, $arr) = each($arrItems))
            {
                  if (count($this->$arr) > 0)
                  {
                        list($var, $val) = explode('=', $this->GetURLArray($attr, $this->$arr, false));
                        $retVal .= $this->GetFormElement($var, htmlspecialchars($val));
                  }
            }

            return $retVal;
      }

      function SetFromURL()
      {
            $this->Clear();

            if (IsSet($GLOBALS['vt']))
                  $this->table = $GLOBALS['vt'];

            if (IsSet($GLOBALS['vs']))
                  $this->style = $GLOBALS['vs'];

            if (IsSet($GLOBALS['vti']))
            {
                  $o = CreateObject('dcl.dbPersonnel');
                  $this->title = $o->GPCStripSlashes($GLOBALS['vti']);
            }

            if (IsSet($GLOBALS['vc']))
                  $this->columns = explode(',', $GLOBALS['vc']);

            if (IsSet($GLOBALS['vch']))
                  $this->columnhdrs = explode(',', $GLOBALS['vch']);

            if (IsSet($GLOBALS['vg']))
                  $this->groups = explode(',', $GLOBALS['vg']);

            if (IsSet($GLOBALS['vo']))
                  $this->order = explode(',', $GLOBALS['vo']);

            $filterSet = array(
                        'vf' => 'filter',
                        'vfn' => 'filternot',
                        'vfd' => 'filterdate',
                        'vfl' => 'filterlike');

            while (list($urlName, $filterName) = each($filterSet))
            {
                  if (IsSet($GLOBALS[$urlName]))
                  {
                        $allFilters = explode(',', $GLOBALS[$urlName]);

                        // Get the field
                        while (list($key, $field) = each($allFilters))
                        {
                              // Get how many are in there
                              list($key, $numValues) = each($allFilters);
                              for ($i = 0; $i < $numValues; $i++)
                              {
                                    // Get that many values and store for that field
                                    list($key, $value) = each($allFilters);
                                    $this->AddDef($filterName, $field, $value);
                              }
                        }
                  }
            }
      }

      function SetFromURLString($strURL)
      {
            $this->Clear();

            parse_str($strURL);

            if (IsSet($vt))
                  $this->table = $vt;

            if (IsSet($vs))
                  $this->style = $vs;

            if (IsSet($vti))
                  $this->title = $vti;

            if (IsSet($vc) && trim($vc) != '')
                  $this->columns = explode(',', $vc);

            if (IsSet($vch) && trim($vch) != '')
                  $this->columnhdrs = explode(',', $vch);

            if (IsSet($vg) && trim($vg) != '')
                  $this->groups = explode(',', $vg);

            if (IsSet($vo) && trim($vo) != '')
                  $this->order = explode(',', $vo);

            $filterSet = array(
                        'vf' => 'filter',
                        'vfn' => 'filternot',
                        'vfd' => 'filterdate',
                        'vfl' => 'filterlike');

            while (list($urlName, $filterName) = each($filterSet))
            {
                  if (IsSet($$urlName) && trim($$urlName) != '')
                  {
                        $allFilters = explode(',', $$urlName);

                        // Get the field
                        while (list($key, $field) = each($allFilters))
                        {
                              // Get how many are in there
                              list($key, $numValues) = each($allFilters);
                              for ($i = 0; $i < $numValues; $i++)
                              {
                                    // Get that many values and store for that field
                                    list($key, $value) = each($allFilters);
                                    $this->AddDef($filterName, $field, $value);
                              }
                        }
                  }
            }
      }

      function AddDef($which, $field, $value = '')
      {
            if ($this->table == 'workorders' && $field == 'account')
                  $field = 'dcl_wo_account.account_id';

            if (is_array($value))
            {
                  foreach ($value as $key => $qvalue)
                  {
                        if (substr($which, 0, 6) == 'filter')
                              $code = sprintf('$this->%s["%s"][] = "%s";', $which, $field, $qvalue);
                        else
                              $code = sprintf('$this->%s[] = "%s";', $which, $qvalue);

                        eval($code);
                  }                             
            }
            else
            {
                  if (substr($which, 0, 6) == 'filter')
                        $code = sprintf('$this->%s["%s"][] = "%s";', $which, $field, $value);
                  else
                        $code = sprintf('$this->%s[] = "%s";', $which, $field);

                  eval($code);
            }
      }
      
      function RemoveDef($which, $field)
      {
            if ($this->table == 'workorders' && $field == 'account')
                  $field = 'dcl_wo_account.account_id';

            if (substr($which, 0, 6) == 'filter')
                  $code = sprintf('unset($this->%s["%s"]);', $which, $field);
            else
                  $code = sprintf('$this->%s = array();', $which);

            eval($code);
      }
      
      function ReplaceDef($which, $field, $value = '')
      {
            $this->RemoveDef($which, $field);
            $this->AddDef($which, $field, $value);
      }

      // That's Comma Separated List :)
      function GetCSLFromArray(&$arr, $appendTableForJoin = false, $bProcessDates = false)
      {
            global $phpgw_baseline;

            reset($arr);
            $retVal = '';

            if (count($arr) > 0)
            {
                  if ($appendTableForJoin == true && count($this->joins) > 0)
                  {
                        while (list($key, $field) = each($arr))
                        {
                              if ($retVal != '')
                                    $retVal .= ',';

                              if ($bProcessDates)
                              {
                                    if (strpos($field, '.') > 0)
                                    {
                                          list($sTable, $sField) = explode('.', $field);
                                          $sRealTable = $sTable;
                                    }
                                    else
                                    {
                                          $sTable = $this->table;
                                          $sRealTable = $sTable;
                                          $sField = $field;
                                    }

                                    if (strlen($sTable) == 1 && ($sTable == 'a' || $sTable == 'b' || $sTable == 'c'))
                                          $sRealTable = 'personnel';

                                    if (!$this->m_oDB)
                                          $this->m_oDB = new dclDB;

                                    LoadSchema($sRealTable);
                                    if ($phpgw_baseline[$sRealTable]['fd'][$sField]['type'] == 'timestamp')
                                          $retVal .= $this->m_oDB->ConvertTimestamp($sTable . '.' . $sField, $sField);
                                    else if ($phpgw_baseline[$sRealTable]['fd'][$sField]['type'] == 'date')
                                          $retVal .= $this->m_oDB->ConvertDate($sTable . '.' . $sField, $sField);
                                    else
                                          $retVal .= $sTable . '.' . $sField;
                              }
                              else
                              {
                                    if (strpos($field, '.') > 0)
                                          $retVal .= $field; // He said they've already got one!
                                    else
                                          $retVal .= $this->table . '.' . $field;
                              }
                        }
                  }
                  else
                        $retVal = implode(',', $arr);
            }

            return $retVal;
      }

      function GetJoinForTable($table)
      {
            if ($table == '' || $table == $this->table)
                  return '';

            $join = '';
            if ($this->table == 'workorders')
            {
                  switch ($table)
                  {
                        case 'severities':
                              $join = 'workorders.severity=severities.id';
                              break;
                        case 'priorities':
                              $join = 'workorders.priority=priorities.id';
                              break;
                        case 'products':
                              $join = 'workorders.product=products.id';
                              break;
                        case 'dcl_projects':
                              $join = 'dcl_projects.projectid = projectmap.projectid';
                              break;
                        case 'dcl_product_module':
                              $join = 'workorders.module_id=dcl_product_module.product_module_id';
                              break;
                        case 'projectmap':
                              $join = 'workorders.jcn = projectmap.jcn AND (workorders.seq = projectmap.seq OR projectmap.seq = 0)';
                              break;
                        case 'statuses':
                              $join = 'workorders.status=statuses.id';
                              break;
                        case 'personnel a':
                              $join = 'workorders.responsible=a.id';
                              break;
                        case 'personnel b':
                              $join = 'workorders.closedby=b.id';
                              break;
                        case 'personnel c':
                              $join = 'workorders.createby=c.id';
                              break;
                        case 'dcl_status_type':
                              $join = '(workorders.status = statuses.id AND statuses.dcl_status_type = dcl_status_type.dcl_status_type_id)';
                              break;
                        case 'dcl_wo_type':
                              $join = 'workorders.wo_type_id = dcl_wo_type.wo_type_id';
                              break;
                        case 'accounts':
                              $join = 'dcl_wo_account.account_id = accounts.id';
                              break;
                        case 'dcl_wo_account':
                              $join = 'workorders.jcn = dcl_wo_account.wo_id AND workorders.seq = dcl_wo_account.seq';
                              break;
                  }
            }
            elseif ($this->table == 'tickets')
            {
                  switch ($table)
                  {
                        case 'severities':
                              $join = 'tickets.type=severities.id';
                              break;
                        case 'priorities':
                              $join = 'tickets.priority=priorities.id';
                              break;
                        case 'accounts':
                              $join = 'tickets.account=accounts.id';
                              break;
                        case 'products':
                              $join = 'tickets.product=products.id';
                              break;
                        case 'dcl_product_module':
                              $join = 'tickets.module_id=dcl_product_module.product_module_id';
                              break;
                        case 'statuses':
                              $join = 'tickets.status=statuses.id';
                              break;
                        case 'personnel a':
                              $join = 'tickets.responsible=a.id';
                              break;
                        case 'personnel b':
                              $join = 'tickets.closedby=b.id';
                              break;
                        case 'personnel c':
                              $join = 'tickets.createdby=c.id';
                              break;
                        case 'dcl_status_type':
                              $join = '(tickets.status = statuses.id AND statuses.dcl_status_type = dcl_status_type.dcl_status_type_id)';
                              break;
                  }
            }
            elseif ($this->table == 'dcl_projects')
            {
                  switch ($table)
                  {
                        case 'statuses':
                              $join = 'dcl_projects.status=statuses.id';
                              break;
                        case 'personnel a':
                              $join = 'dcl_projects.reportto=a.id';
                              break;
                  }
            }
            elseif ($this->table == 'dcl_session')
            {
                  switch ($table)
                  {
                        case 'personnel':
                              $join = 'dcl_session.personnel_id = personnel.id';
                              break;
                  }
            }
            elseif ($this->table == 'personnel')
            {
                  switch ($table)
                  {
                        case 'departments':
                              $join = 'personnel.department = departments.id';
                              break;
                        case 'personnel a':
                              $join = 'personnel.reportto = a.id';
                              break;
                  }
            }

            return $join;
      }

      function AppendJoins(&$arr)
      {
            global $dcl_info;

            if (count($arr) > 0)
            {
                  reset($arr);
                  $joinon = '';
                  $i = 0;
                  while (list($key, $field) = each($arr))
                  {
                        // If field is an array, then it contains values - $key is our real field
                        $bIsValues = is_array($field);
                        if ($bIsValues)
                              $field = $key;

                        if (strpos($field, '.') > 0)
                        {
                              list($table, $tablefield) = explode('.', $field);
                              if ($table == $this->table)
                                    continue;

                              $iJoinType = 1; // 1 = normal, 2 = left
                              switch ($table)
                              {
                                    case 'a':
                                          $table = 'personnel a';
                                          break;
                                    case 'b':
                                          $table = 'personnel b';
                                          break;
                                    case 'c':
                                          $table = 'personnel c';
                                          break;
                                    case 'responsible':
                                          $table = 'personnel a';
                                          if ($bIsValues)
                                          {
                                                $arr[str_replace('responsible', 'a', $key)] = $arr[$key];
                                                unset($arr[$key]);
                                          }
                                          else
                                                $arr[$i] = str_replace('responsible', 'a', $arr[$i]);
                                          break;
                                    case 'closedby':
                                          $table = 'personnel b';
                                          if ($bIsValues)
                                          {
                                                $arr[str_replace('closedby', 'b', $key)] = $arr[$key];
                                                unset($arr[$key]);
                                          }
                                          else
                                                $arr[$i] = str_replace('closedby', 'b', $arr[$i]);
                                          break;
                                    case 'createdby':
                                    case 'createby':
                                          if ($bIsValues)
                                          {
                                                $arr[str_replace($table, 'b', $key)] = $arr[$key];
                                                unset($arr[$key]);
                                          }
                                          else
                                                $arr[$i] = str_replace($table, 'c', $arr[$i]);
                                          $table = 'personnel c';
                                          break;
                                    case 'reportto':
                                          $table = 'personnel a';
                                          if ($bIsValues)
                                          {
                                                $arr[str_replace('reportto', 'a', $key)] = $arr[$key];
                                                unset($arr[$key]);
                                          }
                                          else
                                                $arr[$i] = str_replace('reportto', 'a', $arr[$i]);
                                          break;
                                    case 'accounts':
                                          $iJoinType = 2;
                                          break;
                                    case 'dcl_projects':
                                          $iJoinType = 2;
                                          break;
                                    case 'projectmap':
                                          $iJoinType = 2;
                                          break;
                                    case 'dcl_product_module':
                                          $iJoinType = 2;
                                          break;
                                    case 'dcl_wo_account':
                                          $iJoinType = 2;
                                          break;
                              }

                              if (!IsSet($this->joins[$table]))
                              {
                                    // work orders are associated to projects in the projectmap table
                                    // so append it here - we don't want it in the selected columns

                                    // Ensure we join dcl_wo_account before accounts
                                    if ($table == 'dcl_projects')
                                          $this->joins['projectmap'] = 2;
                                    else if ($this->table == 'workorders' && $table == 'accounts' && !isset($this->joins['dcl_wo_account']))
                                          $this->joins['dcl_wo_account'] = 2;

                                    if (!($dcl_info['DCL_WO_SECONDARY_ACCOUNTS_ENABLED'] == 'Y' &&
                                          $this->table == 'workorders' &&
                                          ($table == 'dcl_wo_account' || $table == 'accounts') &&
                                          !in_array('accounts.name', $this->order) &&
                                          !in_array('accounts.name', $this->groups) &&
                                          !in_array('accounts.name', $this->columns)))
                                          $this->joins[$table] = $iJoinType;
                              }
                        }

                        $i++;
                  }
            }
      }

      function GetSQL($bCount = false)
      {
            global $dcl_domain, $dcl_domain_info, $dcl_info;

            $this->joins = array();
            $this->AppendJoins($this->order);
            $this->AppendJoins($this->groups);
            $this->AppendJoins($this->columns);
            $this->AppendJoins($this->filter);
            $this->AppendJoins($this->filternot);

            $sql = 'SELECT ';

            if ($bCount)
            {
                  $sql .= 'COUNT(*)';
            }
            else
            {
                  if (count($this->columns) > 0 || count($this->groups) > 0)
                  {
                        if (count($this->groups) > 0)
                        {
                              $aGroups = $this->groups;
                              reset($aGroups);
                              while (list($key, $group) = each($aGroups))
                              {
                                    // If we group by weight, we want to display the name while ordering by weight
                                    if ($group == 'priorities.weight')
                                          $aGroups[$key] = 'priorities.name';
                                    else if ($group == 'severities.weight')
                                          $aGroups[$key] = 'severities.name';
                              }

                              $sql .= $this->GetCSLFromArray($aGroups, true);
                        }

                        if (count($this->columns) > 0)
                        {
                              if (count($this->groups) > 0)
                                    $sql .= ",";

                              $sql .= $this->GetCSLFromArray($this->columns, true, true);
                        }
                  }
                  else
                        $sql .= '*';
                        
                  if ($this->table == 'workorders' && $dcl_info['DCL_WO_SECONDARY_ACCOUNTS_ENABLED'] == 'Y')
                  {
                        // If we show account, but don't group by it, then we will want to display an icon to show
                        // the extra accounts.  This count will determine if we need to display the marker for more info
                        // and will be left out of the final rendering by htmlView
                        if (in_array('accounts.name', $this->columns))
                        {
                              $sql .= ', (select count(*) from dcl_wo_account where wo_id = workorders.jcn And seq = workorders.seq) As _num_accounts_';
                        }
                  }

            }

            $sql .= ' FROM ' . $this->table;

            $bDoneDidWhere = false;

            // Add joins, if any
            if (count($this->joins) > 0)
            {
                  reset($this->joins);
                  $joinsql = '';

                  // Ensure these join in the proper order
                  if (isset($this->joins['dcl_status_type']))
                  {
                        if (isset($this->joins['statuses']))
                        {
                              $statusesSQL = $this->joins['statuses'];
                              unset($this->joins['statuses']);
                        }
                        else
                              $statusesSQL = ' JOIN statuses ON ' . $this->GetJoinForTable('statuses');

                        $typesSQL = $this->joins['dcl_status_type'];
                        unset($this->joins['dcl_status_type']);

                        $this->joins['statuses'] = $statusesSQL;
                        $this->joins['dcl_status_type'] = $typesSQL;
                  }

                  while (list($table, $iJoinType) = each($this->joins))
                  {
                        if ($iJoinType == 2)
                              $joinsql .= ' LEFT';
                        else if ($dcl_domain_info[$dcl_domain]['dbType'] == 'mysql') // Hackage to make MySQL happy again
                              $joinsql .= ' INNER';

                        $joinsql .= ' JOIN ' . $table . ' ON ' . $this->GetJoinForTable($table);
                  }

                  $sql .= $joinsql . ' ';
            }

            $productSQL = '';
            $moduleSQL = '';
            $deptSQL = '';
            $responsibleSQL = '';
            $createbySQL = '';
            $closedbySQL = '';
            if (count($this->filter) > 0)
            {
                  if ($bDoneDidWhere == false)
                  {
                        $bDoneDidWhere = true;
                        $sql .= ' WHERE ';
                  }
                  else
                        $sql .= ' AND ';

                  $bFirst = true;
                  reset($this->filter);
                  while (list($field, $values) = each($this->filter))
                  {
                        // prepend table name if not specified to avoid ambiguity
                        if (strpos($field, '.') < 1)
                              $field = $this->table . '.' . $field;

                        if (eregi('^.*\.product', $field))
                        {
                              if (count($values) == 1)
                                    $productSQL = "($field=" . $this->GetCSLFromArray($values) . ')';
                              else
                                    $productSQL = "($field in (" . $this->GetCSLFromArray($values) . '))';
                        }
                        else if (eregi('^.*\.module_id', $field))
                        {
                              if (count($values) == 1)
                                    $moduleSQL = "($field=" . $this->GetCSLFromArray($values) . ')';
                              else
                                    $moduleSQL = "($field in (" . $this->GetCSLFromArray($values) . '))';
                        }
                        else if (eregi('^.*\.department', $field))
                        {
                              if (count($values) == 1)
                                    $deptSQL = "($field=" . $this->GetCSLFromArray($values) . ')';
                              else
                                    $deptSQL = "($field in (" . $this->GetCSLFromArray($values) . '))';
                        }
                        else if (eregi('^.*\.responsible', $field))
                        {
                              if (count($values) == 1)
                                    $responsibleSQL = "($field=" . $this->GetCSLFromArray($values) . ')';
                              else
                                    $responsibleSQL = "($field in (" . $this->GetCSLFromArray($values) . '))';
                        }
                        else if (eregi('^.*\.create[d]?by', $field))
                        {
                              if (count($values) == 1)
                                    $createbySQL = "($field=" . $this->GetCSLFromArray($values) . ')';
                              else
                                    $createbySQL = "($field in (" . $this->GetCSLFromArray($values) . '))';
                        }
                        else if (eregi('^.*\.closedby', $field))
                        {
                              if (count($values) == 1)
                                    $closedbySQL = "($field=" . $this->GetCSLFromArray($values) . ')';
                              else
                                    $closedbySQL = "($field in (" . $this->GetCSLFromArray($values) . '))';
                        }
                        else if ($dcl_info['DCL_WO_SECONDARY_ACCOUNTS_ENABLED'] == 'Y' &&
                                    $this->table == 'workorders' &&
                                    !in_array('accounts.name', $this->order) &&
                                    !in_array('accounts.name', $this->groups) &&
                                    !in_array('accounts.name', $this->columns) &&
                                    eregi('^.*\.account', $field))
                        {
                              // Multi-account filter only does subselect - not MySQL compatible yet
                              if ($bFirst == false)
                                    $sql .= ' AND ';

                              $bFirst = false;

                              $sql .= "((workorders.jcn in (select wo_id from dcl_wo_account where account_id in (" . $this->GetCSLFromArray($values) . ")))";
                              $sql .= " AND (workorders.seq in (select seq from dcl_wo_account where workorders.jcn = wo_id And account_id in (" . $this->GetCSLFromArray($values) . "))";
                              $sql .= '))';
                        }
                        else
                        {
                              if ($bFirst == false)
                                    $sql .= ' AND ';

                              $bFirst = false;
                              if (count($values) == 1)
                                    $sql .= "($field=" . $this->GetCSLFromArray($values) . ')';
                              else
                                    $sql .= "($field in (" . $this->GetCSLFromArray($values) . '))';
                        }
                  }
            }

            // Now handle product and module specially because module is exclusive to product, but
            // we may still have other products where they don't filter by module
            if ($productSQL != '' && $moduleSQL != '')
            {
                  if ($bFirst == false)
                        $sql .= ' AND ';

                  $bFirst = false;
                  $sql .= '(' . $productSQL . ' OR ' . $moduleSQL . ')';
            }
            else if ($productSQL != '')
            {
                  if ($bFirst == false)
                        $sql .= ' AND ';

                  $bFirst = false;
                  $sql .= $productSQL;
            }
            else if ($moduleSQL != '')
            {
                  if ($bFirst == false)
                        $sql .= ' AND ';

                  $bFirst = false;
                  $sql .= $moduleSQL;
            }

            $arrPersonnel = array();
            if ($deptSQL != '')
                  $arrPersonnel[] = $deptSQL;
            if ($responsibleSQL != '')
                  $arrPersonnel[] = $responsibleSQL;
            if ($createbySQL != '')
                  $arrPersonnel[] = $createbySQL;
            if ($closedbySQL != '')
                  $arrPersonnel[] = $closedbySQL;

            if (count($arrPersonnel) > 0)
            {
                  if ($bFirst == false)
                        $sql .= ' AND ';

                  $bFirst = false;

                  if (count($arrPersonnel) > 1)
                  {
                        $sql .= '(';
                        for ($i = 0; $i < count($arrPersonnel); $i++)
                        {
                              if ($i > 0)
                                    $sql .= ' OR ';

                              $sql .= $arrPersonnel[$i];
                        }
                        $sql .= ')';
                  }
                  else
                        $sql .= $arrPersonnel[0];
            }

            if (count($this->filternot) > 0)
            {
                  if ($bDoneDidWhere == false)
                  {
                        $bDoneDidWhere = true;
                        $sql .= ' WHERE ';
                  }
                  else
                        $sql .= ' AND ';

                  $bFirst = true;
                  reset($this->filternot);
                  while (list($field, $values) = each($this->filternot))
                  {
                        // prepend table name if not specified to avoid ambiguity
                        if (strpos($field, '.') < 1)
                              $field = $this->table . '.' . $field;

                        if ($bFirst == false)
                              $sql .= ' AND ';

                        $bFirst = false;
                        if (count($values) == 1)
                              $sql .= "($field!=" . $this->GetCSLFromArray($values) . ')';
                        else
                              $sql .= "($field not in (" . $this->GetCSLFromArray($values) . '))';
                  }
            }

            $objWO = CreateObject('dcl.dbWorkorders');

            if (count($this->filterdate) > 0)
            {
                  if ($bDoneDidWhere == false)
                  {
                        $bDoneDidWhere = true;
                        $sql .= ' WHERE (';
                  }
                  else
                        $sql .= ' AND (';

                  $bFirst = true;
                  reset($this->filterdate);
                  while (list($field, $values) = each($this->filterdate))
                  {
                        // prepend table name if not specified to avoid ambiguity
                        if (strpos($field, '.') < 1)
                              $field = $this->table . '.' . $field;

                        if ($bFirst == false)
                              $sql .= ' ' . $this->logicdate . ' ';

                        $bFirst = false;
                        if ($values[0] != '' && $values[1] != '')
                        {
                              $sql .= sprintf('(%s between %s and %s)',
                                          $field,
                                          $objWO->DisplayToSQL($values[0]),
                                          $objWO->DisplayToSQL($values[1]));
                        }
                        else if ($values[0] != '')
                              {
                                    $sql .= sprintf('(%s >= %s)',
                                                $field,
                                                $objWO->DisplayToSQL($values[0]));
                              }
                              else if ($values[1] != '')
                                    {
                                          $sql .= sprintf('(%s <= %s)',
                                                      $field,
                                                      $objWO->DisplayToSQL($values[1]));
                                    }
                  }

                  $sql .= ')';
            }

            if (count($this->filterlike) > 0)
            {
                  if ($bDoneDidWhere == false)
                  {
                        $bDoneDidWhere = true;
                        $sql .= ' WHERE (';
                  }
                  else
                        $sql .= ' AND (';

                  $bFirst = true;
                  reset($this->filterlike);
                  while (list($field, $values) = each($this->filterlike))
                  {
                        // prepend table name if not specified to avoid ambiguity
                        if (strpos($field, '.') < 1)
                              $field = $this->table . '.' . $field;

                        if ($bFirst == false)
                              $sql .= ' ' . $this->logiclike . ' ';

                        $bFirst = false;
                        $sql .= sprintf('(%s like \'%%%s%%\')', $objWO->GetUpperSQL($field), strtoupper($values[0]));
                  }

                  $sql .= ')';
            }

            if ($this->table == 'workorders' && $dcl_info['DCL_WO_SECONDARY_ACCOUNTS_ENABLED'] == 'Y')
            {
                  // If we group by account, we'll show join the whole lot together.  This will cause a work order
                  // with n accounts to appear in the report n times.  Otherwise, if we only sort or show the account
                  // column, we'll get the first account (in order) and display a link to show the other accounts
                  // as needed
                  if (in_array('accounts.name', $this->columns))
                  {
                        $sql .= ' And (dcl_wo_account.account_id is null Or dcl_wo_account.account_id = ';
                        $sql .= '(Select min(account_id) From dcl_wo_account where wo_id = workorders.jcn And seq = workorders.seq))';
                  }
            }

            if (!$bCount && (count($this->order) > 0 || count($this->groups) > 0))
            {
                  $sql .= ' ORDER BY ';
                  if (count($this->order) > 0 || count($this->groups) > 0)
                        $sql .= $this->GetCSLFromArray(array_merge($this->groups, $this->order), true);
                  else if (count($this->groups) > 0)
                        $sql .= $this->GetCSLFromArray($this->groups, true);
                  else
                        $sql .= $this->GetCSLFromArray($this->order, true);
            }

            return $sql;
      }
}
?>

Generated by  Doxygen 1.6.0   Back to index