1: <?php
  2: /**
  3:  * This file contains the main class for the plugin content allocation.
  4:  *
  5:  * @package Plugin
  6:  * @subpackage ContentAllocation
  7:  * @version SVN Revision $Rev:$
  8:  *
  9:  * @author Marco Jahn
 10:  * @copyright four for business AG <www.4fb.de>
 11:  * @license http://www.contenido.org/license/LIZENZ.txt
 12:  * @link http://www.4fb.de
 13:  * @link http://www.contenido.org
 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:     var $db = NULL;
 33: 
 34:     var $table = NULL;
 35: 
 36:     var $lang = NULL;
 37: 
 38:     var $client = NULL;
 39: 
 40:     var $treeObj = NULL;
 41: 
 42:     /**
 43:      * @modified 27.10.2005 new class variable $this->bDebug (if true print
 44:      * debug information)
 45:      */
 46:     function pApiContentAllocation() {
 47:         global $db, $cfg, $lang, $client;
 48: 
 49:         $this->db = cRegistry::getDb();
 50:         $this->table = $cfg['tab'];
 51:         $this->lang = $lang;
 52:         $this->client = $client;
 53: 
 54:         // use this option carefully and only temporary.
 55:         // the hidden debug output as html-comments can cause display problems.
 56:         $this->bDebug = false;
 57: 
 58:         $this->treeObj = new pApiTree('f31a4384-e5c1-4ede-b1bb-f43657ec73a5');
 59:     }
 60: 
 61:     function storeAllocations($idartlang, $allocations) {
 62:         // empty before insert
 63:         $this->deleteAllocationsByIdartlang($idartlang);
 64: 
 65:         if (is_array($allocations)) {
 66:             foreach ($allocations as $value) {
 67:                 $sql = "INSERT INTO " . $this->table['pica_alloc_con'] . " (idpica_alloc, idartlang) VALUES (" . cSecurity::toInteger($value) . ", " . cSecurity::toInteger($idartlang) . ")";
 68:                 $this->db->query($sql);
 69:             }
 70:         }
 71:     }
 72: 
 73:     function deleteAllocations($idpica_alloc) {
 74:         $sql = "DELETE FROM " . $this->table['pica_alloc_con'] . " WHERE idpica_alloc = " . cSecurity::toInteger($idpica_alloc);
 75:         $this->db->query($sql);
 76:     }
 77: 
 78:     function deleteAllocationsByIdartlang($idartlang) {
 79:         $sql = "DELETE FROM " . $this->table['pica_alloc_con'] . " WHERE idartlang = " . cSecurity::toInteger($idartlang);
 80:         $this->db->query($sql);
 81:     }
 82: 
 83:     /**
 84:      * load all tagging keys
 85:      *
 86:      * @param int $idartlang
 87:      * @return array
 88:      */
 89:     function loadAllocations($idartlang) {
 90:         $this->db->query("-- pApiContentAllocation->loadAllocations()
 91:             SELECT
 92:                 a.idpica_alloc
 93:             FROM
 94:                 `{$this->table['pica_alloc']}` AS a
 95:                 , `{$this->table['pica_alloc_con']}` AS b
 96:             WHERE
 97:                 idartlang = $idartlang
 98:                 AND a.idpica_alloc=b.idpica_alloc
 99:             ;");
100: 
101:         $result = array();
102:         while ($this->db->nextRecord()) {
103:             $result[] = $this->db->f('idpica_alloc');
104:         }
105: 
106:         return $result;
107:     }
108: 
109:     function loadAllocationsWithNames($idartlang, $parent, $firstonly = false) {
110:         global $cfg;
111: 
112:         $sql = "SELECT " . $cfg['tab']['pica_alloc'] . ".idpica_alloc FROM " . $cfg['tab']['pica_alloc'] . "
113:                     INNER JOIN " . $cfg['tab']['pica_alloc_con'] . " ON
114:                     " . $cfg['tab']['pica_alloc'] . ".idpica_alloc = " . $cfg['tab']['pica_alloc_con'] . ".idpica_alloc
115:                     WHERE (" . $cfg['tab']['pica_alloc'] . ".parentid = " . cSecurity::toInteger($parent) . ") AND (" . $cfg['tab']['pica_alloc_con'] . ".idartlang=" . cSecurity::toInteger($idartlang) . ")
116:                     ORDER BY " . $cfg['tab']['pica_alloc'] . ".sortorder";
117: 
118:         $this->db->query($sql);
119: 
120:         while ($this->db->nextRecord()) {
121:             $tmp[$this->db->f("idpica_alloc")] = $this->treeObj->_fetchItemNameLang($this->db->f("idpica_alloc"));
122: 
123:             if ($firstonly) {
124:                 break;
125:             }
126:         }
127: 
128:         return $tmp;
129:     }
130: 
131:     /**
132:      * Build query to find matching content by ContentAllocation
133:      *
134:      * @param array $restrictions
135:      * @return string SQL
136:      *         @modified 17.11.2005 by Willi Man
137:      */
138:     function findMatchingContent($restrictions = NULL, $max = 0) {
139:         if (!is_array($restrictions)) {
140:             return false;
141:         }
142: 
143:         global $aCategoriesToExclude; // @see config.local.php!
144:         $sql = $this->_buildQuery($restrictions, $aCategoriesToExclude, $max);
145: 
146:         return $sql;
147:     }
148: 
149:     /**
150:      * Build query to find matching content by ContentAllocation
151:      *
152:      * @param array $restrictions
153:      * @return string SQL
154:      */
155:     function _buildQuery($restrictions, $aCategoriesToExclude, $max) {
156:         global $cfg;
157: 
158:         $size = sizeof($restrictions);
159: 
160:         if ($size == 0) {
161:             return '';
162:         }
163: 
164:         $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";}');
165: 
166:         $sqlTemplate = "SELECT cal.idart, cal.online, aa.idartlang, cat.idcat FROM {TABLES} WHERE {WHERE} ";
167: 
168:         $tables = array();
169:         $where = array();
170: 
171:         for ($i = 0; $i < $size; $i++) {
172:             if ($i == 0) { // first
173:                 $tables[] = " " . $cfg['tab']['pica_alloc_con'] . " AS " . $sql_concat[$i];
174:             } else {
175:                 $tables[] = " LEFT JOIN " . $cfg['tab']['pica_alloc_con'] . " AS " . $sql_concat[$i] . " USING (idartlang)";
176:             }
177:             if (is_int((int) $restrictions[$i]) and $restrictions[$i] > 0) {
178:                 $where[] = $sql_concat[$i] . ".idpica_alloc = " . $restrictions[$i];
179:             }
180:         }
181: 
182:         // fetch only articles which are online
183:         $where[] = 'cal.online = 1';
184: 
185:         // fetch only articles which are not in following categories
186:         if (count($aCategoriesToExclude) > 0) {
187:             $where[] = "cat.idcat NOT IN (" . implode(',', $aCategoriesToExclude) . ")";
188:         }
189: 
190:         // join art_lang for idart
191:         $tables[] = " LEFT JOIN " . $this->table['art_lang'] . " AS cal USING (idartlang)";
192:         $tables[] = " LEFT JOIN " . $this->table['cat_art'] . " AS cart USING (idart)";
193:         $tables[] = " LEFT JOIN " . $this->table['cat'] . " as cat USING (idcat)";
194: 
195:         $tables = implode('', $tables);
196:         $where = implode(' AND ', $where);
197: 
198:         $sql = str_replace('{TABLES}', $tables, $sqlTemplate);
199:         $sql = str_replace('{WHERE}', $where, $sql);
200: 
201:         $sql .= " ORDER BY cal.published DESC";
202: 
203:         if ($max != 0 && is_integer($max)) {
204:             $sql .= " LIMIT " . $max;
205:         }
206: 
207:         if ($this->bDebug) {
208:             print "<!-- ";
209:             print $sql;
210:             print " -->";
211:         } // @modified
212:                                                                       // 27.10.2005
213: 
214:         return $sql;
215:     }
216: 
217:     /**
218:      * Search articles by ContentAllocation and catgories
219:      *
220:      * @param array $aContentAllocation
221:      * @param array $aCategories
222:      *
223:      * @return array of articles
224:      */
225:     function findMatchingContentByContentAllocationByCategories($aContentAllocation, $aCategories = array(), $iOffset = 0, $iNumOfRows = 0) {
226:         if (!is_array($aContentAllocation) || count($aContentAllocation) == 0) {
227:             return array();
228:         }
229: 
230:         for ($i = 0; $i < count($aContentAllocation); $i++) {
231:             if (!is_int((int) $aContentAllocation[$i]) or !$aContentAllocation[$i] > 0) {
232:                 return array();
233:             }
234:         }
235: 
236:         for ($i = 0; $i < count($aCategories); $i++) {
237:             if (!is_int((int) $aCategories[$i]) or !$aCategories[$i] > 0) {
238:                 return array();
239:             }
240:         }
241: 
242:         $sql = $this->_buildQuery_MatchingContentByContentAllocationByCategories($aContentAllocation, $aCategories, $iOffset, $iNumOfRows);
243: 
244:         $this->db->query($sql);
245: 
246:         $aResult = array();
247:         while (false !== $oRow = $this->db->getResultObject()) {
248:             $aResult[] = $oRow;
249:         }
250:         return $aResult;
251:     }
252: 
253:     /**
254:      * build SQL query to find articles by ContentAllocation and catgories
255:      */
256:     function _buildQuery_MatchingContentByContentAllocationByCategories($aContentAllocation, $aCategories, $iOffset, $iNumOfRows) {
257:         global $cfg;
258: 
259:         $size = sizeof($aContentAllocation);
260: 
261:         $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";}');
262: 
263:         $sqlTemplate = "SELECT cal.idart, cal.online, aa.idartlang, cat.idcat, aa.idpica_alloc FROM {TABLES} WHERE {WHERE} ";
264: 
265:         $tables = array();
266:         $where = array();
267: 
268:         for ($i = 0; $i < $size; $i++) {
269:             if ($i == 0) { // first
270:                 $tables[] = " " . $cfg['tab']['pica_alloc_con'] . " AS " . $sql_concat[$i];
271:             } else {
272:                 $tables[] = " LEFT JOIN " . $cfg['tab']['pica_alloc_con'] . " AS " . $sql_concat[$i] . " USING (idartlang)";
273:             }
274:             if (is_int((int) $aContentAllocation[$i]) and $aContentAllocation[$i] > 0) {
275:                 $where[] = $sql_concat[$i] . ".idpica_alloc = " . $aContentAllocation[$i];
276:             }
277:         }
278: 
279:         // fetch only articles which are online
280:         $where[] = 'cal.online = 1';
281: 
282:         // fetch only articles in following categories
283:         if (count($aCategories) > 0) {
284:             $where[] = "cat.idcat IN (" . implode(',', $aCategories) . ")";
285:         }
286: 
287:         // join art_lang for idart
288:         $tables[] = " LEFT JOIN " . $this->table['art_lang'] . " AS cal USING (idartlang)";
289:         $tables[] = " LEFT JOIN " . $this->table['cat_art'] . " AS cart USING (idart)";
290:         $tables[] = " LEFT JOIN " . $this->table['cat'] . " as cat USING (idcat)";
291: 
292:         $tables = implode('', $tables);
293:         $where = implode(' AND ', $where);
294: 
295:         $sql = str_replace('{TABLES}', $tables, $sqlTemplate);
296:         $sql = str_replace('{WHERE}', $where, $sql);
297: 
298:         $sql .= " ORDER BY cal.published DESC";
299: 
300:         if (is_integer($iNumOfRows) and $iNumOfRows > 0) {
301:             $sql .= " LIMIT " . $iOffset . ", " . $iNumOfRows;
302:         }
303: 
304:         if ($this->bDebug) {
305:             print "<!-- ";
306:             print $sql;
307:             print " -->";
308:         } // @modified
309:                                                                       // 27.10.2005
310: 
311:         return $sql;
312:     }
313: 
314:     /**
315:      * Search articles by catgories without start articles
316:      *
317:      * @param array $aCategories
318:      * @param int $iOffset
319:      * @param int $iNumOfRows
320:      * @param string $sResultType element of {article_id, object}
321:      *
322:      * @return array of articles
323:      */
324:     function findMatchingContentByCategories($aCategories = array(), $iOffset = 0, $iNumOfRows = 0, $sResultType = '') {
325:         for ($i = 0; $i < count($aCategories); $i++) {
326:             if (!is_int((int) $aCategories[$i]) or !$aCategories[$i] > 0) {
327:                 return array();
328:             }
329:         }
330: 
331:         $sql = $this->_buildQuery_MatchingContentByCategories($aCategories, $iOffset, $iNumOfRows);
332: 
333:         $this->db->query($sql);
334: 
335:         $aResult = array();
336: 
337:         while (false !== $oRow = $this->db->getResultObject()) {
338:             if ($sResultType == 'article_language_id') {
339:                 $aResult[] = $oRow->idartlang;
340:             } else {
341:                 $aResult[] = $oRow;
342:             }
343:         }
344:         return $aResult;
345:     }
346: 
347:     /**
348:      * build SQL query to find articles by catgories
349:      */
350:     function _buildQuery_MatchingContentByCategories($aCategories, $iOffset, $iNumOfRows) {
351:         if (count($aCategories) > 0) {
352:             $sWHERE_Category_IN = " c.idcat IN (" . implode(',', $aCategories) . ") AND ";
353:         } else {
354:             $sWHERE_Category_IN = '';
355:         }
356:         if (is_integer($iNumOfRows) and $iNumOfRows > 0) {
357:             $sLimit = " LIMIT " . cSecurity::toInteger($iOffset) . ", " . cSecurity::toInteger($iNumOfRows);
358:         } else {
359:             $sLimit = '';
360:         }
361: 
362:         $sql = '
363:         SELECT
364:             a.idart, a.online, a.idartlang, c.idcat
365:         FROM
366:             ' . $this->table['art_lang'] . ' AS a,
367:             ' . $this->table['art'] . ' AS b,
368:             ' . $this->table['cat_art'] . ' AS c,
369:             ' . $this->table['cat_lang'] . ' AS d
370:         WHERE
371:             ' . $sWHERE_Category_IN . '
372:             b.idclient = ' . cSecurity::toInteger($this->client) . ' AND
373:             a.idlang = ' . cSecurity::toInteger($this->lang) . ' AND
374:             a.idartlang != d.startidartlang AND
375:             a.online = 1 AND
376:             c.idcat = d.idcat AND
377:             b.idart = c.idart AND
378:             a.idart = b.idart
379:             ' . $sLimit . ' ';
380: 
381:         if ($this->bDebug) {
382:             print "<!-- ";
383:             print $sql;
384:             print " -->";
385:         }
386: 
387:         return $sql;
388:     }
389: 
390: }
391: 
392: ?>