MailFiler - allow to sort by subrecords fields
authorPaul Mehrer <p.mehrer@metaways.de>
Mon, 7 Aug 2017 15:59:56 +0000 (17:59 +0200)
committerPaul Mehrer <p.mehrer@metaways.de>
Tue, 8 Aug 2017 14:16:45 +0000 (16:16 +0200)
Change-Id: Id8488e22a44a788b303c5969b21733536317da63
Reviewed-on: http://gerrit.tine20.com/customers/5412
Tested-by: Jenkins CI (http://ci.tine20.com/) <tine20-jenkins@metaways.de>
Reviewed-by: Paul Mehrer <p.mehrer@metaways.de>
Tested-by: Paul Mehrer <p.mehrer@metaways.de>
tests/tine20/Felamimail/Frontend/JsonTest.php
tine20/MailFiler/Controller/Message.php
tine20/MailFiler/Controller/Node.php
tine20/MailFiler/Frontend/Json.php
tine20/MailFiler/Model/Message.php
tine20/MailFiler/Model/Node.php
tine20/MailFiler/Setup/Update/Release11.php [new file with mode: 0644]
tine20/MailFiler/Setup/setup.xml
tine20/Tinebase/Frontend/Json/Abstract.php
tine20/Tinebase/Model/Pagination.php
tine20/Tinebase/Record/Abstract.php

index 416c7a4..253b27a 100644 (file)
@@ -1219,15 +1219,21 @@ class Felamimail_Frontend_JsonTest extends TestCase
             /* $_emailFrom */ '',
             /*$_subject */ 'test\test' // is converted to 'test_test'
         );
+        $message2 = $this->_sendMessage(
+            'INBOX',
+            /* $addtionalHeaders */ array(),
+            /* $_emailFrom */ '',
+            /*$_subject */ 'abctest'
+        );
         $path = '/' . Tinebase_Model_Container::TYPE_PERSONAL
             . '/' . $user->accountLoginName
             . '/' . $personalFilemanagerContainer->name;
         $filter = array(array(
-            'field' => 'id', 'operator' => 'in', 'value' => array($message['id'])
+            'field' => 'id', 'operator' => 'in', 'value' => array($message['id'], $message2['id'])
         ));
         $result = $this->_json->fileMessages($filter, $appName, $path);
         $this->assertTrue(isset($result['totalcount']));
-        $this->assertEquals(1, $result['totalcount'], 'message should be filed in ' . $appName . ': ' . print_r($result, true));
+        $this->assertEquals(2, $result['totalcount'], 'message should be filed in ' . $appName . ': ' . print_r($result, true));
 
         // check if message exists in $appName
         $filter = new Tinebase_Model_Tree_Node_Filter(array(array(
@@ -1282,9 +1288,9 @@ class Felamimail_Frontend_JsonTest extends TestCase
             'value'    => 'test'
         ));
         $mailFilerJson = new MailFiler_Frontend_Json();
-        $emlNodes = $mailFilerJson->searchNodes($filter, array());
-        $this->assertGreaterThan(0, $emlNodes['totalcount'], 'could not find eml file node with subject filter');
-        $emlNode = $emlNodes['results'][0];
+        $emlNodes = $mailFilerJson->searchNodes($filter, array('sort' => 'subject', 'order' => 'ASC'));
+        $this->assertEquals(2, $emlNodes['totalcount'], 'could not find eml file node with subject filter');
+        $emlNode = $emlNodes['results'][1];
 
         // check email fields
         $this->assertTrue(isset($emlNode['message']), 'message not found in node array: ' . print_r($emlNodes['results'], true));
@@ -1292,6 +1298,10 @@ class Felamimail_Frontend_JsonTest extends TestCase
         $this->assertTrue(isset($emlNode['message']['structure']) && is_array($emlNode['message']['structure']), 'structure not found or not an array: ' . print_r($emlNode['message'], true));
         $this->assertTrue(isset($emlNode['message']['body']) && is_string($emlNode['message']['body']), 'body not found or not a string: ' . print_r($emlNode['message'], true));
         $this->assertContains('aaaaaä', $emlNode['message']['body'], print_r($emlNode['message'], true));
+
+        $emlNodesDesc = $mailFilerJson->searchNodes($filter, array('sort' => 'subject', 'order' => 'DESC'));
+        $this->assertEquals(2, $emlNodesDesc['totalcount'], 'could not find eml file node with subject filter');
+        $this->assertEquals($emlNode['id'], $emlNodesDesc['results'][1]['id'], 'sort did not work');
     }
 
     /**
index c78a394..12b2e4d 100644 (file)
@@ -79,7 +79,7 @@ class MailFiler_Controller_Message extends Felamimail_Controller_Message
      * @param Tinebase_Model_Filter_FilterGroup $_filter
      * @param string                            $_action get|update
      */
