<?php

// #5854 - Store custom versions (YetiForce) on installation
function isCustomVtigerVersion()
{
    global $YetiForce_current_version;
    if(!empty($YetiForce_current_version)) {
        return true;
    }

    return false;
}

/**
 * Dependencies
 */
// #5854 - Some custom vtiger version have different file locations
if(isCustomVtigerVersion()) {
    require_once 'include/CRMEntity.php';
    require_once 'include/Tracker.php';
} else {
    require_once 'data/CRMEntity.php';
    require_once 'data/Tracker.php';
}


/**
 * Advanced Reports main module file
 */
class AdvancedReports extends CRMentity
{
    /**
     * Invoked when special actions are performed on the module.
     * @param String Module name
     * @param String Event Type
     */
    function vtlib_handler($modulename, $event_type) 
    {
        $adb = PearDatabase::getInstance();

        if ($event_type == 'module.postinstall') {
            // Handle post installation actions
            $this->addLinks();

            // #5854 - Some custom vtiger version database adapters doesn't allow to use transaction, etc. SQL methods
            if (!isCustomVtigerVersion()) {
                $this->importSQLFromFile();
            }


            //#4031 [start] - generate language files
            global $adb;

            $qry_result = $adb->pquery("SELECT prefix FROM vtiger_language", array());
            $res_cnt = $adb->num_rows($qry_result);
            if ($res_cnt > 0) {
                for ($i = 0; $i < $res_cnt; $i++) {
                    $prefix = $adb->query_result($qry_result, $i, "prefix");
                    $filename = "languages/" . $prefix . "/AdvancedReports.php";
                    if (!file_exists($filename)) {
                        if (file_exists("languages/en_us/AdvancedReports.php")) {
                            copy("languages/en_us/AdvancedReports.php", $filename);
                        }
                    }
                }
            }
            //#4031 [end]

            // Register CRON task
            Vtiger_Cron::register('Advanced Reporting Scheduled reports', 'cron/modules/AdvancedReports/sendScheduledReports.service', 900, 'AdvancedReports', 1, 1, 'Sends Advanced Reporting Scheduled Reports to email');
            Vtiger_Cron::register('Advanced Reporting processes', 'cron/modules/AdvancedReports/executeARJobs.service', 43200, 'AdvancedReports', 1, 1, 'Starts Advanced Reports processes');

            global $adb;
            $adb->dieOnError = false;
            $adb->debug = false;

            // #5854 - Some custom vtiger version database adapters doesn't allow to use following SQL methods
            if (!isCustomVtigerVersion()) {
                // Now using only module.postupdate'
            }

            // Add 'm' prefixes to reports
            $this->patchReports();
        } else if ($event_type == 'module.disabled') {
            // Handle actions when this module is disabled.
            $this->removeLinks();
        } else if ($event_type == 'module.enabled') {
            // Handle actions when this module is enabled.
            $this->addLinks();
            // Add 'm' prefixes to reports so we can test it without updating module
            $this->patchReports();
        } else if ($event_type == 'module.preuninstall') {
            // Handle actions when this module is about to be deleted.
            $this->removeLinks();
        } else if ($event_type == 'module.preupdate') {
            // TODO Handle actions before this module is updated.
        } else if ($event_type == 'module.postupdate') {
            // Handle actions after this module is updated.
            $this->addLinks();

            global $adb;
            $adb->dieOnError = false;
            $adb->debug = false;

            $instance = Vtiger_Cron::getInstance('Advanced Reporting Scheduled reports');

            if($instance === false){
                Vtiger_Cron::register('Advanced Reporting Scheduled reports', 'cron/modules/AdvancedReports/sendScheduledReports.service', 900, 'AdvancedReports', 1, 1, 'Sends Advanced Reporting Scheduled Reports to email');
            }

            $instance = Vtiger_Cron::getInstance('Advanced Reporting processes');
            if($instance === false){
                Vtiger_Cron::register('Advanced Reporting processes', 'cron/modules/AdvancedReports/executeARJobs.service', 43200, 'AdvancedReports', 1, 1, 'Starts Advanced Reports processes');
            }

            // #5854 - Some custom vtiger version database adapters doesn't allow to use following SQL methods
            if (!isCustomVtigerVersion()) {
                // Delete legacy dashboard widget links and existing widgets
                $adb->query("DELETE links, widgets FROM vtiger_links AS links INNER JOIN vtiger_module_dashboard_widgets AS widgets ON (widgets.linkid = links.linkid) WHERE links.linkurl = 'index.php?module=AdvancedReports&view=ShowWidget&name=widget'");
                // Delete legacy dashboard widget links
                $adb->query("DELETE FROM vtiger_links WHERE linkurl = 'index.php?module=AdvancedReports&view=ShowWidget&name=widget'");

                $adb->query("CREATE TABLE IF NOT EXISTS `vtiger_advancedreports_schedule_g` (`id` int(11) NOT NULL,`group` int(11) NOT NULL,UNIQUE KEY `report_idx` (`id`,`group`), KEY `group` (`group`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
                $adb->query("CREATE TABLE IF NOT EXISTS `vtiger_advancedreports_schedule_r` (`id` int(11) NOT NULL,`role` varchar(255) NOT NULL,UNIQUE KEY `report_idx` (`id`,`role`),KEY `role` (`role`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
                $adb->query("CREATE TABLE IF NOT EXISTS `vtiger_advancedreports_schedule_u` (`id` int(11) NOT NULL,`user` int(11) NOT NULL,UNIQUE KEY `report_idx` (`id`,`user`),KEY `user` (`user`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
                $adb->query("CREATE TABLE IF NOT EXISTS `vtiger_advancedreports_config` (`key` varchar(32) DEFAULT NULL,`value` text, UNIQUE KEY `key` (`key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
                $adb->query("CREATE TABLE IF NOT EXISTS `vtiger_advancedreports_templates` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `file` BLOB NULL DEFAULT NULL, PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8");
                $adb->query("CREATE TABLE IF NOT EXISTS `vtiger_advancedreports_jobs` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `description` varchar(255) NOT NULL, `active` tinyint(1) NOT NULL, `type` varchar(255) NOT NULL, `descriptor` blob NOT NULL, `state` blob NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
                $adb->query("ALTER TABLE `vtiger_advancedreports_schedule_g` ADD CONSTRAINT `vtiger_advancedreports_schedule_g_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vtiger_advancedreports` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT `vtiger_advancedreports_schedule_g_ibfk_2` FOREIGN KEY (`group`) REFERENCES `vtiger_groups` (`groupid`)");
                $adb->query("ALTER TABLE `vtiger_advancedreports_schedule_r` ADD CONSTRAINT `vtiger_advancedreports_schedule_r_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vtiger_advancedreports` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT `vtiger_advancedreports_schedule_r_ibfk_2` FOREIGN KEY (`role`) REFERENCES `vtiger_role` (`roleid`) ON DELETE CASCADE ON UPDATE CASCADE");
                $adb->query("ALTER TABLE `vtiger_advancedreports_schedule_u`ADD CONSTRAINT `vtiger_advancedreports_schedule_u_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vtiger_advancedreports` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT `vtiger_advancedreports_schedule_u_ibfk_2` FOREIGN KEY (`user`) REFERENCES `vtiger_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE");
                $adb->query("ALTER TABLE `vtiger_advancedreports` ADD `labels` LONGBLOB NOT NULL AFTER `options`"); // #4544
                $adb->query("ALTER TABLE `vtiger_advancedreports` ADD `visible` INT(1) NOT NULL DEFAULT '1'"); // #4947
                $adb->query("ALTER TABLE `vtiger_advancedreports_categories` ADD `visible` INT(1) NOT NULL DEFAULT '1'"); // #4947
                $adb->query("ALTER TABLE `vtiger_advancedreports` ADD `calcFields` BLOB NULL DEFAULT NULL"); // #5098
                $adb->query("ALTER TABLE `vtiger_advancedreports` ADD `columnstate` BLOB NULL DEFAULT NULL"); // #5098
                $adb->query("ALTER TABLE `vtiger_advancedreports_schedule` ADD `emails` BLOB NULL DEFAULT NULL"); // #5872
                $adb->query("ALTER TABLE vtiger_advancedreports_templates MODIFY file LONGBLOB;");
                $adb->query("ALTER TABLE `vtiger_advancedreports_schedule_u` ADD `emails` BLOB NULL DEFAULT NULL"); // #5098
                $adb->query("CREATE TABLE `vtiger_advancedreports_dashboards` (`id` char(36) NOT NULL, `category_id` int(11) NOT NULL, `visible` int(1) NOT NULL DEFAULT '1', `sequence` int(10) NOT NULL, `title` varchar(255) NOT NULL, `description` text NOT NULL, `filters` blob, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
                $adb->query("CREATE TABLE `vtiger_advancedreports_widgets` (`id` char(36) NOT NULL, `dashboard_id` char(36) NOT NULL, `report_id` int(11) NOT NULL, `type` varchar(50) NOT NULL, `positionx` int(5) NOT NULL, `positiony` int(5) NOT NULL, `width` int(5) NOT NULL, `height` int(5) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
                $adb->query("ALTER TABLE `vtiger_advancedreports_widgets` ADD `settings` TEXT DEFAULT NULL");
                $adb->query("ALTER TABLE `vtiger_advancedreports` MODIFY `filters` MEDIUMBLOB NOT NULL;");
                $adb->query("ALTER TABLE `vtiger_advancedreports` MODIFY `grouping` MEDIUMBLOB NOT NULL;");
                $adb->query("ALTER TABLE `vtiger_advancedreports` MODIFY `aggregates` MEDIUMBLOB NOT NULL;");
                $adb->query("ALTER TABLE `vtiger_advancedreports` MODIFY `labels` MEDIUMBLOB NOT NULL;");
                $adb->query("ALTER TABLE `vtiger_advancedreports` MODIFY `calcFields` MEDIUMBLOB;");
                $adb->query("CREATE TABLE IF NOT EXISTS vtiger_advancedreports_table_mapping (`id` int(11) NOT NULL AUTO_INCREMENT,`original_name` varchar(255) NOT NULL,`hashed_name` varchar(255) NOT NULL,PRIMARY KEY (`id`),INDEX `mapping_orgin_name_idx` (`original_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

                $adb->query("ALTER TABLE vtiger_advancedreports_widgets ADD `settings` TEXT DEFAULT NULL;");
                $adb->query("ALTER TABLE vtiger_advancedreports_dashboards ADD owner varchar(36) NOT NULL;");
                $adb->query("ALTER TABLE vtiger_advancedreports_dashboards ADD shared int(1) NOT NULL;");
                $adb->query("ALTER TABLE vtiger_advancedreports_dashboards ADD sharedlevel int(1) NOT NULL DEFAULT '0';");
                $adb->query("ALTER TABLE vtiger_advancedreports_sharedgroups CHANGE report_id object_id VARCHAR(36) NOT NULL;");
                $adb->query("ALTER TABLE vtiger_advancedreports_sharedusers CHANGE  report_id object_id VARCHAR(36) NOT NULL;");
                $adb->query("ALTER TABLE vtiger_advancedreports CHANGE columnstate columnstate MEDIUMBLOB NULL DEFAULT NULL;");

                $adb->query("CREATE TABLE IF NOT EXISTS `vtiger_advancedreports_logs` (`id` int(11) NOT NULL AUTO_INCREMENT, `created` datetime NOT NULL, `level` varchar(10) NOT NULL, `message` text NOT NULL, `vars` text, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                $adb->query("ALTER TABLE vtiger_advancedreports_config CHANGE `key` id VARCHAR(32) NULL DEFAULT NULL;");

                $adb->query("ALTER TABLE `vtiger_advancedreports` ADD `calculatedColumns` BLOB NULL DEFAULT NULL");

                // Moved to new settings
                $adb->query("ALTER TABLE vtiger_advancedreports ADD `deleted` INT(1) NOT NULL DEFAULT '0';");

                $adb->query("ALTER TABLE vtiger_advancedreports_schedule DROP FOREIGN KEY vtiger_advancedreports_schedule_ibfk_1;");
                $adb->query("ALTER TABLE vtiger_advancedreports_schedule_g DROP FOREIGN KEY vtiger_advancedreports_schedule_g_ibfk_1;");
                $adb->query("ALTER TABLE vtiger_advancedreports_schedule_r DROP FOREIGN KEY vtiger_advancedreports_schedule_r_ibfk_1;");
                $adb->query("ALTER TABLE vtiger_advancedreports_schedule_u DROP FOREIGN KEY vtiger_advancedreports_schedule_u_ibfk_1;");

                $adb->query("ALTER TABLE vtiger_advancedreports_schedule CHANGE id object_id VARCHAR(36) NOT NULL;");
                $adb->query("ALTER TABLE vtiger_advancedreports_schedule_u CHANGE id object_id VARCHAR(36) NOT NULL;");
                $adb->query("ALTER TABLE vtiger_advancedreports_schedule_r CHANGE id object_id VARCHAR(36) NOT NULL;");
                $adb->query("ALTER TABLE vtiger_advancedreports_schedule_g CHANGE id object_id VARCHAR(36) NOT NULL;");

                $adb->query("ALTER TABLE vtiger_advancedreports_schedule ADD object_type CHAR(1) NOT NULL DEFAULT 'R';");
                // #6897 - Add two fields to log table
                $adb->query("ALTER TABLE vtiger_advancedreports_logs ADD memory_usage varchar(30) NOT NULL, ADD time_taken VARCHAR(30) NOT NULL, ADD class_name varchar(200) NULL DEFAULT NULL, ADD method_name varchar(100)  NULL DEFAULT NULL;");

                // Create advancedreports_intervals table
                $adb->query("CREATE TABLE IF NOT EXISTS vtiger_advancedreports_intervals ( `id` int(11) NOT NULL AUTO_INCREMENT, `object_id` varchar(36) NOT NULL, `object_type` varchar(15) NOT NULL, `interval_type` varchar(15) NOT NULL, `interval_options` varchar(15) NOT NULL, `interval_hours` varchar(2) NOT NULL, `interval_minutes` varchar(2) NOT NULL, `nexttime` datetime NOT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

                // #6897 - Added status, max_time, max_memory to jobs table
                $adb->query("ALTER TABLE vtiger_advancedreports_jobs ADD status VARCHAR(10) NULL DEFAULT NULL, ADD `max_time` VARCHAR(15) NULL DEFAULT NULL, ADD `max_memory` VARCHAR(15) NULL DEFAULT NULL;");

                // #6905 - Added SHA1 checksum and JSON worksheet column for XLS templates
                $adb->query("ALTER TABLE vtiger_advancedreports_templates ADD `file_checksum` varchar(40) DEFAULT NULL, ADD `worksheets` text NULL DEFAULT NULL;");

                // Add status, starttime and endtime to scheduler
                $adb->query("ALTER TABLE vtiger_advancedreports_schedule ADD status VARCHAR(10) NULL DEFAULT NULL, ADD starttime datetime NULL DEFAULT NULL, ADD endtime datetime NULL DEFAULT NULL;");

                // performance penalty should now be high
                $adb->query("ALTER TABLE vtiger_advancedreports_schedule MODIFY COLUMN starttime DATETIME NULL DEFAULT NULL, MODIFY COLUMN endtime DATETIME NULL DEFAULT NULL;");

                // Allow totalAggregates to be NULL
                $adb->query("ALTER TABLE vtiger_advancedreports CHANGE `totalAggregates` `totalAggregates` BLOB  NULL DEFAULT NULL;");

                // Init config data
                $this->initConfig($adb);

                //#4594 [start] - generate missing strings in language files
                global $adb;
                $qry_result = $adb->pquery("SELECT prefix FROM vtiger_language", array());
                $res_cnt = $adb->num_rows($qry_result);

                $languages = array();
                if($res_cnt > 0) {
                    for ($i = 0; $i < $res_cnt; $i++) {
                        $languages[] = $prefix = $adb->query_result($qry_result, $i, "prefix");
                    }
                }

                $eng = $this->getTranslationsInLang("en_us");
                foreach ($languages as $lang) {
                    $translated = $this->getTranslationsInLang($lang);
                    $update = false;
                    foreach ($eng as $string => $v) {
                        if (!isset($translated[$string])) {
                            $translated[$string] = $v;
                            $update = true;
                        }
                    }
                    if ($update) {
                        $string = '<' . '?php $languageStrings = ' . var_export($translated, true) . ';';
                        file_put_contents("languages/" . $lang . "/AdvancedReports.php", $string);
                    }
                }

                $this->cleanup();
            }

            // Add 'm' prefixes to reports
            $this->patchReports();
        }

        // #6615 - vTiger 7 have different layout directory, so copy files to them
        global $vtiger_current_version;
        if (version_compare($vtiger_current_version, '7.0.0', '>=')) {
            $this->copyLayoutFiles("vlayout", "v7");
        }
    }

    function getTranslationsInLang($lang)
    {
        include "languages/" . $lang . "/AdvancedReports.php";
        return $languageStrings;
    }
    //#4594 [end]

    /**
     * Import base data into AdvancedReports table from import.sql
     */
    function importSQLFromFile($filename = false)
    {
        global $adb;

        if (!$filename) {
            $filename = dirname(__FILE__) . '/import.sql';
        }

        // Check for file existence
        if (!file_exists($filename)) {
            echo "<b>File $filename doesn't exist. Cancelling SQL data import!</b>\n<br/>";
            return;
        }

        // Temporary variable, used to store current query
        $templine = '';
        $successCount = 0;
        $errorCount = 0;

        // Read in entire file
        $lines = file($filename);
        foreach ($lines as $line) {
            // Skip it if it's a comment
            if (substr($line, 0, 2) == '--' || $line == '') {
                continue;
            }

            // Add this line to the current segment
            $templine .= $line;
            // If it has a semicolon at the end, it's the end of the query
            if (substr(trim($line), -1, 1) == ';') {
                // Perform the query
                $result = $adb->query($templine);
                if ($result) {
                    // Query performed sucessfully
                    $successCount++;
                } else {
                    // Error while performing query
                    $errorCount++;
                    //echo "Error performing query <b>$templine : ". mysql_error() . "</b>\n<br />";
                }

                // Reset temp variable to empty
                $templine = '';
            }
        }

        // echo "SQL data imported successfully ($successCount suceess, $errorCount errors)\n<br />";
    }

    /**
     * Set initial config after config table has been created
     */
    function initConfig($adb)
    {
        // Adding config parameters
        $config = array(
                'version' => 'vtiger600',
                'limit' => '20',
                'dateformat' => 'Y-M-d',
                'builderPublicAccess' => 'false',
                'reportTreeAdminAccess' => 'false',
                'showNullValues' => 'false',
                'apiEndpoint' => 'http://api.dev.itsapiens.eu/',
                'key' => '',
                // TODO: Not available and not known how to retrieve (probably we should add file in modules/AdvancedReports/version.php with version in it)
                // 'moduleVersion' => $this->version, // Inherited from ModuleBasic::__updateVersion
                'clearAllOutputBuffers' => 'true',
        );

        // Config paramters which should always be updated
        $updateConfig = array(
            'version' => $config['version'],
            // TODO: Not available and not known how to retrieve (probably we should add file in modules/AdvancedReports/version.php with version in it)
            // 'moduleVersion' => $this->version // Inherited from ModuleBasic::__updateVersion
        );


        // Try to add key file in config if exists
        $keyFile = 'modules/AdvancedReports/key.php';
        if (file_exists($keyFile)) {
            $key = file_get_contents($keyFile);
            // Add key contents to keyValue array
            $config["key"] = $key;
        }

        // Insert config values
        foreach ($config as $key => $value) {
            $result = $adb->query("SELECT COUNT(*) AS count FROM vtiger_advancedreports_config WHERE id = '{$key}' LIMIT 1");
            $found = $adb->query_result($result, 0, "count");
            if ($found < 1) {
                $adb->query("INSERT INTO vtiger_advancedreports_config (id, value) VALUES ('{$key}', '{$value}')");
            }
        }


        // Update config keys after module update
        foreach ($updateConfig as $key => $value) {
            $adb->query("UPDATE vtiger_advancedreports_config SET value = '{$value}' WHERE id = '{$key}'");
        }
    }

    //Perform cleanup operations
    function cleanup()
    {
        $keyFile = 'modules/AdvancedReports/key.php';
        if (file_exists($keyFile)) {
            if ($this->suhosin_function_exists("chmod")) {
                @chmod($keyFile, 0777);
            }
            // Try to delete key file
            if ($this->suhosin_function_exists("unlink")) {
                @unlink($keyFile);
            }

        }
    }

    function suhosin_function_exists($func)
    {
        if (extension_loaded('suhosin')) {
            $suhosin = @ini_get("suhosin.executor.func.blacklist");
            if (empty($suhosin) == false) {
                $suhosin = explode(',', $suhosin);
                $suhosin = array_map('trim', $suhosin);
                $suhosin = array_map('strtolower', $suhosin);
                return (function_exists($func) == true && array_search($func, $suhosin) === false);
            }
        }
        return function_exists($func);
    }


    /**
     * #6615
     *
     * Copy layout files, for example from layouts/vlayout to layouts/v7
     *
     * @param $fromName layout name (vlayout)
     * @param $toName layout name (v7)
     */
    function copyLayoutFiles($fromName, $toName)
    {
        $from = "layouts/{$fromName}/modules/AdvancedReports/";
        $to = "layouts/{$toName}/modules/AdvancedReports/";

        $this->deepCopy($from, $to);
    }

    /**
     * #6615
     *
     * Recursively copy directory with contents
     *
     * @param $src - source directory
     * @param $dst - destination directory
     */
    function deepCopy($src, $dst)
    {
        $dir = opendir($src);
        @mkdir($dst);

        while(false !== ( $file = readdir($dir)) ) {
            if (( $file != '.' ) && ( $file != '..' )) {
                if ( is_dir($src . '/' . $file) ) {
                    $this->deepCopy($src . '/' . $file, $dst . '/' . $file);
                }
                else {
                    copy($src . '/' . $file, $dst . '/' . $file);
                }
            }
        }

        closedir($dir);
    }

    /**
     * Add global JS files to show dashboard menu
     */
    function addLinks()
    {
        Vtiger_Link::addLink(0, "HEADERSCRIPT", "Advanced Reports Dashboard Header Icon", 'modules/AdvancedReports/assets/js/dashboardlink.js');
    }

    /**
     * Remove global JS files to show dashboard menu
     */
    function removeLinks()
    {
        Vtiger_Link::deleteLink(0, "HEADERSCRIPT", "Advanced Reports Dashboard Header Icon",'modules/AdvancedReports/assets/js/dashboardlink.js');
    }

    // Add 'm' prefixes to all field defintions
    public function patchReports()
    {
        global $adb;

        $sql = "SELECT * FROM vtiger_advancedreports;";
        $result = $adb->query($sql);

        $binaryFields = array(
            "calculatedColumns",
            "columnstate",
            "calcFields",
            "combinedfields",
            "chart",
            "labels",
            "options",
            "totalAggregates",
            "aggregates",
            "grouping",
            "filters",
            "fields",
            "related_data"
        );

        while($row = $adb->fetch_row($result)) {
            // Decode from htmlentities and remove integer keys
            foreach($row as $key => &$value) {
                $value = html_entity_decode($value);
                if(is_numeric($key)) {
                    unset($row[$key]);
                }
            }

            $row = self::addPrefixToColumns($row);
            $id = $row["id"];
            $values = array();
            foreach ($row as $key => $value) {
                if(in_array($key, $binaryFields) && !empty($value)){
                    $value = self::toBinary($value);
                    $values[] = "`{$key}` = {$value}";
                }
            }
            $values = implode(", ", $values);
            $sql = "UPDATE vtiger_advancedreports SET {$values} WHERE id = {$id}";
            $adb->query($sql);
        }
    }

    static public function toBinary($data)
    {
        return '0x'.strtoupper(bin2hex($data));
    }

    protected static function addPrefixToColumns($object)
    {
        foreach($object as $index => &$item) {
            if($index === 'calculatedColumns'){
                continue;
            }
            if(is_string($item)) {
                // Special case for combined reports
                $item = str_replace('"00_', '"m00_', $item);

                foreach(range(0,50) as $i) {
                    // Replace "50_ to "m50_
                    $item = str_replace("\"{$i}_", "\"m{$i}_", $item);
                    // Replace #50_ to #m50_ (calculated fields customSQL formula)
                    $item = str_replace("#{$i}_", "#m{$i}_", $item);

                    // Replace aggregates
                    foreach(array("cnt", "count", "countDistinct", "avg", "min", "max", "sum", "sumDistinct") as $aggregate) {
                        $item = str_replace("\"{$aggregate}_{$i}_", "\"{$aggregate}_m{$i}_", $item);
                    }
                }
            }
        }

        return $object;
    }

}