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