<?php
class SupsysticTablesPro_Tables_Controller extends SupsysticTables_Tables_Controller
{
	public function saveEditableFieldsAction(Rsc_Http_Request $request) {
		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) {
					$this->updateCellsFromFrontend($table_id, $cells);
				} elseif($isDB) {
					$this->updateCellsInDBTable($table_id, $cells, json_decode($request->post->get('uniqFields')));
				} 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 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)) {
										$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 \Rsc_Http_Request $request
	 * @return \Rsc_Http_Response
	 */
	public function saveHistorySettingsAction(Rsc_Http_Request $request)
	{
		$id = $request->post->get('id');
		$data = $request->post->get('settings');
		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(Rsc_Http_Request $request)
	{
		$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 Rsc_Http_Request $request
	 * @return Rsc_Http_Response
	 */
	public function getConditionsAction(Rsc_Http_Request $request)
	{
		/** @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 Rsc_Http_Request $request
	 * @return Rsc_Http_Response
	 */
	public function updateConditionsAction(Rsc_Http_Request $request)
	{
		/** @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 Rsc_Http_Request $request
	 * @return Rsc_Http_Response
	 */
	public function getDBTablesAction(Rsc_Http_Request $request)
	{
		$dbName = $request->post->get('dbName');
		$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('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 Rsc_Http_Request $request
	 * @return Rsc_Http_Response
	 */
	public function getDBTableFieldsAction(Rsc_Http_Request $request)
	{
		$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');

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

		try {
			$connected = $dbTablesModel->connectToExternalDB(array('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());
		}
	}

	/**
	 * Returns DBData for diagram.
	 * @param Rsc_Http_Request $request
	 * @return Rsc_Http_Response
	 */
	public function getRangeDBDataAction(Rsc_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());
		}
	}
}