<?php
/**
 * The model file of upgrade module of ZDOO.
 *
 * @copyright   Copyright 2009-2018 青岛易软天创网络科技有限公司(QingDao Nature Easy Soft Network Technology Co,LTD, www.cnezsoft.com)
 * @license     ZPL (http://zpl.pub/page/zplv12.html)
 * @author      Chunsheng Wang <chunsheng@cnezsoft.com>
 * @package     upgrade
 * @version     $Id: model.php 4227 2016-10-25 08:27:56Z liugang $
 * @link        http://www.zdoo.com
 */
?>
<?php
class upgradeModel extends model
{
    /**
     * Errors.
     * 
     * @static
     * @var array 
     * @access public
     */
    static $errors = array();

    /**
     * The execute method. According to the $fromVersion call related methods.
     * 
     * @param  string $fromVersion 
     * @access public
     * @return void
     */
    public function execute($fromVersion)
    {
        $result = array();
    
        /* Delete useless file.*/
        foreach($this->config->delete as $deleteFiles)
        {
            $basePath = $this->app->getBasePath();
            foreach($deleteFiles as $file)
            {
                $fullPath = $basePath . str_replace('/', DIRECTORY_SEPARATOR, $file);
                if(is_dir($fullPath)  and !rmdir($fullPath))  $result[] = sprintf($this->lang->upgrade->deleteDir, $fullPath);
                if(is_file($fullPath) and !unlink($fullPath)) $result[] = sprintf($this->lang->upgrade->deleteFile, $fullPath);
            }
        }
        if(!empty($result)) return array('' => $this->lang->upgrade->deleteTips) + $result;
    
        switch($fromVersion)
        {
            case '1_0_beta':
                $this->execSQL($this->getUpgradeFile('1.0.beta'));
                $this->createCashEntry();
            case '1_1_beta':
                $this->execSQL($this->getUpgradeFile('1.1.beta'));
                $this->createTeamEntry();
            case '1_2_beta':
                $this->execSQL($this->getUpgradeFile('1.2.beta'));
                $this->transformBlock();
                $this->changeBuildinName();
                $this->computeContactInfo();
            case '1_3_beta':
                $this->execSQL($this->getUpgradeFile('1.3.beta'));
                $this->setCompanyContent();
            case '1_4_beta':
                $this->upgradeContractName();
                $this->upgradeProjectMember();
                $this->safeDropColumns(TABLE_PROJECT, 'master,member');

                /* Exec sqls must after fix data. */
                $this->execSQL($this->getUpgradeFile('1.4.beta'));
            case '1_5_beta':
                $this->execSQL($this->getUpgradeFile('1.5.beta'));
                $this->upgradeEntryLogo();
                $this->upgradeReturnRecords();
                $this->upgradeDeliveryRecords();
                $this->addSearchPriv();
            case '1_6':
                $this->execSQL($this->getUpgradeFile('1.6'));
                $this->addPrivs();
            case '1_7':
                $this->toLowerTable();
                $this->updateAppOrder();
            case '2_0':
                $this->execSQL($this->getUpgradeFile('2.0'));
                $this->fixClosedTask();
                $this->setSalesGroup();
                $this->fixOrderProduct();
            case '2_1':
                $this->execSQL($this->getUpgradeFile('2.1'));
            case '2_2':
                $this->processTradeDesc();
            case '2_3':
                $this->processCustomerEditedDate();
                $this->execSQL($this->getUpgradeFile('2.3'));
            case '2_4':
                $this->addAttendPriv();
                $this->execSQL($this->getUpgradeFile('2.4'));
            case '2_5':
                $this->execSQL($this->getUpgradeFile('2.5'));
            case '2_6':
                $this->execSQL($this->getUpgradeFile('2.6'));
            case '2_7':
                $this->processBlockType();
                $this->execSQL($this->getUpgradeFile('2.7'));
            case '3_0':
                $this->execSQL($this->getUpgradeFile('3.0'));
            case '3_1':
                $this->processStatusForContact();
                $this->execSQL($this->getUpgradeFile('3.1'));
            case '3_2':
                $this->execSQL($this->getUpgradeFile('3.2'));
            case '3_2_1':
                $this->execSQL($this->getUpgradeFile('3.2.1'));
            case '3_3':
                $this->execSQL($this->getUpgradeFile('3.3'));
            case '3_4':
                $this->execSQL($this->getUpgradeFile('3.4'));
                $this->updateTradeCategories();
            case '3_5':
                $this->execSQL($this->getUpgradeFile('3.5'));
                $this->setSystemCategories();
                $this->setSalesAdminPrivileges();
            case '3_6':
                $this->execSQL($this->getUpgradeFile('3.6'));
            case '3_7':
                $this->execSQL($this->getUpgradeFile('3.7'));
                $this->updateDocPrivileges();
                $this->moveDocContent();
                $this->addProjectDoc();
            case '4_0': 
                $this->addProjPrivilege(); 
            case '4_1': 
                $this->execSQL($this->getUpgradeFile('4.1'));
                $this->updateMakeupActions();
            case '4_2': 
                $this->execSQL($this->getUpgradeFile('4.2'));
            case '4_2_1': 
                $this->execSQL($this->getUpgradeFile('4.2.1'));
            case '4_2_2':
            case '4_2_3':
            case '4_3_beta': 
                $this->execSQL($this->getUpgradeFile('4.3.beta'));
            case '4_4': 
                $this->processContractAddress();
                $this->execSQL($this->getUpgradeFile('4.4'));
            case '4_5': 
                $this->execSQL($this->getUpgradeFile('4.5'));
                $this->renameLastCategory();
            case '4_6': 
                $this->execSQL($this->getUpgradeFile('4.6'));
                $this->upgradeProductLine();
            case '4_6_1':
                $this->execSQL($this->getUpgradeFile('4.6.1'));
                $this->processDating();
            case '4_6_2':
                $this->execSQL($this->getUpgradeFile('4.6.2'));
            case '4_6_3':
                $this->execSQL($this->getUpgradeFile('4.6.3'));
            case '4_7':
                $this->execSQL($this->getUpgradeFile('4.7'));
            case '4_8':
            case '4_9':
                $this->execSQL($this->getUpgradeFile('4.9'));
                $this->updateContractProduct();
            case '5_0':
                $this->execSQL($this->getUpgradeFile('5.0'));
            case '5_1':
                $this->execSQL($this->getUpgradeFile('5.1'));
                $this->processTeam();
                $this->processContractHandlers();
                $this->processCustomerNextdate();
                $this->processTradesNoCurrency();
            case '5_2':
                $this->execSQL($this->getUpgradeFile('5.2'));
                $this->initActionReader();
                $this->processTransferTrades();
                $this->processEntry();
            case '6_0':
                $this->execSQL($this->getUpgradeFile('6.0'));
                $this->processRefundTrip();
                $this->addRateFieldInTeam();
            case '6_0_1':
                $this->execSQL($this->getUpgradeFile('6.0.1'));
            case '6_1':
            case '6_2':
                $this->execSQL($this->getUpgradeFile('6.2'));
            case '6_3':
                $this->execSQL($this->getUpgradeFile('6.3'));
                $this->updateTradeCurrency();
            case '6_4':
                $this->execSQL($this->getUpgradeFile('6.4'));
            case '6_5':
                $this->execSQL($this->getUpgradeFile('6.5'));
            case '6_6':
                $this->execSQL($this->getUpgradeFile('6.6'));
            case '6_7':
            case '6_8':
                $this->execSQL($this->getUpgradeFile('6.8'));
                $this->processKanbanData();
            case '6_9':
                $this->execSQL($this->getUpgradeFile('6.9'));
                $this->processKanbanDataAgain();
            case '6_9_1':
                $this->execSQL($this->getUpgradeFile('6.9.1'));
            case '7_0':
                $this->execSQL($this->getUpgradeFile('7.0'));
                $this->processAnnualLeaves();
                $this->processToLieu();
                $this->processToMakeup();
            case '7_1':
                $this->execSQL($this->getUpgradeFile('7.1'));
                $this->processGiftImage();
            case '7_2':
                $this->execSQL($this->getUpgradeFile('7.2'));
                $this->processGift();
            case '7_3':
                $this->execSQL($this->getUpgradeFile('7.3'));
                $this->processGiftAction();
                $this->processLateAndEarly();
            case '7_4':
                $this->execSQL($this->getUpgradeFile('7.4'));
            case '7_5':
                $this->execSQL($this->getUpgradeFile('7.5'));
            case '7_5_1':
                $this->execSQL($this->getUpgradeFile('7.5.1'));
            case '7_5_2':
                $this->execSQL($this->getUpgradeFile('7.5.2'));
                $this->convertAnnualLeaves();
            case '7_6':
            case '7_6_1':
                $this->removeFutureTodo();
                $this->upgradeSavedQuery();
            case '8_0':
                $this->execSQL($this->getUpgradeFile('8.0'));
            case '8_1':
                $this->execSQL($this->getUpgradeFile('8.1'));
                $this->precessPaymentIntention();
                $this->precessHealth();
                $this->processRenewalIntention();
                $this->processTryPlan();
            case '8_2':
                $this->execSQL($this->getUpgradeFile('8.2'));
            case '8_3':
                $this->processExpect();
                $this->execSQL($this->getUpgradeFile('8.3'));
            case '8_4':
                $this->execSQL($this->getUpgradeFile('8.4'));
                $this->processTripStatus();
            default: if(!$this->isError()) $this->loadModel('setting')->updateVersion($this->config->version);
        }

        $this->deletePatch();
    }

    /**
     * Create the confirm contents.
     * 
     * @param  string $fromVersion 
     * @access public
     * @return string
     */
    public function getConfirm($fromVersion)
    {
        $confirmContent = '';
        switch($fromVersion)
        {
            case '1_0_beta': $confirmContent .= file_get_contents($this->getUpgradeFile('1.0.beta'));
            case '1_1_beta': $confirmContent .= file_get_contents($this->getUpgradeFile('1.1.beta'));
            case '1_2_beta': $confirmContent .= file_get_contents($this->getUpgradeFile('1.2.beta'));
            case '1_3_beta': $confirmContent .= file_get_contents($this->getUpgradeFile('1.3.beta'));
            case '1_4_beta': $confirmContent .= file_get_contents($this->getUpgradeFile('1.4.beta'));
            case '1_5_beta': $confirmContent .= file_get_contents($this->getUpgradeFile('1.5.beta'));
            case '1_6'     : $confirmContent .= file_get_contents($this->getUpgradeFile('1.6'));
            case '2_0'     : $confirmContent .= file_get_contents($this->getUpgradeFile('2.0'));
            case '2_1'     : $confirmContent .= file_get_contents($this->getUpgradeFile('2.1'));
            case '2_3'     : $confirmContent .= file_get_contents($this->getUpgradeFile('2.3'));
            case '2_4'     : $confirmContent .= file_get_contents($this->getUpgradeFile('2.4'));
            case '2_5'     : $confirmContent .= file_get_contents($this->getUpgradeFile('2.5'));
            case '2_6'     : $confirmContent .= file_get_contents($this->getUpgradeFile('2.6'));
            case '2_7'     : $confirmContent .= file_get_contents($this->getUpgradeFile('2.7'));
            case '3_0'     : $confirmContent .= file_get_contents($this->getUpgradeFile('3.0'));
            case '3_1'     : $confirmContent .= file_get_contents($this->getUpgradeFile('3.1'));
            case '3_2'     : $confirmContent .= file_get_contents($this->getUpgradeFile('3.2'));
            case '3_2_1'   : $confirmContent .= file_get_contents($this->getUpgradeFile('3.2.1'));
            case '3_3'     : $confirmContent .= file_get_contents($this->getUpgradeFile('3.3'));
            case '3_4'     : $confirmContent .= file_get_contents($this->getUpgradeFile('3.4'));
            case '3_5'     : $confirmContent .= file_get_contents($this->getUpgradeFile('3.5'));
            case '3_6'     : $confirmContent .= file_get_contents($this->getUpgradeFile('3.6'));
            case '3_7'     : $confirmContent .= file_get_contents($this->getUpgradeFile('3.7'));
            case '4_1'     : $confirmContent .= file_get_contents($this->getUpgradeFile('4.1'));
            case '4_2'     : $confirmContent .= file_get_contents($this->getUpgradeFile('4.2'));
            case '4_2_1'   : $confirmContent .= file_get_contents($this->getUpgradeFile('4.2.1'));
            case '4_2_2'   :
            case '4_2_3'   :
            case '4_3_beta': $confirmContent .= file_get_contents($this->getUpgradeFile('4.3.beta'));
            case '4_4'     : $confirmContent .= file_get_contents($this->getUpgradeFile('4.4'));
            case '4_5'     : $confirmContent .= file_get_contents($this->getUpgradeFile('4.5'));
            case '4_6'     : $confirmContent .= file_get_contents($this->getUpgradeFile('4.6'));
            case '4_6_1'   : $confirmContent .= file_get_contents($this->getUpgradeFile('4.6.1'));
            case '4_6_2'   : $confirmContent .= file_get_contents($this->getUpgradeFile('4.6.2'));
            case '4_6_3'   : $confirmContent .= file_get_contents($this->getUpgradeFile('4.6.3'));
            case '4_7'     : $confirmContent .= file_get_contents($this->getUpgradeFile('4.7'));
            case '4_8'     :
            case '4_9'     : $confirmContent .= file_get_contents($this->getUpgradeFile('4.9'));
            case '5_0'     : $confirmContent .= file_get_contents($this->getUpgradeFile('5.0'));
            case '5_1'     : $confirmContent .= file_get_contents($this->getUpgradeFile('5.1'));
            case '5_2'     : $confirmContent .= file_get_contents($this->getUpgradeFile('5.2'));
            case '6_0'     : $confirmContent .= file_get_contents($this->getUpgradeFile('6.0'));
            case '6_0_1'   : $confirmContent .= file_get_contents($this->getUpgradeFile('6.0.1'));
            case '6_1'     :
            case '6_2'     : $confirmContent .= file_get_contents($this->getUpgradeFile('6.2'));
            case '6_3'     : $confirmContent .= file_get_contents($this->getUpgradeFile('6.3'));
            case '6_4'     : $confirmContent .= file_get_contents($this->getUpgradeFile('6.4'));
            case '6_5'     : $confirmContent .= file_get_contents($this->getUpgradeFile('6.5'));
            case '6_6'     : $confirmContent .= file_get_contents($this->getUpgradeFile('6.6'));
            case '6_7'     :
            case '6_8'     : $confirmContent .= file_get_contents($this->getUpgradeFile('6.8'));
            case '6_9'     : $confirmContent .= file_get_contents($this->getUpgradeFile('6.9'));
            case '6_9_1'   : $confirmContent .= file_get_contents($this->getUpgradeFile('6.9.1'));
            case '7_0'     : $confirmContent .= file_get_contents($this->getUpgradeFile('7.0'));
            case '7_1'     : $confirmContent .= file_get_contents($this->getUpgradeFile('7.1'));
            case '7_2'     : $confirmContent .= file_get_contents($this->getUpgradeFile('7.2'));
            case '7_3'     : $confirmContent .= file_get_contents($this->getUpgradeFile('7.3'));
            case '7_4'     : $confirmContent .= file_get_contents($this->getUpgradeFile('7.4'));
            case '7_5'     : $confirmContent .= file_get_contents($this->getUpgradeFile('7.5'));
            case '7_5_1'   : $confirmContent .= file_get_contents($this->getUpgradeFile('7.5.1'));
            case '7_5_2'   : $confirmContent .= file_get_contents($this->getUpgradeFile('7.5.2'));
            case '7_6'     :
            case '7_6_1'   :
            case '8_0'     : $confirmContent .= file_get_contents($this->getUpgradeFile('8.0'));
            case '8_1'     : $confirmContent .= file_get_contents($this->getUpgradeFile('8.1'));
            case '8_2'     : $confirmContent .= file_get_contents($this->getUpgradeFile('8.2'));
            case '8_3'     : $confirmContent .= file_get_contents($this->getUpgradeFile('8.3'));
            case '8_4'     : $confirmContent .= file_get_contents($this->getUpgradeFile('8.4'));
        }
        return $confirmContent;
    }

    /**
     * Delete the patch record.
     * 
     * @access public
     * @return void
     */
    public function deletePatch()
    {
        return true;
        $this->dao->delete()->from(TABLE_EXTENSION)->where('type')->eq('patch')->exec();
    }

    /**
     * Get the upgrade sql file.
     * 
     * @param  string $version 
     * @access public
     * @return string
     */
    public function getUpgradeFile($version)
    {
        return $this->app->getBasepath() . 'db' . DS . 'upgrade' . $version . '.sql';
    }

    /**
     * Execute a sql.
     * 
     * @param  string  $sqlFile 
     * @access public
     * @return void
     */
    public function execSQL($sqlFile)
    {
        $mysqlVersion = $this->loadModel('install')->getMysqlVersion();
        $ignoreCode   = '|1050|1060|1062|1091|1169|';

        /* Read the sql file to lines, remove the comment lines, then join theme by ';'. */
        $sqls = explode("\n", file_get_contents($sqlFile));
        foreach($sqls as $key => $line) 
        {
            $line       = trim($line);
            $sqls[$key] = $line;
            if(strpos($line, '--') !== false or empty($line)) unset($sqls[$key]);
        }
        $sqls = explode(";\n", join("\n", $sqls));

        foreach($sqls as $sql)
        {
            $sql = trim($sql);
            if(empty($sql)) continue;

            if($mysqlVersion <= 4.1)
            {
                $sql = str_replace('DEFAULT CHARSET=utf8', '', $sql);
                $sql = str_replace('CHARACTER SET utf8 COLLATE utf8_general_ci', '', $sql);
            }

            /* Add table prefix. */
            if($this->config->db->prefix) $sql = preg_replace('/`(\w+)_/', "`{$this->config->db->prefix}\${1}_", $sql);

            try
            {
                $this->dbh->exec($sql);
            }
            catch (PDOException $e) 
            {
                $errorInfo = $e->errorInfo;
                $errorCode = $errorInfo[1];
                if(strpos($ignoreCode, "|$errorCode|") === false) self::$errors[] = $e->getMessage() . "<p>The sql is: $sql</p>";
            }
        }
    }

    /**
     * Judge any error occers.
     * 
     * @access public
     * @return bool
     */
    public function isError()
    {
        return !empty(self::$errors);
    }

    /**
     * Get errors during the upgrading.
     * 
     * @access public
     * @return array
     */
    public function getError()
    {
        $errors = self::$errors;
        self::$errors = array();
        return $errors;
    }

    /**
     * create cash entry.
     * 
     * @access public
     * @return bool
     */
    public function createCashEntry()
    {
        $entry = new stdclass();

        $entry->name     = 'cash';
        $entry->code     = 'cash';
        $entry->open     = 'iframe';
        $entry->order    = 2;
        $entry->ip       = '*';
        $entry->key      = '438d85f2c2b04372662c63ebfb1c4c2f';
        $entry->logo     = $this->config->webRoot . 'theme/default/images/ips/app-cash.png';
        $entry->login    = '../cash';
        $entry->ip       = '*';
        $entry->control  = 'simple';
        $entry->visible  = 1;
        $entry->size     = 'max';
        $entry->position = 'default';

        $block = REQUESTTYPE == 'GET' ? 'cash/index.php?m=block&f=index' : 'cash/block-index.html';
        $entry->block = $this->config->webRoot . $block;

        $this->dao->insert(TABLE_ENTRY)->data($entry)->exec();

        return !dao::isError();
    }

    /**
     * create team entry.
     * 
     * @access public
     * @return bool
     */
    public function createTeamEntry()
    {
        $entry = new stdclass();

        $entry->name     = 'team';
        $entry->code     = 'team';
        $entry->open     = 'iframe';
        $entry->order    = 4;
        $entry->ip       = '*';
        $entry->key      = '6c46d9fe76a1afa1cd61f946f1072d1e';
        $entry->logo     = $this->config->webRoot . 'theme/default/images/ips/app-team.png';
        $entry->login    = '../team';
        $entry->ip       = '*';
        $entry->control  = 'simple';
        $entry->size     = 'max';
        $entry->position = 'default';

        $block = REQUESTTYPE == 'GET' ? 'team/index.php?m=block&f=index' : 'team/block-index.html';
        $entry->block = $this->config->webRoot . $block;

        $this->dao->insert(TABLE_ENTRY)->data($entry)->exec();

        return !dao::isError();
    }

    /**
     * Transform block from config to block table.
     * 
     * @access public
     * @return bool
     */
    public function transformBlock()
    {
        $blocks  = $this->dao->select('*')->from(TABLE_CONFIG)->where('section')->eq('block')->andWhere('module')->eq('index')->fetchAll('id');
        $entries = $this->dao->select('id,code')->from(TABLE_ENTRY)->fetchPairs('id', 'code');

        foreach($blocks as $block)
        {
            if(empty($block->owner)) continue;
            $block->value = json_decode($block->value);

            $source  = '';
            $blockID = $block->value->type;
            if($block->value->type == 'system')
            {
                if($block->app == 'sys' and isset($block->value->entryID) and !isset($entries[$block->value->entryID])) continue;
                $source  = $block->app == 'sys' ? $entries[$block->value->entryID] : $block->app;
                $blockID = $block->value->blockID;
            }

            if($blockID == 'html') $block->value->params = helper::jsonEncode(array('html' => $block->value->html));
            if(!isset($block->value->params)) $block->value->params = array();

            $data = new stdclass();
            $data->account = $block->owner;
            $data->app     = $block->app;
            $data->title   = $block->value->name;
            $data->source  = $source;
            $data->block   = $blockID;
            $data->params  = helper::jsonEncode($block->value->params);
            $data->order   = str_replace('b', '', $block->key);
            $data->grid    = 3;

            $this->dao->replace(TABLE_BLOCK)->data($data)->exec();
        }
        if(dao::isError()) return false;

        $this->dao->delete()->from(TABLE_CONFIG)->where('section')->eq('block')->andWhere('module')->eq('index')->exec();
        return true;
    }

    /**
     * Change buildin entry name.
     * 
     * @access public
     * @return bool
     */
    public function changeBuildinName()
    {
        $this->app->loadLang('install');

        foreach($this->lang->install->buildinEntry as $code => $name)
        {
            $this->dao->update(TABLE_ENTRY)
                ->set('name')->eq($name['name'])
                ->set('abbr')->eq($name['abbr'])
                ->set('buildin')->eq(1)
                ->set('integration')->eq(1)
                ->set('visible')->eq(1)
                ->where('code')->eq($code)
                ->exec();
        }

        if(dao::isError()) return false;
        return true;
    }

    /**
     * Compute contacteddate and contactedby fields.
     * 
     * @access public
     * @return bool
     */
    public function computeContactInfo()
    {
        $orders    = $this->dao->select('id')->from(TABLE_ORDER)->fetchAll();
        $customers = $this->dao->select('id')->from(TABLE_CUSTOMER)->fetchAll();
        $contracts = $this->dao->select('id')->from(TABLE_CONTRACT)->fetchAll();
        $contacts  = $this->dao->select('id')->from(TABLE_CONTACT)->fetchAll();

        foreach($orders as $order)
        {
            $lastContact = $this->dao->select('actor as contactedBy, date as contactedDate')->from(TABLE_ACTION)
                ->where('action')->eq('record')
                ->andWhere('objectType')->eq('order')
                ->andWhere('objectID')->eq($order->id)
                ->orderBY('date_desc')
                ->limit(1)
                ->fetch();
            if($lastContact) $this->dao->update(TABLE_ORDER)->data($lastContact)->where('id')->eq($order->id)->exec();
        }

        foreach($customers as $customer)
        {
            $lastContact = $this->dao->select('actor as contactedBy, date as contactedDate')->from(TABLE_ACTION)
                ->where('action')->eq('record')
                ->andWhere('customer')->eq($customer->id)
                ->orderBY('date_desc')
                ->limit(1)
                ->fetch();
            if($lastContact) $this->dao->update(TABLE_CUSTOMER)->data($lastContact)->where('id')->eq($customer->id)->exec();
        }

        foreach($contacts as $contact)
        {
            $lastContact = $this->dao->select('actor as contactedBy, date as contactedDate')->from(TABLE_ACTION)
                ->where('action')->eq('record')
                ->andWhere('contact')->eq($contact->id)
                ->orderBY('date_desc')
                ->limit(1)
                ->fetch();
            if($lastContact) $this->dao->update(TABLE_CONTACT)->data($lastContact)->where('id')->eq($contact->id)->exec();
        }

        foreach($contracts as $contract)
        {
            $lastContact = $this->dao->select('actor as contactedBy, date as contactedDate')->from(TABLE_ACTION)
                ->where('action')->eq('record')
                ->andWhere('objectType')->eq('contract')
                ->andWhere('objectID')->eq($contract->id)
                ->orderBY('date_desc')
                ->limit(1)
                ->fetch();
            if($lastContact) $this->dao->update(TABLE_CONTRACT)->data($lastContact)->where('id')->eq($contract->id)->exec();
        }

        return !dao::isError();

    }

    /**
     * Set content of company when upgrade from 1.3.beta.
     * 
     * @access public
     * @return bool
     */
    public function setCompanyContent()
    {
        if(empty($this->config->company->content) and $this->config->company->desc)
        {
            $this->dao->update(TABLE_CONFIG)->set('value')->eq($this->config->company->desc)->where('`key`')->eq('content')->andWhere('section')->eq('company')->exec();
            $this->dao->delete()->from(TABLE_CONFIG)->where('`key`')->eq('desc')->andWhere('section')->eq('company')->exec();
        }
        return !dao::isError();
    }

    /**
     * Set name of contract when upgrade from 1.4.beta.
     * 
     * @access public
     * @return bool
     */
    public function upgradeContractName()
    {
        $contracts = $this->dao->select('*')->from(TABLE_CONTRACT)->fetchAll();

        foreach($contracts as $contract)
        {
            $name = preg_replace('/^\[(\d+)\]/', '', $contract->name);
            $this->dao->update(TABLE_CONTRACT)->set('name')->eq($name)->where('id')->eq($contract->id)->exec();
        }

        return !dao::isError();
    }

    /**
     * Update project member.
     * 
     * @access public
     * @return bool
     */
    public function upgradeProjectMember()
    {
        $projects = $this->dao->select('*')->from(TABLE_PROJECT)->fetchAll('id');
        foreach($projects as $project)
        {
            $member = new stdclass();
            $member->type = 'project';
            $member->id   = $project->id;
 
            /* Move master to team table. */
            if(!empty($project->master))
            {
                $member->account = $project->master;
                $member->role    = 'role';
                $this->dao->replace(TABLE_TEAM)->data($member)->exec();
            }

            /* Move members to team table. */
            if(!empty($project->member))
            {
                $members = explode(',', $project->member);
                $member->role = 'member';
                foreach($members as $account)
                {
                    if($account == $project->master) continue;
                    if(!validater::checkAccount($account)) continue;

                    $member->account = $account;
                    $this->dao->replace(TABLE_TEAM)->data($member)->exec();
                }
            }

            return true;
        }
    }

    /**
     * Change system application logo path to relative path.
     * 
     * @access public
     * @return bool
     */
    public function upgradeEntryLogo()
    {
        $entryList = array('crm', 'cash', 'oa', 'team');
        foreach($entryList as $entry)
        {
            $entryObj = $this->dao->select('*')->from(TABLE_ENTRY)->where('code')->eq($entry)->fetch();
            $path     = substr($entryObj->logo, strpos($entryObj->logo, 'theme'));
            $this->dao->update(TABLE_ENTRY)->set('logo')->eq($path)->where('code')->eq($entry)->exec();
        }
        return !dao::isError();
    }

    /**
     * Update return records.
     * 
     * @access public
     * @return bool
     */
    public function upgradeReturnRecords()
    {
        $contracts = $this->dao->select('*')->from(TABLE_CONTRACT)->where('`return`')->eq('done')->fetchAll();
        if(empty($contracts)) return false;

        foreach($contracts as $contract)
        {
            $data = new stdclass();
            $data->contract     = $contract->id;
            $data->amount       = $contract->amount;
            $data->returnedBy   = $contract->returnedBy;
            $data->returnedDate = $contract->returnedDate;

            $this->dao->insert(TABLE_PLAN)->data($data)->autoCheck()->exec();
        }

        return !dao::isError();
    }

    /**
     * Update delivery records.
     * 
     * @access public
     * @return bool
     */
    public function upgradeDeliveryRecords()
    {
        $contracts = $this->dao->select('*')->from(TABLE_CONTRACT)->where('`delivery`')->eq('done')->fetchAll();
        if(empty($contracts)) return false;

        foreach($contracts as $contract)
        {
            $data = new stdclass();
            $data->contract      = $contract->id;
            $data->deliveredBy   = $contract->deliveredBy;
            $data->deliveredDate = $contract->deliveredDate;

            $this->dao->insert(TABLE_DELIVERY)->data($data)->autoCheck()->exec();
        }

        return !dao::isError();
    }

    /**
     * Add search priv when upgrade 1.5.beta.
     * 
     * @access public
     * @return bool
     */
    public function addSearchPriv()
    {
        $groups = $this->dao->select('id')->from(TABLE_GROUP)->fetchAll('id');
        foreach($groups as $group)
        {
            $priv = new stdclass();
            $priv->group  = $group->id;
            $priv->module = 'search';
            $priv->method = 'buildForm';
            $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();

            $priv->method = 'buildQuery';
            $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();

            $priv->method = 'saveQuery';
            $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();

            $priv->method = 'deleteQuery';
            $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();
        }

        return !dao::isError();
    }

    /**
     * Safe drop columns.
     * 
     * @param string $table 
     * @param string $columns 
     * @access public
     * @return bool
     */
    public function safeDropColumns($table, $columns)
    {
        if($columns == '') return false;

        $fieldsOBJ = $this->dao->query('desc ' . TABLE_PROJECT);
        while($field = $fieldsOBJ->fetch())
        {
            $fields[$field->Field] = $field->Field;
        }

        $columns = explode(',', $columns);
        foreach($columns as $column)
        {
            if($column == '') continue;
            if(isset($fields[$column]))
            {
                $this->dao->query("ALTER TABLE $table DROP $column;");
            }
        }

        return true;
    }

    /**
     * Add app priv when upgrade from 1.6.
     * 
     * @access public
     * @return bool
     */
    public function addPrivs()
    {
        $groups = $this->dao->select('id')->from(TABLE_GROUP)->fetchAll('id');

        foreach($groups as $group)
        {
            if($group->id == 1)
            {
                $privs = array('balance', 'depositor', 'order', 'product', 'project', 'schema', 'setting', 'task', 'trade');

                $modules['balance']   = array('browse', 'create', 'delete', 'edit');
                $modules['depositor'] = array('activate', 'browse', 'check', 'create', 'delete', 'edit', 'forbid', 'savebalance');
                $modules['order']     = array('delete');
                $modules['product']   = array('view');
                $modules['project']   = array('activate', 'suspend');
                $modules['schema']    = array('browse', 'create', 'delete', 'edit', 'view');
                $modules['setting']   = array('lang', 'reset');
                $modules['task']      = array('kanban', 'outline', 'start');
                $modules['trade']     = array('batchCreate', 'batchEdit', 'browse', 'create', 'delete', 'detail', 'edit', 'import', 'showimport', 'transfer');

                foreach($privs as $module)
                {
                    $priv = new stdclass();
                    $priv->group  = 1;
                    $priv->module = $module;

                    foreach($modules[$module] as $method)
                    {
                        $priv->method = $method;
                        $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();
                    }
                }
            }

            if($group->id == 2)
            {
                $priv = new stdclass();
                $priv->group  = 2;
                $priv->module = 'depositor';
                $priv->method = 'delete';
                $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();

                $priv->method = 'savabalance';
                $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();
            }

            if($group->id == 3)
            {
                $priv = new stdclass();
                $priv->group  = 3;
                $priv->module = 'project';

                $methods = array('activate', 'finish', 'index', 'suspend');
                foreach($methods as $method)
                {
                    $priv->method = $method;
                    $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();
                }
            }

            $priv = new stdclass();
            $priv->group  = $group->id;
            $priv->module = 'apppriv';
            $priv->method = 'crm';
            $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();

            $priv->method = 'cash';
            $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();

            $priv->method = 'oa';
            $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();

            $priv->method = 'team';
            $this->dao->replace('`sys_groupPriv`')->data($priv)->exec();
        }

        return !dao::isError();
    }

    /**
     * To lower table.
     * 
     * @access public
     * @return bool
     */
    public function toLowerTable()
    {
        $results    = $this->dbh->query("show Variables like '%table_names'")->fetchAll();
        $hasLowered = false;
        foreach($results as $result)
        {
            if(strtolower($result->Variable_name) == 'lower_case_table_names' and $result->Value == 1)
            {
                $hasLowered = true;
                break;
            }
        }
        if($hasLowered) return true;

        $tables2Rename = $this->config->upgrade->lowerTables;
        if(!isset($tables2Rename)) return false;

        $tablesExists = $this->dbh->query('SHOW TABLES')->fetchAll();
        foreach($tablesExists as $key => $table) $tablesExists[$key] = current((array)$table);
        $tablesExists = array_flip($tablesExists);

        foreach($tables2Rename as $oldTable => $newTable)
        {
            if(!isset($tablesExists[$oldTable])) continue;
            
            $upgradebak = $newTable . '_othertablebak';
            if(isset($tablesExists[$upgradebak])) $this->dbh->query("DROP TABLE `$upgradebak`");
            if(isset($tablesExists[$newTable])) $this->dbh->query("RENAME TABLE `$newTable` TO `$upgradebak`");

            $tempTable = $oldTable . '_ranzhitmp';
            $this->dbh->query("RENAME TABLE `$oldTable` TO `$tempTable`");
            $this->dbh->query("RENAME TABLE `$tempTable` TO `$newTable`");
        }

        return true;
    }

    /**
     * Update app orders.
     * 
     * @access public
     * @return bool
     */
    public function updateAppOrder()
    {
        $entries = $this->dao->select('*')->from(TABLE_ENTRY)->orderBy('`order`, id')->fetchAll();
        $order   = 10;
        foreach($entries as $entry)
        {
            $this->dao->update(TABLE_ENTRY)->set('`order`')->eq($order)->where('id')->eq($entry->id)->exec();
            $order += 10;
        }
        return !dao::isError();
    }

    /**
     * Set assignedTo is closed if the task is closed when upgrade from 2.0.
     * 
     * @access public
     * @return bool
     */
    public function fixClosedTask()
    {
        $this->dao->update(TABLE_TASK)->set('assignedTo')->eq('closed')->where('status')->eq('closed')->exec();

        return !dao::isError();
    }

    /**
     * Set default salesGroup when upgrade from 2.0.
     * 
     * @access public
     * @return bool
     */
    public function setSalesGroup()
    {
        $sales = $this->dao->select('DISTINCT createdBy')->from(TABLE_CUSTOMER)->fetchPairs();

        $manageAllUsers = $this->dao->select('t1.account, t1.group, t2.group, t2.method')
            ->from(TABLE_USERGROUP)->alias('t1')
            ->leftJoin(TABLE_GROUPPRIV)->alias('t2')->on('t1.group=t2.group')
            ->where('t2.method')->eq('manageAll')
            ->fetchAll();

        if(!empty($manageAllUsers))
        {
            foreach($manageAllUsers as $manageAllUser)
            {
                if(isset($sales[$manageAllUser->account])) continue;
                $sales[$manageAllUser->account] = $manageAllUser->account;
            }
        }

        $users = ',' . implode(',', $sales) . ',';

        $group = new stdclass(); 
        $group->name  = '销售人员';
        $group->desc  = '';
        $group->users = $users;

        $this->dao->insert(TABLE_SALESGROUP)->data($group)->exec();

        $groupID = $this->dao->lastInsertID();

        if(!empty($manageAllUsers))
        {
            foreach($manageAllUsers as $manageAllUser)
            {
                $data['salesgroup'] = $groupID;
                $data['account']    = $manageAllUser->account;
                $data['priv']       = 'view';
                $this->dao->insert(TABLE_SALESPRIV)->data($data)->exec();

                $data['priv'] = 'edit';
                $this->dao->insert(TABLE_SALESPRIV)->data($data)->exec();
            }
        }

        $this->dao->delete()->from(TABLE_GROUPPRIV)->where('method')->eq('manageAll')->exec();

        return !dao::isError();
    }

    /**
     * Format product for order when upgrade from 2.0.
     * 
     * @access public
     * @return bool
     */
    public function fixOrderProduct()
    {
        $orders = $this->dao->select('*')->from(TABLE_ORDER)->fetchAll();

        foreach($orders as $order) 
        {
            $this->dao->update(TABLE_ORDER)->set('product')->eq(',' . $order->product . ',')->where('id')->eq($order->id)->exec();
        }

        return !dao::isError();
    }

    /**
     * Process desc of trade when upgrade from 2.2.
     * 
     * @access public
     * @return bool
     */
    public function processTradeDesc()
    {
        $trades = $this->dao->select('id, `desc`')->from(TABLE_TRADE)->fetchPairs();

        foreach($trades as $id => $trade)
        {
            $desc = strip_tags(htmlspecialchars_decode($trade));
            $this->dao->update(TABLE_TRADE)->set('desc')->eq($desc)->where('id')->eq($id)->exec();
        }

        return !dao::isError();
    }

    /**
     * Process customer edited date when upgrade from 2.3.
     * 
     * @access public
     * @return bool
     */
    public function processCustomerEditedDate()
    {
        $customers = $this->dao->select('*')->from(TABLE_CUSTOMER)->fetchAll('id');
        foreach($customers as $customer)
        {
            $editedDate = $customer->editedDate;
            $this->app->loadLang('order', 'crm');
            $orders = $this->dao->select('*')->from(TABLE_ORDER)->where('customer')->eq($customer->id)->fetchAll('id');
            foreach($orders as $order) 
            {
                if(!empty($order) and strtotime($order->editedDate) > strtotime($editedDate))  $editedDate = $order->editedDate;
                if(!empty($order) and strtotime($order->createdDate) > strtotime($editedDate)) $editedDate = $order->createdDate;
            }

            $contracts = $this->dao->select('*')->from(TABLE_CONTRACT)->where('customer')->eq($customer->id)->fetchAll('id');
            foreach($contracts as $contract) 
            {
                if(!empty($contract) and strtotime($contract->editedDate) > strtotime($editedDate))  $editedDate = $contract->editedDate;
                if(!empty($contract) and strtotime($contract->createdDate) > strtotime($editedDate)) $editedDate = $contract->createdDate;
            }

            $this->app->loadLang('contact', 'crm');
            $this->app->loadModuleConfig('contact', 'crm');
            $contacts = $this->dao->select('*')->from(TABLE_CONTACT)->where('customer')->eq($customer->id)->fetchAll('id');
            foreach($contacts as $contact) 
            {
                if(!empty($contact) and strtotime($contact->editedDate) > strtotime($editedDate))  $editedDate = $contact->editedDate;
                if(!empty($contact) and strtotime($contact->createdDate) > strtotime($editedDate)) $editedDate = $contact->createdDate;
            }

            if($editedDate != $customer->editedDate) $this->dao->update(TABLE_CUSTOMER)->set('editedDate')->eq($editedDate)->where('id')->eq($customer->id)->exec();
        }
        return !dao::isError();
    }

    /**
     * Add attend holiday leave trip privilages. when upgrade from 2.4.
     * 
     * @access public
     * @return bool
     */
    public function addAttendPriv()
    {
        $groups = $this->dao->select('id')->from(TABLE_GROUP)->fetchAll('id');
        $privs = array();
        $privs['attend']['personal'] = 'personal';
        $privs['attend']['edit']     = 'edit';
        $privs['leave']['personal']  = 'personal';
        $privs['leave']['create']    = 'create';
        $privs['leave']['edit']      = 'edit';
        $privs['leave']['delete']    = 'delete';
        $privs['trip']['personal']   = 'personal';
        $privs['trip']['create']     = 'create';
        $privs['trip']['edit']       = 'edit';
        $privs['trip']['delete']     = 'delete';
        foreach($groups as $group)
        {
            $priv = new stdclass();
            $priv->group  = $group->id;
            foreach($privs as $module => $modulePriv)
            {
                $priv->module = $module;
                foreach($modulePriv as $method => $methodPriv)
                {
                    $priv->method = $method;
                    $this->dao->replace(TABLE_GROUPPRIV)->data($priv)->exec();
                }
            }
        }

        return !dao::isError();
    }

    /**
     * Process block type.
     * 
     * @access public
     * @return bool
     */
    public function processBlockType()
    {
        $blocksHasType = 'order,contract,customer,task,project,thread';
        $blocks = $this->dao->select('*')->from(TABLE_BLOCK)->where('block')->in($blocksHasType)->fetchAll();
        foreach($blocks as $block)
        {
            $block->params = json_decode($block->params);
            if($block->block == 'project')
            {
                if(!isset($block->params->status))
                {
                    $block->params->status = 'doing';
                    $params = helper::jsonEncode($block->params);
                    $this->dao->update(TABLE_BLOCK)->set('params')->eq($params)->where('id')->eq($block->id)->exec();
                }
            }
            else
            {
                if(!isset($block->params->type))
                {
                    if($block->block == 'order')    $block->params->type = 'assignedTo';
                    if($block->block == 'contract') $block->params->type = 'returnedBy';
                    if($block->block == 'customer') $block->params->type = 'today';
                    if($block->block == 'task')     $block->params->type = 'assignedTo';
                    if($block->block == 'thread')   $block->params->type = 'new';

                    $params = helper::jsonEncode($block->params);
                    $this->dao->update(TABLE_BLOCK)->set('params')->eq($params)->where('id')->eq($block->id)->exec();
                }
            }
        }

        return !dao::isError();
    }

    /**
     * Remove old todo module files. 
     * 
     * @access public
     * @return bool
     */
    public function removeOldTodoFile()
    {
        $dir = $this->app->getBasePath() . "app/oa/todo/";
        if(!file_exists($dir)) return true;
        return $this->app->loadClass('zfile')->removeDir($dir);
    }

    /**
     * Process status for contact when upgrade from 3.1.
     * 
     * @access public
     * @return bool
     */
    public function processStatusForContact()
    {
        $contactList = $this->dao->select('*')->from(TABLE_CONTACT)->fetchAll('id');
        foreach($contactList as $id => $contact)
        {
            $this->dao->update(TABLE_CONTACT)->set('status')->eq('normal')->where('id')->eq($id)->exec();
        }

        return !dao::isError();
    }

    /**
     * Update trade categories.
     * 
     * @access public
     * @return bool
     */
    public function updateTradeCategories() 
    {
        $this->app->loadLang('tree');

        $majorIncomeCategories = $this->dao->select('*')->from(TABLE_CATEGORY)
            ->where('major')->eq('1')
            ->andWhere('type')->eq('in')
            ->andWhere('grade')->eq('1')
            ->fetchAll();

        $majorExpenseCategories = $this->dao->select('*')->from(TABLE_CATEGORY)
            ->where('major')->eq('1')
            ->andWhere('type')->eq('out')
            ->andWhere('grade')->eq('1')
            ->fetchAll();

        $this->dao->update(TABLE_CATEGORY)->set('major')->eq(0)->where('type')->in('in,out')->andWhere('grade')->ne('1')->exec();

        foreach($this->lang->upgrade->majorList['3_5'] as $key => $major)
        {
            $data = new stdclass();
            $data->name  = $major;
            $data->major = $key;
            $data->type  = $key < 3 ? 'in' : 'out';
            $data->grade = '1';

            $this->dao->insert(TABLE_CATEGORY)->data($data)->exec();
            $newCategoryID = $this->dao->lastInsertID();
            $this->dao->update(TABLE_CATEGORY)->set('path')->eq(',' . $newCategoryID . ',')->where('id')->eq($newCategoryID)->exec();
            
            if($key == '1' or $key == '3')
            {
                $categories = $key == '1' ? $majorIncomeCategories : $majorExpenseCategories;
                foreach($categories as $category)
                {
                    $children = $this->dao->select('*')->from(TABLE_CATEGORY)->where('path')->like($category->path . '%')->fetchAll();
                    foreach($children as $child)
                    {
                        $path  = ',' . $newCategoryID . $child->path;
                        $grade = $child->grade + 1;
                        if($grade == 2) $this->dao->update(TABLE_CATEGORY)->set('major')->eq(0)->set('path')->eq($path)->set('grade')->eq($grade)->set('parent')->eq($newCategoryID)->where('id')->eq($child->id)->exec();
                        if($grade != 2) $this->dao->update(TABLE_CATEGORY)->set('major')->eq(0)->set('path')->eq($path)->set('grade')->eq($grade)->where('id')->eq($child->id)->exec();
                    }
                }
            }
        }

        return !dao::isError();
    }

    /**
     * Set system category.
     * 
     * @access public
     * @return bool
     */
    public function setSystemCategories()
    {
        $this->app->loadLang('tree');
        foreach($this->lang->upgrade->majorList['3_6'] as $key => $major)
        {
            if($key < 5) continue;

            $data = new stdclass();
            $data->name  = $major;
            $data->major = $key;
            $data->type  = $key == 5 ? 'in' : 'out';
            $data->grade = '1';

            $this->dao->insert(TABLE_CATEGORY)->data($data)->exec();
            $newCategoryID = $this->dao->lastInsertID();
            $this->dao->update(TABLE_CATEGORY)->set('path')->eq(',' . $newCategoryID . ',')->where('id')->eq($newCategoryID)->exec();

            if($key == 5) $this->dao->update(TABLE_TRADE)->set('category')->eq($newCategoryID)->where('category')->eq('profit')->exec();
            if($key == 6) $this->dao->update(TABLE_TRADE)->set('category')->eq($newCategoryID)->where('category')->eq('loss')->exec();
            if($key == 7) $this->dao->update(TABLE_TRADE)->set('category')->eq($newCategoryID)->where('category')->eq('fee')->exec();
        }

        return !dao::isError();
    }

    /**
     * Set sales admin privileges.
     * 
     * @access public
     * @return bool
     */
    public function setSalesAdminPrivileges()
    {
        $groups = $this->dao->select('`group`')->from(TABLE_GROUPPRIV)->where('module')->eq('sales')->andWhere('method')->eq('browse')->fetchPairs();
        $grouppriv = new stdclass();
        $grouppriv->module = 'sales';
        $grouppriv->method = 'admin';
        foreach($groups as $group)
        {
            $grouppriv->group = $group;
            $this->dao->insert(TABLE_GROUPPRIV)->data($grouppriv)->exec();
        }
        return !dao::isError();
    }

    /**
     * Set doc entry privileges when upgrade from 3.7.
     * 
     * @access public
     * @return bool
     */
    public function updateDocPrivileges()
    {
        $groups = $this->dao->select('`group`')->from(TABLE_GROUPPRIV)->where('module')->eq('doc')->fetchPairs();
        foreach($groups as $group)
        {
            $data = new stdclass();
            $data->group = $group;
            $data->module = 'apppriv';
            $data->method = 'doc';
            $this->dao->replace(TABLE_GROUPPRIV)->data($data)->exec();

            $data->module = 'doc';
            $data->method = 'index';
            $this->dao->replace(TABLE_GROUPPRIV)->data($data)->exec();

            $data->method = 'allLibs';
            $this->dao->replace(TABLE_GROUPPRIV)->data($data)->exec();

            $data->method = 'showFiles';
            $this->dao->replace(TABLE_GROUPPRIV)->data($data)->exec();

            $data->method = 'projectLibs';
            $this->dao->replace(TABLE_GROUPPRIV)->data($data)->exec();

            $data->method = 'sort';
            $this->dao->replace(TABLE_GROUPPRIV)->data($data)->exec();
        }

        return !dao::isError();
    }

    /**
     * Move doc content to table oa_doccontent.
     * 
     * @access public
     * @return bool
     */
    public function moveDocContent()
    {
        $descDoc = $this->dao->query('DESC ' .  TABLE_DOC)->fetchAll();
        $processFields = 0;
        foreach($descDoc as $field)
        {
            if($field->Field == 'content' or $field->Field == 'digest' or $field->Field == 'url') $processFields ++; 
        }
        if($processFields < 3) return true;

        $this->dao->exec('TRUNCATE TABLE ' . TABLE_DOCCONTENT);
        $stmt = $this->dao->select('id,title,digest,content,url')->from(TABLE_DOC)->query();
        $fileGroups = $this->dao->select('id,objectID')->from(TABLE_FILE)->where('objectType')->eq('doc')->fetchGroup('objectID', 'id');
        while($doc = $stmt->fetch())
        {
            $url = empty($doc->url) ? '' : urldecode($doc->url);
            $docContent = new stdclass();
            $docContent->doc      = $doc->id;
            $docContent->title    = $doc->title;
            $docContent->digest   = $doc->digest;
            $docContent->content  = $doc->content;
            $docContent->content .= empty($url) ? '' : $url;
            $docContent->version  = 1;
            $docContent->type     = 'html';
            if(isset($fileGroups[$doc->id])) $docContent->files = join(',', array_keys($fileGroups[$doc->id]));
            $this->dao->insert(TABLE_DOCCONTENT)->data($docContent)->exec();
        }
        $this->dao->exec('ALTER TABLE ' . TABLE_DOC . ' DROP `digest`');
        $this->dao->exec('ALTER TABLE ' . TABLE_DOC . ' DROP `content`');
        $this->dao->exec('ALTER TABLE ' . TABLE_DOC . ' DROP `url`');
        return true;
    }

    /**
     * Add project default doc.
     * 
     * @access public
     * @return bool
     */
    public function addProjectDoc()
    {
        set_time_limit(0);
        $this->app->loadLang('doc', 'doc');

        $allProjectIdList  = $this->dao->select('id,name,whitelist')->from(TABLE_PROJECT)->where('deleted')->eq('0')->fetchAll('id');
        foreach($allProjectIdList as $projectID => $project)
        {
            $this->dao->delete()->from(TABLE_DOCLIB)->where('project')->eq($projectID)->exec();

            $lib = new stdclass();
            $lib->project = $projectID;
            $lib->name    = $this->lang->doc->projectMainLib;
            $lib->main    = 1;
            $lib->private = 0;
            $lib->createdDate = helper::now();

            $teams = $this->dao->select('account')->from(TABLE_TEAM)->where('type')->eq('project')->andWhere('id')->eq($projectID)->fetchPairs('account', 'account');
            $lib->users = join(',', $teams);
            $lib->groups = isset($project->whitelist) ? $project->whitelist : '';
            $this->dao->insert(TABLE_DOCLIB)->data($lib)->exec();
        }

        return !dao::isError();
    }

    /**
     * Add privilege of proj app when upgrade from 4.0.
     * 
     * @access public
     * @return bool
     */
    public function addProjPrivilege()
    {
        $groups = $this->dao->select('*')->from(TABLE_GROUP)->fetchAll();

        $data = new stdclass();
        $data->module = 'apppriv';
        $data->method = 'proj';

        foreach($groups as $group)
        {
            $data->group = $group->id;
            $this->dao->replace(TABLE_GROUPPRIV)->data($data)->exec();
        }

        return !dao::isError();
    }

    /**
     * Update makeup actions. 
     * 
     * @access public
     * @return bool
     */
    public function updateMakeupActions()
    {
        $makeupList = $this->dao->select('id')->from(TABLE_OVERTIME)->where('type')->eq('compensate')->fetchPairs();

        $this->dao->update(TABLE_ACTION)->set('objectType')->eq('makeup')->where('objectType')->eq('overtime')->andWhere('objectID')->in($makeupList)->exec();

        return !dao::isError();
    }

    /**
     * Process addresses of contracts. 
     * 
     * @access public
     * @return bool
     */
    public function processContractAddress()
    {
        $address = new stdclass();
        $address->objectType = 'customer';
    
        $this->app->loadLang('contract', 'crm');
        $contracts = $this->dao->select('*')->from(TABLE_CONTRACT)->where('address')->ne('')->fetchAll();
        foreach($contracts as $contract)
        {
            $address->objectID = $contract->customer;
            $address->title    = $this->lang->contract->address;
            $address->location = $contract->address;

            $this->dao->insert(TABLE_ADDRESS)->data($address)->exec();
            $addressID = $this->dao->lastInsertId();

            $this->dao->update(TABLE_CONTRACT)->set('address')->eq($addressID)->where('id')->eq($contract->id)->exec();
        }
        return !dao::isError();
    }

    /**
     * Rename category to lastCategory for trade settings when upgrade from 4.5.
     * 
     * @access public
     * @return bool
     */
    public function renameLastCategory()
    {
        if(isset($this->config->cash->trade->settings->category))
        {
            $this->dao->update(TABLE_CONFIG)->set('`key`')->eq('lastCategory')
                ->where('app')->eq('cash')
                ->andWhere('module')->eq('trade')
                ->andWhere('section')->eq('settings')
                ->andWhere('`key`')->eq('category')
                ->exec();

            return !dao::isError();
        }

        return true;
    }

    /**
     * Upgrade product line function.
     * 
     * @access public
     * @return bool
     */
    public function upgradeProductLine()
    {
        $fields = $this->dao->query('DESC ' .  TABLE_PRODUCT)->fetchAll();
        $hasCategory = false;
        foreach($fields as $field)
        {
            if($field->Field == 'category') 
            {
                $hasCategory = true;
                break;
            }
        }

        if(!$hasCategory) $this->dbh->exec("ALTER TABLE " . TABLE_PRODUCT  . " CHANGE `line` `category` mediumint(8) UNSIGNED NOT NULL DEFAULT 0 AFTER `id`");

        return true;
    }

    /**
     * Process dating;
     *
     * @access public
     * @return bool
     */
    public function processDating()
    {
        /* Process order's next contact. */
        $orders = $this->dao->select('id, nextDate')->from(TABLE_ORDER)
            ->where('deleted')->eq('0')
            ->andWhere('nextDate')->ne('0000-00-00')
            ->fetchPairs();

        $actions = $this->dao->select('id, objectID, contact, actor, date')->from(TABLE_ACTION)
            ->where('objectType')->eq('order')
            ->andWhere('objectID')->in(array_keys($orders))
            ->andWhere('action')->eq('record')
            ->andWhere('contact')->ne(0)
            ->orderBy('id_desc')
            ->fetchGroup('objectID');

        $dating = new stdclass();
        $dating->objectType = 'order';
        foreach($orders as $order => $nextDate)
        {
            if(!isset($actions[$order])) continue;

            $action = reset($actions[$order]);

            $dating->objectID    = $order;
            $dating->action      = $action->id;
            $dating->contact     = $action->contact;
            $dating->account     = $action->actor;
            $dating->date        = $nextDate;
            $dating->createdBy   = $action->actor;
            $dating->createdDate = $action->date;

            $this->dao->insert(TABLE_DATING)->data($dating)->autoCheck()->exec();
        }

        /* Process contract's next contact. */
        $contracts = $this->dao->select('id, nextDate')->from(TABLE_CONTRACT)
            ->where('deleted')->eq('0')
            ->andWhere('nextDate')->ne('0000-00-00')
            ->fetchPairs();

        $actions = $this->dao->select('id, objectID, contact, actor, date')->from(TABLE_ACTION)
            ->where('objectType')->eq('contract')
            ->andWhere('objectID')->in(array_keys($contracts))
            ->andWhere('action')->eq('record')
            ->andWhere('contact')->ne(0)
            ->orderBy('id_desc')
            ->fetchGroup('objectID');

        $dating = new stdclass();
        $dating->objectType = 'contract';
        foreach($contracts as $contract => $nextDate)
        {
            if(!isset($actions[$contract])) continue;

            $action = reset($actions[$contract]);

            $dating->objectID    = $contract;
            $dating->action      = $action->id;
            $dating->contact     = $action->contact;
            $dating->account     = $action->actor;
            $dating->date        = $nextDate;
            $dating->createdBy   = $action->actor;
            $dating->createdDate = $action->date;

            $this->dao->insert(TABLE_DATING)->data($dating)->autoCheck()->exec();
        }

        /* Process customer's next contact. */
        $customers = $this->dao->select('id, nextDate')->from(TABLE_CUSTOMER)
            ->where('deleted')->eq('0')
            ->andWhere('nextDate')->ne('0000-00-00')
            ->fetchPairs();

        $actions = $this->dao->select('id, customer, contact, actor, date')->from(TABLE_ACTION)
            ->where('objectType')->in('order, contract, customer')
            ->andWhere('customer')->in(array_keys($customers))
            ->andWhere('action')->eq('record')
            ->andWhere('contact')->ne(0)
            ->orderBy('customer,id_desc')
            ->fetchGroup('customer');

        $dating = new stdclass();
        $dating->objectType = 'customer';
        foreach($customers as $customer => $nextDate)
        {
            if(!isset($actions[$customer])) continue;

            $action = reset($actions[$customer]);

            $dating->objectID    = $customer;
            $dating->action      = $action->id;
            $dating->contact     = $action->contact;
            $dating->account     = $action->actor;
            $dating->date        = $nextDate;
            $dating->createdBy   = $action->actor;
            $dating->createdDate = $action->date;

            $this->dao->insert(TABLE_DATING)->data($dating)->autoCheck()->exec();
        }

        /* Process contact's next contact. */
        $contacts = $this->dao->select('id, nextDate')->from(TABLE_CONTACT)
            ->where('deleted')->eq('0')
            ->andWhere('nextDate')->ne('0000-00-00')
            ->fetchPairs();

        $actions = $this->dao->select('id, objectID, contact, actor, date')->from(TABLE_ACTION)
            ->where('objectType')->eq('contact')
            ->andWhere('objectID')->in(array_keys($contacts))
            ->andWhere('action')->eq('record')
            ->andWhere('contact')->ne(0)
            ->orderBy('id_desc')
            ->fetchGroup('objectID');

        $dating = new stdclass();
        $dating->objectType = 'contact';
        foreach($contacts as $contact => $nextDate)
        {
            if(!isset($actions[$contact])) continue;

            $action = reset($actions[$contact]);

            $dating->objectID    = $contact;
            $dating->action      = $action->id;
            $dating->contact     = $action->contact;
            $dating->account     = $action->actor;
            $dating->date        = $nextDate;
            $dating->createdBy   = $action->actor;
            $dating->createdDate = $action->date;

            $this->dao->insert(TABLE_DATING)->data($dating)->autoCheck()->exec();
        }

        return !dao::isError();
    }

    /**
     * Update contract product.
     *
     * @access public
     * @return bool
     */
    public function updateContractProduct()
    {
        $orders         = array();
        $contracts      = array();
        $contractOrders = $this->dao->select('*')->from(TABLE_CONTRACTORDER)->where('contract')->ne(0)->andWhere('`order`')->ne(0)->fetchAll();
        foreach($contractOrders as $contractOrder)
        {
            $orders[$contractOrder->order] = $contractOrder->order;
            $contracts[$contractOrder->contract][$contractOrder->order] = $contractOrder->order;
        }

        $orders = $this->dao->select('id, product')->from(TABLE_ORDER)->where('id')->in($orders)->fetchAll('id');
        foreach($orders as $order) $order->product = explode(',', trim($order->product, ','));

        foreach($contracts as $contract => $contractOrders)
        {
            $product = array();
            foreach($contractOrders as $orderID)
            {
                if(!isset($orders[$orderID])) continue;

                $order   = $orders[$orderID];
                $product = array_merge($product, $order->product);
            }

            if($product)
            {
                $product = ',' . implode(',', $product) . ',';
                $this->dao->update(TABLE_CONTRACT)->set('product')->eq($product)->where('id')->eq($contract)->exec();
            }
        }

        return !dao::isError();
    }

    /**
     * Process team.
     *
     * @access public
     * @return bool
     */
    public function processTeam()
    {
        $hasContribution = false;
        $fields = $this->dbh->query('DESC ' . TABLE_TEAM)->fetchAll();
        foreach($fields as $field)
        {
            if($field->Field == 'contribution') return true;
        }

        try
        {
            $this->dbh->exec('ALTER TABLE ' . TABLE_TEAM . ' CHANGE `rate` `contribution` decimal(6, 2) NOT NULL');
        }
        catch (PDOException $e)
        {
            return false;
        }

        return true;
    }

    /**
     * Process contract handlers to team.
     *
     * @access public
     * @return bool
     */
    public function processContractHandlers()
    {
        $teamContracts = $this->dao->select('DISTINCT id')->from(TABLE_TEAM)->where('type')->eq('contract')->fetchPairs();
        $contracts     = $this->dao->select('*')->from(TABLE_CONTRACT)
            ->where('handlers')->ne('')
            ->andWhere('id')->notin($teamContracts)
            ->fetchAll();
        foreach($contracts as $contract)
        {
            $member = new stdclass();
            $member->type = 'contract';
            $member->id   = $contract->id;

            $handlers = explode(',', trim($contract->handlers, ','));
            if(count($handlers) == 1) $member->contribution = 100;

            foreach($handlers as $account)
            {
                $member->account = $account;

                $this->dao->insert(TABLE_TEAM)->data($member)->autoCheck()->exec();
            }
        }

        return !dao::isError();
    }

    /**
     * Process customer's nextdate.
     * 
     * @access public
     * @return bool
     */
    public function processCustomerNextdate()
    {
        $customerMindateList = $this->dao->select('objectID, MIN(date) AS date')->from(TABLE_DATING)
            ->where('status')->eq('wait')
            ->andWhere('objectType')->eq('customer')
            ->groupBy('objectID')
            ->fetchPairs();

        foreach($customerMindateList as $customerID => $nextDate)
        {
            $this->dao->update(TABLE_CUSTOMER)->set('nextDate')->eq($nextDate)->where('id')->eq($customerID)->exec();
        }

        return !dao::isError();
    }

    /**
     * Process the trades without currency.
     * 
     * @access public
     * @return bool
     */
    public function processTradesNoCurrency()
    {
        $tradeDepositorPairs    = $this->dao->select('id,depositor')->from(TABLE_TRADE)->where('currency')->eq('')->andWhere('parent')->eq(0)->fetchPairs();
        $depositorCurrencyPairs = $this->dao->select('id,currency')->from(TABLE_DEPOSITOR)->fetchPairs();

        foreach($tradeDepositorPairs as $tradeID => $tradeDepositor)
        {
            $this->dao->update(TABLE_TRADE)->set('currency')->eq($depositorCurrencyPairs[$tradeDepositor])->where('id')->eq($tradeID)->exec();
        }

        return !dao::isError();
    }

    /**
     * Initial the records of the action reader table according to the reader field of the action table.
     *
     * @access public
     * @return bool
     */
    public function initActionReader()
    {
        $data    = new stdclass();
        $actions = $this->dao->select('id, reader')->from(TABLE_ACTION)->where('reader')->ne('')->fetchPairs();
        $users   = $this->dao->select('account')->from(TABLE_USER)->where('deleted')->eq('1')->fetchPairs();
        foreach($actions as $id => $readers)
        {
            $data->action = $id;
            
            $readers = explode(',', trim($readers, ','));

            foreach($readers as $account)
            {
                if(!$account) continue;
                if(isset($users[$account])) continue;

                $data->account = $account;

                $this->dao->replace(TABLE_ACTIONREADER)->data($data)->exec();
            }
        }

        return !dao::isError();
    }

    /**
     * Process transfer trades.
     * 
     * @access public
     * @return bool
     */
    public function processTransferTrades()
    {
        $feeCategories = $this->dao->select('id')->from(TABLE_CATEGORY)->where('major')->eq('7')->fetchPairs();

        $transferTrades = $this->dao->select('*')->from(TABLE_TRADE)
            ->where('type')->like('transfer%')
            ->orWhere('category')->in($feeCategories)
            ->fetchAll('id');

        foreach($transferTrades as $transferTradeID => $transferTrade)
        {
            if($transferTrade->type == 'transferin' or $transferTrade->type == 'out')
            {
                $transferOutID = $transferTrade->type == 'transferin' ? $transferTradeID - 1 : $transferTradeID - 2;
                if(isset($transferTrades[$transferOutID]))
                {
                    $this->dao->update(TABLE_TRADE)->set('transferID')->eq($transferOutID)->where('id')->eq($transferTradeID)->exec();
                }
            }
        }

        return !dao::isError();
    }

    /**
     * Process platform field of sys_entry table when upgrade from 5.2.
     * 
     * @access public
     * @return bool
     */
    public function processEntry()
    {
        $hasPlatform = false;
        $fields      = $this->dbh->query('DESC ' . TABLE_ENTRY)->fetchAll();
        foreach($fields as $field)
        {
            if($field->Field == 'platform')
            {
                $hasPlatform = true;
                break;
            }
        }

        try
        {
            if($hasPlatform)
            {
                $this->dbh->exec('ALTER TABLE ' . TABLE_ENTRY . " CHANGE `platform` `platform` varchar(255) NOT NULL DEFAULT 'zdoo';");
                $this->dbh->exec('UPDATE ' . TABLE_ENTRY . " SET `platform` = REPLACE(`platform`, 'ranzhi', 'zdoo');");
            }
            else
            {
                $this->dbh->exec('ALTER TABLE ' . TABLE_ENTRY . " ADD `platform` varchar(255) NOT NULL DEFAULT 'zdoo' AFTER `version`;");
            }
        }
        catch (PDOException $e)
        {
            return false;
        }

        return true;
    }

    /**
     * Check if the refund table has the field trip.
     *
     * @access public
     * @return bool
     */
    public function processRefundTrip()
    {
        $hasTrip = false;
        $fields  = $this->dbh->query('DESC ' . TABLE_REFUND)->fetchAll();
        foreach($fields as $field)
        {
            if($field->Field == 'trip') return true;
        }

        try
        {
            $this->dbh->exec('ALTER TABLE ' . TABLE_REFUND . ' ADD `trip` mediumint(8) unsigned NOT NULL AFTER `project`;');
        }
        catch (PDOException $e)
        {
            return false;
        }

        return true;
    }

    /**
     * Add rate in team table.
     *
     * @access public
     * @return bool
     */
    public function addRateFieldInTeam()
    {
        $fields  = $this->dbh->query('DESC ' . TABLE_TEAM)->fetchAll();
        foreach($fields as $field)
        {
            if($field->Field == 'rate') return true;
        }

        try
        {
            $this->dbh->exec('ALTER TABLE ' . TABLE_TEAM . ' ADD `rate` decimal(6,2) unsigned NOT NULL AFTER `contribution`;');
        }
        catch (PDOException $e)
        {
            return false;
        }

        return true;
    }

    /**
     * Update trade currency.
     *
     * @access public
     * @return bool
     */
    public function updateTradeCurrency()
    {
        $depositors = $this->dao->select('id, currency')->from(TABLE_DEPOSITOR)->fetchPairs();
        $trades     = $this->dao->select('id, depositor')->from(TABLE_TRADE)->where('currency')->eq('')->fetchAll();

        $tradeList = array();
        foreach($trades as $id => $depositor)
        {
            if(!isset($depositors[$depositor])) continue;

            $currency = $depositors[$depositor];
            $tradeList[$currency][] = $id;
        }

        foreach($tradeList as $currency => $trades) $this->dao->update(TABLE_TRADE)->set('currency')->eq($currency)->where('id')->in($trades)->exec();

        return !dao::isError();
    }

    /**
     * Process kanban data.
     *
     * @access public
     * @return bool
     */
    public function processKanbanData()
    {
        $projects = $this->dao->select('DISTINCT project, id')->from(TABLE_KANBANREGION)->fetchGroup('project', 'id');

        $this->dao->update(TABLE_PROJECT)->set('taskStatus')->eq('classic')->where('id')->in(array_keys($projects))->exec();
        $this->dao->delete()->from(TABLE_KANBANCOLUMN)->exec();

        $column = new stdclass();
        $column->group       = 1;
        $column->limit       = 0;
        $column->color       = '#272E33';
        $column->createdBy   = $this->app->user->account;
        $column->createdDate = helper::now();

        $this->loadModel('kanban');
        $this->app->loadLang('task');
        foreach($projects as $projectID => $regions)
        {
            $regions = array_keys($regions);

            $data = new stdclass();
            $data->group  = 1;

            if(count($regions) > 1)
            {
                $data->region = $regions[0];
                $this->dao->delete()->from(TABLE_KANBANREGION)->where('project')->eq($projectID)->andWhere('id')->ne($regions[0])->exec();
            }
            $this->dao->update(TABLE_KANBANLANE)->data($data)->where('region')->in($regions)->exec();

            $order = 1;
            foreach($this->lang->task->statusList as $key => $value)
            {
                if(!$value) continue;

                $column->region     = $regions[0];
                $column->order      = $order++;
                $column->name       = $value;
                $column->taskStatus = $key;

                $this->dao->insert(TABLE_KANBANCOLUMN)->data($column)->exec();
            }

            $this->dao->update(TABLE_TASK)->set('kanbanRegion')->eq($regions[0])->where('project')->eq($projectID)->exec();
        }

        $this->dao->update(TABLE_KANBANREGION)->set('name')->eq($this->lang->kanban->defaultRegion)->exec();
    }

