1: <?php
  2: /**
  3:  * This is the sql implementation of the Sesha Driver.
  4:  *
  5:  * Required values for $params:<pre>
  6:  *      'phptype'       The database type (e.g. 'pgsql', 'mysql', etc.).
  7:  *      'charset'       The database's internal charset.</pre>
  8:  *
  9:  * Required by some database implementations:<pre>
 10:  *      'hostspec'      The hostname of the database server.
 11:  *      'protocol'      The communication protocol ('tcp', 'unix', etc.).
 12:  *      'database'      The name of the database.
 13:  *      'username'      The username with which to connect to the database.
 14:  *      'password'      The password associated with 'username'.
 15:  *      'options'       Additional options to pass to the database.
 16:  *      'tty'           The TTY on which to connect to the database.
 17:  *      'port'          The port on which to connect to the database.</pre>
 18:  *
 19:  *
 20:  * Copyright 2003-2012 Horde LLC (http://www.horde.org/)
 21:  * Copyright 2004-2007 Andrew Coleman <mercury@appisolutions.net>
 22:  *
 23:  * See the enclosed file COPYING for license information (GPL). If you
 24:  * did not receive this file, see http://www.horde.org/licenses/gpl.
 25:  *
 26:  * @author  Bo Daley <bo@darkwork.net>
 27:  * @author  Andrew Coleman <mercury@appisolutions.net>
 28:  * @package Sesha
 29:  */
 30: class Sesha_Driver_Sql extends Sesha_Driver
 31: {
 32:     /**
 33:      * Handle for the database connection.
 34:      * @var DB
 35:      * @access private
 36:      */
 37:     protected $_db;
 38: 
 39:     /**
 40:      * Flag for the SQL server connection.
 41:      * @var boolean
 42:      * @access private
 43:      */
 44:     protected $_connected;
 45: 
 46:     /**
 47:      * This is the basic constructor for the sql driver.
 48:      *
 49:      * @param array $params  Hash containing the connection parameters.
 50:      */
 51:     public function __construct($params = array())
 52:     {
 53:         $this->_db = $params['db'];
 54:     }
 55: 
 56:     /**
 57:      * This will retrieve all of the stock items from the database, or just a
 58:      * particular category of items.
 59:      *
 60:      * @param integer $category_id  The category ID you want to fetch.
 61:      * @param array $property_ids   The ids of any properties to include in the list.
 62:      *
 63:      * @return array  Array of results on success;
 64:      */
 65:     public function listStock($category_id = null, $property_ids = array())
 66:     {
 67:         if (!$property_ids) {
 68:             $sql = 'SELECT i.stock_id AS stock_id, i.stock_name AS stock_name, i.note AS note FROM sesha_inventory i';
 69:             if ($category_id) {
 70:                 $sql .= ', sesha_inventory_categories c WHERE c.category_id = ' .
 71:                     (int)$category_id . ' AND i.stock_id = c.stock_id';
 72:             }
 73:             $values = array();
 74:         } else {
 75:             // More complicated join to include property values
 76:             if ($category_id) {
 77:                 $sql = '
 78: SELECT i.stock_id AS stock_id, i.stock_name AS stock_name, i.note AS note, p.property_id AS property_id, a.attribute_id AS attribute_id, a.int_datavalue AS int_datavalue, a.txt_datavalue AS txt_datavalue
 79:   FROM sesha_inventory i
 80:   JOIN sesha_inventory_categories c ON c.category_id = ? AND i.stock_id = c.stock_id
 81:   LEFT JOIN sesha_inventory_properties a ON a.stock_id = i.stock_id AND a.property_id IN (?' . str_repeat(', ?', count($property_ids) - 1) . ')
 82:   LEFT JOIN sesha_properties p ON a.property_id = p.property_id ORDER BY a.stock_id, p.priority DESC';
 83:                 $values = array_merge(array($category_id), $property_ids);
 84:             } else {
 85:                 $sql = '
 86: SELECT i.stock_id AS stock_id, i.stock_name AS stock_name, i.note AS note, p.property_id AS property_id, a.attribute_id AS attribute_id, a.int_datavalue AS int_datavalue, a.txt_datavalue AS txt_datavalue
 87:   FROM sesha_inventory i
 88:   LEFT JOIN sesha_inventory_properties a ON a.stock_id = i.stock_id AND a.property_id IN (?' . str_repeat(', ?', count($property_ids) - 1) . ')
 89:   LEFT JOIN sesha_properties p ON a.property_id = p.property_id ORDER BY a.stock_id, p.priority DESC';
 90:                 $values = $property_ids;
 91:             }
 92:         }
 93: 
 94:         try {
 95:             $result = $this->_db->selectAll($sql, $values);
 96:         } catch (Horde_Db_Exception $e) {
 97:             throw new Sesha_Exception($e);
 98:         }
 99: 
100:         if ($property_ids) {
101:             return $this->_normalizeStockProperties($result, $property_ids);
102:         } else {
103:             return $result;
104:         }
105:     }
106: 
107:     /**
108:      * This will retrieve all matching items from the database.
109:      *
110:      * @param string $what     What to find.
111:      * @param constant $where  Where to find the information (bitmask).
112:      * @param array $property_ids   The ids of any properties to include in the list.
113:      *
114:      * @return array  Array of results on success
115:      */
116:     public function searchStock($what, $where = SESHA_SEARCH_NAME, $property_ids = array())
117:     {
118:         if (is_null($what) || is_null($where)) {
119:             throw new Sesha_Exception("Invalid search parameters");
120:         }
121: 
122:         // Start the query
123:         if ($property_ids) {
124:             $sql = 'SELECT DISTINCT i.stock_id AS stock_id, i.stock_name AS stock_name, i.note AS note, p1.property_id AS property_id, a.attribute_id AS attribute_id, a.int_datavalue AS int_datavalue, a.txt_datavalue AS txt_datavalue
125:   FROM sesha_inventory i
126:   LEFT JOIN sesha_inventory_properties a ON a.stock_id = i.stock_id AND a.property_id IN (?' . str_repeat(', ?', count($property_ids) - 1) . ')
127:   LEFT JOIN sesha_properties p1 ON a.property_id = p1.property_id';
128:             $values = $property_ids;
129:         } else {
130:             $sql = 'SELECT DISTINCT i.stock_id AS stock_id, i.stock_name AS stock_name, i.note AS note FROM sesha_inventory i';
131:             $values = array();
132:         }
133: 
134:         // Create where clause
135:         $what = $this->_db->quote(sprintf('%%%s%%', $what));
136:         $whereClause = array();
137:         if ($where & SESHA_SEARCH_ID) {
138:             $whereClause[] = 'i.stock_id LIKE ' . $what;
139:         }
140:         if ($where & SESHA_SEARCH_NAME) {
141:             $whereClause[] = 'i.stock_name LIKE ' . $what;
142:         }
143:         if ($where & SESHA_SEARCH_NOTE) {
144:             $whereClause[] = 'i.note like ' . $what;
145:         }
146:         if ($where & SESHA_SEARCH_PROPERTY) {
147:             $sql .= ', sesha_inventory_properties p2';
148:             $whereClause[] = '(p2.txt_datavalue LIKE ' . $what .
149:                 ' AND i.stock_id = p2.stock_id)';
150:         }
151: 
152:         /*
153: SELECT i.stock_id AS stock_id, i.stock_name AS stock_name, i.note AS note, p.property_id AS property_id, a.attribute_id AS attribute_id, a.int_datavalue AS int_datavalue, a.txt_datavalue AS txt_datavalue
154:   FROM sesha_inventory i
155:   LEFT JOIN sesha_inventory_properties a ON a.stock_id = i.stock_id AND a.property_id IN (?' . str_repeat(', ?', count($property_ids) - 1) . ')
156:   LEFT JOIN sesha_properties p ON a.property_id = p.property_id ORDER BY a.stock_id, p.priority DESC
157:         */
158: 
159:         if (count($whereClause)) {
160:             $sql .= ' WHERE ' . implode(' OR ', $whereClause);
161:         }
162:         $sql .= ' ORDER BY i.stock_id';
163:         if ($property_ids) {
164:             $sql .= ', p1.priority DESC';
165:         }
166: 
167:         try {
168:             $result = $this->_db->selectAll($sql, $values);
169:         } catch (Horde_Db_Exception $e) {
170:             throw new Sesha_Exception($e);
171:         }
172: 
173:         if ($property_ids) {
174:             return $this->_normalizeStockProperties($result, $property_ids);
175:         } else {
176:             return $result;
177:         }
178:     }
179: 
180:     /**
181:      * This function retrieves a single stock item from the database.
182:      *
183:      * @param integer $stock_id  The numeric ID of the stock item to fetch.
184:      *
185:      * @return array  a stock item
186:      * @throws Sesha_Exception
187:      */
188:     public function fetch($stock_id)
189:     {
190: 
191:         // Build the query
192:         $sql = 'SELECT * FROM sesha_inventory WHERE stock_id = ?';
193:         $values = array((int)$stock_id);
194: 
195:         // Perform the search
196:         try {
197:             return $this->_db->selectOne($sql, $values);
198:         } catch (Horde_Db_Exception $e) {
199:             throw new Sesha_Exception($e);
200:         }
201:     }
202: 
203:     /**
204:      * Removes a stock entry from the database. Also removes all related
205:      * category and property information.
206:      *
207:      * @param integer $stock_id  The ID of the item to delete.
208:      *
209:      * @return boolean  True on success
210:      * @throws Sesha_Exception
211:      *
212:      */
213:     public function delete($stock_id)
214:     {
215: 
216:         // Build, log, and issue the query
217:         $sql = 'DELETE FROM sesha_inventory WHERE stock_id = ?';
218:         $values = array((int)$stock_id);
219:         try {
220:             $result = $this->_db->delete($sql, $values);
221:         } catch (Horde_Db_Exception $e) {
222:             throw new Sesha_Exception($e);
223:         }
224: 
225:         $this->clearPropertiesForStock($stock_id);
226:         $this->updateCategoriesForStock($stock_id, array());
227:         return true;
228:     }
229: 
230:     /**
231:      * This will add a new item to the inventory.
232:      *
233:      * @param array $stock  A hash of values for the stock item.
234:      *
235:      * @return integer  The numeric ID of the newly added item or false.
236:      * @throws Sesha_Exception
237:      */
238:     public function add($stock)
239:     {
240:         // Create the queries
241:         $sql = 'INSERT INTO sesha_inventory(stock_name,note) VALUES(?,?)';
242: 
243:         $values = array($stock['stock_name'], $stock['note']);
244: 
245:         // Perform the queries
246:         try {
247:             $result = $this->_db->insert($sql,$values);
248:         } catch (Horde_Db_Exception $e) {
249:             throw new Sesha_Exception($e);
250:         }
251: 
252:         return $result;
253:     }
254: 
255:     /**
256:      * This function will modify a pre-existing stock entry with new values.
257:      *
258:      * @param array $stock  The hash of values for the inventory item.
259:      *
260:      * @return boolean  True on success.
261:      * @throws Sesha_Exception
262:      */
263:     public function modify($stock_id, $stock)
264:     {
265:         $sql = 'UPDATE sesha_inventory SET stock_name = ?, note = ? WHERE stock_id = ?';
266: 
267:         $values = array($stock['stock_name'],$stock['note'], $stock_id);
268:         // Perform the queries
269:         try { 
270:             $result = $this->_db->update($sql, $values);
271:         } catch (Horde_Db_Exception $e) {
272:             throw new Sesha_Exception($e);
273:         }
274: 
275:         return true;
276:     }
277: 
278:     /**
279:      * This will return the first category found matching a specific id.
280:      *
281:      * @param integer $category_id  The integer ID of the category to find.
282:      *
283:      * @return array  The category on success
284:      */
285:     public function getCategory($category_id)
286:     {
287:         $categories = $this->getCategories(null, $category_id);
288:         return array_shift($categories);
289:     }
290: 
291:     /**
292:      * This function return all the categories matching an id.
293:      *
294:      * @param integer $stock_id     The stock ID of categories to fetch.
295:      *                              Returns all categories if null.
296:      * @param integer $category_id  The numeric ID of the categories to find.
297:      *
298:      * @return array  The array of matching categories on success, an empty
299:      *                array otherwise.
300:      */
301:     public function getCategories($stock_id = null, $category_id = null)
302:     {
303:         $where = ' WHERE 1 = 1 ';
304:         $sql = 'SELECT c.category_id AS id, c.category_id AS category_id, c.category AS category, c.description AS description, c.priority AS priority FROM sesha_categories c';
305:         if (!empty($stock_id)) {
306:             $sql .= ', sesha_inventory_categories dc ';
307:             $where .= sprintf('AND c.category_id = dc.category_id AND ' .
308:                 'dc.stock_id = %d', $stock_id);
309:         }
310:         if (!empty($category_id)) {
311:             $where .= sprintf(' AND category_id = %d', $category_id);
312:         }
313:         $sql .= $where . ' ORDER BY c.priority DESC, c.category';
314: 
315:         try {
316:             $rows = $this->_db->selectAll($sql);
317:         } catch (Horde_Db_Exception $e) {
318:             throw new Sesha_Exception($e);
319:         }
320: 
321:         return $rows;
322:     }
323: 
324:     /**
325:      * This will find all the available properties matching a specified ID.
326:      *
327:      * @param integer $property_id  The numeric ID of properties to find.
328:      *                              Matches all properties when null.
329:      *
330:      * @return array  matching properties on success
331:      * @throws Sesha_Exception
332:      */
333:     public function getProperties($property_id = array())
334:     {
335:         $sql = 'SELECT * FROM sesha_properties';
336:         if (!empty($property_id)) {
337:             $sql .= ' WHERE property_id IN (';
338:             foreach ($property_id as $id) {
339:                 $sql .= (int)$id . ',';
340:             }
341:             $sql = substr($sql, 0, -1) . ')';
342:         }
343: 
344:         try {
345:             $properties = $this->_db->selectAll($sql);
346:         } catch (Horde_Db_Exception $e) {
347:             throw new Sesha_Exception($e);
348:         }
349: 
350:         array_walk($properties, array($this, '_unserializeParameters'));
351:         $keyed = array();
352:         foreach ($properties as $property) {
353:             $keyed[$property['property_id']] = $property;
354:         }
355: 
356:         return $keyed;
357:     }
358: 
359:     /**
360:      * Finds the first matching property for a specified property ID.
361:      *
362:      * @param integer $property_id  The numeric ID of properties to find.
363:      *
364:      * @return mixed  The specified property on success
365:      * @throws Sesha_Exception
366:      */
367:     public function getProperty($property_id)
368:     {
369:         $result = $this->getProperties(array($property_id));
370:         return array_shift($result);
371:     }
372: 
373:     /**
374:      * Updates the attributes stored by a category.
375:      *
376:      * @param array $info  Updated category attributes.
377:      *
378:      * @return integer The number of affected rows
379:      * @throws Sesha_Exception
380:      */
381:     public function updateCategory($info)
382:     {
383:         $sql = 'UPDATE sesha_categories' .
384:                ' SET category = ?, description = ?, priority = ?' .
385:                ' WHERE category_id = ?';
386:         $values = array($info['category'], $info['description'], $info['priority'], $info['category_id']);
387:         try {
388:             return $this->_db->execute($sql, $values);
389:         } catch (Horde_Db_Exception $e) {
390:             throw new Sesha_Exception($e);
391:         }
392: 
393:     }
394: 
395:     /**
396:      * Adds a new category for classifying inventory.
397:      *
398:      * @param array $info  The new category's attributes.
399:      *
400:      * @return integer  The ID of the new of the category on success
401:      * @throws Sesha_Exception
402:      */
403:     public function addCategory($info)
404:     {
405: 
406:         $sql = 'INSERT INTO sesha_categories' .
407:                ' (category, description, priority)' .
408:                ' VALUES (?, ?, ?)';
409:         $values = array($info['category'], $info['description'], $info['priority']);
410: 
411:         try {
412:             $result = $this->_db->insert($sql, $values);
413:         } catch (Horde_Db_Exception $e) {
414:             throw new Sesha_Exception($e);
415:         }
416:         return $result;
417:     }
418: 
419:     /**
420:      * Deletes a category.
421:      *
422:      * @param integer $category_id  The numeric ID of the category to delete.
423:      *
424:      * @return integer The number of rows deleted
425:      */
426:     public function deleteCategory($category_id)
427:     {
428:         $sql = 'DELETE FROM sesha_categories WHERE category_id = ?';
429:         $values = array($category_id);
430:         try {
431:             return $this->_db->delete($sql, $values);
432:         } catch (Horde_Db_Exception $e) {
433:             throw new Sesha_Exception($e);
434:         }
435: 
436:     }
437: 
438:     /**
439:      * Determines if a category exists in the storage backend.
440:      *
441:      * @param string $category  The string representation of the category to
442:      *                          find.
443:      *
444:      * @return boolean  True on success; false otherwise.
445:      */
446:     public function categoryExists($category)
447:     {
448:         $sql = 'SELECT * FROM sesha_categories WHERE category = ?';
449:         $values = array($category);
450: 
451:         $result = $this->_db->selectOne($sql, $values);
452:         if (count($result)) {
453:             return true;
454:         }
455:         return false;
456:     }
457: 
458:     /**
459:      * Updates a property with new attributes.
460:      *
461:      * @param array $info Array with updated property values.
462:      *
463:      * @return object  The PEAR DB_Result object from the query.
464:      */
465:     public function updateProperty($info)
466:     {
467:         $sql = 'UPDATE sesha_properties SET property = ?, datatype = ?, parameters = ?, unit = ?, description = ?, priority = ?, WHERE property_id = ?';
468:         $values = array(
469:             $info['property'],
470:             $info['datatype'],
471:             serialize($info['parameters']),
472:             $info['unit'],
473:             $info['description'],
474:             $info['priority'],
475:             $info['property_id'],
476:         );
477:         try {
478:             return $this->_db->query($sql, $values);
479:         } catch (Horde_Db_Exception $e) {
480:             throw new Sesha_Exception($e);
481:         }
482:     }
483: 
484:     /**
485:      * Adds a new property to the storage backend.
486:      *
487:      * @param array $info Array with new property values.
488:      *
489:      * @return object  The PEAR DB_Result from the sql query.
490:      */
491:     public function addProperty($info)
492:     {
493: 
494:         $sql = 'INSERT INTO sesha_properties (property, datatype, parameters, unit, description, priority) VALUES (?, ?, ?, ?, ?, ?)';
495:         $values = array(
496:             $info['property'],
497:             $info['datatype'],
498:             serialize($info['parameters']),
499:             $info['unit'],
500:             $info['description'],
501:             $info['priority'],
502:         );
503: 
504:         try {
505:             return $this->_db->insert($sql, $values);
506:         } catch (Horde_Db_Exception $e) {
507:             throw new Sesha_Exception($e);
508:         }
509:     }
510: 
511:     /**
512:      * Deletes a property from the storage backend.
513:      *
514:      * @param integer $property_id  The numeric ID of the property to delete.
515:      *
516:      * @return object  The PEAR DB_Result object from the sql query.
517:      */
518:     public function deleteProperty($property_id)
519:     {
520:         $sql = 'DELETE FROM sesha_properties WHERE property_id = ?';
521:         $values = array($property_id);
522:         try {
523:             return $this->_db->delete($sql, $values);
524:         } catch (Horde_Db_Exception $e) {
525:             throw new Sesha_Exception($e);
526:         }
527:     }
528: 
529:     /**
530:      * This will return a set of properties for a set of specified categories.
531:      *
532:      * @param array $categories  The set of categories to fetch properties.
533:      *
534:      * @return mixed  An array of properties on success
535:      * @throws Sesha_Exception
536:      */
537:     public function getPropertiesForCategories($categories = array())
538:     {
539:         if (!is_array($categories)) {
540:             $categories = array($categories);
541:         }
542: 
543:         $in = '';
544:         foreach ($categories as $category) {
545:             $in .= !empty($in) ? ', ' . $category : $category;
546:         }
547:         $sql = sprintf('SELECT c.category_id AS category_id, c.category AS category, p.property_id AS property_id, p.property AS property, p.unit AS unit, p.description AS description, p.datatype AS datatype, p.parameters AS parameters FROM sesha_categories c, sesha_properties p, sesha_relations cp WHERE c.category_id = cp.category_id AND cp.property_id = p.property_id AND c.category_id IN (%s) ORDER BY p.priority DESC',
548:                        empty($in) ? $this->_db->quote($in) : $in);
549:         try {
550:             $properties = $this->_db->selectAll($sql);
551:         } catch (Horde_Db_Exception $e) {
552:             throw new Sesha_Exception($e);
553:         }
554: 
555:         array_walk($properties, array($this, '_unserializeParameters'));
556: 
557:         return $properties;
558:     }
559: 
560:     /**
561:      * Updates a category with a set of properties.
562:      *
563:      * @param integer   $category_id    The numeric ID of the category to update.
564:      * @param array     $properties     An array of property ID's to add.
565:      *
566:      * @return integer  number of inserted row
567:      * @throws Sesha_Exception
568:      */
569:     public function setPropertiesForCategory($category_id, $properties = array())
570:     {
571:         $this->clearPropertiesForCategory($category_id);
572:         foreach ($properties as $property) {
573:             $sql = 'INSERT INTO sesha_relations
574:                 (category_id, property_id) VALUES (?, ?)';
575:             try {
576:                 $result = $this->_db->execute($sql, array($category_id, $property));
577:             } catch (Horde_Db_Exception $e) {
578:                 throw new Sesha_Exception($e);
579:             }
580:         }
581:         return $result;
582:     }
583: 
584:     /**
585:      * Removes all properties for a specified category.
586:      *
587:      * @param integer $category_id  The numeric ID of the category to update.
588:      *
589:      * @return integer The number of deleted properties
590:      * @throws Sesha_Exception
591:      */
592:     public function clearPropertiesForCategory($category_id)
593:     {
594:         $sql = 'DELETE FROM sesha_relations WHERE category_id = ?';
595:         try {
596:             return $this->_db->delete($sql, array($category_id));
597:         } catch (Horde_Db_Exception $e) {
598:             throw new Sesha_Exception($e);
599:         }
600:     }
601: 
602:     /**
603:      * Returns a set of properties for a particular stock ID number.
604:      *
605:      * @param integer $stock_id  The numeric ID of the stock to find the
606:      *                           properties for.
607:      *
608:      * @return object  The PEAR DB_Result object from the sql query.
609:      * @throws Sesha_Exception
610:      */
611:     public function getPropertiesForStock($stock_id)
612:     {
613:         $sql = 'SELECT p.property_id AS property_id, p.property AS property, p.datatype AS datatype, ' .
614:             'p.unit AS unit, p.description AS description, a.attribute_id AS attribute_id, a.int_datavalue AS int_datavalue, ' .
615:             'a.txt_datavalue AS txt_datavalue FROM sesha_properties p, ' .
616:             'sesha_inventory_properties a WHERE p.property_id = ' .
617:             'a.property_id AND a.stock_id = ? ORDER BY p.priority DESC';
618:         try {
619:             $properties = $this->_db->selectAll($sql, array($stock_id));
620:         } catch (Horde_Db_Exception $e) {
621:             throw new Sesha_Exception($e);
622:         }
623: 
624:         for ($i = 0; $i < count($properties); $i++) {
625:             $value = @unserialize($properties[$i]['txt_datavalue']);
626:             if ($value !== false) {
627:                 $properties[$i]['txt_datavalue'] = $value;
628:             }
629:         }
630: 
631:         return $properties;
632:     }
633: 
634:     /**
635:      * Removes all categories from a particular stock item.
636:      *
637:      * @param integer $stock_id  The numeric ID of the stock item to update.
638:      * @param array $categories  The array of categories to remove.
639:      *
640:      * @return object  The PEAR DB_Result object from the sql query.
641:      * @throws Sesha_Exception
642:      */
643:     public function clearPropertiesForStock($stock_id, $categories = array())
644:     {
645:         if (!is_array($categories)) {
646:             $categories = array(0 => array('category_id' => $categories));
647:         }
648:         /* Get list of properties for this set of categories. */
649:         try {
650:             $properties = $this->getPropertiesForCategories($categories);
651:         } catch (Horde_Db_Exception $e) {
652:             throw new Sesha_Exception($e);
653:         }
654: 
655:         $propertylist = '';
656:         for ($i = 0;$i < count($properties); $i++) {
657:             if (!empty($propertylist)) {
658:                 $propertylist .= ', ';
659:             }
660:             $propertylist .= $properties[$i]['property_id'];
661:         }
662:         $sql = sprintf('DELETE FROM sesha_inventory_properties
663:                         WHERE stock_id = %d
664:                         AND property_id IN (%s)',
665:                             $stock_id,
666:                             $propertylist);
667:         try {
668:             return $this->_db->execute($sql);
669:         } catch (Horde_Db_Exception $e) {
670:             throw new Sesha_Exception($e);
671:         }
672:     }
673: 
674:     /**
675:      * Updates the set of properties for a particular stock item.
676:      *
677:      * @param integer $stock_id  The numeric ID of the stock to update.
678:      * @param array $properties  The hash of properties to update.
679:      *
680:      * @return mixed  The DB_Result object on success
681:      * @throws Sesha_Exception
682:      */
683:     public function updatePropertiesForStock($stock_id, $properties = array())
684:     {
685:         $result = false;
686:         foreach ($properties as $property_id => $property_value) {
687:             // Now clear any existing attribute values for this property_id
688:             // and stock_id.
689:             $sql = 'DELETE FROM sesha_inventory_properties ' .
690:                    'WHERE stock_id = ? AND property_id = ?';
691: 
692:             try {
693:                 $result = $this->_db->execute($sql, array($stock_id, $property_id));
694:             } catch (Horde_Db_Exception $e) {
695:                 throw new Sesha_Exception($e);
696:             }
697:             if (!is_null($result) && !empty($property_value)) {
698:                 $sql = 'INSERT INTO sesha_inventory_properties' .
699:                        ' (property_id, stock_id, txt_datavalue)' .
700:                        ' VALUES (?, ?, ?)';
701:                 $values = array($property_id, $stock_id, is_string($property_value) ? $property_value : serialize($property_value));
702:                 try {
703:                     $result = $this->_db->insert($sql, $values);
704:                 } catch (Horde_Db_Exception $e) {
705:                     throw new Sesha_Exception($e);
706:                 }
707:             }
708:         }
709:         return $result;
710:     }
711: 
712:     /**
713:      * Updates the set of categories for a specified stock item.
714:      *
715:      * @param integer $stock_id  The numeric stock ID to update.
716:      * @param array $category    The array of categories to change.
717:      *
718:      * @return object  The PEAR DB_Result object from the sql query.
719:      */
720:     public function updateCategoriesForStock($stock_id, $category = array())
721:     {
722:         if (!is_array($category)) {
723:             $category = array($category);
724:         }
725:         /* First clear any categories that might be set for this item. */
726:         $sql = 'DELETE FROM sesha_inventory_categories ' .
727:                        'WHERE stock_id = ? ';
728: 
729:         try {
730:             $result = $this->_db->execute($sql, array($stock_id));
731:         } catch (Sesha_Exception $e) {
732:             throw new Sesha_Exception($e);
733:         }
734:         for ($i = 0; $i < count($category); $i++) {
735:             $category_id = $category[$i];
736:             $sql = sprintf('INSERT INTO sesha_inventory_categories ' .
737:                 '(stock_id, category_id) VALUES (%d, %d)',
738:                 $stock_id, $category_id);
739: 
740:             $result = $this->_db->insert($sql);
741:         }
742: 
743:         return $result;
744:     }
745: 
746:     /**
747:      */
748:     public function _unserializeParameters(&$val, $key)
749:     {
750:         $val['parameters'] = @unserialize($val['parameters']);
751:     }
752: 
753:     public function _normalizeStockProperties($rows, $property_ids)
754:     {
755:         $stock = array();
756:         foreach ($rows as $row) {
757:             if (!isset($stock[$row['stock_id']])) {
758:                 $stock[$row['stock_id']] = array(
759:                     'stock_id' => $row['stock_id'],
760:                     'stock_name' => $row['stock_name'],
761:                     'note' => $row['note'],
762:                 );
763:                 foreach ($property_ids as $property_id) {
764:                     $stock[$row['stock_id']]['p' . $property_id] = '';
765:                 }
766:             }
767: 
768:             $stock[$row['stock_id']]['p' . $row['property_id']] = strlen($row['txt_datavalue']) ? $row['txt_datavalue'] : $row['int_datavalue'];
769:         }
770: 
771:         return $stock;
772:     }
773: 
774: }
775: