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;
}
