1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11:
12: class Ingo_Storage_Filters_Sql extends Ingo_Storage_Filters {
13:
14: 15: 16: 17: 18:
19: protected $_db;
20:
21: 22: 23: 24: 25:
26: protected $_params;
27:
28: 29: 30: 31: 32: 33:
34: public function __construct(Horde_Db_Adapter $db, $params)
35: {
36: $this->_db = $db;
37: $this->_params = $params;
38: }
39:
40: 41: 42: 43: 44:
45: public function init($readonly = false)
46: {
47: $query = sprintf('SELECT * FROM %s WHERE rule_owner = ? ORDER BY rule_order',
48: $this->_params['table_rules']);
49: $values = array(Ingo::getUser());
50: try {
51: $result = $this->_db->selectAll($query, $values);
52: } catch (Horde_Db_Exception $e) {
53: throw new Ingo_Exception($e);
54: }
55: $data = array();
56: foreach ($result as $row) {
57: $data[$row['rule_order']] = array(
58: 'id' => (int)$row['rule_id'],
59: 'name' => Horde_String::convertCharset($row['rule_name'], $this->_params['charset'], 'UTF-8'),
60: 'action' => (int)$row['rule_action'],
61: 'action-value' => Horde_String::convertCharset($row['rule_value'], $this->_params['charset'], 'UTF-8'),
62: 'flags' => (int)$row['rule_flags'],
63: 'conditions' => empty($row['rule_conditions']) ? null : Horde_String::convertCharset(unserialize($row['rule_conditions']), $this->_params['charset'], 'UTF-8'),
64: 'combine' => (int)$row['rule_combine'],
65: 'stop' => (bool)$row['rule_stop'],
66: 'disable' => !(bool)$row['rule_active']);
67: }
68: $this->setFilterlist($data);
69:
70: if (empty($data) && !$readonly) {
71: $data = @unserialize($GLOBALS['prefs']->getDefault('rules'));
72: if ($data) {
73: foreach ($data as $val) {
74: $this->addRule($val, false);
75: }
76: } else {
77: $this->addRule(
78: array('name' => 'Whitelist',
79: 'action' => Ingo_Storage::ACTION_WHITELIST),
80: false);
81: $this->addRule(
82: array('name' => 'Vacation',
83: 'action' => Ingo_Storage::ACTION_VACATION,
84: 'disable' => true),
85: false);
86: $this->addRule(
87: array('name' => 'Blacklist',
88: 'action' => Ingo_Storage::ACTION_BLACKLIST),
89: false);
90: $this->addRule(
91: array('name' => 'Spam Filter',
92: 'action' => Ingo_Storage::ACTION_SPAM,
93: 'disable' => true),
94: false);
95: $this->addRule(
96: array('name' => 'Forward',
97: 'action' => Ingo_Storage::ACTION_FORWARD),
98: false);
99: }
100: }
101: }
102:
103: 104: 105: 106: 107: 108: 109: 110:
111: protected function _ruleToBackend(array $rule)
112: {
113: return array(Horde_String::convertCharset($rule['name'], 'UTF-8', $this->_params['charset']),
114: (int)$rule['action'],
115: isset($rule['action-value']) ? Horde_String::convertCharset($rule['action-value'], 'UTF-8', $this->_params['charset']) : null,
116: isset($rule['flags']) ? (int)$rule['flags'] : null,
117: isset($rule['conditions']) ? serialize(Horde_String::convertCharset($rule['conditions'], 'UTF-8', $this->_params['charset'])) : null,
118: isset($rule['combine']) ? (int)$rule['combine'] : null,
119: isset($rule['stop']) ? (int)$rule['stop'] : null,
120: isset($rule['disable']) ? (int)(!$rule['disable']) : 1);
121: }
122:
123: 124: 125: 126: 127: 128: 129:
130: public function addRule(array $rule, $default = true)
131: {
132: if ($default) {
133: $rule = array_merge($this->getDefaultRule(), $rule);
134: }
135:
136: $query = sprintf('INSERT INTO %s (rule_owner, rule_name, rule_action, rule_value, rule_flags, rule_conditions, rule_combine, rule_stop, rule_active, rule_order) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
137: $this->_params['table_rules']);
138:
139: $order = key(array_reverse($this->_filters, true)) + 1;
140: $values = array_merge(array(Ingo::getUser()),
141: $this->_ruleToBackend($rule),
142: array($order));
143: try {
144: $result = $this->_db->insert($query, $values);
145: } catch (Horde_Db_Exception $e) {
146: throw new Ingo_Exception($e);
147: }
148: $rule['id'] = $result;
149: $this->_filters[$order] = $rule;
150: }
151:
152: 153: 154: 155: 156: 157:
158: public function updateRule($rule, $id)
159: {
160: $query = sprintf('UPDATE %s SET rule_name = ?, rule_action = ?, rule_value = ?, rule_flags = ?, rule_conditions = ?, rule_combine = ?, rule_stop = ?, rule_active = ?, rule_order = ? WHERE rule_id = ? AND rule_owner = ?',
161: $this->_params['table_rules']);
162: $values = array_merge($this->_ruleToBackend($rule),
163: array($id, $rule['id'], Ingo::getUser()));
164: try {
165: $this->_db->update($query, $values);
166: }catch (Horde_Db_Exception $e) {
167: throw new Ingo_Exception($e);
168: }
169:
170: $this->_filters[$id] = $rule;
171: }
172:
173: 174: 175: 176: 177: 178: 179:
180: public function deleteRule($id)
181: {
182: if (!isset($this->_filters[$id])) {
183: return false;
184: }
185:
186: $query = sprintf('DELETE FROM %s WHERE rule_id = ? AND rule_owner = ?',
187: $this->_params['table_rules']);
188: $values = array($this->_filters[$id]['id'], Ingo::getUser());
189: try {
190: $result = $this->_db->delete($query, $values);
191: } catch (Horde_Db_Exception $e) {
192: throw new Ingo_Exception($e);
193: }
194:
195:
196: unset($this->_filters[$id]);
197: $this->_filters = array_combine(
198: range(1, count($this->_filters)),
199: array_values($this->_filters)
200: );
201:
202: $query = sprintf('UPDATE %s SET rule_order = rule_order - 1 WHERE rule_owner = ? AND rule_order > ?',
203: $this->_params['table_rules']);
204: $values = array(Ingo::getUser(), $id);
205: try {
206: $this->_db->update($query, $values);
207: } catch (Horde_Db_Exception $e) {
208: throw new Ingo_Exception($e);
209: }
210:
211: return true;
212: }
213:
214: 215: 216: 217: 218: 219: 220: 221: 222: 223:
224: public function copyRule($id)
225: {
226: if (isset($this->_filters[$id])) {
227: $newrule = $this->_filters[$id];
228: $newrule['name'] = sprintf(_("Copy of %s"), $this->_filters[$id]['name']);
229: $this->addRule($newrule, false);
230: $this->ruleUp(count($this->_filters) - 1, count($this->_filters) - $id - 2);
231: return true;
232: }
233:
234: return false;
235: }
236:
237: 238: 239: 240: 241: 242:
243: public function ruleUp($id, $steps = 1)
244: {
245: return $this->_ruleMove($id, -$steps);
246: }
247:
248: 249: 250: 251: 252: 253:
254: public function ruleDown($id, $steps = 1)
255: {
256: return $this->_ruleMove($id, $steps);
257: }
258:
259: 260: 261: 262: 263: 264: 265:
266: protected function _ruleMove($id, $steps)
267: {
268: $query = sprintf('UPDATE %s SET rule_order = rule_order %s 1 WHERE rule_owner = ? AND rule_order %s ? AND rule_order %s ?',
269: $this->_params['table_rules'],
270: $steps > 0 ? '-' : '+',
271: $steps > 0 ? '>' : '>=',
272: $steps > 0 ? '<=' : '<');
273: $values = array(Ingo::getUser());
274: if ($steps < 0) {
275: $values[] = (int)($id + $steps);
276: $values[] = (int)$id;
277: } else {
278: $values[] = (int)$id;
279: $values[] = (int)($id + $steps);
280: }
281:
282: try {
283: $this->_db->update($query, $values);
284: } catch (Horde_Db_Exception $e) {
285: throw new Ingo_Exception($e);
286: }
287: $query = sprintf('UPDATE %s SET rule_order = ? WHERE rule_owner = ? AND rule_id = ?',
288: $this->_params['table_rules']);
289: $values = array((int)($id + $steps),
290: Ingo::getUser(),
291: $this->_filters[$id]['id']);
292: try {
293: $this->_db->update($query, $values);
294: } catch (Horde_Db_Exception $e) {
295: throw new Ingo_Exception($e);
296: }
297:
298: $this->init();
299: }
300:
301: 302: 303: 304: 305:
306: public function ruleDisable($id)
307: {
308: $rule = $this->_filters[$id];
309: $rule['disable'] = true;
310: $this->updateRule($rule, $id);
311: }
312:
313: 314: 315: 316: 317:
318: public function ruleEnable($id)
319: {
320: $rule = $this->_filters[$id];
321: $rule['disable'] = false;
322: $this->updateRule($rule, $id);
323: }
324:
325: }
326: