<?php
class SupsysticTablesPro_Tables_Controller extends SupsysticTables_Tables_Controller
{
    public $table_path_num = 0;
    public $fileTypes = array(
        'png', 'jpeg', 'jpg', 'gif', 'ico', 'svg', 'bmp',
        'zip', '7z', 'rar', 'tar',
        'txt', 'pdf', 'html', 'csv', 'xls', 'xlsx', 'ppt', 'pptx', 'doc', 'docx'
    );

	public function saveEditableFieldsAction(RscDtgs_Http_Request $request) {
      if (!$this->_checkNonce($request) && !$this->_checkNonceFrontend($request)) die();
		if(!function_exists('date_create')) {
			return $this->ajaxError('You should to use PHP v.5.2.0 or greater to use the period feature for history table.');
		}
		$table_id = (int) $request->post->get('id');
		$cells = json_decode($request->post->get('cells'));
		$history = (int) $request->post->get('history');
		$period = $request->post->get('period');
		$isSSP = (bool) $request->post->get('isSSP');
		$isDB = (bool) $request->post->get('isDB');

		$period = !empty($period) ? date_create($period) : null;

		if($this->checkEditableFieldsAbility($table_id, $isDB)) {
			try {
				if($isSSP && $isDB || $isDB) {
                    $this->updateCellsInDBTable($table_id, $cells, json_decode($request->post->get('uniqFields')));
				} elseif($isSSP) {
                    $this->updateCellsFromFrontend($table_id, $cells);
				} else {
					$this->updateRowsFromFrontend($table_id, $cells, $history, $period);
				}
				$this->cleanCache($table_id);
			} catch (RuntimeException $e) {
				return $this->ajaxError($e->getMessage());
			} catch (Exception $e) {
				return $this->ajaxError($e->getMessage());
			}
			return $this->ajaxSuccess();
		}
		return $this->ajaxError('There is no permissions to save data through editable fields.');
	}

    public function saveEditableFieldsFileAction(RscDtgs_Http_Request $request) {
      if (!$this->_checkNonce($request) && !$this->_checkNonceFrontend($request)) die();
        $table_id = (int) $request->post->get('id');
        $cell = json_decode($request->post->get('cell'));
        $cellFile = $request->files->get('cellFile');

        $this->table_path_num = $table_id;

        if (!empty($cellFile)) {
            if ( ! function_exists( 'wp_handle_upload' ) ) {
                require_once(ABSPATH . 'wp-admin/includes/file.php');
            }

            add_filter('upload_dir', array($this, 'changeUploadPath'));
            add_filter('upload_mimes', array($this, 'changeUploadMimes'));

            $upload = wp_handle_upload($cellFile, array('test_form' => false));

            remove_filter('upload_dir', array($this, 'changeUploadPath'));
            remove_filter('upload_mimes', array($this, 'changeUploadMimes'));

            if($upload && empty($upload['error'])) {
                $mime = strpos($upload['type'], '/') ? explode('/', $upload['type']) : array($upload['type']);
                switch($mime[0]){
                    case 'image':
                        $message = '<img src="'. $upload['url']. '" data-path="'. $upload['file']. '" style="max-width:100%;height:auto;" alt="">';
                        break;
                    default:
                        $message = '<a href="'. $upload['url']. '" data-path="'. $upload['file']. '" onclick="window.open(this.href,\'_blank\'); return false;">'. $this->translate('Download'). '</a>';
                        break;
                }
                $message .= '<a href="#" class="delete-upload-file" title="'. $this->translate('Delete'). '">&times;</a>';
                return $this->ajaxSuccess(array('file'=>$upload,'message'=>$message));
            } else {
                return $this->ajaxError(array('file'=>$upload,'message'=>$this->translate('Upload failed! Please try again.')));
            }
        }

        return $this->ajaxError(array('message'=>$this->translate('Upload failed! Please try again.')));
    }

    public function deleteEditableFieldsFileAction(RscDtgs_Http_Request $request) {
      if (!$this->_checkNonce($request) && !$this->_checkNonceFrontend($request)) die();
        $table_id = (int) $request->post->get('id');
        $cell = json_decode($request->post->get('cell'));
        $fileSrc = $request->post->get('file_src');
        //$fileSrc = iconv('utf-8','windows-1251',$fileSrc);
        if ($fileSrc && file_exists($fileSrc)) {
            unlink($fileSrc);
            return $this->ajaxSuccess(array('file'=>$fileSrc,'message'=>'&nbsp;'));
        }

        return $this->ajaxError(array('file'=>$fileSrc,'message'=>$this->translate('File not deleted')));
    }

    public function changeUploadPath( $param ){
        $mydir = '/dtbl-upload-files';

        if ($this->table_path_num) {
            $mydir .= '/table-'. $this->table_path_num;
        }

        $param['path'] = $param['basedir'] . $mydir;
        $param['url'] = $param['baseurl'] . $mydir;

        return $param;
    }

    public function changeUploadMimes($mimes){
        $fileMimes = array(
            'svg' => 'image/svg+xml',
            'rar' => array(
                'application/x-rar-compressed',
                'application/x-rar'
            )
        );

        foreach ($fileMimes as $type => $mime){
            !is_array($mime) && $mime = array($mime);
            foreach ($mime as $single){
                $mimes[$type] = $single;
            }
        }

	    return $mimes;
    }

	public function updateCellsFromFrontend($table_id, $newCells) {
		$environment = $this->getEnvironment();
		$tablesModel = $this->getModel('tables');

		foreach($newCells as $newCell) {
			$x = (int)$newCell->c;
			$y = (int)$newCell->r;
			try {
				$rows = $tablesModel->getRowsWithId($table_id, 1, true, $y - 1);
			} catch (Exception $e) {
				throw new Exception(sprintf($this->translate('Failed to get table row: %s'), $e->getMessage()));
			};
			foreach($rows as $id => $row) {
				foreach($row['cells'] as $index => $cell) {
					if($index == $x && $cell['y'] == $y) {
						$row['cells'][$index]['data'] = $newCell->v;
						if(isset($newCell->ov)) {
							$row[$id]['cells'][$index]['calculatedValue'] = $newCell->ov;
						}
						try {
							$tablesModel->updateRow($id, $row);
						} catch (Exception $e) {
							throw new Exception(sprintf($this->translate('Failed to save table row: %s'), $e->getMessage()));
						}
					}
				}
			}
		}
	}

	public function updateCellsInDBTable($table_id, $cells, $uniqFields) {
		if(!isset($uniqFields) || !is_array($uniqFields) || count($uniqFields) == 0) {
			throw new Exception(sprintf($this->translate('request does not contain a unique index'), $cell->v));
		}

		$settings = $this->getModel('tables')->getSettings($table_id);
		$source = (isset($settings['source']) && isset($settings['source']['database']) && $settings['source']['database'] == 'on') ? $settings['source'] : '';
		$dbTable = isset($source['dbTable']) ? $source['dbTable'] : '';

		if($dbTable == 'SQL Query') {
			throw new Exception(sprintf($this->translate('can not edit data for SQL Query'), $cell->v));
		} else if(empty($dbTable)) {
			throw new Exception(sprintf($this->translate('table not found'), $cell->v));
		}

		$dbTablesModel = $this->getModel('DBTables', 'tables');

		$connected = $dbTablesModel->connectToExternalDB($source);
		if($connected !== true) {
			throw new Exception(sprintf($connected, $cell->v));
		}

		foreach($cells as $cell) {
			if(!isset($cell->f) || strlen($cell->f) == 0) {
				throw new Exception(sprintf($this->translate('request does not contain a table field'), $cell->v));
			}
			if(!isset($cell->u) || !is_array($cell->u) || count($cell->u) == 0) {
				throw new Exception(sprintf($this->translate('request does not contain data for a unique index'), $cell->v));
			}
			$field = "`{$cell->f}`";
			$uniq = $cell->u;
			try {
				$updated = $dbTablesModel->updateField($dbTable, $field, "{$cell->v}", $uniqFields, $uniq);
				if($updated == 0) {
					throw new Exception(sprintf($this->translate('value %s not saved'), $cell->v));
				}
			} catch (Exception $e) {
				throw new Exception(sprintf($this->translate('Failed to save table cell: %s'), $e->getMessage()));
			}
		}
	}

	public function updateRowsFromFrontend($table_id, $allData, $history, $period) {
		$environment = $this->getEnvironment();
		$tablesModel = $this->getModel('tables');
		$historyModel = $this->getModel('history', 'tables');
		$user = false;

		if($history) {
			$user = $environment->getModule('ui')->getCurrentUserInfo();
		}
		try {
			if($user && !empty($user->ID)) {
				$history = $historyModel->getUserTableHistory($user->ID, $table_id, $period);
				$rows = $history->data;
			} else {
				$rows = $tablesModel->getRows($table_id);
			}
		} catch (Exception $e) {
			throw new Exception(sprintf($this->translate('Failed to get table rows: %s'), $e->getMessage()));
		};
		if(is_array($rows) && count($rows) && is_array($allData) && count($allData)) {
			foreach($allData as $data) {
				foreach($rows as $key => $row) {
					if($key == ((int)$data->r) - 1) {	// Rows' numbers begin from 1
						if(isset($row['cells']) && !empty($row['cells'])) {
							foreach($row['cells'] as $index => $cell) {
								if($index == ((int)$data->c)) {
									$rows[$key]['cells'][$index]['data'] = $data->v;

									if(isset($data->fv) && !empty($data->v)) {
										$rows[$key]['cells'][$index]['formattedValue'] = $data->fv;
									} else if($rows[$key]['cells'][$index]['baseType'] == 'text') {
										$rows[$key]['cells'][$index]['formattedValue'] = $data->v;
									}
									if(isset($data->ov)) {
										$rows[$key]['cells'][$index]['calculatedValue'] = $data->ov;
									}
								}
							}
						}
					}
				}
			}
		}
		try {
			$tablesModel->transactionStart();
			if($user && !empty($user->ID)) {
				$historyModel->updateUserTableHistory($user->ID, $table_id, $rows, $period);
			} else {
				$tablesModel->setRows($table_id, $rows);
			}
			$tablesModel->transactionCommit();
		} catch (Exception $e) {
			$tablesModel->transactionRollback();
			throw new Exception(sprintf($this->translate('Failed to save table rows: %s'), $e->getMessage()));
		}
	}

	public function checkEditableFieldsAbility($id, $isDB = false)
	{
		$use_editable_fields = false;
		$logged_in_only = true;
		$environment = $this->getEnvironment();
		$tables = $this->getModel('tables');
		$table = $tables->getById($id);

		if ($table) {
			if ($isDB) {
				$source = (isset($table->settings['source']) ? $table->settings['source'] : '');
				$logged_in_only = isset($source['dbEditLoggedIn']) && $source['dbEditLoggedIn'] == 'on' ? true : false;
			} else {
				$logged_in_only = isset($table->settings['useEditableFieldsForLoggedInOnly']) ? $table->settings['useEditableFieldsForLoggedInOnly'] : false;
			}

			if (!$logged_in_only) {
				$use_editable_fields = true;
			} else {
				$is_logged_in = $environment->getModule('ui')->isUserLoggedIn();

				if ($is_logged_in) {
					if ($isDB) {
						$cur_roles_only = isset($source['dbEditRoles']) ? $source['dbEditRoles'] : false;
					} else {
						$cur_roles_only = isset($table->settings['useEditableFieldsForCurRoles']) ? $table->settings['useEditableFieldsForCurRoles'] : false;
					}

					if (!$cur_roles_only) {
						$use_editable_fields = true;
					} else {
						$cur_user_info = $environment->getModule('ui')->getCurrentUserInfo();

						if (!empty($cur_user_info->roles)) {
							foreach ($cur_user_info->roles as $role) {
								if (in_array($role, $cur_roles_only)) {
									$use_editable_fields = true;
									break;
								}
							}
						}
					}
				}
			}
		}
		return $use_editable_fields;
	}

	/**
	 * Save table history settings
	 * @param \RscDtgs_Http_Request $request
	 * @return \RscDtgs_Http_Response
	 */
	public function saveHistorySettingsAction(RscDtgs_Http_Request $request)
	{
      if (!$this->_checkNonce($request)) die();
		$id = $request->post->get('id');
		$data = $request->post->get('settings');
      
      if (!defined('PHP_VERSION_ID')) {
          $version = explode('.', PHP_VERSION);
          define('PHP_VERSION_ID', ($version[0] * 10000 + $version[1] * 100 + $version[2]));
      }
      if (PHP_VERSION_ID < 70400) {
         if (get_magic_quotes_gpc()) {
           $data = stripslashes($data);
        }
      }

		parse_str($data, $settings);

		try {
			$this->getEnvironment()->getModule('tables')->setIniLimits();
			$this->getModel('tables')->set($id, array('history_settings' => serialize($settings)));
		} catch (Exception $e) {
			return $this->ajaxError($e->getMessage());
		}

		$this->cleanCache($id);
		return $this->ajaxSuccess();
	}

	public function renderFromHistoryAction(RscDtgs_Http_Request $request)
	{
      if (!$this->_checkNonce($request) && !$this->_checkNonceFrontend($request)) die();
		$tables = $this->getEnvironment()->getModule('tables');
		$tables->useHistory = true;
		$tables->userId = (int) $request->post->get('user_id');
		$period = date_create($request->post->get('period'));
		$tables->historyData = array(
			'attr_period' => date_format($period, 'Y-m-d'),
			'period' => $period,
		);
		$table_id = (int) $request->post->get('table_id');
		$tables->setIniLimits();

		return $this->ajaxSuccess(array('table' => $tables->render($table_id)));
	}

	/**
	 * Returns the table conditions.
	 * @param RscDtgs_Http_Request $request
	 * @return RscDtgs_Http_Response
	 */
	public function getConditionsAction(RscDtgs_Http_Request $request)
	{
      if (!$this->_checkNonce($request) && !$this->_checkNonceFrontend($request)) die();
		/** @var SupsysticTablesPro_Tables_Model_Conditions $conditionModel */
		$conditionModel = $this->getModel('conditions', 'tables');
		$table_id = $request->post->get('table_id');

		try {
			$this->getEnvironment()->getModule('tables')->setIniLimits();

			return $this->ajaxSuccess(array(
				'conditions' => $conditionModel->getConditions($table_id)
			));
		} catch (Exception $e) {
			return $this->ajaxError($e->getMessage());
		}
	}

	/**
	 * Updates the table conditions.
	 * @param RscDtgs_Http_Request $request
	 * @return RscDtgs_Http_Response
	 */
	public function updateConditionsAction(RscDtgs_Http_Request $request)
	{
      if (!$this->_checkNonce($request) && !$this->_checkNonceFrontend($request)) die();
		/** @var SupsysticTablesPro_Tables_Model_Conditions $conditionModel */
		$conditionModel = $this->getModel('conditions', 'tables');
		$table_id = $request->post->get('table_id');
		$conditions = $request->post->get('conditions', array());

		if (null === $conditions) {
			$message = $this->translate('Can\'t decode table conditions from JSON.');

			if (function_exists('json_last_error')) {
				$message .= 'Error: ' . json_last_error();
			}
			return $this->ajaxError($message);
		}

		try {
			$this->getEnvironment()->getModule('tables')->setIniLimits();

			$conditionModel->setConditions($table_id, $conditions);
		} catch (Exception $e) {
			return $this->ajaxError(sprintf($this->translate('Failed to save table conditions: %s'), $e->getMessage()));
		}

		return $this->ajaxSuccess();
	}
	/**
	 * Returns db tables.
	 * @param RscDtgs_Http_Request $request
	 * @return RscDtgs_Http_Response
	 */
	public function getDBTablesAction(RscDtgs_Http_Request $request)
	{
		$dbName = $request->post->get('dbName');
        $dbExternalHost = $request->post->get('dbExternalHost');
		$dbExternal = $request->post->get('dbExternal');
		$dbLogin = $request->post->get('dbLogin');
		$dbPassword = $request->post->get('dbPassword');

		$this->getEnvironment()->getModule('tables')->setIniLimits();
		$dbTablesModel = $this->getModel('DBTables', 'tables');

		try {
			$connected = $dbTablesModel->connectToExternalDB(array('dbExternalHost' => $dbExternalHost, 'dbName' => $dbName, 'dbExternal' => $dbExternal, 'dbLogin' => $dbLogin, 'dbPassword' => $dbPassword));
			if($connected !== true) {
				return $this->ajaxError($connected);
			}
			return $this->ajaxSuccess(array('tables' => $dbTablesModel->getDBTables()));

		} catch (Exception $e) {
			return $this->ajaxError($e->getMessage());
		}
	}
	/**
	 * Returns dbTable fields.
	 * @param RscDtgs_Http_Request $request
	 * @return RscDtgs_Http_Response
	 */
	public function getDBTableFieldsAction(RscDtgs_Http_Request $request)
	{
		$dbName = $request->post->get('dbName');
        $dbExternalHost = $request->post->get('dbExternalHost');
		$dbTable = $request->post->get('dbTable');
		$dbExternal = $request->post->get('dbExternal');
		$dbLogin = $request->post->get('dbLogin');
		$dbPassword = $request->post->get('dbPassword');

		$dbTablesModel = $this->getModel('DBTables', 'tables');

		try {
			$connected = $dbTablesModel->connectToExternalDB(array('dbExternalHost' => $dbExternalHost, 'dbName' => $dbName, 'dbExternal' => $dbExternal, 'dbLogin' => $dbLogin, 'dbPassword' => $dbPassword));
			if($connected !== true) {
				return $this->ajaxError($connected);
			}
			return $this->ajaxSuccess(array('fields' => $dbTablesModel->getFields($dbTable)));

		} catch (Exception $e) {
			return $this->ajaxError($e->getMessage());
		}
	}
	/**
	 * Updates the table conditions.
	 * @param RscDtgs_Http_Request $request
	 * @return RscDtgs_Http_Response
	 */
	public function sendTableToEmailAction(RscDtgs_Http_Request $request)
	{
		$table = $request->post->get('table');
		$tableId = $request->post->get('tableId');

		$success = false;
		if (!empty($table)) {
			//$table = base64_decode(urldecode($table));
			$adminEmail = get_option('admin_email');
			if (!empty($adminEmail)) {
				$to = $adminEmail;
				$subject = get_bloginfo('name');
				$subject = $subject.': '.$this->translate('New letter from frontend table ID:');
				$subject = $subject.' '.$tableId;
				$message = $table;
				$headers = array(
					'content-type: text/html',
				);
				$success = wp_mail($to, $subject, $message, $headers);
			}
		}
		if ($success) {
			return $this->ajaxSuccess(array('answer' => $this->translate('Email sent successfully.')));
		} else {
			return $this->ajaxError($this->translate('Email not sended.'));
		}
	}

	/**
	 * Returns DBData for diagram.
	 * @param RscDtgs_Http_Request $request
	 * @return RscDtgs_Http_Response
	 */
	public function getRangeDBDataAction(RscDtgs_Http_Request $request)
	{
		$settings = array('source' => array(
			'dbName' => $request->post->get('dbName'),
			'dbTable' => $request->post->get('dbTable'),
			'dbExternal' => $request->post->get('dbExternal'),
			'dbLogin' => $request->post->get('dbLogin'),
			'dbPassword' => $request->post->get('dbPassword'),
			'dbSQL' => $request->post->get('dbSQL'),
			'dbFields' => $request->post->get('dbFields')));
		$range = $request->post->get('range');
		if(!isset($range['from']) || !isset($range['to'])){
			return $this->ajaxError($this->translate('Range is required'));
		}

		$dbTablesModel = $this->getModel('DBTables', 'tables');

		try {
			$rows = $dbTablesModel->getRowsData($settings);
			$rowFrom = isset($range['from']['row']) ? $range['from']['row'] : 0;
			$rowTo = isset($range['to']['row']) ? $range['to']['row'] : 0;
			$colFrom = isset($range['from']['col']) ? $range['from']['col'] : 0;
			$colTo = isset($range['to']['col']) ? $range['to']['col'] : 0;

			$rangeData = array();
			for($i = $rowFrom; $i <= $rowTo; $i++) {
				$data = array();

				if(isset($rows[$i])) {
					for($j = $colFrom; $j <= $colTo; $j++) {
						if(isset($rows[$i]['cells'][$j])) {
							$data[] = is_null($rows[$i]['cells'][$j]['data']) ? '' : $rows[$i]['cells'][$j]['data'];
						}
					}
					$rangeData[] = $data;
				}
			}
			return $this->ajaxSuccess(array('rangeData' => $rangeData));

		} catch (Exception $e) {
			return $this->ajaxError($e->getMessage());
		}
	}
}
