<?php
/**
 * @package     BreezingForms
 * @author      Markus Bopp
 * @link        http://www.crosstec.de
 * @license     GNU/GPL
*/

// TODO: uninstall routine

// no direct access
defined( '_JEXEC' ) or die( 'Restricted access' );

if(!defined('DS')){
    define('DS', DIRECTORY_SEPARATOR);
}

jimport( 'joomla.plugin.plugin' );
jimport('joomla.version');
               
class plgBreezingforms_addonsGdata extends JPlugin
{

    function __construct( &$subject, $params )
    {
        parent::__construct($subject, $params);
    
        $lang = JFactory::getLanguage();
        $lang->load('plg_breezingforms_addons_gdata', JPATH_ADMINISTRATOR);
        
        $db = JFactory::getDBO();
        
        $tables = $db->getTableList();
        
        if( !in_array( $db->getPrefix().'breezingforms_addons_gdata', $tables ) ){
            $db->setQuery("CREATE TABLE `#__breezingforms_addons_gdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL DEFAULT '0',
  `username` varchar(255) NOT NULL DEFAULT '',
  `password` varchar(255) NOT NULL DEFAULT '',
  `enabled` tinyint(1) NOT NULL DEFAULT '0',
  `spreadsheet_id` varchar(255) NOT NULL DEFAULT '',
  `worksheet_id` varchar(255) NOT NULL DEFAULT '',
  `fields` text NOT NULL,
  `meta` varchar(255) NOT NULL,
  `debug` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `form_id_2` (`form_id`),
  KEY `form_id` (`form_id`,`spreadsheet_id`,`worksheet_id`)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1");
            $db->query();
        }
    }
    
    function onPropertiesDisplay($form_id, $tabs){
        
        if(!$form_id) return '';
        
        $error = '';
        
        $db = JFactory::getDBO();
        
        $db->setQuery("Select `title`,`name` From #__facileforms_elements Where form = " . intval($form_id) . " And `title` Not In ('bfFakeTitle','bfFakeTitle2','bfFakeTitle3','bfFakeTitle4','bfFakeTitle5') Order By ordering");
        $breezingforms_fields = $db->loadObjectList();
        
        $db->setQuery("Select `enabled`, `username`, `password`, `worksheet_id`, `spreadsheet_id`, `fields`, `meta`, `debug` From #__breezingforms_addons_gdata Where form_id = " . intval($form_id));
        $gdata = $db->loadObject();
        
        if( $gdata === null ){
            $gdata = new stdClass();
            $gdata->username = '';
            $gdata->password = '';
            $gdata->enabled = 0;
            $gdata->worksheet_id = '';
            $gdata->spreadsheet_id = '';
            $gdata->fields = '';
            $gdata->meta = '';
            $gdata->debug = 0;
        }
        
        $gdata->fields = explode('/,/', $gdata->fields);
        $gdata->meta   = explode('/,/', $gdata->meta);
        
        $gdata_spreadsheets = array();
        $gdata_worksheets = array();
        $gdata_columns = array();
        
        if( $gdata->enabled == 1 ){
            
            try{
            
                $version = new JVersion();
                if(version_compare($version->getShortVersion(), '1.6', '<')){
                    set_include_path(JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'breezingforms_addons_gdata_libraries' . PATH_SEPARATOR . get_include_path());
                    require_once JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'breezingforms_addons_gdata_libraries' . DS . 'Zend' . DS . 'Loader.php';
                }else{
                    set_include_path(JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'gdata' . DS . 'breezingforms_addons_gdata_libraries' . PATH_SEPARATOR . get_include_path());
                    require_once JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'gdata' . DS . 'breezingforms_addons_gdata_libraries' . DS . 'Zend' . DS . 'Loader.php';
                    
                }
                
                Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
                Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

                $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
                $client = Zend_Gdata_ClientLogin::getHttpClient($gdata->username, $gdata->password, $service);
                $spreadsheetService = new Zend_Gdata_Spreadsheets($client);
                $sheets = $spreadsheetService->getSpreadsheetFeed();

                foreach($sheets As $sheet){
                    $spreadsheet_id = explode('/',$sheet->getId()->getText());
                    if(isset($spreadsheet_id[count($spreadsheet_id)-1])){
                        $spreadsheet_id = $spreadsheet_id[count($spreadsheet_id)-1];
                        $gdata_spreadsheets[$spreadsheet_id] = $sheet->getTitleValue();
                    }
                }

                if($gdata->spreadsheet_id != ''){

                    $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
                    $query->setSpreadsheetKey($gdata->spreadsheet_id);
                    $sheets = $spreadsheetService->getWorksheetFeed($query);
                    
                    foreach ( $sheets->entries as $sheet ){
                        $worksheet_id = explode('/',$sheet->getId()->getText());
                        if(isset($worksheet_id[count($worksheet_id)-1])){
                            $worksheet_id = $worksheet_id[count($worksheet_id)-1];
                            $gdata_worksheets[$worksheet_id] = $sheet->getTitleValue();
                        }
                    }
                    
                    if($gdata->worksheet_id != ''){
                        
                        $query = new Zend_Gdata_Spreadsheets_CellQuery();
                        $query->setSpreadsheetKey($gdata->spreadsheet_id);
                        $query->setWorksheetId($gdata->worksheet_id);
                        $query->setMaxRow(1);
                        $cellFeed = $spreadsheetService->getCellFeed($query);
                        
                        foreach($cellFeed as $cellEntry){
                            $gdata_columns[] = $cellEntry->getCell()->getText();
                        }
                    }
                }
            
            } catch(Exception $e){
                
                $error = $e->getMessage();
            }
        }
        
        ob_start();
        $version = new JVersion();
        if(version_compare($version->getShortVersion(), '1.6', '<')){
            require_once JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'breezingforms_addons_gdata_tmpl' . DS . 'properties.php';

        }else{
            require_once JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'gdata' . DS . 'breezingforms_addons_gdata_tmpl' . DS . 'properties.php';
        }
        $c = ob_get_contents();
        ob_end_clean();
        return $c;
    }
    
    function onPropertiesSave($form_id){
        
        if(!$form_id) return '';
        
        if(isset($_POST['gdata_fields']) && is_array($_POST['gdata_fields'])){
            $_POST['gdata_fields'] = implode('/,/', $_POST['gdata_fields']);
        }else{
            $_POST['gdata_fields'] = '';
        }
        
        if(isset($_POST['gdata_meta']) && is_array($_POST['gdata_meta'])){
            $_POST['gdata_meta'] = implode('/,/', $_POST['gdata_meta']);
        }else{
            $_POST['gdata_meta'] = '';
        }
        
        $db = JFactory::getDBO();
        
        $db->setQuery("Select form_id From #__breezingforms_addons_gdata Where form_id = " . intval($form_id));
        $exists = $db->loadResult();
        
        if(!$exists){
            $db->setQuery("Insert Into #__breezingforms_addons_gdata (
                `form_id`, `enabled`,`username`,`password`,`spreadsheet_id`,`worksheet_id`,`fields`,`meta`) Values 
                (   ".intval($form_id).",
                    ".JRequest::getInt('gdata_enabled', 0).",
                    ".$db->quote(JRequest::getVar('gdata_username', '')).",
                    ".$db->quote(JRequest::getVar('gdata_password', '')).",
                    ".$db->quote(JRequest::getVar('gdata_spreadsheet_id', '')).",
                    ".$db->quote(JRequest::getVar('gdata_worksheet_id', '')).",
                    ".$db->quote($_POST['gdata_fields']).",
                    ".$db->quote($_POST['gdata_meta'])."
                )");
            $db->query();
        } else {
            $db->setQuery("Update #__breezingforms_addons_gdata Set
                `enabled`  = ".JRequest::getInt('gdata_enabled', 0).",
                `username` = ".$db->quote(JRequest::getVar('gdata_username', '')).",
                `password` = ".$db->quote(JRequest::getVar('gdata_password', '')).",
                `spreadsheet_id` = ".$db->quote(JRequest::getVar('gdata_spreadsheet_id', '')).",
                `worksheet_id` = ".$db->quote(JRequest::getVar('gdata_worksheet_id', '')).",
                `fields` = ".$db->quote($_POST['gdata_fields']).",
                `meta` = ".$db->quote($_POST['gdata_meta'])."
            ");
            $db->query();
        }
        
        
        if(JRequest::getVar('gdata_synch', 0) == 1){
            
            $db = JFactory::getDBO();
        
            $db->setQuery("Select `enabled`, `username`, `password`, `worksheet_id`, `spreadsheet_id`, `fields`, `meta`, `debug` From #__breezingforms_addons_gdata Where form_id = " . intval($form_id));
            $gdata = $db->loadObject();

            if( $gdata === null ){
                $gdata = new stdClass();
                $gdata->username = '';
                $gdata->password = '';
                $gdata->enabled = 0;
                $gdata->worksheet_id = '';
                $gdata->spreadsheet_id = '';
                $gdata->fields = '';
                $gdata->meta = '';
                $gdata->debug = 0;
            }

            $gdata->fields = explode('/,/', $gdata->fields);
            $gdata->meta = explode('/,/', $gdata->meta);

            if( $gdata->enabled == 1 ){

                try{
                    
                    $version = new JVersion();
                    if(version_compare($version->getShortVersion(), '1.6', '<')){
                        set_include_path(JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'breezingforms_addons_gdata_libraries' . PATH_SEPARATOR . get_include_path());
                        require_once JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'breezingforms_addons_gdata_libraries' . DS . 'Zend' . DS . 'Loader.php';
                    }else{
                        set_include_path(JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'gdata' . DS . 'breezingforms_addons_gdata_libraries' . PATH_SEPARATOR . get_include_path());
                        require_once JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'gdata' . DS . 'breezingforms_addons_gdata_libraries' . DS . 'Zend' . DS . 'Loader.php';
                        
                    }
                    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
                    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
                    
                    $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
                    $client = Zend_Gdata_ClientLogin::getHttpClient($gdata->username, $gdata->password, $service);
                    $spreadsheetService = new Zend_Gdata_Spreadsheets($client);

                    $bffields = array();
                    $gdatafields = array();
                    $gdatameta = array();
                    $bfmeta = array();

                    // meta

                    $meta_fields = array();
                    $meta_fields[0] = new stdClass();
                    $meta_fields[0]->title = 'Record ID';

                    $meta_fields[1] = new stdClass();
                    $meta_fields[1]->title = 'Form ID';

                    $meta_fields[2] = new stdClass();
                    $meta_fields[2]->title = 'Form Title';

                    $meta_fields[3] = new stdClass();
                    $meta_fields[3]->title = 'Form Name';

                    $meta_fields[4] = new stdClass();
                    $meta_fields[4]->title = 'Timestamp';

                    $meta_fields[5] = new stdClass();
                    $meta_fields[5]->title = 'Date';

                    $meta_fields[6] = new stdClass();
                    $meta_fields[6]->title = 'Time';

                    $meta_fields[7] = new stdClass();
                    $meta_fields[7]->title = 'IP';

                    $meta_fields[8] = new stdClass();
                    $meta_fields[8]->title = 'USER AGENT';

                    $meta_fields[9] = new stdClass();
                    $meta_fields[9]->title = 'User ID';

                    $meta_fields[10] = new stdClass();
                    $meta_fields[10]->title = 'Username';

                    $meta_fields[11] = new stdClass();
                    $meta_fields[11]->title = 'User Fullname';

                    foreach($gdata->meta As $fields){
                        $field = explode('::', $fields);
                        if(isset($field[1])){
                            $bfmeta[]    = $field[0];
                            $gdatameta[$field[0]] = $field[1];
                        }
                    }
                    
                    foreach($gdata->fields As $fields){
                        $field = explode('::', $fields);
                        if(isset($field[1])){
                            $bffields[]    = $field[0];
                            $gdatafields[$field[0]] = $field[1];
                        }
                    }

                    // record start
                    
                    require_once(JPATH_SITE . '/administrator/components/com_breezingforms/libraries/Zend/Json/Decoder.php');
                    require_once(JPATH_SITE . '/administrator/components/com_breezingforms/libraries/Zend/Json/Encoder.php');
                    $db->setQuery("Select template_areas, template_code_processed From #__facileforms_forms Where id = " . intval($form_id));
                    $formrow = $db->loadObject();
                    $areas = Zend_Json::decode($formrow->template_areas);
                    
                    $db->setQuery("Select * From #__facileforms_records Where form = " . intval($form_id) . " Order By submitted");
                    $records = $db->loadAssocList();
                    
                    foreach($records As $record){
                    
                        $rowData = array();

                        foreach($meta_fields As $data){
                            if( in_array($data->title, $bfmeta) ){
                                $value = '';
                                switch($data->title){
                                    case 'Record ID'; $value = $record['id'];break;
                                    case 'Form ID'; $value = $record['form'];break;
                                    case 'Form Title'; $value = $record['title'];break;
                                    case 'Form Name'; $value = $record['name'];break;
                                    case 'Timestamp'; $value = $record['submitted'];break;
                                    case 'Date'; $value = date('Y-m-d', strtotime($record['submitted']));break;
                                    case 'Time'; $value = date('H:i:s', strtotime($record['submitted']));break;
                                    case 'IP'; $value = $record['ip'];break;
                                    case 'USER AGENT'; $value = $record['browser'];break;
                                    case 'User ID'; $value = $record['user_id'];break;
                                    case 'Username'; $value = $record['username'];break;
                                    case 'User Fullname'; $value = $record['user_full_name'];break;
                                }
                                $rowData[$gdatameta[$data->title]] = $value;
                            }
                        }

                        // fields

                        $db->setQuery("Select * From #__facileforms_subrecords Where record = " . intval($record['id']) . " Order By id");
                        $subrecords = $db->loadAssocList();
                        $subs = array();
                        $uploads = array();
                        foreach($subrecords As $subrecord){
                            $subs[$subrecord['title']][] = $subrecord['value'];
                            if( $subrecord['type'] == 'File Upload' ){
                                $uploads[$subrecord['title']] = $subrecord['value'];
                            }
                        }
                        
                        foreach($subs As $title => $value){
                            if( in_array($title, $bffields) ){
                                if(isset($uploads[$title])){
                                    $useUrl = false;
                                    $useUrlDownloadDirectory = '';
                                    if (trim($formrow->template_code_processed) == 'QuickMode' && is_array($areas)) {
                                        foreach ($areas As $area) { // don't worry, size is only 1 in QM
                                            if (isset($area['elements'])) {
                                                foreach ($area['elements'] As $element) {
                                                    if (isset($element['options']) && isset($element['options']['useUrl']) && isset($element['name']) && trim($element['title']) == trim($title) && isset($element['internalType']) && $element['internalType'] == 'bfFile') {
                                                        $useUrl = $element['options']['useUrl'];
                                                        $useUrlDownloadDirectory = $element['options']['useUrlDownloadDirectory'];
                                                        break;
                                                    }
                                                }
                                            }
                                            break; // just in case
                                        }
                                    }
                                    if($uploads[$title] != '' && $useUrl && $useUrlDownloadDirectory != ''){
                                        $value = '';
                                        $files = explode("\n", $uploads[$title]);
                                        echo $uploads[$title];
                                        foreach($files As $file){
                                           $value .= $useUrlDownloadDirectory . '/' . basename($file) . "\n";
                                        }
                                        $rowData[$gdatafields[$title]] = $value;
                                    }else{
                                        $rowData[$gdatafields[$title]] = implode(', ',$value);
                                    }
                                }else{
                                    $rowData[$gdatafields[$title]] = implode(', ',$value);
                                }
                            }
                        }

                        $insertedListEntry = $spreadsheetService->insertRow($rowData,
                                                            $gdata->spreadsheet_id,
                                                            $gdata->worksheet_id);
                    
                    }
                    // record end
                    
                }catch(Exception $e){}
            }
        }
        //exit;
    }
    
    function onPropertiesExecute($processor){
        
        if(!$processor->form) return;
        
        $db = JFactory::getDBO();
        
        $db->setQuery("Select `enabled`, `username`, `password`, `worksheet_id`, `spreadsheet_id`, `fields`, `meta`, `debug` From #__breezingforms_addons_gdata Where form_id = " . intval($processor->form));
        $gdata = $db->loadObject();
        
        if( $gdata === null ){
            $gdata = new stdClass();
            $gdata->username = '';
            $gdata->password = '';
            $gdata->enabled = 0;
            $gdata->worksheet_id = '';
            $gdata->spreadsheet_id = '';
            $gdata->fields = '';
            $gdata->meta = '';
            $gdata->debug = 0;
        }
        
        $gdata->fields = explode('/,/', $gdata->fields);
        $gdata->meta = explode('/,/', $gdata->meta);
        
        if( $gdata->enabled == 1 ){
            
            try{
            
                $version = new JVersion();
                if(version_compare($version->getShortVersion(), '1.6', '<')){
                    set_include_path(JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'breezingforms_addons_gdata_libraries' . PATH_SEPARATOR . get_include_path());
                    require_once JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'breezingforms_addons_gdata_libraries' . DS . 'Zend' . DS . 'Loader.php';
                }else{
                    set_include_path(JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'gdata' . DS . 'breezingforms_addons_gdata_libraries' . PATH_SEPARATOR . get_include_path());
                    require_once JPATH_SITE . DS . 'plugins' . DS . 'breezingforms_addons' . DS . 'gdata' . DS . 'breezingforms_addons_gdata_libraries' . DS . 'Zend' . DS . 'Loader.php';
                    
                }
                
                Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
                Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

                $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
                $client = Zend_Gdata_ClientLogin::getHttpClient($gdata->username, $gdata->password, $service);
                $spreadsheetService = new Zend_Gdata_Spreadsheets($client);
                
                $rowData = array();
                $bffields = array();
                $gdatafields = array();
                $gdatameta = array();
                $bfmeta = array();
                
                // meta
                
                $meta_fields = array();
                $meta_fields[0] = new stdClass();
                $meta_fields[0]->title = 'Record ID';

                $meta_fields[1] = new stdClass();
                $meta_fields[1]->title = 'Form ID';

                $meta_fields[2] = new stdClass();
                $meta_fields[2]->title = 'Form Title';

                $meta_fields[3] = new stdClass();
                $meta_fields[3]->title = 'Form Name';

                $meta_fields[4] = new stdClass();
                $meta_fields[4]->title = 'Timestamp';

                $meta_fields[5] = new stdClass();
                $meta_fields[5]->title = 'Date';

                $meta_fields[6] = new stdClass();
                $meta_fields[6]->title = 'Time';

                $meta_fields[7] = new stdClass();
                $meta_fields[7]->title = 'IP';

                $meta_fields[8] = new stdClass();
                $meta_fields[8]->title = 'USER AGENT';

                $meta_fields[9] = new stdClass();
                $meta_fields[9]->title = 'User ID';

                $meta_fields[10] = new stdClass();
                $meta_fields[10]->title = 'Username';

                $meta_fields[11] = new stdClass();
                $meta_fields[11]->title = 'User Fullname';
                
                foreach($gdata->meta As $fields){
                   $field = explode('::', $fields);
                   if(isset($field[1])){
                       $bfmeta[]    = $field[0];
                       $gdatameta[$field[0]] = $field[1];
                   }
                }
                
                foreach($meta_fields As $data){
                    if( in_array($data->title, $bfmeta) ){
                        $value = '';
                        switch($data->title){
                            case 'Record ID'; $value = $processor->record_id;break;
                            case 'Form ID'; $value = $processor->form;break;
                            case 'Form Title'; $value = $processor->formrow->title;break;
                            case 'Form Name'; $value = $processor->formrow->name;break;
                            case 'Timestamp'; $value = $processor->submitted;break;
                            case 'Date'; $value = date('Y-m-d', strtotime($processor->submitted));break;
                            case 'Time'; $value = date('H:i:s', strtotime($processor->submitted));break;
                            case 'IP'; $value = $processor->ip;break;
                            case 'USER AGENT'; $value = $processor->browser;break;
                            case 'User ID'; $value = JFactory::getUser()->get('id', 0);break;
                            case 'Username'; $value = JFactory::getUser()->get('username', '');break;
                            case 'User Fullname'; $value = JFactory::getUser()->get('name', '');break;
                        }
                        $rowData[$gdatameta[$data->title]] = $value;
                    }
                }
                
                // fields
                
                foreach($gdata->fields As $fields){
                   $field = explode('::', $fields);
                   if(isset($field[1])){
                       $bffields[]    = $field[0];
                       $gdatafields[$field[0]] = $field[1];
                   }
                }
                
                foreach($processor->maildata As $data){
                    if( in_array($data[_FF_DATA_TITLE], $bffields) ){
                        $rowData[$gdatafields[$data[_FF_DATA_TITLE]]] = $data[_FF_DATA_VALUE];
                    }
                }
                
                $insertedListEntry = $spreadsheetService->insertRow($rowData,
                                                    $gdata->spreadsheet_id,
                                                    $gdata->worksheet_id);
                
            } catch(Exception $e){
                //if($gdata->debug){
                //    echo $e->getMessage();
                //}
            }
        }
    }
}
