1: <?php 
  2: 
  3: use Laravella\Crud\Params;
  4: use Laravella\Crud\DbGopher;
  5: use Laravella\Crud\Options;
  6: 
  7: /**
  8:  * All database requests are handled by this controller, 
  9:  * even the DbApiController ones, although DbApiController is leaner on the response i.e. json.
 10:  */
 11: class DbController extends Controller {
 12: 
 13:     //protected $layout = //getLayout;
 14:     private $log = array();
 15: 
 16:     const SUCCESS = "success";
 17:     const INFO = "info";
 18:     const IMPORTANT = "important";
 19: 
 20:     const HTML = "text/html";
 21:     const XML = "text/xml";
 22:     const JSON = "text/json";
 23:     
 24:     public $displayType = self::HTML;
 25:     
 26:     /**
 27:      * A cache of db tables to minimize db requests. See getPkSelects()
 28:      * 
 29:      * @var type 
 30:      */
 31:     private $dbTables = array();
 32: 
 33:     /**
 34:      * 
 35:      * @param type $severity
 36:      * @param type $message
 37:      */
 38:     protected function log($severity, $message)
 39:     {
 40:         $this->log[] = array("severity" => $severity, "message" => $message);
 41:     }
 42: 
 43:     /**
 44:      * Getter for $log
 45:      * 
 46:      * @return type
 47:      */
 48:     public function getLog()
 49:     {
 50:         return $this->log();
 51:     }
 52: 
 53:     public function getSkin() {
 54:         $skin = array('admin'=>Options::get('skin','admin'), 'frontend'=>Options::get('skin','frontend'));
 55:     }
 56:     
 57:     /**
 58:      * 
 59:      * 
 60:      * @param type $type Either 'admin' or 'frontend'
 61:      * @return type
 62:      */
 63:     public function getLayout($type = 'admin') {
 64:         return 'skins::'.Options::get('skin','admin').'.default';
 65:     }
 66:     
 67:     /**
 68:      * The root of the crud application /db
 69:      * 
 70:      * @return type
 71:      */
 72:     public function getIndex()
 73:     {
 74:         
 75:         return $this->getPage('contents');
 76:         
 77:     }
 78: 
 79:     /**
 80:      * The root of the crud application /db
 81:      * 
 82:      * @return type
 83:      */
 84:     public function getAdmin()
 85:     {
 86:         
 87:         return $this->getPage('contents');
 88:         
 89:     }
 90: 
 91:     /**
 92:      * 
 93:      * @param type $page
 94:      */
 95:     public function getPage($page) {
 96:         return $this->getSelect($page);
 97:     }
 98:     
 99:     /**
100:      * Find the right view to use with the action
101:      * 
102:      * @param type $tableName
103:      * @param type $action
104:      */
105:     protected function __getView($tableName, $action)
106:     {
107:         $views = DB::table('_db_pages')
108:                 ->join('_db_tables', '_db_pages.table_id', '=', '_db_tables.id')
109:                 ->join('_db_actions', '_db_pages.action_id', '=', '_db_actions.id')
110:                 ->join('_db_views', '_db_pages.view_id', '=', '_db_views.id')
111:                 ->where('_db_actions.name', '=', $action)
112:                 ->where('_db_tables.name', '=', $tableName)
113:                 ->first();
114:         return $views;
115:     }
116: 
117:     /**
118:      * Find the right object and view to use with the page slug
119:      * 
120:      * @param type $tableName
121:      * @param type $action
122:      */
123:     protected function __getSlug($slug)
124:     {
125:         $views = DB::table('_db_pages')
126:                 ->join('_db_objects', '_db_pages.object_id', '=', '_db_objects.id')
127:                 ->join('_db_views', '_db_pages.view_id', '=', '_db_views.id')
128:                 ->where('_db_pages.slug', '=', $slug)
129:                 ->first();
130:         return $views;
131:     }
132: 
133:     /**
134:      * Get a record from _db_pages as an stdClass object
135:      * 
136:      * @param type $tableName
137:      * @param type $viewId
138:      * @param type $action
139:      * @return type
140:      */
141:     protected function __getTableActionView($tableName, $viewId, $action)
142:     {
143:         $tva = DB::table('_db_pages')
144:                 ->join('_db_tables', '_db_pages.table_id', '=', '_db_tables.id')
145:                 ->join('_db_actions', '_db_pages.action_id', '=', '_db_actions.id')
146:                 ->join('_db_views', '_db_pages.view_id', '=', '_db_views.id')
147:                 ->select('_db_pages.view_id', '_db_pages.id as page_id', '_db_pages.action_id', 
148:                         '_db_views.name as view_name', '_db_actions.name as action_name', 
149:                         '_db_tables.name as table_name', '_db_pages.title')
150:                 ->where('_db_pages.view_id', '=', $viewId)
151:                 ->where('_db_actions.name', '=', $action)
152:                 ->where('_db_tables.name', '=', $tableName)
153:                 ->first();
154:         return $tva;
155:     }
156: 
157:     /**
158:      * Check permissions
159:      * 
160:      * @param type $tableName
161:      * @param type $action
162:      */
163:     protected function __getPermissions($tableName, $action)
164:     {
165:         //
166:         return true;
167:     }
168: 
169:     /**
170:      * /db/select/{tablename}
171:      * 
172:      * @param type $table
173:      * @return type
174:      */
175:     public function getSelect($tableName = null, $message = "")
176:     {
177:         $action = 'getSelect';
178: 
179:         //select table data from database
180:         $table = DB::table($tableName);
181: 
182:         if (empty($message)) {
183:             $message = "Data selected.";
184:         }
185:         
186:         $this->log(self::SUCCESS, "$tableName selected");
187: 
188:         //get related data
189:         $params = $this->__makeParams(self::SUCCESS, $message, $table, $tableName, $action);
190:         
191:         return View::make($this->getLayout())->nest('content', $params->view->name, $params->asArray());
192:     }
193: 
194:     /**
195:      * Index an array of records (of type StdClass) according to the pk value
196:      */
197:     protected function __indexByPk($array, $pkFieldName)
198:     {
199:         $newArray = array();
200:         foreach ($array as $key => $rec)
201:         {
202:             $newArray[$rec->$pkFieldName] = $rec;
203:         }
204:         return $newArray;
205:     }
206: 
207:     /**
208:      * Index an array of records according to the value of $fieldName
209:      */
210:     protected function __indexByValue($array, $fieldName)
211:     {
212:         $newArray = array();
213:         foreach ($array as $key => $rec)
214:         {
215:             $newArray[$rec[$fieldName]] = $rec;
216:         }
217:         return $newArray;
218:     }
219: 
220:     protected function __attachRelatedData($records, $ma)
221:     {
222:         $pkTables = array();
223:         
224:         return $pkTables;
225:     }    
226:     
227:     /**
228:      * 
229:      * 
230:      * @param type $records
231:      * @param type $ma
232:      * @return array
233:      */
234:     protected function __attachPkData($records, $ma)
235:     {
236:         $pkTables = array();
237:         $pkRec = array();
238: 
239:         foreach ($records as $record)
240:         {
241:             foreach ($record as $name => $value)
242:             {
243:                 if (isset($ma[$name]['pk']))
244:                 {
245:                     $this->log(self::INFO, "{$ma[$name]['name']} has a pk");
246:                     //$name is a foreign key, it contains a reference to a primary key
247:                     //pk display field's meta data array
248:                     $pkdfMetaA = $ma[$name]['pk_display'];
249: 
250:                     //pk meta data array
251:                     $pkfMetaA = $ma[$name]['pk'];
252: 
253:                     //get the name of the display field
254:                     $pkdfName = $pkdfMetaA['name'];
255: 
256:                     //get the name of the pk field
257:                     $pkfName = $pkfMetaA['name'];
258: 
259:                     //the primary key's tablename
260:                     $pkTableName = $ma[$name]['pk']['tableName'];
261: 
262:                     //the value of the foreign key, which is also the value of the pk we are looking for
263:                     $pkValue = $value;
264: 
265:                     if (!array_key_exists($pkTableName, $this->dbTables))
266:                     {
267: 
268:                         
269:                         $pkData = DB::table($pkTableName)->get();
270:                         $pkData = $this->__indexByPk($pkData, $pkfName);
271:                         $pktMeta = Table::getTableMeta($pkTableName);
272: //an array of 
273:                         $pkDataA = DbGopher::makeArray($pktMeta['fields'], $pkData);
274: 
275:                         $this->dbTables[$pkTableName] = array('data' => $pkData, 'meta' => $pktMeta, 'dataA' => $pkDataA);
276:                         
277: //                        $this->dbTables[$pkTableName] = array();
278:                     }
279: 
280:                     //get the actual data of the primary key related to this field (not the meta data)
281:                     $pkData = DB::table($pkTableName)->where($pkfName, $pkValue)->get();
282:                     
283:                     //get the value of the display field related to the pk
284:                     if (isset($this->dbTables[$pkTableName]['data'][$pkValue])) {
285:                         $pkdValue = $this->dbTables[$pkTableName]['data'][$pkValue]->$pkdfName;
286:                     } else {
287:                         $pkdValue = '';
288:                     }
289:                     
290:                     $pkRec[$pkValue] = $pkdValue;
291:                     
292:                     $this->log(self::INFO, "{$ma[$name]['name']} : key {$pkTableName}.{$pkfName} = {$pkValue} display : {$pkdfName} = {$pkRec[$pkValue]}");
293: 
294:                     if (!array_key_exists($pkTableName, $pkTables))
295:                     {
296:                         $pkTables[$pkTableName] = array();
297:                     }
298: 
299:                     $pkTables[$pkTableName][$pkValue] = $pkdValue;
300:                     
301:                 }
302:             }
303:         }
304: //        print_r($pkTables);
305:         return $pkTables;
306:     }
307: 
308:     /**
309:      * Handle a search request and display it in the select view
310:      * 
311:      * @param type $tableName
312:      * @return type
313:      */
314:     public function getSearch($tableName = null, $q = null)
315:     {
316:         $action = 'getSelect';
317: 
318:         //get the json string from the http querystring ?q=json
319: //        $json = Input::get('q');
320:         $json = $q;
321: 
322:         $searchObj = json_decode($json, true);
323: 
324:         foreach ($searchObj as $sTable => $sFields)
325:         {
326:             $table = DB::table($sTable);
327: 
328:             foreach ($sFields as $sField => $sValue)
329:             {
330:                 $table->where($sField, '=', $sValue);
331:             }
332:         }
333: 
334:         $params = $this->__makeParams(self::SUCCESS, "Records selected.", $table, $tableName, $action);
335: 
336:         return View::make($this->layout)->nest('content', $params->view->name, $params->asArray());
337:     }
338: 
339:     /**
340:      * Create a standard params object that will be passed to the view.  The params object (instance of Laravella\Crud\Params
341:      * is at the heart of every view and contains all the variables that will be passed to the view, from the DbController.
342:      * 
343:      * Data is not fetched yet, use data->get(), or data->paginate() to fetch
344:      * 
345:      * @param type $data
346:      * @param type $tableName
347:      * @param type $action
348:      * @return \Laravella\Crud\Params
349:      */
350:     protected function __makeParams($status, $message, $data, $tableName, $action)
351:     {
352: 
353:         $this->log(self::INFO, "tableName = $tableName");
354: 
355:         $prefix = array("id" => "/db/edit/$tableName/");
356: 
357:         $tables = array();
358:         $pkTables = array();
359: 
360:         $tableMeta = Table::getTableMeta($tableName);
361: 
362:         $view = $this->__getView($tableName, $action);
363: 
364:         $tableActionViews = array();
365:         try {
366:             if (is_object($view)) {
367:                 $tableActionViews = $this->__getTableActionView($tableName, $view->id, $action);
368:             }
369:         } catch (Exception $e) {
370:             $tableActionViews = array();
371:         }
372: 
373:         $selects = $this->__getPkSelects($tableMeta['fields_array']);
374: 
375:         $this->log(self::INFO, "makeParams");
376: 
377:         if (is_object($data)) {
378:         
379:             $pageSize = (is_object($view))?$view->page_size:10;
380:             
381:             $paginated = $data->paginate($pageSize);
382: 
383:             $dataA = DbGopher::makeArray($tableMeta['fields'], $paginated);
384: 
385:             $tables[$tableName] = new Table($tableName, $dataA, $tableMeta);
386: 
387:             $pkTables = $this->__attachPkData($paginated, $tableMeta['fields_array']);
388: 
389:             $relatedData = $this->__attachRelatedData($paginated, $tableMeta['fields_array']);
390:             
391:             foreach ($pkTables as $pktName => $pkTable)
392:             {
393:                 $tables[$pktName] = new Table($pktName, $this->dbTables[$pktName]['dataA'], $this->dbTables[$pktName]['meta']);
394: //                $tables[$pktName] = new Table($pktName, array(), array());
395:             }
396: 
397:             $p = new Params($status, $message, $this->log, $view, $action, $tableMeta, 
398:                     $tableActionViews, $prefix, $selects, $this->displayType, $dataA, $tables, $paginated, $pkTables);
399:         
400:         } else {
401:             
402:             $p = new Params($status, $message, $this->log, $view, $action, $tableMeta, 
403:                     $tableActionViews, $prefix, $selects, $this->displayType);
404:             
405: //            print_r($p);
406: //            die;
407:             
408:         }
409:         
410:         return $p;
411:     }
412: 
413:     /**
414:      * Delete a record
415:      * 
416:      * @param type $tableName
417:      * @param type $recorid
418:      * @return type
419:      */
420:     public function getDelete($tableName = null, $recorid = null)
421:     {
422:         //check for foreign key constraints
423:         $action = 'getDelete';
424:         $params = null;
425:         try {
426:             DB::table($tableName)->where('id', '=' ,$recorid)->delete();
427:             $params = $this->__makeParams(self::SUCCESS, "Record deleted.", null, $tableName, $action);
428:         } catch (Exception $e) {
429:             $params = $this->__makeParams(self::IMPORTANT, "Failed to delete record.", null, $tableName, $action);
430:         }
431:         $res = '{"status":"failed"}';
432:         if (is_object($params)) {
433:             $res = json_encode($params->asArray());
434:         }
435:         echo $res;
436:         die;
437:     }
438:     
439:     /**
440:      * Prompt user to insert a new record
441:      * 
442:      * @param type $table
443:      * @param type $pkValue
444:      * @return type
445:      */
446:     public function getInsert($tableName = null)
447:     {
448:         $action = 'getInsert';
449: 
450:         $params = $this->__makeParams(self::INFO, "Enter data to insert.", null, $tableName, $action);
451:         
452:         return View::make($this->getLayout())->nest('content', $params->view->name, $params->asArray());
453:     }
454: 
455:     /**
456:      * Display a single record on screen to be edited by the user
457:      * 
458:      * @param type $table
459:      * @param type $pkValue
460:      * @return type
461:      */
462:     public function getEdit($tableName = null, $pkValue = 0)
463:     {
464:         $action = 'getEdit';
465: 
466:         $tableMeta = Table::getTableMeta($tableName);
467: 
468:         //get metadata as an array
469:         $pkName = $tableMeta['table']['pk_name'];
470: 
471:         $table = DB::table($tableName)->where($pkName, '=', $pkValue);
472: 
473:         $params = $this->__makeParams(self::INFO, 'Edit data.', $table, $tableName, $action);
474: 
475:         $paramsA = $params->asArray();
476: 
477:         return View::make($this->getLayout())->nest('content', $paramsA['view']->name, $paramsA);
478:     }
479: 
480:     /**
481:      * Update data to the database
482:      * 
483:      * @param type $tableName
484:      * @param type $id
485:      * @return type
486:      */
487:     public function postEdit($tableName = null, $pkValue = null)
488:     {
489:         $action = 'postEdit';
490: 
491:         Model::getInstance($tableName)->editRec($pkValue, Input::get('data'));
492: 
493:         return Redirect::to("db/select/$tableName");
494:     }
495: 
496:     /**
497:      * Insert a new record into $tableName
498:      */
499:     public function postInsert($tableName)
500:     {
501: 
502:         $action = 'postInsert';
503: 
504:         $id = Model::getInstance($tableName)->insertRec();
505: 
506:         return Redirect::to("/db/edit/$tableName/$id");
507:     }
508: 
509:     /**
510:      * Loop through foreign keys and generate an array of select boxes for each
511:      * related primary key
512:      * 
513:      * @param type $meta
514:      */
515:     protected function __getPkSelects($meta)
516:     {
517:         $selectA = array();
518:         foreach ($meta as $metaField)
519:         {
520:             if (isset($metaField['pk']))
521:             {
522:                 //metadata of the primary key
523:                 $pk = $metaField['pk'];
524:                 
525:                 //meta data of the field used to display the primary key
526:                 $pkd = $pk;
527:                 if (isset($metaField['pk_display']) && !empty($metaField['pk_display'])) {
528:                     $pkd = $metaField['pk_display'];
529:                 }
530:                 
531:                 $selectA[$metaField['name']] = $this->__getSelect($pk['tableName'], $pk['name'], $pkd['name']);
532:             }
533:         }
534:         return $selectA;
535:     }
536: 
537:     /**
538:      * Get a select array(object(value, text))
539:      */
540:     protected function __getSelect($table, $valueField, $textField)
541:     {
542:         $data = DB::table($table)->select($valueField, $textField)->get();
543:         $arr = array();
544:         if (is_array($data))
545:         {
546:             foreach ($data as $rec)
547:             {
548:                 $arr[$rec->$valueField] = array('value' => $rec->$valueField, 'text' => $rec->$textField);
549:             }
550:         }
551:         return $arr;
552:     }
553: 
554:     /**
555:      * This is for custom front-end actions that simply needs the Params object 
556:      * and defines it's own view via the table_action_view table
557:      * 
558:      * @param type $parameters
559:      * @return type
560:      */
561:     protected function _customAction($parameters) {
562:         $action = 'get'.$parameters[0];
563:         $tableName = $parameters[1];
564:         $identifier = (isset($parameters[2]))?$parameters[2]:'';
565: 
566:         $tableMeta = Table::getTableMeta($tableName);
567: 
568:         //get metadata as an array
569:         $pkName = $tableMeta['table']['pk_name'];
570:         
571:         $table = null;
572:         $pkName = ($tableName == 'contents')?'slug':'id';
573:         
574:         if (!empty($identifier)) {
575:             $table = DB::table($tableName)->where($pkName, '=', $identifier);
576:         } else if ($action == 'getRegister') {
577:             $table = array();
578:         } else {
579:             $table = DB::table($tableName);
580:         }
581:         
582:         $params = $this->__makeParams(self::INFO, 'Edit data.', $table, $tableName, $action);
583:         $paramsA = $params->asArray();
584:         
585:         return View::make($paramsA['view']->name)->with($paramsA);
586: //        return View::make('cart::layouts.frontend')->nest('content', $paramsA['view']->name, $paramsA);
587:     }
588:     
589:     public function getTest() {
590:         
591: //        $tables = Model::getInstance('_db_tables', array('name' => 'noTable'));
592: //
593: //        $field = Model::getInstance('_db_fields', array('id'=>1000, 'name' => 'noTable', 
594: //            'fullname'=>'asdf', 'label'=>'asdf', 'table_id'=>0, 'pk_field_id'=>0, 'pk_display_field_id'=>0));
595: //        
596: //        return "save";
597: //        $field = $tables->push($field);
598: //        echo var_dump($field);
599:     }
600:     
601:     /**
602:      * If method is not found
603:      * 
604:      * @param type $parameters
605:      * @return string
606:      */
607:     public function missingMethod($parameters)
608:     {
609:         return $this->_customAction($parameters);
610: //        print_r($parameters);
611: //        return "missing";
612:     }
613: 
614: }
615: 
616: ?>
617: