1: <?php
  2: /**
  3:  * Ulaform_Driver_Sql Class
  4:  *
  5:  * Copyright 2003-2012 Horde LLC (http://www.horde.org/)
  6:  *
  7:  * See the enclosed file COPYING for license information (GPL). If you
  8:  * did not receive this file, see http://www.horde.org/licenses/gpl.
  9:  *
 10:  * @author  Marko Djukic <marko@oblo.com>
 11:  * @author  Vilius Šumskas <vilius@lnk.lt>
 12:  * @package Ulaform
 13:  */
 14: class Ulaform_Driver_Sql extends Ulaform_Driver {
 15: 
 16:     /**
 17:      * The database connection object.
 18:      *
 19:      * @var Horde_Db_Adapter
 20:      */
 21:     protected $_db;
 22: 
 23:     /**
 24:      * Charset
 25:      *
 26:      * @var string
 27:      */
 28:     protected $_charset;
 29: 
 30:     /**
 31:      * Construct a new SQL storage object.
 32:      *
 33:      * @param array $params    The connection parameters
 34:      *
 35:      * @throws InvalidArguementException
 36:      */
 37:     public function __construct($params = array())
 38:     {
 39:         if (empty($params['db'])) {
 40:             throw new InvalidArgumentException('Missing required connection parameter(s).');
 41:         }
 42:         $this->_db = $params['db'];
 43:         $this->_charset = $params['charset'];
 44:     }
 45: 
 46:     /**
 47:      * Saves the passed form into the db, either inserting a
 48:      * new form if no form_id is available, or updating an
 49:      * existing form if a form_id has been passed.
 50:      *
 51:      * @param array  $info  An array with the form details.
 52:      *
 53:      * @return integer  The form id.
 54:      * @throws Ulaform_Exception
 55:      */
 56:     public function saveForm(&$info)
 57:     {
 58:         $values = array();
 59:         if (!empty($info['form_id'])) {
 60:             $values[] = (int)$info['form_id'];
 61:         }
 62: 
 63:         /* Serialize the form params. */
 64:         $info['form_params'] = Horde_Serialize::serialize($info['form_params'], Horde_Serialize::UTF7_BASIC);
 65: 
 66:         array_unshift($values,
 67:                       $GLOBALS['registry']->getAuth(),
 68:                       Horde_String::convertCharset($info['form_name'], 'UTF-8', $this->_charset),
 69:                       $info['form_action'],
 70:                       Horde_String::convertCharset($info['form_params'], 'UTF-8', $this->_charset),
 71:                       $info['form_onsubmit']);
 72: 
 73:         if (empty($info['form_id'])) {
 74:             $sql = 'INSERT INTO ulaform_forms (user_uid, form_name, form_action, form_params, form_onsubmit) VALUES (?, ?, ?, ?, ?)';
 75:             try {
 76:                 $info['form_id'] = $this->_db->insert($sql, $values);
 77:             } catch (Horde_Db_Exception $e) {
 78:                 throw new Ulaform_Exception($e->getMessage());
 79:             }
 80:         } else {
 81:             $sql = 'UPDATE ulaform_forms SET user_uid = ?, form_name = ?, form_action = ?, form_params = ?, form_onsubmit = ? WHERE form_id = ?';
 82:             try {
 83:                 $this->_db->execute($sql, $values);
 84:             } catch (Horde_Db_Exception $e) {
 85:                 throw new Ulaform_Exception($e->getMessage());
 86:             }
 87:         }
 88: 
 89:         return $info['form_id'];
 90:     }
 91: 
 92:     /**
 93:      * Saves the passed field into the db, either inserting
 94:      * a new field if no field_id is available, or updating
 95:      * an existing field if a field_id has been passed.
 96:      * If no form_id is available will throw an exception.
 97:      *
 98:      * @param array  $params  An array with the field details.
 99:      *
100:      * @return boolean  True on success.
101:      * @throws Horde_Exception_NotFound
102:      * @throws Ulaform_Exception
103:      */
104:     public function saveField(&$info)
105:     {
106:         if (empty($info['form_id'])) {
107:             throw new Horde_Exception_NotFound(_("Missing form"));
108:         }
109: 
110:         $values = array();
111:         if (!empty($info['field_id'])) {
112:             $values[] = $info['field_id'];
113:         } else {
114:             if (empty($info['field_order'])) {
115:                 $info['field_order'] = $this->_nextFieldOrder($info['form_id']);
116:             }
117:         }
118: 
119:         /* Set up the field data. */
120:         $info['field_required'] = ($info['field_required'] ? 1 : 0);
121:         $info['field_readonly'] = ($info['field_readonly'] ? 1 : 0);
122: 
123:         if (!empty($info['field_params'])) {
124:             $info['field_params'] = Horde_Serialize::serialize($info['field_params'], Horde_Serialize::UTF7_BASIC);
125:         } else {
126:             $info['field_params'] = null;
127:         }
128: 
129:         array_unshift($values,
130:                       $info['form_id'],
131:                       Horde_String::convertCharset($info['field_name'], 'UTF-8', $this->_charset),
132:                       Horde_String::convertCharset($info['field_label'], 'UTF-8', $this->_charset),
133:                       $info['field_type'],
134:                       Horde_String::convertCharset($info['field_params'], 'UTF-8', $this->_charset),
135:                       $info['field_required'],
136:                       $info['field_readonly'],
137:                       Horde_String::convertCharset($info['field_desc'], 'UTF-8', $this->_charset),
138:                       $info['field_order']);
139: 
140:         if (empty($info['field_id'])) {
141:             $sql = 'INSERT INTO ulaform_fields (form_id, field_name, field_label, field_type, field_params, field_required, field_readonly, field_desc, field_order) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)';
142:             try {
143:                 $this->_db->execute($sql, $values);
144:             } catch (Horde_Db_Exception $e) {
145:                 throw new Ulaform_Exception($e->getMessage());
146:             }
147:         } else {
148:             $sql = 'UPDATE ulaform_fields SET form_id = ?, field_name = ?, field_label = ?, field_type = ?, field_params = ?, field_required = ?, field_readonly = ?, field_desc = ?, field_order = ? WHERE field_id = ?';
149:             try {
150:                 $this->_db->execute($sql, $values);
151:             } catch (Horde_Db_Exception $e) {
152:                 throw new Ulaform_Exception($e->getMessage());
153:             }
154:         }
155: 
156:         return true;
157:     }
158: 
159:     /**
160:      * Sets the specified sort order to the fields in a form.
161:      *
162:      * @param array  $info  An array with the field ids in
163:      *                      a specific order.
164:      *
165:      * @return boolean  True on success.
166:      * @throws Horde_Exception_NotFound
167:      * @throws Ulaform_Exception
168:      */
169:     public function sortFields(&$info)
170:     {
171:         if (empty($info['form_id'])) {
172:             throw new Horde_Exception_NotFound(_("Missing form"));
173:         }
174: 
175:         foreach ($info['field_order'] as $field_order => $field_id) {
176:             $sql = 'UPDATE ulaform_fields
177:                    SET field_order = ?
178:                    WHERE field_id = ?';
179:             try {
180:                 $this->_db->execute($sql, array((int)$field_order, (int)$field_id));
181:             } catch (Horde_Db_Exception $e) {
182:                 throw new Ulaform_Exception($e->getMessage());
183:             }
184:         }
185: 
186:         return true;
187:     }
188: 
189:     /**
190:      * Fetches the a list of available forms and the basic data.
191:      *
192:      * @return array  An array of the available forms.
193:      * @throws Ulaform_Exception
194:      */
195:     public function getForms($form_id = null)
196:     {
197:         $wsql = '';
198:         $values = array();
199:         if (!is_null($form_id)) {
200:             $wsql = ' WHERE form_id = ?';
201:             $values[] = (int)$form_id;
202:         }
203: 
204:         /* Get the forms. */
205:         $sql = 'SELECT form_id, user_uid, form_name, form_action, form_params,'
206:                 . ' form_onsubmit FROM ulaform_forms' . $wsql;
207:         try {
208:             $result = $this->_db->selectAll($sql, $values);
209:         } catch (Horde_Db_Exception $e) {
210:             throw new Ulaform_Exception($e->getMessage());
211:         }
212: 
213:         return Ulaform::checkPermissions($result, 'form', Horde_Perms::SHOW, 'form_id');
214:     }
215: 
216:     /**
217:      * Fetches the a list of available forms to use.
218:      *
219:      * @return array  An array of the available forms.
220:      * @throws Ulaform_Exception
221:      */
222:     public function getAvailableForms()
223:     {
224:         /* Fetch a list of all forms for now. */
225:         $sql = 'SELECT form_id, user_uid, form_name, form_action, form_params,'
226:                 . ' form_onsubmit FROM ulaform_forms';
227:         try {
228:             return $this->_db->selectAll($sql);
229:         } catch (Horde_Db_Exception $e) {
230:             throw new Ulaform_Exception($e->getMessage());
231:         }
232:     }
233: 
234:     /**
235:      * Fetches all the data specific to the supplied form id.
236:      *
237:      * @param integer $form_id  The form id of the form to return.
238:      *
239:      * @return array            The form data.
240:      * @throws Horde_Exception_PermissionDenied
241:      * @throws Horde_Exception_NotFound
242:      * @throws Ulaform_Exception
243:      */
244:     public function getForm($form_id, $permission = Horde_Perms::SHOW)
245:     {
246:         /* Check permissions */
247:         if (!parent::hasPermission($permission, $form_id)) {
248:             throw new Horde_Exception_PermissionDenied(_("You don't have the right permission to access this form."));
249:         }
250: 
251:         /* Get the main form data. */
252:         $sql = 'SELECT form_id, user_uid, form_name, form_action, form_params,'
253:                 . ' form_onsubmit FROM ulaform_forms WHERE form_id = ?';
254:         try {
255:             $form = $this->_db->selectOne($sql, array((int)$form_id));
256:         } catch (Horde_Db_Exception $e) {
257:             throw new Ulaform_Exception($e->getMessage());
258:         }
259: 
260:         /* Check if the form exists. */
261:         if (empty($form)) {
262:             throw new Horde_Exception_NotFound(sprintf(_("No such form ID \"%s\"."), $form_id));
263:         }
264: 
265:         /* Unserialize the form params. */
266:         $form['form_params'] = Horde_Serialize::unserialize($form['form_params'], Horde_Serialize::UTF7_BASIC);
267: 
268:         return $form;
269:     }
270: 
271:     /**
272:      * Fetches the fields for a particular form.
273:      *
274:      * @param integer $form_id  The form id of the form to return.
275:      *
276:      * @return array  The fields.
277:      * @throws Ulaform_Exception
278:      */
279:     public function getFields($form_id, $field_id = null)
280:     {
281:         $values = array($form_id);
282:         $sql = 'SELECT field_id, form_id, field_name, field_order, field_label, field_type, '
283:             . ' field_params, field_required, field_readonly, field_desc FROM ulaform_fields '
284:             . ' WHERE form_id = ?';
285: 
286:         if (!is_null($field_id)) {
287:             $sql .= ' AND field_id = ?';
288:             $values[] = (int)$field_id;
289:         }
290:         $sql .= ' ORDER BY field_order';
291: 
292:         try {
293:             $results = $this->_db->selectAll($sql, $values);
294:         } catch (Horde_Db_Exception $e) {
295:             throw new Ulaform_Exception($e);
296:         }
297: 
298:         $fields = array();
299:         foreach ($results as $field) {
300:             /* If no internal name set, generate one using field_id. */
301:             if (empty($field['field_name'])) {
302:                 $field['field_name'] = 'field_' . $field['field_id'];
303:             }
304: 
305:             /* Check if any params and unserialize, otherwise return null. */
306:             if (!empty($field['field_params'])) {
307:                 $field['field_params'] = Horde_Serialize::unserialize($field['field_params'], Horde_Serialize::UTF7_BASIC);
308:             } else {
309:                 $field['field_params'] = null;
310:             }
311:             $fields[] = $field;
312:         }
313: 
314:         return $fields;
315:     }
316: 
317:     /**
318:      * Deletes a form and all of its fields from the database.
319:      *
320:      * @param integer $form_id  The form id of the form to delete.
321:      *
322:      * @return boolean  True on success.
323:      * @throws Ulaform_Exception
324:      */
325:     public function deleteForm($form_id)
326:     {
327:         /* Delete the form. */
328:         $sql = 'DELETE FROM ulaform_forms WHERE form_id = ?';
329:         try {
330:             $this->_db->execute($sql, array((int)$form_id));
331:         } catch (Horde_Db_Exception $e) {
332:             throw new Ulaform_Exception($e->getMessage());
333:         }
334: 
335:         /* Delete the fields for this form. */
336:         $sql = 'DELETE FROM ulaform_fields WHERE form_id = ?';
337:         try {
338:             $this->_db->execute($sql, array((int)$form_id));
339:         } catch (Horde_Db_Exception $e) {
340:             throw new Ulaform_Exception($e->getMessage());
341:         }
342: 
343:         return true;
344:     }
345: 
346:     /**
347:      * Deletes a field from the database.
348:      *
349:      * @param integer $field_id  The field id of the field to delete.
350:      *
351:      * @return boolean  True on success.
352:      * @throws Ulaform_Exception
353:      */
354:     public function deleteField($field_id)
355:     {
356:         /* Delete the field. */
357:         $sql = 'DELETE FROM ulaform_fields WHERE field_id = ?';
358:         try {
359:             $this->_db->execute($sql, array((int)$field_id));
360:         } catch (Horde_Db_Exception $e) {
361:             throw new Ulaform_Exception($e->getMessage());
362:         }
363: 
364:         return true;
365:     }
366: 
367:     /**
368:      * Gets the next field order position within a form.
369:      *
370:      * @param integer  $form_id
371:      *
372:      * @return integer
373:      * @throws Ulaform_Exception
374:      */
375:     protected function _nextFieldOrder($form_id)
376:     {
377:         $sql = 'SELECT MAX(field_order) FROM ulaform_fields WHERE form_id = ?';
378:         try {
379:             return $this->_db->selectValue($sql, array($form_id)) + 1;
380:         } catch (Horde_Db_Exception $e) {
381:             throw new Ulaform_Exception($e->getMessage);
382:         }
383:     }
384: 
385: }
386: