1: <?php
  2: 
  3: use Laravella\Crud\DbGopher;
  4: 
  5: class Table extends Eloquent {
  6: 
  7:     protected $tableName = "";
  8:     public $tableMetaData = null;
  9:     public $records = array();
 10: 
 11:     protected $primaryKey;
 12:     private $dbFields;
 13:     private $pageSize = 10;
 14:     private $selectBox = array();
 15: 
 16:     public static function getMetaFields() {
 17:         return array('_db_fields.id', '_db_fields.name', '_db_tables.name as tableName', '_db_fields.label', 
 18:             '_db_fields.key', '_db_fields.display_type_id', '_db_fields.type', '_db_fields.length', 
 19:             '_db_fields.default', '_db_fields.extra', '_db_fields.href', 
 20:             '_db_key_fields.pk_field_id', '_db_key_fields.pk_display_field_id', 
 21:             '_db_fields.display_order', '_db_fields.width', 
 22:             '_db_fields.widget_type_id', '_db_fields.searchable');
 23:     }
 24:     
 25:     /**
 26:      * Constructor
 27:      * 
 28:      * @param type $tableName
 29:      * @param type $tableMeta
 30:      * @param type $data
 31:      * @param type $pageSize
 32:      * @param type $selectBox
 33:      */
 34:     public function __construct($tableName, $data, $tableMeta = null, $pageSize = 10, $selectBox = array()) {
 35:         $this->records = $data;
 36:         $this->tableName = $tableName;
 37:         if ($tableMeta == null) {
 38:             $this->tableMetaData = Table::getTableMeta($tableName);
 39:         } else {
 40:             $this->tableMetaData = $tableMeta;
 41:         }
 42:         $this->pageSize = $pageSize;
 43:         $this->selectBox = $selectBox;
 44:     }
 45:     
 46:     /**
 47:      * Getter for records
 48:      * 
 49:      * @return type
 50:      */
 51:     public function records() {
 52:         return $this->records;
 53:     }
 54:     
 55:     /**
 56:      * Getter for pageSize
 57:      * 
 58:      * @return type
 59:      */
 60:     public function pageSize() {
 61:         return $this->pageSize;
 62:     }
 63:     
 64:     /**
 65:      * Getter for selectBox
 66:      * 
 67:      * @return type
 68:      */
 69:     public function selectBox() {
 70:         return $this->selectBox();
 71:     }
 72:     
 73:     /*
 74:       'table' => array('name' => $tableName, 'pk_name' => $pkName),
 75:       'fields_array' => $fieldMeta,
 76:       'fields' => $meta,
 77:      */
 78: 
 79:     public function meta()
 80:     {
 81:         if ($this->tableMetaData == null) {
 82:             $this->tableMetaData = Table::getTableMeta($this->tableName);
 83:         }        
 84:         return $this->$tableMetaData['fields'];
 85:     }
 86: 
 87:     public function metaA()
 88:     {
 89:         if ($this->tableMetaData == null) {
 90:             $this->tableMetaData = Table::getTableMeta($this->tableName);
 91:         }        
 92:         return $this->$tableMetaData['fields_array'];
 93:     }
 94: 
 95:     public function pk()
 96:     {
 97:         return $this->$tableMetaData['table']['pk_name'];
 98:     }
 99: 
100:     public function name()
101:     {
102:         //return $this->tableName;
103:     }
104: 
105:     protected function setTableName($tableName)
106:     {
107:         $this->tableName = $tableName;
108:     }
109: 
110:     public static function get($tableName)
111:     {
112:         $table = new Table();
113:         $table->setTableName($tableName);
114:         $table->tableMetaData = Table::getTableMeta($tableName); //Table::getMeta("_db_fields");
115:         return $table;
116:     }
117: 
118:     /**
119:      * Gets field metadata from the fieldname and tablename
120:      */
121:     public static function getFieldMetaN($fieldName, $tableName)
122:     {
123:         //get metadata of a single field from database
124:         $fieldMeta = DB::table("_db_fields")
125:                         ->join('_db_tables', '_db_fields.table_id', '=', '_db_tables.id')
126:                         ->leftJoin('_db_key_fields', function($join)
127:                                {
128:                                    $join->on('_db_key_fields.fk_field_id', '=', '_db_fields.id');
129:                                })                
130:                         ->select(static::getMetaFields)
131:                         ->where("_db_tables.name", $tableName)
132:                         ->where("_db_fields.name", $fieldName)->get();
133:                                
134:         return Table::__field_meta($fieldMeta);
135:     }
136: 
137:     /**
138:      * 
139:      * 
140:      * @param type $fieldId the id of the field in _db_fields
141:      * @param type $dbFieldsMeta [optional] always the result of Table::getMeta("_db_fields");
142:      * @return type
143:      */
144:     public static function getFieldMeta($fieldId, $dbFieldsMeta = null)
145:     {
146:         //get metadata of a single field from database
147:         $fieldMeta = DB::table("_db_fields")
148:                         ->join('_db_tables', '_db_fields.table_id', '=', '_db_tables.id')
149:                         ->leftJoin('_db_key_fields', function($join)
150:                                {
151:                                    $join->on('_db_key_fields.pk_field_id', '=', '_db_fields.id');
152:                                })                
153:                         ->select(static::getMetaFields())
154: 
155:                         ->where("_db_fields.id", $fieldId)->get();
156: 
157:         return Table::__field_meta($fieldMeta);
158:     }
159: 
160:     private static function __field_meta($fieldMeta, $dbFieldsMeta = null)
161:     {
162:         $tableName = $fieldMeta[0]->tableName;
163: 
164:         if (empty($dbFieldsMeta))
165:         {
166: //            $dbFieldsMeta = Table::getMeta("_db_fields");
167:             $dbFieldsMeta = Table::getMultiMeta(static::getMetaFields());
168:         }
169: 
170:         //turn $fieldMeta into an array
171:         $fieldMetaA = DbGopher::makeArray($dbFieldsMeta, $fieldMeta);
172:         $fieldMetaA[0]['tableName'] = $tableName;
173: 
174:         return $fieldMetaA[0];
175:     }
176: 
177:     /**
178:      * Safely return a value from an array
179:      * 
180:      * @param type $arr
181:      * @param type $key
182:      * @return type
183:      */
184:     public static function val($arr, $key) {
185:         $val = null;
186:   //      if (!isset($arr) && !empty($arr) && isset($key) && !empty($key) && is_array($arr) && isset($arr[$key])) {
187:             $val = $arr[$key];
188: //        }
189:         return $val;
190:     }
191:     
192:     
193:     
194:     /**
195:      * return an array(
196:      *  'table'=>array('name'=>'name', 'pk_name'=>'fieldname'), 
197:      *  'fields_array'=array('fieldname'=>fieldData,...))
198:      *  
199:      *  fieldData is in the format : 
200:      *  
201:      *  array("id" => 1, "name" => fieldName, "label" => 'Field Name' ...)
202:      * 
203:      */
204:     public static function getTableMeta($tableName)
205:     {
206:         //get metadata from database
207:         $meta = Table::getMeta($tableName);
208: 
209:         $fmeta =  Table::getMultiMeta(static::getMetaFields()); //Table::getMeta("_db_fields");
210: 
211:         //turn metadata into array
212:         $metaA = DbGopher::makeArray($fmeta, $meta);
213:         
214:         $fieldMeta = Table::addPkData($tableName, $metaA);
215: 
216:         $pkName = "";
217:         foreach ($metaA as $name => $data)
218:         {
219:             if (Table::val($data,'key') == 'PRI')
220:             {
221:                 $pkName = Table::val($data,'name');
222:             }
223:         }
224: 
225:         $tmData = array(
226:             'table' => array('name' => $tableName, 'pk_name' => $pkName),
227:             'fields_array' => $fieldMeta,
228:             'fields' => $meta,
229:         );
230: 
231:         return $tmData;
232:     }
233: 
234:     /**
235:      * get field metadata from database
236:      * 
237:      * @param type $tableName
238:      * @return type
239:      */
240:     public static function getMeta($tableName)
241:     {
242:         $tableMeta = DB::table("_db_fields")
243:                         ->join('_db_tables', '_db_fields.table_id', '=', '_db_tables.id')
244:                         ->join('_db_display_types', '_db_fields.display_type_id', '=', '_db_display_types.id')
245:                         ->leftJoin('_db_key_fields', function($join)
246:                                {
247:                                    $join->on('_db_key_fields.fk_field_id', '=', '_db_fields.id');
248:                                })  
249:                         ->select(static::getMetaFields())
250:                         ->orderBy('display_order', 'asc')
251:                         ->where("_db_tables.name", "=", $tableName)->get();
252: 
253:         return $tableMeta;
254:     }
255:     
256:     /**
257:      * get field metadata from database
258:      * 
259:      * @param type $tableName
260:      * @return type
261:      */
262:     public static function getMultiMeta($fieldNames)
263:     {
264:         
265:         $tableMeta = DB::table("_db_fields")
266:                         ->join('_db_tables', '_db_fields.table_id', '=', '_db_tables.id')
267:                         ->join('_db_display_types', '_db_fields.display_type_id', '=', '_db_display_types.id')
268:                         ->leftJoin('_db_key_fields', function($join)
269:                                {
270:                                    $join->on('_db_key_fields.fk_field_id', '=', '_db_fields.id');
271:                                })                
272:                         ->select(static::getMetaFields())
273:                         ->orderBy('display_order', 'asc')
274:                         ->whereIn("_db_fields.fullname", $fieldNames)->get();
275: 
276:         return $tableMeta;
277:     }    
278:     
279:     /**
280:      * Get a table's metadata (from _db_fields table) as an array
281:      * 
282:      * @param type $tableName
283:      * @return type
284:      */
285:     public static function getMetaArray($tableName)
286:     {
287: 
288:         //get metadata from database
289:         $meta = Table::getMeta($tableName);
290: 
291:         $fieldsMeta =  Table::getMultiMeta(static::getMetaFields()); //Table::getMeta("_db_fields");
292: 
293:         //turn metadata into array
294:         $ma = DbGopher::makeArray($fieldsMeta, $meta);
295: 
296:         $metaA = Table::addPkData($tableName, $ma, $fieldsMeta);
297: 
298:         return $metaA;
299:     }
300: 
301:     /**
302:      * 
303:      * @param type $tableName
304:      * @param type $ma
305:      * @param type $mk
306:      * @param type $fieldsMeta
307:      * @return type
308:      */
309:     public static function addPkData($tableName, $ma, $fieldsMeta = null)
310:     {
311: 
312:         //set field name as key in meta array
313:         $metaA = array();
314:         
315:         try {
316:             if (empty($fieldsMeta))
317:             {
318:                 $fieldsMeta =  Table::getMultiMeta(static::getMetaFields()); //Table::getMeta("_db_fields");
319:             }
320: 
321:             foreach ($ma as $mk)
322:             {
323:                 //the name of the table
324:                 $mk['tableName'] = $tableName;
325:                 if (!empty($mk['pk_field_id']))
326:                 {
327:                     //add primary key's metadata to foreignkey metadata
328:                     $mk['pk'] = Table::getFieldMeta($mk['pk_field_id'], $fieldsMeta);
329:                     if (!empty($mk['pk_display_field_id']))
330:                     {
331:                         //add primary key's (displayed one) metadata to foreignkey metadata
332:                         $mk['pk_display'] = Table::getFieldMeta($mk['pk_display_field_id'], $fieldsMeta);
333:                     }
334:                 }
335:                 $fname = Table::val($mk, 'name');
336:                 $metaA[$fname] = $mk;
337:             }
338:         } catch(Exception $e) {
339:             echo $e->getMessage();
340:         }
341:         return $metaA;
342:     }
343: 
344: }
345: 
346: ?>
347: