1: <?php
2: namespace Module\DB;
3:
4: /**
5: * Creates a grid view of a sql statement
6: *
7: * ### Usage
8: *
9: * <code>
10: * $dbg = new \Module\DB\Grid('users', 'SELECT * FROM users');
11: *
12: * $dbg->exclude(array(
13: * 'user_id',
14: * 'password'
15: * ));
16: *
17: * $dbg->mapFields(array(
18: * 'name' => 'user_id'
19: * ));
20: *
21: * $dbg->sortable(array(
22: * 'name',
23: * 'date'
24: * ));
25: *
26: * $dbg->show();
27: * </code>
28: *
29: * ### Changelog
30: *
31: * ## Version 1.2
32: * * Added namespacing
33: *
34: * ## Version 1.1
35: * * Added the date section to documentation
36: *
37: * ### Dependencies
38: * * class.db.php
39: * * class.hooks.php
40: *
41: * @date August 13, 2014
42: * @author Jaime A. Rodriguez <hi.i.am.jaime@gmail.com>
43: * @version 1.2
44: * @license http://opensource.org/licenses/MIT
45: */
46:
47: class Grid {
48: /**
49: * DB The DB object
50: */
51: protected $db;
52:
53: /**
54: * string The name of our grid
55: */
56: protected $name;
57:
58: /**
59: * string The SQL to run to make the grid
60: */
61: protected $sql;
62:
63: /**
64: * string The column to sort by
65: */
66: protected $sortBy = '';
67:
68: /**
69: * string Ascending? or Descending?
70: */
71: protected $asc = true;
72:
73: /**
74: * int How many records per page?
75: */
76: public $maxRecords = 10;
77:
78: /**
79: * int Which page are we on?
80: */
81: public $page = 1;
82:
83: /**
84: * object The meta data for the fields
85: **/
86: public $meta;
87:
88: /**
89: * array string fields that are sortable. Used when rendering column header links
90: */
91: protected $canSortby;
92:
93: /**
94: * array string Which fields to exclude from the table
95: */
96: protected $excluded;
97:
98: /**
99: * array associative An array of fields => id that gets passed to the hooks
100: */
101: protected $map;
102:
103: /**
104: * string Querystring to pass into the sorting links in the th
105: */
106: protected $querystring;
107:
108: /**
109: * Constructor
110: * @param string $name Give our DBForm a name, used for hooks and id
111: * @param string $sql What SQL to run?
112: */
113: public function __construct($name, $sql) {
114: $this->db = \Module\DB\DB::getInstance();
115: $this->sql = $sql;
116: $this->name = $name;
117: }
118:
119: /**
120: * What columns can be sorted?
121: * @param array $array fields that are sortable, used when rendering the
122: * table headers
123: */
124: public function sortable($array) {
125: $this->canSortBy = $array;
126: }
127:
128: /**
129: * Sets the Querystring to pass into the sorting links in the th
130: * @param string $query the querystring
131: */
132: public function setQuerystring($query) {
133: $this->querystring = $query;
134: }
135:
136: /**
137: * Sorts the table data
138: * @param string $column The column to sort by
139: * @param boolean $asc Sort ascending?
140: */
141: public function sort($column, $asc=true) {
142: $this->sortBy = $column;
143: $this->asc = $asc;
144: }
145:
146: /**
147: * Which fields are excluded from the table
148: * @param array $array a list of fields
149: */
150: public function exclude($array) {
151: $this->excluded = $array;
152: }
153:
154: /**
155: * Checks to see if a column has been excluded
156: * @param string $str a column
157: * @return boolean true if it has been excluded
158: */
159: private function isExcluded($str) {
160: if (!is_array($this->excluded)) {
161: return false;
162: }
163:
164: foreach ($this->excluded as $e) {
165: if ($e == $str) {
166: return true;
167: }
168: }
169:
170: return false;
171: }
172:
173: /**
174: * Maps IDs to fields for processing later
175: * @param array $array a list of fields => id that gets passed to the hooks
176: */
177: public function mapFields($array) {
178: $this->map = $array;
179: }
180:
181: /**
182: * Returns how many pages of results we have
183: * @return int The number of pages in our resultset
184: */
185: public function numberOfPages() {
186: // Create SQL Statement
187: $sql = $this->sql;
188:
189: $query = $this->db->prepare($sql);
190: $query->execute();
191: $query->setFetchMode(PDO::FETCH_OBJ);
192:
193: return ceil($query->rowCount() / $this->maxRecords);
194: }
195:
196: /**
197: * Shows an editable form
198: */
199: public function show() {
200: // Create SQL Statement
201: $sql = $this->sql;
202:
203: // Change the sort order
204: if (!empty($this->sortBy)) {
205: $asc = ($this->asc) ? "ASC" : "DESC";
206:
207: $sql = $sql . " ORDER BY `" . $this->sortBy . "` $asc";
208: }
209:
210: // Limit records
211: if ($this->maxRecords > 0) {
212: $sql = $sql . " LIMIT " . ($this->page - 1) * $this->maxRecords . ", " . $this->maxRecords;
213: }
214:
215: $query = $this->db->prepare($sql);
216: $query->execute();
217: $query->setFetchMode(\PDO::FETCH_OBJ);
218:
219: // Get column data
220: for ($i = 0; $i <= $query->columnCount(); $i ++) {
221: $meta = $query->getColumnMeta($i);
222: if (isset($meta['name'])) {
223: $this->meta[$meta['name']] = $meta;
224: }
225: }
226:
227: /**
228: * @ingroup hooks
229: */
230: \Sleepy\Hook::addAction($this->name . "_beforeTable");
231: /**
232: * @ingroup hooks
233: */
234: \Sleepy\Hook::addAction("dbGrid_beforeTable");
235: echo "<table id='{$this->name}_dbgrid' cellpadding='0' cellspacing='0'>\n<tr>";
236:
237: foreach ($this->meta as $meta) {
238: /**
239: * @ingroup hooks
240: */
241: $metaName = \Sleepy\Hook::addFilter($this->name . "_tableHeader", $meta['name']);
242: /**
243: * @ingroup hooks
244: */
245: $metaName = \Sleepy\Hook::addFilter("dbgrid_tableHeader", $metaName);
246: if (!$this->isExcluded($meta['name'])) {
247: if ($this->sortBy == $meta['name']) {
248: if ($this->asc) {
249: $asc = "&asc=false";
250: $class = "asc";
251: $char = "⇧";
252: } else {
253: $asc = "&asc=true";
254: $class = "desc";
255: }
256: } else {
257: $asc = "";
258: $class = "";
259: }
260: echo "<th>";
261: if ($this->canSortBy[$meta['name']]) {
262: if (!empty($this->querystring)) {
263: echo "<a class='{$class}' href='{$this->querystring}&sort=" . urlencode($meta['name']) . $asc . "'>" .$metaName . "</a>";
264: } else {
265: echo "<a class='{$class}' href='?sort=" . urlencode($meta['name']) . $asc . "'>" .$metaName . "</a>";
266: }
267: } else {
268: echo $metaName;
269: }
270:
271: echo "</th>";
272: }
273: }
274:
275: echo "</tr>";
276:
277: foreach($query->fetchAll() as $row) {
278: if (empty($rzebra)) {
279: $rzebra = "zebra";
280: } else {
281: $rzebra = "";
282: }
283: echo "<tr class='{$rzebra}'>";
284: foreach ($row as $key => $value) {
285: $value = htmlentities($value);
286:
287: if (empty($czebra)) {
288: $czebra = "zebra";
289: } else {
290: $czebra = "";
291: }
292:
293: if (isset($this->map[$key])) {
294: $kid = $this->map[$key];
295: $id = $row->$kid;
296: } else {
297: $id = 0;
298: }
299:
300: /**
301: * @ingroup hooks
302: */
303: $value = \Sleepy\Hook::addFilter($this->name . "_tableColumn", array(
304: $value,
305: $key,
306: $id
307: ));
308:
309: /**
310: * @ingroup hooks
311: */
312: $value = \Sleepy\Hook::addFilter("dbgrid_tableColumn", array(
313: $value,
314: $key,
315: $id
316: ));
317:
318: if (!$this->isExcluded($key)) {
319: echo "<td class={$czebra}>{$value}</td>";
320: }
321: }
322: echo "</tr>";
323: }
324:
325: echo "</table>";
326: \Sleepy\Hook::addAction($this->name . "_afterTable");
327: \Sleepy\Hook::addAction("dbGrid_afterTable");
328:
329: if (isset($labels[$meta['name']])) {
330: $label = $labels[$meta['name']];
331: } else {
332: $label = $meta['name'];
333: }
334: }
335: }