1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10:
11:
12: namespace CRUDlex;
13:
14: use CRUDlex\CRUDEntity;
15: use CRUDlex\CRUDData;
16: use CRUDlex\CRUDFileProcessorInterface;
17:
18: 19: 20:
21: class CRUDMySQLData extends CRUDData {
22:
23: 24: 25:
26: protected $db;
27:
28: 29: 30: 31: 32: 33: 34: 35: 36: 37:
38: public function __construct(CRUDEntityDefinition $definition, CRUDFileProcessorInterface $fileProcessor, $db) {
39: $this->definition = $definition;
40: $this->fileProcessor = $fileProcessor;
41: $this->db = $db;
42: }
43:
44: 45: 46:
47: public function get($id) {
48: $entities = $this->listEntries(array('id' => $id));
49: if (count($entities) == 0) {
50: return null;
51: }
52: return $entities[0];
53: }
54:
55: 56: 57:
58: public function listEntries(array $filter = array()) {
59: $fieldNames = $this->definition->getFieldNames();
60: $sql = 'SELECT `'.implode('`,`', $fieldNames).'`';
61: $sql .= ' FROM '.$this->definition->getTable().' WHERE deleted_at IS NULL';
62: $values = array();
63: foreach ($filter as $field => $value) {
64: $sql .= ' AND `'.$field.'` = ?';
65: $values[] = $value;
66: }
67: $rows = $this->db->fetchAll($sql, $values);
68: $entities = array();
69: foreach ($rows as $row) {
70: $entities[] = $this->hydrate($row);
71: }
72: return $entities;
73: }
74:
75: 76: 77:
78: public function create(CRUDEntity $entity) {
79: $formFields = $this->definition->getEditableFieldNames();
80: $fields = array_merge(array('created_at', 'updated_at', 'version'),
81: $formFields);
82: $placeHolders = array();
83: $values = array();
84: for ($i = 0; $i < count($formFields); ++$i) {
85: $placeHolders[] = '?';
86: $value = $entity->get($formFields[$i]);
87: if ($this->definition->getType($formFields[$i]) == 'bool') {
88: $value = $value ? 1 : 0;
89: }
90: $values[] = $value;
91: }
92: $sql = 'INSERT INTO '.$this->definition->getTable().' (`'.implode('`,`', $fields).'`) VALUES (NOW(), NOW(), 0, '.implode(',', $placeHolders).')';
93: $this->db->executeUpdate($sql, $values);
94:
95: $entity->set('id', $this->db->lastInsertId());
96: }
97:
98: 99: 100:
101: public function update(CRUDEntity $entity) {
102: $formFields = $this->definition->getEditableFieldNames();
103: $fields = array_merge(array('updated_at', 'version'),
104: $formFields);
105: $values = array();
106: $sets = array();
107: for ($i = 0; $i < count($formFields); ++$i) {
108: $value = $entity->get($formFields[$i]);
109: if ($this->definition->getType($formFields[$i]) == 'bool') {
110: $value = $value ? 1 : 0;
111: }
112: $values[] = $value;
113: $sets[] = '`'.$formFields[$i].'`=?';
114: }
115: $values[] = $entity->get('id');
116: $sql = 'UPDATE '.$this->definition->getTable().' SET updated_at = NOW(), ';
117: $sql .= implode(',', $sets).' WHERE id=?';
118: $this->db->executeUpdate($sql, $values);
119:
120: return $this->db->lastInsertId();
121: }
122:
123: 124: 125:
126: public function delete($id) {
127: foreach ($this->definition->getChildren() as $parent) {
128: $sql = 'SELECT COUNT(id) AS amount FROM '.$parent[0].' WHERE ';
129: $sql .= $parent[1].' = ? AND deleted_at IS NULL';
130: $result = $this->db->fetchAssoc($sql, array($id));
131: if ($result['amount'] > 0) {
132: return false;
133: }
134: }
135:
136: $sql = 'UPDATE '.$this->definition->getTable().' SET deleted_at = NOW() WHERE id = ?';
137: $this->db->executeUpdate($sql, array($id));
138: return true;
139: }
140:
141: 142: 143:
144: public function getReferences($table, $nameField) {
145: $sql = 'SELECT id, `'.$nameField.'` FROM '.$table.' WHERE deleted_at IS NULL ORDER BY `'.$nameField.'`';
146: $entries = $this->db->fetchAll($sql);
147: $result = array();
148: foreach ($entries as $entry) {
149: $result[$entry['id']] = $entry[$nameField];
150: }
151: return $result;
152: }
153:
154: 155: 156:
157: public function countBy($table, array $params, array $paramsOperators, $includeDeleted) {
158: $sql = 'SELECT COUNT(id) AS amount FROM '.$table;
159: $paramValues = array();
160: $paramSQLs = array();
161: foreach($params as $name => $value) {
162: $paramSQLs[] = '`'.$name.'`'.$paramsOperators[$name].'?';
163: $paramValues[] = $value;
164: }
165: $sql .= ' WHERE '.implode(' AND ', $paramSQLs);
166: if ($includeDeleted) {
167: $sql .= ' AND deleted_at IS NULL';
168: }
169: $result = $this->db->fetchAssoc($sql, $paramValues);
170: return intval($result['amount']);
171: }
172:
173: 174: 175:
176: public function fetchReferences(CRUDEntity $entity = null) {
177: if (!$entity) {
178: return;
179: }
180: foreach ($this->definition->getFieldNames() as $field) {
181: if ($this->definition->getType($field) !== 'reference') {
182: continue;
183: }
184: $nameField = $this->definition->getReferenceNameField($field);
185: $sql = 'SELECT '.$nameField.' FROM ';
186: $sql .= $this->definition->getReferenceTable($field).' WHERE id = ? AND deleted_at IS NULL';
187: $result = $this->db->fetchAssoc($sql, array($entity->get($field)));
188: if ($result) {
189: $entity->set($field,
190: array('id' => $entity->get($field), 'name' => $result[$nameField]));
191: }
192: }
193: }
194:
195: }
196: