[Sommaire] [Imprimer]

   1  <?php
   2  /**************************************************************************\
   3  * eGroupWare - ProjectManager - Pricelist storage object                   *
   4  * http://www.egroupware.org                                                *
   5  * Written and (c) 2005 by Ralf Becker <RalfBecker@outdoor-training.de>     *
   6  * --------------------------------------------                             *
   7  *  This program is free software; you can redistribute it and/or modify it *
   8  *  under the terms of the GNU General Public License as published by the   *
   9  *  Free Software Foundation; either version 2 of the License, or (at your  *
  10  *  option) any later version.                                              *
  11  \**************************************************************************/
  12  
  13  /* $Id: class.sopricelist.inc.php 23135 2006-12-27 11:12:45Z ralfbecker $ */
  14  
  15  include_once (EGW_INCLUDE_ROOT.'/etemplate/inc/class.so_sql.inc.php');
  16  
  17  /**
  18   * Pricelist storage object of the projectmanager
  19   *
  20   * Tables: egw_pm_pricelist, egw_pm_prices
  21   *
  22   * @package projectmanager
  23   * @author RalfBecker-AT-outdoor-training.de
  24   * @copyright (c) 2005 by RalfBecker-AT-outdoor-training.de
  25   * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License
  26   */
  27  class sopricelist extends so_sql
  28  {
  29      /**
  30       * @var string $prices_table table name of the prices table
  31       */
  32      var $prices_table = 'egw_pm_prices';
  33      /**
  34       * @var string $prices_join default join with the prices table
  35       */
  36      var $prices_join = 'JOIN egw_pm_prices p ON egw_pm_pricelist.pl_id=p.pl_id';
  37      /**
  38       * @var array $links_extracols extracolumns from the links table
  39       */
  40      var $prices_extracols = array('pm_id','pl_validsince','pl_price','pl_customertitle','pl_modifier','pl_modified','pl_billable');
  41      /**
  42       * @var int $pm_id project we work on or 0 for standard pricelist only
  43       */
  44      var $pm_id;
  45  
  46      /**
  47       * Constructor, calls the constructor of the extended class
  48       * 
  49       * @param int $pm_id=0 pm_id of the project to use, default 0 (project independent / standard prices)
  50       */
  51  	function sopricelist($pm_id=0)
  52      {
  53          $this->so_sql('projectmanager','egw_pm_pricelist');    // sets $this->table_name
  54  
  55          $this->pm_id = (int) $pm_id;
  56      }
  57      
  58      /**
  59       * reads one pricelist-itme specified by $keys, reimplemented to use $this->pm_id, if no pm_id given
  60       *
  61       * @param array $keys array with keys in form internalName => value, may be a scalar value if only one key
  62       * @param string/array $extra_cols string or array of strings to be added to the SELECT, eg. "count(*) as num"
  63       * @param string/boolean $join=true default join with links-table or string as in so_sql
  64       * @return array/boolean data if row could be retrived else False
  65      */
  66  	function read($keys,$extra_cols='',$join=true)
  67      {
  68          if (!is_array($keys)) $keys = array('pl_id' => (int) $keys);
  69  
  70          if ($join === true)    // add join with links-table and extra-columns
  71          {
  72              // just pl_id would be ambigues
  73              $keys[] = $this->table_name.'.pl_id='.(int)$keys['pl_id'];
  74              unset($keys['pl_id']);
  75              
  76              if (isset($keys['pl_validsince']))
  77              {
  78                  $keys[] = 'pl_validsince <= '.(int)$keys['pl_validsince'];
  79                  unset($keys['pl_validsince']);
  80              }
  81              if (isset($keys['pm_id']))
  82              {
  83                  $keys[] = $this->db->expression($this->prices_table,array('pm_id' => $keys['pm_id']));
  84                  unset($keys['pm_id']);
  85              }
  86              $join = $this->prices_join;
  87              
  88              if (!$extra_cols) $extra_cols = $this->prices_extracols;
  89              
  90              // we use search as the join might return multiple columns, which we put in the prices and project_prices array
  91              if (!($prices = $this->search(false,false,'pm_id DESC,pl_validsince DESC',$extra_cols,'',false,'AND',false,$keys,$join)))
  92              {
  93                  return false;
  94              }
  95              list(,$this->data) = each($prices);
  96              $this->data['prices'] = $this->data['project_prices'] = array();            
  97              
  98              foreach($prices as $price)
  99              {
 100                  if ($price['pm_id'])
 101                  {
 102                      $this->data['project_prices'][] = $price;
 103                  }
 104                  else
 105                  {
 106                      $this->data['prices'][] = $price;
 107                  }
 108              }
 109              return $this->data;
 110          }
 111          return parent::read($keys,$extra_cols,$join);
 112      }
 113      
 114      /**
 115       * sql to define a priority depending on the relation-ship of the project the price was defined for to the given one
 116       *
 117       * The given project $pm_id has the highest priority, if $use_standard the standard-pricelist has priority 0
 118       *
 119       * @param int $pm_id=0 project to use, default $this->pm_id
 120       * @param string $col='pm_id' column-name to use, default 'pm_id', can be changed to use a table-name/-alias too
 121       * @param boolean $use_general=true should the general-pricelist be included too (pm_id=0)
 122       * @return string sql with CASE WHEN
 123       */
 124  	function sql_priority($pm_id=0,$col='pm_id',$use_general=true)
 125      {
 126          if (!$pm_id) $pm_id = $this->pm_id;
 127          
 128          $ancestors = array($pm_id);
 129          
 130          if (!($ancestors = $this->project->ancestors($pm_id,$ancestors)))
 131          {
 132              echo "<p>sql_priority($pm_id,$col,$use_general) ancestory returnd false</p>\n";
 133              return false;
 134          }
 135          $sql = 'CASE '.$col;
 136          foreach($ancestors as $n => $pm_id)
 137          {
 138              $sql .= ' WHEN '.$pm_id.' THEN '.(count($ancestors)-$n);
 139          }
 140          if ($use_general)
 141          {
 142              $sql .= ' WHEN 0 THEN 0';
 143          }
 144          $sql .= ' END';
 145      
 146          return $sql;
 147      }
 148  
 149      /**
 150       * search elements, reimplemented to use $this->pm_id, if no pm_id given in criteria or filter and join with the prices table
 151       *
 152       * @param array/string $criteria array of key and data cols, OR a SQL query (content for WHERE), fully quoted (!)
 153       * @param boolean $only_keys True returns only keys, False returns all cols
 154       * @param string $order_by fieldnames + {ASC|DESC} separated by colons ','
 155       * @param string/array $extra_cols string or array of strings to be added to the SELECT, eg. "count(*) as num"
 156       * @param string $wildcard appended befor and after each criteria
 157       * @param boolean $empty False=empty criteria are ignored in query, True=empty have to be empty in row
 158       * @param string $op defaults to 'AND', can be set to 'OR' too, then criteria's are OR'ed together
 159       * @param int/boolean $start if != false, return only maxmatch rows begining with start
 160       * @param array $filter if set (!=null) col-data pairs, to be and-ed (!) into the query without wildcards
 161       * @param string/boolean $join=true default join with prices-table or string as in so_sql
 162       * @return array of matching rows (the row is an array of the cols) or False
 163       */
 164  	function search($criteria,$only_keys=false,$order_by='pl_title',$extra_cols='',$wildcard='',$empty=False,$op='AND',$start=false,$filter=null,$join=true)
 165      {
 166          if ($join === true)    // add join with prices-table
 167          {
 168              $join = $this->prices_join;
 169              
 170              if (isset($filter['pl_validsince']))
 171              {
 172                  $filter[] = 'pl_validsince <= '.($validsince=(int)$filter['pl_validsince']);
 173                  unset($filter['pl_validsince']);
 174              }
 175              else
 176              {
 177                  $validsince = time();
 178              }
 179              if (!$extra_cols)
 180              {
 181                  $extra_cols = $this->prices_extracols;
 182              }
 183              else
 184              {
 185                  $extra_cols = array_merge($this->prices_extracols,
 186                      is_array($extra_cols) ? $extra_cols : explode(',',$extra_cols));
 187              }
 188              if (($no_general = isset($criteria['pm_id'])))
 189              {
 190                  $pm_id = $criteria['pm_id'];
 191                  unset($criteria['pm_id']);
 192              }
 193              else
 194              {
 195                  $pm_id = isset($filter['pm_id']) ? $filter['pm_id'] : $this->pm_id;
 196                  unset($filter['pm_id']);
 197              }
 198              if ($this->db->capabilities['sub_queries'])
 199              {
 200                  $filter[] = "pl_validsince = (SELECT MAX(t.pl_validsince) FROM $this->prices_table t WHERE p.pl_id=t.pl_id AND p.pm_id=t.pm_id AND t.pl_validsince <= $validsince)";
 201  
 202                  if (!$order_by) $order_by = 'pl_title';
 203  
 204                  if ($pm_id)
 205                  {
 206                      $filter[] = $this->sql_priority($pm_id,'p.pm_id').' = (select MAX('.$this->sql_priority($pm_id,'m.pm_id').
 207                          ") FROM $this->prices_table m WHERE m.pl_id=p.pl_id)";
 208  
 209                      if ($no_general) $filter[] = 'pm_id != 0 AND pl_billable IN (0,1)';
 210                  }
 211                  else
 212                  {
 213                      $filter['pm_id'] = 0;
 214                  }
 215              }
 216              else    // mysql 4.0 or less
 217              {
 218                  // ToDo: avoid subqueries (eg. use a join) or do it manual inside php
 219                  //echo "<p>Pricelist needs a DB capabal of subqueries (eg. MySQL 4.1+) !!!</p>\n";
 220                  $filter['pm_id'] = $this->project->ancestors($this->pm_id,array(0,(int)$this->pm_id));
 221                  $filter[] = 'pl_validsince <= '.$validsince;
 222              }
 223              $prices_filter = array();
 224              foreach($extra_cols as $col)
 225              {
 226                  if (isset($filter[$col]))
 227                  {
 228                      $prices_filter[$col] = $filter[$col];
 229                      unset($filter[$col]);
 230                  }
 231              }
 232              if (count($prices_filter))
 233              {
 234                  $filter[] = $this->db->expression($this->prices_table,$prices_filter);
 235              }
 236              // do we run a search
 237              if ($op == 'OR' && $wildcard == '%' && $criteria['pl_id'] && $criteria['pl_id'] == $criteria['pl_title'])
 238              {
 239                  foreach(array('pl_customertitle','pl_price') as $col)
 240                  {
 241                      $criteria[] = $col. ' LIKE '.$this->db->quote('%'.$criteria['pl_id'].'%');
 242                  }
 243                  unset($criteria['pl_id']);    // its ambigues
 244              }
 245          }
 246          // include sub-categories in the search
 247          if ($filter['cat_id'])
 248          {
 249              if (!is_object($GLOBALS['egw']->categories))
 250              {
 251                  $GLOBALS['egw']->categories =& CreateObject('phpgwapi.categories');
 252              }
 253              $filter['cat_id'] = $GLOBALS['egw']->categories->return_all_children($filter['cat_id']);
 254          }
 255          return parent::search($criteria,$only_keys,$order_by,$extra_cols,$wildcard,$empty,$op,$start,$filter,$join);
 256      }
 257      
 258      /**
 259       * saves a single price 
 260       *
 261       * @param array $price price-data
 262       * @param boolean $touch_modified=true should the modififcation date and user be set
 263       * @param int 0 on success, db::Errno otherwise
 264       */
 265  	function save_price(&$price,$touch_modified=true)
 266      {
 267          //echo "<p>sopricelist::save_price(".print_r($price,true).",$touch_modified)</p>\n";
 268          $price = $this->data2db($price);
 269  
 270          if ($touch_modified)
 271          {
 272              $price['pl_modified'] = time();
 273              $price['pl_modifier'] = $GLOBALS['egw_info']['user']['account_id'];
 274          }
 275          $this->db->insert($this->prices_table,$price,array(
 276              'pm_id' => $price['pm_id'],
 277              'pl_id' => $price['pl_id'],
 278              'pl_validsince' => $price['pl_validsince'],
 279          ),__LINE__,__FILE__);
 280          
 281          $price = $this->db2data($price);
 282  
 283          return $this->db->Errno;
 284      }
 285      
 286      /**
 287       * delete pricelist-entries and price(s) specified by keys pl_id, pm_id and/or pl_validsince
 288       *
 289       * If the last price of a pricelist-entry gets deleted, the pricelist entry is automatic deleted too!
 290       *
 291       * @param array/int $keys array with keys pm_id, pl_id and/or pl_validsince to delete or integer pm_id
 292       * @return int number of deleted prices
 293       */
 294  	function delete($keys)
 295      {
 296          //echo "<p>sopricelist::delete(".print_r($keys,true).",$touch_modified)</p>\n";
 297          if (!is_array($keys) && (int) $keys) $keys = array('pm_id' => (int) $keys);
 298          
 299          $keys = $this->data2db($keys);    // adjust the validsince timestampt to server-time
 300  
 301          $where = array();
 302          foreach(array('pm_id','pl_id','pl_validsince') as $key)
 303          {
 304              if (isset($keys[$key])) $where[$key] = $keys[$key];
 305          }
 306          if (!$where) return false;
 307  
 308          $this->db->delete($this->prices_table,$where,__LINE__,__FILE__);
 309          $deleted = $this->db->affected_rows();
 310  
 311          // check for pricelist items with no prices and delete them too
 312          $this->db->select($this->table_name,$this->table_name.'.pl_id',$this->prices_table.'.pl_id IS NULL',__LINE__,__FILE__,false,'',false,0,"LEFT JOIN $this->prices_table ON $this->table_name.pl_id=$this->prices_table.pl_id");
 313          $pl_ids = array();
 314          while($this->db->next_record())
 315          {
 316              $pl_ids[] = $this->db->f(0);
 317          }
 318          if ($pl_ids)
 319          {
 320              $this->db->delete($this->table_name,array('pl_id' => $pl_ids),__LINE__,__FILE__);
 321          }
 322          return $deleted;
 323      }
 324  }