    /**
     * Update the project of the kanban lane and column.
     * Update the group of the kanban lane and column.
     * Update the kanban group of the task.
     *
     * @access public
     * @return bool
     */
    public function processKanbanDataAgain()
    {
        $projects = $this->dao->select('id, project')->from(TABLE_KANBANREGION)->fetchGroup('project', 'id');
        foreach($projects as $project => $regions)
        {
            $regions = array_keys($regions);
            $this->dao->update(TABLE_KANBANLANE)->set('project')->eq($project)->where('region')->in($regions)->exec();
            $this->dao->update(TABLE_KANBANCOLUMN)->set('project')->eq($project)->where('region')->in($regions)->exec();
        }

        $group = new stdclass();
        $group->id          = 0;
        $group->createdBy   = $this->app->user->account;
        $group->createdDate = helper::now();

        $this->dao->delete()->from(TABLE_KANBANGROUP)->exec();

        $laneGroup = array();
        $lanes     = $this->dao->select('DISTINCT project, region, `group`')->from(TABLE_KANBANLANE)->orderBy('project, region, `group`')->fetchAll();
        foreach($lanes as $lane) $laneGroup[$lane->project][$lane->region][] = $lane->group;

        foreach($laneGroup as $project => $regions)
        {
            $group->project = $project;

            foreach($regions as $region => $groups)
            {
                $group->region = $region;
                $group->order  = 0;

                foreach($groups as $laneGroup)
                {
                    $group->id ++;
                    $group->order ++;

                    $this->dao->insert(TABLE_KANBANGROUP)->data($group)->exec();

                    $this->dao->update(TABLE_KANBANLANE)->set('`group`')->eq($group->id)
                        ->where('project')->eq($project)
                        ->andWhere('region')->eq($region)
                        ->andWhere('`group`')->eq($laneGroup)
                        ->exec();
                    $this->dao->update(TABLE_KANBANCOLUMN)->set('`group`')->eq($group->id)
                        ->where('project')->eq($project)
                        ->andWhere('region')->eq($region)
                        ->andWhere('`group`')->eq($laneGroup)
                        ->exec();
                }
            }
        }
        $lanesGroup = $this->dao->select('id, `group`')->from(TABLE_KANBANLANE)->fetchGroup('group', 'id');
        foreach($lanesGroup as $group => $lanes)
        {
            $this->dao->update(TABLE_TASK)->set('kanbanGroup')->eq($group)->where('kanbanLane')->in(array_keys($lanes))->exec();
        }

        return !dao::isError();
    }

    /**
     * Process annual leaves.
     * 
     * @access public
     * @return bool
     */
    public function processAnnualLeaves()
    {
        if(empty($this->config->leave->annualSetting)) return true;

        $annualLeaves = json_decode($this->config->leave->annualSetting, true);
        if(!is_array($annualLeaves)) return true;

        foreach($annualLeaves as $account => $annualLeave)
        {
            $days = round($annualLeave['totalDays']);
            if($days <= 0) continue;

            $datas = array();
            $data = new stdclass();
            $data->days    = $days;
            $data->times   = 3;
            $data->balance = $days;

            $year = substr($annualLeave['begin'], 0, 4);
            $datas[$year] = $data;

            $this->dao->update(TABLE_USER)->set('annualLeave')->eq(json_encode($datas))->where('account')->eq($account)->exec();
        }

        return !dao::isError();
    }

    /**
     * Process toLieu.
     *
     * @access public
     * @return void
     */
    public function processToLieu()
    {
        $overtimes = $this->dao->select('*')->from(TABLE_OVERTIME)
            ->where('status')->eq('pass')
            ->andWhere('type')->ne('compensate')
            ->orderBy('begin')
            ->fetchAll('id');

        $usedOvertimes = $this->dao->select('overtime')->from(TABLE_LIEU)->where('status')->eq('pass')->andWhere('overtime')->ne('')->fetchAll('overtime');
        $usedOvertimes = array_keys($usedOvertimes);

        foreach($overtimes as $overtime)
        {
            if(in_array($overtime->id, $usedOvertimes)) continue;
            $this->loadModel('attend', 'oa')->saveBalance('overtime', $overtime);
        }

        $lieus = $this->dao->select('*')->from(TABLE_LIEU)
            ->where('status')->eq('pass')
            ->andWhere('overtime')->eq('')
            ->andWhere('trip')->eq('')
            ->andWhere('begin')->ge('2022-01-01')
            ->orderBy('begin')
            ->fetchAll('id');

        foreach($lieus as $lieu) $this->loadModel('lieu', 'oa')->saveToLieu($lieu);

        return !dao::isError();
    }

    /**
     * Process to makeup.
     * 
     * @access public
     * @return bool
     */
    public function processToMakeup()
    {
        $begin       = date('Y-m-01');
        $month       = date('Ym');
        $leaves      = $this->dao->select('*')->from(TABLE_LEAVE)->where('begin')->ge($begin)->andWhere('status')->eq('pass')->fetchAll();
        $attendStats = $this->dao->select('*')->from(TABLE_ATTENDSTAT)->where('month')->ge($month)->fetchGroup('account', 'month');

        $toMakeups = array();
        foreach($leaves as $leave)
        {
            if($leave->type != 'affairs' && $leave->type != 'sick') continue;

            $currentMonth = date('Ym', strtotime($leave->begin));

            if($leave->type == 'sick')    $toMakeup = $leave->hours;
            if($leave->type == 'affairs') $toMakeup = $leave->hours * 1.5;

            if(!isset($toMakeups[$leave->createdBy][$currentMonth])) $toMakeups[$leave->createdBy][$currentMonth] = 0;
            $toMakeups[$leave->createdBy][$currentMonth] += $toMakeup;
        }

        foreach($toMakeups as $account => $userToMakeups)
        {
            foreach($userToMakeups as $userMonth => $toMakeup)
            {
                if($toMakeup <= 0) continue;

                if(isset($attendStats[$account][$userMonth]))
                {
                    $attendStatID = $attendStats[$account][$userMonth]->id;
                    $this->dao->update(TABLE_ATTENDSTAT)->set('toMakeup')->eq($toMakeup)->where('id')->eq($attendStatID)->exec();
                }
                else
                {
                    $stat = new stdclass();
                    $stat->account  = $account;
                    $stat->month    = $month;
                    $stat->status   = 'wait';
                    $stat->toMakeup = $toMakeup;

                    $this->dao->insert(TABLE_ATTENDSTAT)->data($stat)->autoCheck()->exec();
                    $attendStatID = $this->dao->lastInsertID();
                }

                $this->loadModel('action')->create('attendstat', $attendStatID, 'addedToMakup', '', $toMakeup);
            }
        }

        return !dao::isError();
    }

    /**
     * Process gift image. 
     * 
     * @access public
     * @return bool
     */
    public function processGiftImage()
    {
        $giftList =  $this->dao->select('*')->from(TABLE_PRODUCT)
            ->where('deleted')->eq('0')
            ->andWhere('gift')->eq('1')
            ->fetchAll('id');
        $files = $this->loadModel('file')->getByObject($objectType = 'product', array_keys($giftList), $isImage = true, $editor = '1');
        foreach($files as $file)
        {
            if(!$file) continue;
            foreach($file as $fileImage)
            {
                $fileImage->editor = '0';
                unset($fileImage->id);
                unset($fileImage->fullURL);
                unset($fileImage->middleURL);
                unset($fileImage->smallURL);
                unset($fileImage->isImage);
                unset($fileImage->largeURL);
                $this->dao->insert(TABLE_FILE)->data($fileImage)->exec();
            }
        }
        return !dao::isError();
    }

    /**
     * Process gift.
     *
     * @access public
     * @return bool
     */
    public function processGift()
    {
        $oldGiftList =  $this->dao->select('*')->from(TABLE_PRODUCT)
            ->where('gift')->eq('1')
            ->fetchAll('id');

        $data = new stdclass();
        $data->scoreGift = 1;
        foreach($oldGiftList as $oldGiftID => $oldGift)
        {
            $data->name        = $oldGift->name;
            $data->score       = $oldGift->score;
            $data->amount      = $oldGift->amount;
            $data->status      = $oldGift->status == 'developing' ? 'offline' : $oldGift->status;
            $data->limit       = $oldGift->limit;
            $data->desc        = $oldGift->desc;
            $data->createdBy   = $oldGift->createdBy;
            $data->createdDate = $oldGift->createdDate;
            $data->editedBy    = $oldGift->editedBy;
            $data->editedDate  = $oldGift->editedDate;
            $data->deleted     = $oldGift->deleted;

            $this->dao->insert(TABLE_GIFT)->data($data)->exec();

            $giftID = $this->dao->lastInsertID();
            $this->dao->update(TABLE_EXCHANGE)->set('giftID')->eq($giftID)->where('giftID')->eq($oldGiftID)->exec();
            $this->dao->update(TABLE_FILE)
                ->set('objectID')->eq($giftID)
                ->set('objectType')->eq('gift')
                ->where('objectID')->eq($oldGiftID)
                ->andWhere('objectType')->eq('product')
                ->exec();
            $this->dao->update(TABLE_PRODUCT)->set('deleted')->eq('1')->where('id')->eq($oldGiftID)->exec();
        }

        return !dao::isError();
    }

    /**
     * Process gift action.
     *
     * @access public
     * @return void
     */
    public function processGiftAction()
    {
        $oldGiftList = $this->dao->select('name, id')->from(TABLE_PRODUCT)->where('gift')->eq('1')->fetchPairs();
        $newGiftList = $this->dao->select('id, name')->from(TABLE_GIFT)->fetchPairs();
        foreach($newGiftList as $newGiftID => $newGiftName)
        {
            if(!isset($oldGiftList[$newGiftName])) continue;
            $oldGiftID = $oldGiftList[$newGiftName];
            $this->dao->update(TABLE_ACTION)
                ->set('objectID')->eq($newGiftID)
                ->set('objectType')->eq('gift')
                ->where('objectType')->eq('product')
                ->andWhere('objectID')->eq($oldGiftID)
                ->exec();
        }
        return !dao::isError();
    }

    /**
     * Process late and early.
     *
     * @access public
     * @return void
     */
    public function processLateAndEarly()
    {
        $this->app->loadModuleConfig('attend', 'oa');
        $signIn  = new DateTime($this->config->attend->signInLimit);
        $signOut = new DateTime($this->config->attend->signOutLimit);

        $attendList = $this->dao->select('*')->from(TABLE_ATTEND)
            ->where('date')->ge('2022-01-01')
            ->andWhere('status')->in('both,late,early')
            ->fetchAll();
        foreach($attendList as $attend)
        {
            $lateMinutes  = 0;
            $earlyMinutes = 0;
            if($attend->status == 'late' || $attend->status == 'both')
            {
                $todaySignIn = new DateTime($attend->signIn);
                $lateSeconds = $todaySignIn->getTimestamp() - $signIn->getTimestamp();
                if($lateSeconds > 0) $lateMinutes = ceil($lateSeconds / 60);
            }
            if($attend->status == 'early' || $attend->status == 'both')
            {
                $todaySignOut   = new DateTime($attend->signOut);
                $signOutSeconds = $signOut->getTimestamp() - $todaySignOut->getTimestamp();
                if($signOutSeconds > 0) $earlyMinutes = ceil($signOutSeconds / 60);
            }

            $this->dao->update(TABLE_ATTEND)
                ->set('lateMinutes')->eq($lateMinutes)
                ->set('earlyMinutes')->eq($earlyMinutes)
                ->where('id')->eq($attend->id)
                ->exec();
        }
        return !dao::isError();
    }