-    public function checkFilterACL(Tinebase_Model_Filter_FilterGroup $_filter, $_action = 'get')
+    public function checkFilterACL(Tinebase_Model_Filter_FilterGroup $_filter = null, $_action = 'get')
     {
         // TODO acl - we already checked acl on the nodes and this has no public api
     }
index bdd14a9..95914ac 100644 (file)
@@ -155,6 +155,9 @@ class MailFiler_Controller_Node extends Filemanager_Controller_Node
         $mailFilerMessage->setFromJsonInUsersTimezone($message->toArray());
         $mailFilerMessage->structure = $message->structure;
         $mailFilerMessage->node_id = $node->getId();
+        $to = $mailFilerMessage->to;
+        sort($to);
+        $mailFilerMessage->to_flat = join(', ', $to);
 
         MailFiler_Controller_Message::getInstance()->create($mailFilerMessage);
     }
index 2c2f8b6..fb5a5cc 100644 (file)
@@ -36,10 +36,16 @@ class MailFiler_Frontend_Json extends Tinebase_Frontend_Json_Abstract
      */
     public function searchNodes($filter, $paging)
     {
-        $result = $this->_search($filter, $paging, MailFiler_Controller_Node::getInstance(), 'MailFiler_Model_NodeFilter');
-        $this->_removeAppIdFromPathFilter($result);
-        
-        return $result;
+        $this->_paginationModel = 'MailFiler_Model_Node';
+        try {
+            $result = $this->_search($filter, $paging, MailFiler_Controller_Node::getInstance(),
+                'MailFiler_Model_NodeFilter');
+            $this->_removeAppIdFromPathFilter($result);
+
+            return $result;
+        } finally {
+            $this->_paginationModel = null;
+        }
     }
     
     /**
index dd3471d..68e861f 100644 (file)
  * @property    string  $sender         the sender of the email
  * @property    string  $content_type   the content type of the message
  * @property    string  $body_content_type   the content type of the message body
- * @property    array   $to             the to receipients
- * @property    array   $cc             the cc receipients
- * @property    array   $bcc            the bcc receipients
+ * @property    array   $to             the to recipients
+ * @property    string  $to_flat        the to recipients as one string, comma separated email list
+ * @property    array   $cc             the cc recipients
+ * @property    array   $bcc            the bcc recipients
  * @property    array   $structure      the message structure
  * @property    array   $attachments    the attachments
  * @property    string  $messageuid     the message uid on the imap server
@@ -50,6 +51,7 @@ class MailFiler_Model_Message extends Felamimail_Model_Message
         'from_name'             => array(Zend_Filter_Input::ALLOW_EMPTY => true),
         'sender'                => array(Zend_Filter_Input::ALLOW_EMPTY => true),
         'to'                    => array(Zend_Filter_Input::ALLOW_EMPTY => true),
+        'to_flat'               => array(Zend_Filter_Input::ALLOW_EMPTY => true),
         'cc'                    => array(Zend_Filter_Input::ALLOW_EMPTY => true),
         'bcc'                   => array(Zend_Filter_Input::ALLOW_EMPTY => true),
         'received'              => array(Zend_Filter_Input::ALLOW_EMPTY => true),
index 0e96174..c40531b 100644 (file)
@@ -5,8 +5,8 @@
  * @package     MailFiler
  * @subpackage  Model
  * @license     http://www.gnu.org/licenses/agpl.html AGPL Version 3
- * @author      AirMike <airmike23@gmail.com>
- * @copyright   Copyright (c) 2012 Metaways Infosystems GmbH (http://www.metaways.de)
+ * @author      Philipp Schüle <p.schuele@metaways.de>
+ * @copyright   Copyright (c) 2017 Metaways Infosystems GmbH (http://www.metaways.de)
  */
 
 /**
@@ -32,6 +32,33 @@ class MailFiler_Model_Node extends Tinebase_Model_Tree_Node
      */
     protected $_application = 'MailFiler';
 
