protected function save_one_cell($sheet, $cell, $processed = array())
{
$coord = SpreadSheetCell::getColName($cell['col']) . $cell['row'];
if ($this->is_math()) {
if (isset($processed[$coord])) {
$cell['calc'] = '#CIRC_REFERENCE';
} else {
$cell = $this->calculateValue($cell);
}
} else {
$cell['calc'] = $cell['value'];
}
/*! selection action type
*/
if ($cell['value'] == '' && $cell['style'] == '') {
// delete cell from database if its value is empty
$status = "deleted";
} else {
// if the same cell already save in DB we have to update it, have to insert it otherwise
$res = $this->wrapper->query("SELECT * FROM {$this->db_prefix}data WHERE sheetid='{$sheet}' AND columnid='{$cell['col']}' AND rowid='{$cell['row']}'");
$result = $this->wrapper->next($res);
$status = $result != false ? "updated" : "inserted";
}
// action running
switch ($status) {
case 'inserted':
$res = $this->wrapper->query("INSERT INTO {$this->db_prefix}data (sheetid, columnid, rowid, data, style, parsed, calc) VALUES ('{$sheet}', {$cell['col']}, {$cell['row']}, '{$cell['value']}', '{$cell['style']}', '{$cell['parsed']}', '{$cell['calc']}')");
break;
case 'updated':
$res = $this->wrapper->query("UPDATE {$this->db_prefix}data SET data='{$cell['value']}', parsed='{$cell['parsed']}', calc='{$cell['calc']}', style='{$cell['style']}' WHERE sheetid='{$sheet}' AND columnid='{$cell['col']}' AND rowid='{$cell['row']}'");
break;
case 'deleted':
$res = $this->wrapper->query("DELETE FROM {$this->db_prefix}data WHERE sheetid='{$sheet}' AND columnid='{$cell['col']}' AND rowid='{$cell['row']}'");
break;
}
$response = array();
if (!$this->is_math()) {
$cell['calc'] = $cell['value'];
}
if ($res) {
$response[] = $this->driver->success_response($cell['row'], $cell['col'], $cell['value'], $cell['calc']);
} else {
$response[] = $this->driver->error_response($cell['row'], $cell['col']);
}
if ($cell['calc'] === '#CIRC_REFERENCE') {
return $response;
}
$processed[$coord] = true;
$query = "SELECT * FROM {$this->db_prefix}triggers WHERE `trigger`='{$coord}'";
$result = $this->wrapper->query($query);
$triggers = array();
while ($trigger = $this->wrapper->next($result)) {
$triggers[] = $trigger['source'];
}
for ($i = 0; $i < count($triggers); $i++) {
$coords = SpreadSheetCell::parse_coord($triggers[$i]);
$query = "SELECT * FROM {$this->db_prefix}data WHERE `sheetid`='{$sheet}' AND `rowid`='{$coords['row']}' AND `columnid`={$coords['col']}";
$res = $this->wrapper->query($query);
$obj = $this->wrapper->next($res);
$cell = array("row" => $obj['rowid'], "col" => $obj['columnid'], "value" => $obj['data'], "style" => $obj['style'], "parsed" => $obj['parsed']);
$response = array_merge($response, $this->save_one_cell($sheet, $cell, $processed));
}
return $response;
}
public function replaceCellsCallback($matches)
{
// LOG10 is a function, not a cell!
if ($matches[0] == 'LOG10') {
return 'LOG10';
}
$coord = str_replace(':', '', $matches[1]);
$coord = str_replace('$', '', $matches[1]);
$cell = new SpreadSheetCell($this->connection, $this->sheetid, $coord, $this->prefix, $this->db_type);
$value = $cell->getCalculatedValue();
if ($value === null) {
$value = '0';
}
if ($value === '#CIRC_REFERENCE') {
$value = '0';
}
return $value . $matches[2];
}
protected function save_cell()
{
$rows = $this->request->get("rows");
$cols = $this->request->get("cols");
$values = $this->request->get("values");
$styles = $this->request->get("styles");
$responses = array();
for ($i = 0; $i < count($rows); $i++) {
$cell = $this->api->getCell(SpreadSheetCell::getColName($cols[$i]) . $rows[$i]);
$cell->setStyle($styles[$i]);
$r = $cell->setValue($values[$i]);
$responses = array_merge($responses, $r);
}
$result = array();
foreach ($responses as $v) {
if ($v['status']) {
$result[] = $this->driver->success_response($v['row'], $v['col'], $v['value'], $this->is_math() ? $v['calc'] : $v['value']);
} else {
$result[] = $this->driver->error_response($v['row'], $v['col']);
}
}
// sending response
$response = $this->driver->get_start_response() . join($this->driver->separator(), $result) . $this->driver->get_end_response();
$this->out($response);
return true;
}
public function parse_coord($coord)
{
if (is_array($coord)) {
if (isset($coord[0])) {
$row = $coord[0];
}
if (isset($coord[1])) {
$col = $coord[1];
}
if (isset($coord['r'])) {
$row = $coord['r'];
}
if (isset($coord['row'])) {
$row = $coord['row'];
}
if (isset($coord['rowid'])) {
$row = $coord['rowid'];
}
if (isset($coord['c'])) {
$col = $coord['c'];
}
if (isset($coord['col'])) {
$col = $coord['col'];
}
if (isset($coord['column'])) {
$col = $coord['column'];
}
if (isset($coord['columnid'])) {
$col = $coord['columnid'];
}
if (isset($coord['cLetter'])) {
$colLetter = $coord['cLetter'];
} else {
if (isset($coord['colLetter'])) {
$colLetter = $coord['colLetter'];
} else {
if (isset($coord['columnLetter'])) {
$colLetter = $coord['columnLetter'];
}
}
}
if (!isset($col) && !isset($colLetter)) {
return false;
}
if (!isset($col)) {
$col = SpreadSheetCell::getColIndex($colLetter);
}
if (!isset($colLetter)) {
$colLetter = SpreadSheetCell::getColName($col);
} else {
SpreadSheetCell::getColName($col);
}
return array('col' => $col, 'row' => $row, 'colLetter' => $colLetter);
}
preg_match("/^([a-z]+)([0-9]+)\$/i", $coord, $coords);
if (count($coords) != 3) {
return false;
}
$colLetter = $coords[1];
$row = $coords[2];
$col = SpreadSheetCell::getColIndex($colLetter);
return array('col' => $col, 'row' => $row, 'colLetter' => $colLetter);
}