    /**
     * Convert the annual leave from user->annualLeaves to the annual leave table.
     *
     * @access public
     * @return bool
     */
    public function convertAnnualLeaves()
    {
        $this->loadModel('action');

        $annual = new stdclass();
        $annual->createdBy   = 'system';
        $annual->createdDate = helper::now();

        $users = $this->dao->select('account, annualLeave')->from(TABLE_USER)
            ->where('deleted')->eq('0')
            ->andWhere('locked')->lt('2199-12-31')
            ->andWhere('annualLeave')->ne('')
            ->fetchPairs();

        foreach($users as $account => $leaves)
        {
            $leaves = json_decode($leaves);
            if(empty($leaves) or !is_object($leaves)) continue;

            $annual->account = $account;

            foreach($leaves as $year => $leave)
            {
                $annual->year    = $year;
                $annual->days    = $leave->days;
                $annual->times   = $leave->times;
                $annual->balance = $leave->balance;

                $this->dao->insert(TABLE_ANNUAL)->data($annual)->exec();

                $annualID = $this->dao->lastInsertID();
                $this->action->create('annual', $annualID, 'created', '', '', 'system');
            }
        }
        return !dao::isError();
    }

    /**
     * Delete too advanced to-do.
     *
     * @access public
     * @return bool
     */
    public function removeFutureTodo()
    {
        $todoList = $this->dao->select('*')->from(TABLE_TODO)->where('cycle')->eq(1)->fetchAll('id');
        foreach($todoList as $id => $todo)
        {
            $config     = json_encode($todo->config);
            $beforeDays = isset($config->beforeDays) ? $config->beforeDays : 30;
            $lastDate   = date('Y-m-d', strtotime(" + $beforeDays days"));
            $this->dao->delete()->from(TABLE_TODO)->where('type')->eq('cycle')->andWhere('idvalue')->eq($todo->id)->andWhere('date')->gt($lastDate)->exec();
        }
        return !dao::isError();
    }

    /**
     * Upgrade saved query.
     *
     * @access public
     * @return bool
     */
    public function upgradeSavedQuery()
    {
        $queries = $this->dao->select('id,form,`sql`')->from(TABLE_USERQUERY)->where('module')->eq('customer')->fetchAll('id');

        foreach($queries as $id => $query)
        {
            $query->form = htmlspecialchars_decode($query->form, ENT_QUOTES);
            $query->sql  = htmlspecialchars_decode($query->sql, ENT_QUOTES);
            $query->form = unserialize($query->form);
            $query->form = str_replace(array('t1.nextDate', 't1.contactedBy'), array('t2.date', 't2.account'), $query->form);
            $query->sql  = str_replace(array('t1.nextDate', 't1.contactedBy'), array('t2.date', 't2.account'), $query->sql);
            $query->form = htmlspecialchars(serialize($query->form));

            $this->dao->update(TABLE_USERQUERY)->data($query)->where('id')->eq($id)->exec();
        }
        return !dao::isError();
    }

    /**
     * Precess payment intention for customer.
     *
     * @access public
     * @return void
     */
    public function precessPaymentIntention()
    {
        $this->dao->query("
            UPDATE " . TABLE_CUSTOMER . "  t1
            LEFT JOIN " . TABLE_STAGEGUIDANCE . " AS t2 ON t1.id = t2.customer
            SET t1.paymentIntention = t2.paymentIntention
            WHERE t2.id IS NOT NULL
        ");
        return !dao::isError();
    }

    /**
     * Precess health for customer.
     *
     * @access public
     * @return void
     */
    public function precessHealth()
    {
        $this->dao->query("
            UPDATE " . TABLE_CUSTOMER . "  t1
            LEFT JOIN " . TABLE_CONTRACT . " AS t2 ON t1.id = t2.customer
            LEFT JOIN " . TABLE_STAGEDELIVERY . " AS t3 ON t2.id = t3.contract
            SET t1.health = t3.health
            WHERE t2.id IS NOT NULL AND t3.id IS NOT NULL
        ");
        return !dao::isError();
    }

    /**
     * Precess renewal intention for customer.
     *
     * @access public
     * @return void
     */
    public function processRenewalIntention()
    {
        $this->dao->query("
            UPDATE " . TABLE_CUSTOMER . "  t1
            LEFT JOIN " . TABLE_CONTRACT . " AS t2 ON t1.id = t2.customer
            LEFT JOIN " . TABLE_STAGESERVICE . " AS t3 ON t2.id = t3.contract
            SET t1.renewalIntention = t3.renewalIntention
            WHERE t2.id IS NOT NULL AND t3.id IS NOT NULL
        ");
        return !dao::isError();
    }

    /**
     * Process file.
     *
     * @access public
     * @return void
     */
    public function processTryPlan()
    {
        $this->dao->update(TABLE_FILE)->set('extra')->eq('plan')->where('objectType')->eq('stagetrial')->exec();
        return !dao::isError();
    }

    /**
     * Process expect.
     *
     * @access public
     * @return bool
     */
    public function processExpect()
    {
        $maps = array(
            'leadertransformation'     => 'BOSS_A_01',
            'leaderprogress'           => 'BOSS_B_01',
            'leadernoPublic'           => 'PM_A_04',
            'leadermanage'             => 'BOSS_F_02',
            'leadercost'               => 'BOSS_A_05',
            'leadernotScalable'        => 'BOSS_B_02',
            'leadernoMeasure'          => 'BOSS_E_01',
            'leaderCMMI'               => 'BOSS_A_02',
            'leaderteamManage'         => 'BOSS_C_01',
            'leadernoMarking'          => 'BOSS_E_01',
            'leaderunsafe'             => 'BOSS_B_03',
            'controllerresources'      => 'PM_A_01',
            'controllerunclearMode'    => 'PM_B_01',
            'controlleruncloerDuty'    => 'PM_A_02',
            'controllernotControl'     => 'PM_B_03',
            'controllernotOpen'        => 'PM_A_04',
            'controllernoMeasure'      => 'PM_D_01',
            'controllerteamStatus'     => 'PM_A_04',
            'controllerquality'        => 'PM_B_04',
            'controllernonstandard'    => 'PM_B_05',
            'controllerteamManage'     => 'BOSS_C_01',
            'controllernoAccumulation' => 'BOSS_E_02',
            'controllernotPublic'      => 'PM_B_02',
            'staffuncloerDuty'         => 'USERS_pain_01',
            'staffbusy'                => 'USERS_pain_05',
            'staffheavy'               => 'USERS_pain_05',
            'staffovertime'            => 'USERS_pain_03',
            'staffteamwork'            => 'USERS_pain_04',
            'staffpoorPrograess'       => 'USERS_pain_05',
        );

        $communicationList = $this->dao->select('*')->from(TABLE_STAGECOMMUNICATION)->fetchAll('id');
        foreach($communicationList as $id => $communication)
        {
            $expect = $communication->expect;
            if(empty($expect)) continue;

            $expect = json_decode($expect);
            if(!isset($expect->leaderExpect)) continue;

            $newExpect = '{"BOSS":{"other":{"other":""}},"PM":{"other":{"other":""}},"PO":{"other":{"other":""}},"USERS":{"other":{"other":""}}}';
            $newExpect = json_decode($newExpect);

            $leaderExpect          = $expect->leaderExpect;
            $leaderExpectOther     = $expect->leaderExpectOther;
            $controllerExpect      = $expect->controllerExpect;
            $controllerExpectOther = $expect->controllerExpectOther;
            $staffExpect           = $expect->staffExpect;
            $staffExpectOther      = $expect->staffExpectOther;

            foreach(explode(',', $leaderExpect) as $code)
            {
                if($code === 'other')
                {
                    $newExpect->BOSS->other->other = $leaderExpectOther;
                }
                else
                {
                    if(empty($code)) continue;
                    $newCode = zget($maps, 'leader' . $code, null);
                    if(empty($newCode)) continue;
                    $newCodeArray = explode('_', $newCode);
                    if(count($newCodeArray) !== 3) continue;

                    $typeCode = $newCodeArray[0] . '_' . $newCodeArray[1];

                    if(isset($newExpect->{$newCodeArray[0]}->$typeCode))
                    {
                        if(in_array($newCode, $newExpect->{$newCodeArray[0]}->$typeCode, true)) continue;
                        $newExpect->{$newCodeArray[0]}->$typeCode[] = $newCode;
                    }
                    else
                    {
                        $newExpect->{$newCodeArray[0]}->$typeCode = array($newCode);
                    }
                }
            }

            foreach(explode(',', $controllerExpect) as $code)
            {
                if($code === 'other')
                {
                    $newExpect->PM->other->other = $controllerExpectOther;
                }
                else
                {
                    if(empty($code)) continue;
                    $newCode = zget($maps, 'controller' . $code, null);
                    if(empty($newCode)) continue;
                    $newCodeArray = explode('_', $newCode);
                    if(count($newCodeArray) !== 3) continue;

                    $typeCode = $newCodeArray[0] . '_' . $newCodeArray[1];

                    if(isset($newExpect->{$newCodeArray[0]}->$typeCode))
                    {
                        if(in_array($newCode, $newExpect->{$newCodeArray[0]}->$typeCode, true)) continue;
                        $newExpect->{$newCodeArray[0]}->$typeCode[] = $newCode;
                    }
                    else
                    {
                        $newExpect->{$newCodeArray[0]}->$typeCode = array($newCode);
                    }
                }
            }

            foreach(explode(',', $staffExpect) as $code)
            {
                if($code === 'other')
                {
                    $newExpect->USERS->other->other = $staffExpectOther;
                }
                else
                {
                    if(empty($code)) continue;
                    $newCode = zget($maps, 'staff' . $code, null);
                    if(empty($newCode)) continue;
                    $newCodeArray = explode('_', $newCode);
                    if(count($newCodeArray) !== 3) continue;

                    $typeCode = $newCodeArray[0] . '_' . $newCodeArray[1];

                    if(isset($newExpect->{$newCodeArray[0]}->$typeCode))
                    {
                        if(in_array($newCode, $newExpect->{$newCodeArray[0]}->$typeCode, true)) continue;
                        $newExpect->{$newCodeArray[0]}->$typeCode[] = $newCode;
                    }
                    else
                    {
                        $newExpect->{$newCodeArray[0]}->$typeCode = array($newCode);
                    }
                }
            }

            $this->dao->update(TABLE_STAGECOMMUNICATION)->set('expect')->eq(json_encode($newExpect))->where('id')->eq($id)->exec();
        }
    }

    /**
     * Process status for trip and egress.
     *
     * @access public
     * @return void
     */
    public function processTripStatus()
    {
        $this->loadModel('trip', 'oa');
        $now = helper::now();
        $this->dao->update(TABLE_TRIP)
            ->set('status')->eq('pass')
            ->set('reviewedDate')->eq($now)
            ->where('begin')->lt($now)
            ->exec();

        $tripList = $this->dao->select('*')->from(TABLE_TRIP)->where('begin')->ge($now)->fetchAll();

        foreach($tripList as $trip)
        {
            $reviewer = $this->trip->getReviewedBy($trip->createdBy, $trip->type);
            $this->dao->update(TABLE_TRIP)->set('status')->eq('wait')->set('reviewedBy')->eq($reviewer)->where('id')->eq($trip->id)->exec();
        }

        return !dao::isError();
    }
}
