1: <?php
  2: /**
  3:  * This file contains the main class for the plugin content allocation.
  4:  *
  5:  * @package Plugin
  6:  * @subpackage ContentAllocation
  7:  * @author Marco Jahn
  8:  * @copyright four for business AG <www.4fb.de>
  9:  * @license http://www.contenido.org/license/LIZENZ.txt
 10:  * @link http://www.4fb.de
 11:  * @link http://www.contenido.org
 12:  */
 13: defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');
 14: 
 15: plugin_include('repository', 'custom/FrontendNavigation.php');
 16: 
 17: /**
 18:  * Main class for content allocation
 19:  *
 20:  * @package Plugin
 21:  * @subpackage ContentAllocation
 22:  */
 23: class pApiContentAllocation {
 24: 
 25:     /**
 26:      * References database object
 27:      *
 28:      * @var cDb
 29:      */
 30:     protected $_db = null;
 31: 
 32:     /*
 33:      *
 34:      * @var boolean
 35:      */
 36:     protected $_debug = false;
 37: 
 38:     /**
 39:      * @var array
 40:      */
 41:     protected $_table = array();
 42: 
 43:     /**
 44:      * @var integer
 45:      */
 46:     protected $_lang = 0;
 47: 
 48:     /**
 49:      * @var integer
 50:      */
 51:     protected $_client = 0;
 52: 
 53:     /**
 54:      * @var object
 55:      */
 56:     protected $_treeObj = null;
 57: 
 58:     /**
 59:      * pApiContentAllocation constructor
 60:      */
 61:     public function __construct() {
 62:         $cfg = cRegistry::getConfig();
 63: 
 64:         $this->_db = cRegistry::getDb();
 65:         $this->_table = $cfg['tab'];
 66:         $this->_lang = cRegistry::getLanguageId();
 67:         $this->_client = cRegistry::getClientId();
 68: 
 69:         $this->_treeObj = new pApiTree('f31a4384-e5c1-4ede-b1bb-f43657ec73a5');
 70:     }
 71: 
 72:     /**
 73:      * Old constructor
 74:      *
 75:      * @deprecated [2016-02-11]
 76:      *              This method is deprecated and is not needed any longer. Please use __construct() as constructor function.
 77:      * @return __construct()
 78:      */
 79:     public function pApiContentAllocation() {
 80:         cDeprecated('This method is deprecated and is not needed any longer. Please use __construct() as constructor function.');
 81:         return $this->__construct();
 82:     }
 83: 
 84:     /**
 85:      * Store allocations
 86:      *
 87:      * @param integer $idartlang
 88:      * @param array $allocations
 89:      */
 90:     public function storeAllocations($idartlang, $allocations) {
 91:         // empty before insert
 92:         $this->deleteAllocationsByIdartlang($idartlang);
 93: 
 94:         if (is_array($allocations)) {
 95:             foreach ($allocations as $value) {
 96:                 $sql = "INSERT INTO " . $this->_table['pica_alloc_con'] . " (idpica_alloc, idartlang) VALUES (" . cSecurity::toInteger($value) . ", " . cSecurity::toInteger($idartlang) . ")";
 97:                 $this->_db->query($sql);
 98:             }
 99:         }
100:     }
101: 
102:     /**
103:      * Delete allocations by allocation id
104:      *
105:      * @param integer $idpica_alloc
106:      */
107:     public function deleteAllocations($idpica_alloc) {
108:         $sql = "DELETE FROM " . $this->_table['pica_alloc_con'] . " WHERE idpica_alloc = " . cSecurity::toInteger($idpica_alloc);
109:         $this->_db->query($sql);
110:     }
111: 
112:     /**
113:      * Delete allocations by language id
114:      *
115:      * @param integer $idartlang
116:      */
117:     public function deleteAllocationsByIdartlang($idartlang) {
118:         $sql = "DELETE FROM " . $this->_table['pica_alloc_con'] . " WHERE idartlang = " . cSecurity::toInteger($idartlang);
119:         $this->_db->query($sql);
120:     }
121: 
122:     /**
123:      * Load all tagging keys
124:      *
125:      * @param int $idartlang
126:      * @return array $result
127:      */
128:     public function loadAllocations($idartlang) {
129:         $this->_db->query("-- pApiContentAllocation->loadAllocations()
130:             SELECT
131:                 a.idpica_alloc
132:             FROM
133:                 `{$this->_table['pica_alloc']}` AS a
134:                 , `{$this->_table['pica_alloc_con']}` AS b
135:             WHERE
136:                 idartlang = $idartlang
137:                 AND a.idpica_alloc=b.idpica_alloc
138:             ;");
139: 
140:         $result = array();
141:         while ($this->_db->nextRecord()) {
142:             $result[] = $this->_db->f('idpica_alloc');
143:         }
144: 
145:         return $result;
146:     }
147: 
148:     /**
149:      * Load allocations by language id and parent id
150:      *
151:      * @param $idartlang
152:      * @param $parent
153:      * @param boolean $firstonly [optional]
154:      * @return array $tmp
155:      */
156:     public function loadAllocationsWithNames($idartlang, $parent, $firstonly = false) {
157:         $cfg = cRegistry::getConfig();
158: 
159:         $sql = "SELECT " . $cfg['tab']['pica_alloc'] . ".idpica_alloc FROM " . $cfg['tab']['pica_alloc'] . "
160:                     INNER JOIN " . $cfg['tab']['pica_alloc_con'] . " ON
161:                     " . $cfg['tab']['pica_alloc'] . ".idpica_alloc = " . $cfg['tab']['pica_alloc_con'] . ".idpica_alloc
162:                     WHERE (" . $cfg['tab']['pica_alloc'] . ".parentid = " . cSecurity::toInteger($parent) . ") AND (" . $cfg['tab']['pica_alloc_con'] . ".idartlang=" . cSecurity::toInteger($idartlang) . ")
163:                     ORDER BY " . $cfg['tab']['pica_alloc'] . ".sortorder";
164: 
165:         $this->_db->query($sql);
166: 
167:         while ($this->_db->nextRecord()) {
168:             $tmp[$this->_db->f("idpica_alloc")] = $this->_treeObj->fetchItemNameLang($this->_db->f("idpica_alloc"));
169: 
170:             if ($firstonly) {
171:                 break;
172:             }
173:         }
174: 
175:         return $tmp;
176:     }
177: 
178:     /**
179:      * Build query to find matching content by ContentAllocation
180:      *
181:      * @param array $restrictions [optional]
182:      * @param integer $max [optional]
183:      * @return string $sql
184:      */
185:     public function findMatchingContent($restrictions = null, $max = 0) {
186:         if (!is_array($restrictions)) {
187:             return false;
188:         }
189: 
190:         global $aCategoriesToExclude; // @see config.local.php!
191:         $sql = $this->_buildQuery($restrictions, $aCategoriesToExclude, $max);
192: 
193:         return $sql;
194:     }
195: 
196:     /**
197:      * Build query to find matching content by ContentAllocation
198:      *
199:      * @param array $restrictions
200:      * @param array $categoriesToExclude
201:      * @param integer $max
202:      * @return string $sql
203:      */
204:     protected function _buildQuery($restrictions, $categoriesToExclude, $max) {
205:         $cfg = cRegistry::getConfig();
206: 
207:         $size = sizeof($restrictions);
208: 
209:         if ($size == 0) {
210:             return '';
211:         }
212: 
213:         $sql_concat = unserialize('a:78:{i:0;s:2:"aa";i:1;s:2:"ab";i:2;s:2:"ac";i:3;s:2:"ad";i:4;s:2:"ae";i:5;s:2:"af";i:6;s:2:"ag";i:7;s:2:"ah";i:8;s:2:"ai";i:9;s:2:"aj";i:10;s:2:"ak";i:11;s:2:"al";i:12;s:2:"am";i:13;s:2:"an";i:14;s:2:"ao";i:15;s:2:"ap";i:16;s:2:"aq";i:17;s:2:"ar";i:18;s:2:"as";i:19;s:2:"at";i:20;s:2:"au";i:21;s:2:"av";i:22;s:2:"aw";i:23;s:2:"ax";i:24;s:2:"ay";i:25;s:2:"az";i:26;s:2:"ca";i:27;s:2:"cb";i:28;s:2:"cc";i:29;s:2:"cd";i:30;s:2:"ce";i:31;s:2:"cf";i:32;s:2:"cg";i:33;s:2:"ch";i:34;s:2:"ci";i:35;s:2:"cj";i:36;s:2:"ck";i:37;s:2:"cl";i:38;s:2:"cm";i:39;s:2:"cn";i:40;s:2:"co";i:41;s:2:"cp";i:42;s:2:"cq";i:43;s:2:"cr";i:44;s:2:"cs";i:45;s:2:"ct";i:46;s:2:"cu";i:47;s:2:"cv";i:48;s:2:"cw";i:49;s:2:"cx";i:50;s:2:"cy";i:51;s:2:"cz";i:52;s:1:"a";i:53;s:1:"b";i:54;s:1:"c";i:55;s:1:"d";i:56;s:1:"e";i:57;s:1:"f";i:58;s:1:"g";i:59;s:1:"h";i:60;s:1:"i";i:61;s:1:"j";i:62;s:1:"k";i:63;s:1:"l";i:64;s:1:"m";i:65;s:1:"n";i:66;s:1:"o";i:67;s:1:"p";i:68;s:1:"q";i:69;s:1:"r";i:70;s:1:"s";i:71;s:1:"t";i:72;s:1:"u";i:73;s:1:"v";i:74;s:1:"w";i:75;s:1:"x";i:76;s:1:"y";i:77;s:1:"z";}');
214: 
215:         $sqlTemplate = "SELECT cal.idart, cal.online, aa.idartlang, cat.idcat FROM {TABLES} WHERE {WHERE} ";
216: 
217:         $tables = array();
218:         $where = array();
219: 
220:         for ($i = 0; $i < $size; $i++) {
221:             if ($i == 0) { // first
222:                 $tables[] = " " . $cfg['tab']['pica_alloc_con'] . " AS " . $sql_concat[$i];
223:             } else {
224:                 $tables[] = " LEFT JOIN " . $cfg['tab']['pica_alloc_con'] . " AS " . $sql_concat[$i] . " USING (idartlang)";
225:             }
226:             if (is_int((int) $restrictions[$i]) and $restrictions[$i] > 0) {
227:                 $where[] = $sql_concat[$i] . ".idpica_alloc = " . $restrictions[$i];
228:             }
229:         }
230: 
231:         // fetch only articles which are online
232:         $where[] = 'cal.online = 1';
233: 
234:         // fetch only articles which are not in following categories
235:         if (count($categoriesToExclude) > 0) {
236:             $where[] = "cat.idcat NOT IN (" . implode(',', $categoriesToExclude) . ")";
237:         }
238: 
239:         // join art_lang for idart
240:         $tables[] = " LEFT JOIN " . $this->_table['art_lang'] . " AS cal USING (idartlang)";
241:         $tables[] = " LEFT JOIN " . $this->_table['cat_art'] . " AS cart USING (idart)";
242:         $tables[] = " LEFT JOIN " . $this->_table['cat'] . " as cat USING (idcat)";
243: 
244:         $tables = implode('', $tables);
245:         $where = implode(' AND ', $where);
246: 
247:         $sql = str_replace('{TABLES}', $tables, $sqlTemplate);
248:         $sql = str_replace('{WHERE}', $where, $sql);
249: 
250:         $sql .= " ORDER BY cal.published DESC";
251: 
252:         if ($max != 0 && is_integer($max)) {
253:             $sql .= " LIMIT " . $max;
254:         }
255: 
256:         if ($this->_debug) {
257:             print "<!-- ";
258:             print $sql;
259:             print " -->";
260:         }
261: 
262:         return $sql;
263:     }
264: 
265:     /**
266:      * Search articles by ContentAllocation and catgories
267:      *
268:      * @param array $contentAllocation
269:      * @param array $categories
270:      * @param integer $offset [optional]
271:      * @param integer $numOfRows [optional]
272:      *
273:      * @return array of articles
274:      */
275:     public function findMatchingContentByContentAllocationByCategories($contentAllocation, $categories = array(), $offset = 0, $numOfRows = 0) {
276:         if (!is_array($contentAllocation) || count($contentAllocation) == 0) {
277:             return array();
278:         }
279: 
280:         for ($i = 0; $i < count($contentAllocation); $i++) {
281:             if (!is_int((int) $contentAllocation[$i]) || !$contentAllocation[$i] > 0) {
282:                 return array();
283:             }
284:         }
285: 
286:         for ($i = 0; $i < count($categories); $i++) {
287:             if (!is_int((int) $categories[$i]) || !$categories[$i] > 0) {
288:                 return array();
289:             }
290:         }
291: 
292:         $sql = $this->_buildQuery_MatchingContentByContentAllocationByCategories($contentAllocation, $categories, $offset, $numOfRows);
293: 
294:         $this->_db->query($sql);
295: 
296:         $result = array();
297:         while (false !== $oRow = $this->_db->getResultObject()) {
298:             $result[] = $oRow;
299:         }
300: 
301:         return $result;
302:     }
303: 
304:     /**
305:      * Build SQL query to find articles by ContentAllocation and catgories
306:      *
307:      * @param array $contentAllocation
308:      * @param array $categories
309:      * @param integer offset
310:      * @param integer numOfRows
311:      *
312:      * @return array of articles
313:      */
314:     protected function _buildQuery_MatchingContentByContentAllocationByCategories($contentAllocation, $categories, $offset, $numOfRows) {
315:         $cfg = cRegistry::getConfig();
316: 
317:         $size = sizeof($contentAllocation);
318: 
319:         $sql_concat = unserialize('a:78:{i:0;s:2:"aa";i:1;s:2:"ab";i:2;s:2:"ac";i:3;s:2:"ad";i:4;s:2:"ae";i:5;s:2:"af";i:6;s:2:"ag";i:7;s:2:"ah";i:8;s:2:"ai";i:9;s:2:"aj";i:10;s:2:"ak";i:11;s:2:"al";i:12;s:2:"am";i:13;s:2:"an";i:14;s:2:"ao";i:15;s:2:"ap";i:16;s:2:"aq";i:17;s:2:"ar";i:18;s:2:"as";i:19;s:2:"at";i:20;s:2:"au";i:21;s:2:"av";i:22;s:2:"aw";i:23;s:2:"ax";i:24;s:2:"ay";i:25;s:2:"az";i:26;s:2:"ca";i:27;s:2:"cb";i:28;s:2:"cc";i:29;s:2:"cd";i:30;s:2:"ce";i:31;s:2:"cf";i:32;s:2:"cg";i:33;s:2:"ch";i:34;s:2:"ci";i:35;s:2:"cj";i:36;s:2:"ck";i:37;s:2:"cl";i:38;s:2:"cm";i:39;s:2:"cn";i:40;s:2:"co";i:41;s:2:"cp";i:42;s:2:"cq";i:43;s:2:"cr";i:44;s:2:"cs";i:45;s:2:"ct";i:46;s:2:"cu";i:47;s:2:"cv";i:48;s:2:"cw";i:49;s:2:"cx";i:50;s:2:"cy";i:51;s:2:"cz";i:52;s:1:"a";i:53;s:1:"b";i:54;s:1:"c";i:55;s:1:"d";i:56;s:1:"e";i:57;s:1:"f";i:58;s:1:"g";i:59;s:1:"h";i:60;s:1:"i";i:61;s:1:"j";i:62;s:1:"k";i:63;s:1:"l";i:64;s:1:"m";i:65;s:1:"n";i:66;s:1:"o";i:67;s:1:"p";i:68;s:1:"q";i:69;s:1:"r";i:70;s:1:"s";i:71;s:1:"t";i:72;s:1:"u";i:73;s:1:"v";i:74;s:1:"w";i:75;s:1:"x";i:76;s:1:"y";i:77;s:1:"z";}');
320: 
321:         $sqlTemplate = "SELECT cal.idart, cal.online, aa.idartlang, cat.idcat, aa.idpica_alloc FROM {TABLES} WHERE {WHERE} ";
322: 
323:         $tables = array();
324:         $where = array();
325: 
326:         for ($i = 0; $i < $size; $i++) {
327:             if ($i == 0) { // first
328:                 $tables[] = " " . $cfg['tab']['pica_alloc_con'] . " AS " . $sql_concat[$i];
329:             } else {
330:                 $tables[] = " LEFT JOIN " . $cfg['tab']['pica_alloc_con'] . " AS " . $sql_concat[$i] . " USING (idartlang)";
331:             }
332:             if (is_int((int) $contentAllocation[$i]) && $contentAllocation[$i] > 0) {
333:                 $where[] = $sql_concat[$i] . ".idpica_alloc = " . $contentAllocation[$i];
334:             }
335:         }
336: 
337:         // fetch only articles which are online
338:         $where[] = 'cal.online = 1';
339: 
340:         // fetch only articles in following categories
341:         if (count($categories) > 0) {
342:             $where[] = "cat.idcat IN (" . implode(',', $categories) . ")";
343:         }
344: 
345:         // join art_lang for idart
346:         $tables[] = " LEFT JOIN " . $this->_table['art_lang'] . " AS cal USING (idartlang)";
347:         $tables[] = " LEFT JOIN " . $this->_table['cat_art'] . " AS cart USING (idart)";
348:         $tables[] = " LEFT JOIN " . $this->_table['cat'] . " as cat USING (idcat)";
349: 
350:         $tables = implode('', $tables);
351:         $where = implode(' AND ', $where);
352: 
353:         $sql = str_replace('{TABLES}', $tables, $sqlTemplate);
354:         $sql = str_replace('{WHERE}', $where, $sql);
355: 
356:         $sql .= " ORDER BY cal.published DESC";
357: 
358:         if (is_integer($numOfRows) && $numOfRows > 0) {
359:             $sql .= " LIMIT " . $offset . ", " . $numOfRows;
360:         }
361: 
362:         if ($this->_debug) {
363:             print "<!-- ";
364:             print $sql;
365:             print " -->";
366:         }
367: 
368:         return $sql;
369:     }
370: 
371:     /**
372:      * Search articles by catgories without start articles
373:      *
374:      * @param array $categories [optional]
375:      * @param int $offset [optional]
376:      * @param int $numOfRows [optional]
377:      * @param string $resultType element of {article_id, object} [optional]
378:      *
379:      * @return array of articles
380:      */
381:     public function findMatchingContentByCategories($categories = array(), $offset = 0, $numOfRows = 0, $resultType = '') {
382:         for ($i = 0; $i < count($categories); $i++) {
383:             if (!is_int((int) $categories[$i]) || !$categories[$i] > 0) {
384:                 return array();
385:             }
386:         }
387: 
388:         $sql = $this->_buildQuery_MatchingContentByCategories($categories, $offset, $numOfRows);
389:         $this->_db->query($sql);
390:         $result = array();
391: 
392:         while (false !== $row = $this->_db->getResultObject()) {
393:             if ($resultType == 'article_language_id') {
394:                 $result[] = $row->idartlang;
395:             } else {
396:                 $result[] = $row;
397:             }
398:         }
399: 
400:         return $result;
401:     }
402: 
403:     /**
404:      * Build SQL query to find articles by catgories
405:      *
406:      * @param array $categories
407:      * @param integer offset
408:      * @param integer numOfRows
409:      * @return string $sql
410:      */
411:     public function _buildQuery_MatchingContentByCategories($categories, $offset, $numOfRows) {
412: 
413:         if (count($categories) > 0) {
414:             $whereCategoryIN = " c.idcat IN (" . implode(',', $categories) . ") AND ";
415:         } else {
416:             $whereCategoryIN = '';
417:         }
418: 
419:         if (is_integer($numOfRows) and $numOfRows > 0) {
420:             $limit = " LIMIT " . cSecurity::toInteger($offset) . ", " . cSecurity::toInteger($numOfRows);
421:         } else {
422:             $limit = '';
423:         }
424: 
425:         $sql = '
426:         SELECT
427:             a.idart, a.online, a.idartlang, c.idcat
428:         FROM
429:             ' . $this->_table['art_lang'] . ' AS a,
430:             ' . $this->_table['art'] . ' AS b,
431:             ' . $this->_table['cat_art'] . ' AS c,
432:             ' . $this->_table['cat_lang'] . ' AS d
433:         WHERE
434:             ' . $whereCategoryIN . '
435:             b.idclient = ' . cSecurity::toInteger($this->_client) . ' AND
436:             a.idlang = ' . cSecurity::toInteger($this->_lang) . ' AND
437:             a.idartlang != d.startidartlang AND
438:             a.online = 1 AND
439:             c.idcat = d.idcat AND
440:             b.idart = c.idart AND
441:             a.idart = b.idart
442:             ' . $limit . ' ';
443: 
444:         if ($this->_debug) {
445:             print "<!-- ";
446:             print $sql;
447:             print " -->";
448:         }
449: 
450:         return $sql;
451:     }
452: 
453: }
454: ?>