1: <?php
  2: namespace Module\DB;
  3: 
  4: require_once(dirname(__FILE__) . '/class.db.php');
  5: require_once(dirname(__FILE__) . '/../../core/class.debug.php');
  6: 
  7: /**
  8:  * Base class that represents a record in a table.
  9:  *
 10:  * Extend this class and add a property $table that is equal to the table name
 11:  * in your database. The columns will be loaded automatically and basic load(),
 12:  * save(), and delete() methods are immediately available. Update the data by
 13:  * changing the value of the columns like this:
 14:  *
 15:  * ### Usage
 16:  *
 17:  * <code>
 18:  *   // load a record with id= 5 from a table called 'user'
 19:  *   class user extends \Module\DB\Record {
 20:  *     public $table = 'user';
 21:  *   }
 22:  *
 23:  *   $u = new user();
 24:  *   $u->load(5);
 25:  *   $u->columns['first_name'] = 'Joe';
 26:  * </code>
 27:  *
 28:  * You can then save the new information by calling the save method:
 29:  *
 30:  * <code>
 31:  *   $u->save();
 32:  * </code>
 33:  *
 34:  * You can also show a nice form to edit or add new records like this
 35:  *
 36:  * <code>
 37:  *   $u->form(array(
 38:  *     'first_name' => 'First Name: ',
 39:  *     'last_name' => 'Last Name: ',
 40:  *     'phone' => '(800) 555-5555'
 41:  *   ));
 42:  * </code>
 43:  *
 44:  * ### Changelog
 45:  *
 46:  * ## Version 1.2
 47:  * * Added namespacing
 48:  *
 49:  * ## Version 1.1
 50:  * * Added the date section to the documentation
 51:  *
 52:  * @section dependencies Dependencies
 53:  * * class.hooks.php
 54:  * * class.db.php
 55:  *
 56:  * @date June 16, 2014
 57:  * @author Jaime A. Rodriguez <hi.i.am.jaime@gmail.com>
 58:  * @version  1.1
 59:  * @license  http://opensource.org/licenses/MIT
 60:  **/
 61: 
 62: class Record {
 63:     /**
 64:      * PDO The PDO object
 65:      */
 66:     protected $db;
 67: 
 68:     /**
 69:      * string The name of the table
 70:      */
 71:     protected $table;
 72: 
 73:     /**
 74:      * string The primary key of the table
 75:      */
 76:     protected $primaryKey = 'id';
 77: 
 78:     /**
 79:      * array The data of the record get loaded here
 80:      */
 81:     public $columns;
 82: 
 83:     /**
 84:      * stdObject The record meta data gets loaded here
 85:      */
 86:     public $meta;
 87: 
 88:     /**
 89:      * Initializes db and gets column information.
 90:      *
 91:      * @param string $id The id to load automatically
 92:      */
 93:     public function __construct($id=0) {
 94:         $this->db = DB::getInstance();
 95: 
 96:         $select = $this->db->query("SELECT * FROM `{$this->table}` LIMIT 1");
 97: 
 98:         for ($i = 0; $i <= $select->columnCount(); $i ++) {
 99:             $meta = $select->getColumnMeta($i);
100:             if (isset($meta['name'])) {
101:                 $this->meta[$meta['name']] = $meta;
102:             }
103:         }
104: 
105:         if ($id) {
106:             $this->load($id);
107:         }
108:     }
109: 
110:     /**
111:      * Loads a record as an object.
112:      *
113:      * @param  integer $id id of a record to load
114:      * @return bool True if loaded correctly
115:      */
116:     public function load($id=0) {
117:         if ($this->table == '') {
118:             throw new \Exception('$this->table is not set.');
119:         }
120: 
121:         $query = $this->db->prepare("SELECT * FROM `{$this->table}` WHERE {$this->primaryKey}=:{$this->primaryKey}");
122:         $query->execute(array(":{$this->primaryKey}" => $id));
123:         $query->setFetchMode(\PDO::FETCH_ASSOC);
124: 
125:         if ($this->columns = $query->fetch()) {
126:             return true;
127:         } else {
128:             throw new \Exception("{$this->table}: Record does not exist.");
129:         }
130:     }
131: 
132:     /**
133:      * Saves the record to the database.
134:      *
135:      * @return  mixed Returns the Primary Key
136:      */
137:     public function save() {
138:         $sql = "";
139:         $col = array();
140: 
141:         // If id is set, then update. Else, insert
142:         if ($new = !isset($this->columns[$this->primaryKey])) {
143:             $sql = "INSERT INTO {$this->table} SET";
144:         } else {
145:             $sql = "UPDATE {$this->table} SET";
146:         }
147: 
148:         // set up insert statement, don't update id
149:         foreach ($this->columns as $key => $value) {
150:             if ($key !== $this->primaryKey) {
151:                 $sql .= " {$key}=:{$key},";
152:                 $col[":{$key}"] = $value;
153:             } else {
154:                 $col[":{$key}"] = $value;
155:             }
156:         }
157: 
158:         // remove the trailing comma
159:         $sql = substr($sql, 0, -1);
160: 
161:         if (!$new) {
162:             $sql = $sql . " WHERE {$this->primaryKey}=:{$this->primaryKey}";
163:         }
164: 
165:         $result = $this->db->prepare($sql);
166: 
167:         // save
168:         \Sleepy\Hook::addAction('recordBeforeSave');
169:         $result->execute($col);
170:         \Sleepy\Hook::addAction('recordAfterSave');
171: 
172:         if ($new) {
173:             if ($result->rowCount()) {
174:                 $this->columns[$this->primaryKey] = $this->db->lastInsertId();
175:                 return $this->columns[$this->primaryKey];
176:             } else {
177:                 throw new Exception("{$this->table}: Record was not saved.");
178:             }
179:         } else {
180:             return $this->columns[$this->primaryKey];
181:         }
182:     }
183: 
184:     /**
185:      * Deletes this record from the database
186:      *
187:      * @return bool True if delete is successful
188:      */
189:     public function delete() {
190:         \Sleepy\Hook::addAction('recordBeforeDelete');
191:         $query = $this->db->prepare('DELETE FROM ' . $this->table . " WHERE {$this->primaryKey}=:{$this->primaryKey}");
192:         //\Sleepy\Debug::out($query->debugDumpParams());
193:         if ($query->execute(array(":{$this->primaryKey}" => $this->columns[$this->primaryKey]))) {
194:             \Sleepy\Hook::addAction('recordDeleteSucessful');
195:             return true;
196:         } else {
197:             \Sleepy\Hook::addAction('recordDeleteError');
198:             throw new \Exception("{$this->table}: Record was not deleted.");
199:         }
200:     }
201: 
202:     /**
203:      * Shows an editable form
204:      *
205:      * @param  Array   $fields An array of columns => labels
206:      * @param  string  $legend Customize the fieldset legend
207:      * @param  boolean $submit Show the submit button?
208:      * @return void            returns nothing
209:      */
210:     public function form(Array $fields, $legend='table_name', $submit=true) {
211:         // Legend defaults to the table name
212:         if ($legend == 'table_name') {
213:             $legend = $this->table;
214:         }
215: 
216:         // If there are no fields sent, load all fields in the table.
217:         if (empty($fields)) {
218:             foreach ($this->meta as $meta) {
219:                 $fields[$meta['name']] = $meta['name'];
220:             }
221:         }
222: 
223:         ?>
224:         <fieldset>
225:             <legend><?php echo $legend;?></legend>
226:             <ul>
227:             <?php
228:                 // Display each field
229:                 foreach ($fields as $field => $label) {
230:                     $class = "";
231:                     $buffer = "";
232:                     $meta = $this->meta[$field];
233: 
234:                     // If the field is not in the database, then add a
235:                     // "fake" field instead
236:                     if (!is_array($meta)) {
237:                         $meta['name'] = $field;
238:                         $meta['flags'] = Array();
239:                         $meta['native_type'] = 'VAR_STRING';
240:                         $class .= 'not-binded ';
241:                     }
242: 
243:                     // Add classes for field flags
244:                     foreach ($meta['flags'] as $flag) {
245:                         if ($flag == 'not_null') {
246:                             $class .= 'required ';
247:                         }
248: 
249:                         if ($flag == 'primary_key') {
250:                             $class .= 'primary-key ';
251:                         }
252:                     }
253: 
254:                     $label = \Sleepy\Hook::addFilter('dbForm_label', $label);
255: 
256:                     ?>
257:                     <li>
258:                         <?php
259:                             // fix for tinyint so it works correctly. Damn PDO!
260:                             if (!isset($meta['native_type'])) {
261:                                 if ($meta['len'] == 1) {
262:                                     $meta['native_type'] = 'BOOL';
263:                                 }
264:                             }
265: 
266:                             // Is this a password field?
267:                             if (strpos(strtolower($label), 'password') > -1) {
268:                                 $meta['native_type'] = 'PASSWORD';
269:                             }
270: 
271:                             // Is this an email field?
272:                             if (strpos(strtolower($label), 'email') > -1) {
273:                                 $class = "email ";
274:                             }
275: 
276:                             $value = htmlspecialchars($this->columns[$field]);
277: 
278:                             switch ($meta['native_type']) {
279:                             case 'LONG':
280:                             case 'FLOAT':
281:                                 $class .= "digits ";
282:                                 $inputId = "txt_" . $meta['name'];
283: 
284:                                 $buffer .= "<label class=\"{$class}\" for=\"{$inputId}\">{$label}</label>";
285:                                 $buffer .= "<input class=\"{$class}\" type=\"text\" name=\"{$inputId}\" id=\"{$inputId}\" maxlength=\"{$meta['len']}\" value=\"{$value}\" />";
286:                                 break;
287:                             case 'STRING':
288:                             case 'VAR_STRING':
289:                                 $class .= "string ";
290:                                 $inputId = "txt_" . $meta['name'];
291:                                 $buffer .= "<label class=\"{$class}\" for=\"{$inputId}\">{$label}</label>";
292:                                 $buffer .= "<input class=\"{$class}\" type=\"text\" name=\"{$inputId}\" id=\"{$inputId}\" maxlength=\"{$meta['len']}\" value=\"{$value}\" />";
293:                                 break;
294:                             case 'PASSWORD':
295:                                 $class .= "password ";
296:                                 $inputId = "pwd_" . $meta['name'];
297:                                 $buffer .= "<label class=\"{$class}\" for=\"{$inputId}\">{$label}</label>";
298:                                 $buffer .= "<input class=\"{$class}\" type=\"password\" name=\"{$inputId}\" id=\"{$inputId}\" maxlength=\"{$meta['len']}\" />";
299:                                 break;
300:                             case 'DATETIME':
301:                                 $class .= "datetime ";
302:                                 $inputId = "txt_" . $meta['name'];
303:                                 $buffer .= "<label class=\"{$class}\" for=\"{$inputId}\">{$label}</label>";
304:                                 $buffer .= "<input class=\"{$class}\" type=\"text\" name=\"{$inputId}\" id=\"{$inputId}\" maxlength=\"{$meta['len']}\" value=\"{$value}\" />";
305:                                 break;
306:                             case 'TIMESTAMP':
307:                                 $class .= "timestamp ";
308:                                 $inputId = "txt_" . $meta['name'];
309:                                 $buffer .= "<label class=\"{$class}\" for=\"{$inputId}\">{$label}</label>";
310:                                 $buffer .= "<input class=\"{$class}\" type=\"text\" name=\"{$inputId}\" id=\"{$inputId}\" maxlength=\"{$meta['len']}\" value=\"{$value}\" />";
311:                                 break;
312:                             case 'NEWDECIMAL':
313:                                 $class .= "decimal ";
314:                                 $inputId = "txt_" . $meta['name'];
315:                                 $buffer .= "<label class=\"{$class}\" for=\"{$inputId}\">{$label}</label>";
316:                                 $buffer .= "<input class=\"{$class}\" type=\"text\" name=\"{$inputId}\" id=\"{$inputId}\" maxlength=\"{$meta['len']}\" value=\"{$value}\" />";
317:                                 break;
318:                             case 'BOOL':
319:                                 $class .= "bool ";
320:                                 $inputId = "chk_" . $meta['name'];
321:                                 if ($value) {
322:                                     $checked = "checked=\"checked\"";
323:                                 }
324:                                 $buffer .= "<label class=\"{$class}\" for=\"{$inputId}\">{$label}</label>";
325:                                 $buffer .= "<input class=\"{$class}\" type=\"checkbox\" name=\"{$inputId}\" id=\"{$inputId}\" value=\"1\" {$checked} />";
326:                                 break;
327:                             case 'BLOB':
328:                                 $class .= "string ";
329:                                 $inputId = "txt_" . $meta['name'];
330:                                 $buffer .= "<label class=\"{$class}\" for=\"{$inputId}\">{$label}</label>";
331:                                 $buffer .= "<textarea class=\"{$class}\" type=\"text\" name=\"{$inputId}\" id=\"{$inputId}\" maxlength=\"{$meta['len']}\">{$value}</textarea>";
332:                                 break;
333:                             default:
334:                                 $buffer .= "No Handler for {$meta['native_type']} defined.";
335:                             }
336: 
337:                             // Add Hook filters to the buffer
338:                             foreach(Array(
339:                                 'dbForm_list',
340:                                 $this->table . "_dbForm_list"
341:                             ) as $filterName) {
342:                                 $buffer = \Sleepy\Hook::addFilter($filterName, Array(
343:                                     $buffer,
344:                                     $meta['native_type'],
345:                                     $label,
346:                                     $value,
347:                                     $class,
348:                                     $inputId
349:                                 ));
350:                             }
351: 
352:                             echo $buffer;
353:                         ?>
354:                     </li>
355:                     <?php
356:                 }
357:                 ?>
358:             </ul>
359:         </fieldset>
360:         <?php
361:         if ($submit) {
362:             ?>
363:             <fieldset class="submit">
364:                 <legend></legend>
365:                 <ul>
366:                     <li><input type="submit" value="Save"></li>
367:                 </ul>
368:             </fieldset>
369:             <?php
370:         }
371:     }
372: }