+    protected static $_sortExternalMapping = array(
+        'subject'       => array(
+            'table'         => 'mailfiler_message',
+            'on'            => 'tree_nodes.id = mailfiler_message.node_id'
+        ),
+        'from_email'    => array(
+            'table'         => 'mailfiler_message',
+            'on'            => 'tree_nodes.id = mailfiler_message.node_id'
+        ),
+        'from_name'     => array(
+            'table'         => 'mailfiler_message',
+            'on'            => 'tree_nodes.id = mailfiler_message.node_id'
+        ),
+        'sender'        => array(
+            'table'         => 'mailfiler_message',
+            'on'            => 'tree_nodes.id = mailfiler_message.node_id'
+        ),
+        'received'      => array(
+            'table'         => 'mailfiler_message',
+            'on'            => 'tree_nodes.id = mailfiler_message.node_id'
+        ),
+        'sent'          => array(
+            'table'         => 'mailfiler_message',
+            'on'            => 'tree_nodes.id = mailfiler_message.node_id'
+        ),
+    );
+
     /**
      * overwrite constructor to add more filters
      *
diff --git a/tine20/MailFiler/Setup/Update/Release11.php b/tine20/MailFiler/Setup/Update/Release11.php
new file mode 100644 (file)
index 0000000..0297849
--- /dev/null
@@ -0,0 +1,47 @@
+<?php
+/**
+ * Tine 2.0
+ *
+ * @package     MailFiler
+ * @subpackage  Setup
+ * @license     http://www.gnu.org/licenses/agpl.html AGPL3
+ * @copyright   Copyright (c) 2017 Metaways Infosystems GmbH (http://www.metaways.de)
+ * @author      Paul Mehrer <p.mehrer@metaways.de>
+ */
+class MailFiler_Setup_Update_Release11 extends Setup_Update_Abstract
+{
+    /**
+     * update to 11.1
+     *
+     * @return void
+     */
+    public function update_0()
+    {
+        if (!$this->_backend->columnExists('to_flat', 'mailfiler_message')) {
+            $this->_backend->addCol('mailfiler_message',
+                new Setup_Backend_Schema_Field_Xml('<field>
+                    <name>to_flat</name>
+                    <type>text</type>
+                </field>')
+            );
+            $this->setTableVersion('mailfiler_message', 2);
+        }
+
+        $mailFilerMessageController = MailFiler_Controller_Message::getInstance();
+        $pagination = new Tinebase_Model_Pagination(array('start' => 0, 'limit' => 1000, 'sort' => 'id'));
+        do {
+            $result = $mailFilerMessageController->search(null, $pagination);
+            $pagination->start += 1000;
+
+            /** @var MailFiler_Model_Message $message */
+            foreach ($result as $message) {
+                $to = $message->to;
+                sort($to);
+                $message->to_flat = join(', ', $to);
+                $mailFilerMessageController->update($message);
+            }
+        } while ($result->count() > 0);
+
+        $this->setApplicationVersion('MailFiler', '11.1');
+    }
+}
index 8b00324..06741c5 100644 (file)
@@ -1,7 +1,7 @@
 <?xml version="1.0" encoding="utf-8"?>
 <application>
     <name>MailFiler</name>
-    <version>11.0</version>
+    <version>11.1</version>
     <order>12</order>
     <depends>
         <application>Felamimail</application>
         </table>
         <table>
             <name>mailfiler_message</name>
-            <version>1</version>
+            <version>2</version>
             <declaration>
                 <field>
                     <name>id</name>
                     <name>timestamp</name>
                     <type>datetime</type>
                 </field>
+                <field>
+                    <name>to_flat</name>
+                    <type>text</type>
+                </field>
                 <index>
                     <name>id</name>
                     <primary>true</primary>
index 77130fd..026b3a4 100644 (file)
@@ -49,6 +49,13 @@ abstract class Tinebase_Frontend_Json_Abstract extends Tinebase_Frontend_Abstrac
     protected $_defaultImportDefinitionName = null;
 
     /**
+     * the model from which the pagination object should read its configuration
+     *
+     * @var string
+     */
+    protected $_paginationModel = null;
+
+    /**
      * Configured plugins for filter model
      * @var array
      */
@@ -187,6 +194,11 @@ abstract class Tinebase_Frontend_Json_Abstract extends Tinebase_Frontend_Abstrac
     protected function _search($_filter, $_paging, Tinebase_Controller_SearchInterface $_controller, $_filterModel, $_getRelations = FALSE, $_totalCountMethod = self::TOTALCOUNT_CONTROLLER)
     {
         $decodedPagination = $this->_prepareParameter($_paging);
+        if (null !== $this->_paginationModel) {
+            $decodedPagination['model'] = $this->_paginationModel;
+        } else {
+            unset($decodedPagination['model']);
+        }
         $pagination = new Tinebase_Model_Pagination($decodedPagination);
         $filter = $this->_decodeFilter($_filter, $_filterModel);
         
index a0502ea..240bbf4 100644 (file)
@@ -5,7 +5,7 @@
  * @package     Tinebase
  * @license     http://www.gnu.org/licenses/agpl.html AGPL3
  * @author      Cornelius Weiss <c.weiss@metaways.de>
- * @copyright   Copyright (c) 2008-2016 Metaways Infosystems GmbH (http://www.metaways.de)
+ * @copyright   Copyright (c) 2008-2017 Metaways Infosystems GmbH (http://www.metaways.de)
  *
  */
 
@@ -18,6 +18,7 @@
  * @property string limit
  * @property string sort
  * @property string dir
+ * @property string model
  */
 class Tinebase_Model_Pagination extends Tinebase_Record_Abstract
 {
@@ -48,7 +49,9 @@ class Tinebase_Model_Pagination extends Tinebase_Record_Abstract
         'dir'                  => array('presence'      => 'required',
                                         'allowEmpty'    => false,
                                         array('InArray', array('ASC', 'DESC')),
-                                        'default'       => 'ASC'        )
+                                        'default'       => 'ASC'        ),
+        'model'                => array('allowEmpty'    => true,
+                                        'default'       => NULL         ),
     );
     
     /**
@@ -59,19 +62,54 @@ class Tinebase_Model_Pagination extends Tinebase_Record_Abstract
      */
     public function appendPaginationSql($_select)
     {
+        // check model for required joins etc.
+        $this->appendModelConfig($_select);
+
         $this->appendLimit($_select);
         $this->appendSort($_select);
     }
-    
+
+    /**
+     * Appends limit statement to a given select object
+     *
+     * @param  Zend_Db_Select $_select
+     * @return void
+     */
+    public function appendModelConfig($_select)
+    {
+        if (empty($this->model) || empty($this->sort) || empty($this->dir)) {
+            return;
+        }
+
+        /** @var Tinebase_Record_Abstract $model */
+        $model = $this->model;
+        if (empty($mapping = $model::getSortExternalMapping())) {
+            return;
+        }
+        $joined = array();
+        foreach ((array)$this->sort as $field) {
+            if (!isset($mapping[$field])) {
+                continue;
+            }
+            $mappingDef = $mapping[$field];
+            if (isset($joined[$mappingDef['table']])) {
+                continue;
+            }
+            $_select->joinLeft(array($mappingDef['table'] => SQL_TABLE_PREFIX . $mappingDef['table']), $mappingDef['on'],
+                array());
+            $joined[$mappingDef['table']] = true;
+        }
+    }
+
     /**
      * Appends limit statement to a given select object
      * 
-     * @param  Zend_Db_Select
+     * @param  Zend_Db_Select $_select
      * @return void
      */
     public function appendLimit($_select)
     {
-        if (! empty($this->limit)) {
+        if (!empty($this->limit)) {
             $start = ($this->start >= 0) ? $this->start : 0;
             $_select->limit($this->limit, $start);
         }
@@ -80,7 +118,7 @@ class Tinebase_Model_Pagination extends Tinebase_Record_Abstract
     /**
      * Appends sort statement to a given select object
      * 
-     * @param  Zend_Db_Select
+     * @param  Zend_Db_Select $_select
      * @return void
      */
     public function appendSort($_select)
@@ -97,15 +135,10 @@ class Tinebase_Model_Pagination extends Tinebase_Record_Abstract
      */
     protected function _getSortCols()
     {
-        if (is_array($this->sort)) {
-            $order = array();
-            foreach ($this->sort as $sort) {
-                $order[] = $sort . ' ' . $this->dir;
-            }
-        } else {
-            $order = array($this->sort . ' ' . $this->dir);
+        $order = array();
+        foreach ((array)$this->sort as $sort) {
+            $order[] = $sort . ' ' . $this->dir;
         }
-        
         return $order;
     }
 }
index bf91c85..9ae05b3 100644 (file)
@@ -215,6 +215,8 @@ abstract class Tinebase_Record_Abstract implements Tinebase_Record_Interface
      */
     protected static $_requiredRight = NULL;
 
+    protected static $_sortExternalMapping = array();
+
 
     /******************************** functions ****************************************/
     
@@ -1481,4 +1483,9 @@ abstract class Tinebase_Record_Abstract implements Tinebase_Record_Interface
     {
         return null;
     }
+
+    public static function getSortExternalMapping()
+    {
+        return static::$_sortExternalMapping;
+    }
 }