1 <?php
2
3 require_once(drupal_get_path('module', 'sheetnode') . '/socialcalc.inc');
4
5 define('SHEETNODE_EXPANSION_VERTICAL', 0);
6 define('SHEETNODE_EXPANSION_HORIZONTAL', 1);
7
8 class sheetnode_plugin_style extends views_plugin_style {
9 function option_definition() {
10 $options = parent::option_definition();
11 $options['expansion'] = array('default' => SHEETNODE_EXPANSION_VERTICAL);
12 $options['template'] = array('default' => FALSE);
13 $options['sheetsave'] = array('default' => '');
14 return $options;
15 }
16
17 function options_form(&$form, &$form_values) {
18 parent::options_form($form, $form_values);
19 $form['expansion'] = array(
20 '#type' => 'radios',
21 '#title' => t('Expansion of results'),
22 '#description' => t('You can specify whether view results should be expanded horizontally or vertically.'),
23 '#options' => array(
24 SHEETNODE_EXPANSION_VERTICAL => t('Consecutive rows'),
25 SHEETNODE_EXPANSION_HORIZONTAL => t('Consecutive columns'),
26 ),
27 '#default_value' => $this->options['expansion'],
28 '#weight' => 97,
29 );
30 $form['template'] = array(
31 '#type' => 'checkbox',
32 '#title' => t('Use template'),
33 '#description' => t('Check this box to use the spreadsheet below as template for your view.
34 To place view results in the template, use the following syntax in the cells:
35 <ul>
36 <li><code>${field_label}</code> to indicate that this cell and subsequent ones should be filled with values of this field.</li>
37 <li><code>${FUNCTION(@field_label@, $field_label$, @cell_reference@, ...)}</code> to indicate that this cell and subsequent ones should be filled with a calculation.
38 <code>@field_label@</code> are replaced with the corresponding cell references, and <code>$field_label$</code> are replaced with actual field values.</li>
39 <li><code>$[FUNCTION(@field_label@, @cell_reference@, ...)]</code> to indicate that a calculation should be placed in this cell.
40 <code>@field_label@</code> are replaced with the corresponding cell ranges.</li>
41 </ul>'),
42 '#default_value' => $this->options['template'],
43 '#weight' => 98,
44 );
45 $settings = drupal_to_js(array('sheetnode' => array(
46 'value' => $this->options['sheetsave'],
47 'imagePrefix' => base_path() . drupal_get_path('module', 'sheetnode') . '/socialcalc/images/sc-',
48 'containerElement' => 'sheetview',
49 'saveElement' => 'edit-style-options-sheetsave',
50 'viewMode' => variable_get('sheetnode_view_mode', SHEETNODE_VIEW_FIDDLE),
51 'showToolbar' => variable_get('sheetnode_view_toolbar', FALSE),
52 'permissions' => array(
53 'edit sheetnode settings' => user_access('edit sheetnode settings'),
54 ),
55 'context' => array('entity-name' => 'view', 'oid' => isset($this->view->vid) ? $this->view->vid : $this->view->name),
56 )));
57 $form['sheetview']['#weight'] = 99;
58 $form['sheetview']['#value'] = <<<EOS
59 <div class="sheetview" id="sheetview">
60 <script language="javascript" type="text/javascript">
61 Drupal.behaviors.sheetnode = function(context) {
62 jQuery.extend(Drupal.settings, $settings);
63 Drupal.sheetnode.start(context);
64 }
65 </script>
66 </div>
67 EOS;
68 $form['sheetsave'] = array('#type' => 'hidden');
69 }
70
71 function render_sheet() {
72 $tangent = $this->options['expansion'];
73 $normal = 1-$tangent;
74 if ($this->options['template']) {
75 $socialcalc = socialcalc_parse($this->options['sheetsave']);
76 $lastpos = array(0,0);
77
78 // Iterate through cells, gathering placeholder values.
79 if (!empty($socialcalc['sheet']['cells'])) foreach ($socialcalc['sheet']['cells'] as $coord => $cell) {
80 // Field placeholder?
81 $matches = array();
82 if (isset($cell['datavalue']) && $cell['datatype'] == 't') {
83 if (preg_match('/^\${(.*?)\}$/', $cell['datavalue'], $matches)) {
84 $field_name = $this->get_real_field_name($matches[1]);
85 if ($field_name && !$this->view->field[$field_name]->options['exclude']) { // it's a field expansion
86 $fields[$field_name] = array(
87 'coord' => $coord,
88 'cell' => $cell,
89 'info' => $this->view->field[$field_name],
90 );
91 }
92 else { // it's a formula expansion
93 $formula_expansions[] = array(
94 'coord' => $coord,
95 'cell' => $cell,
96 'expression' => $matches[1],
97 );
98 }
99 }
100 else if (preg_match('/^\$\[(.*?)]$/', $cell['datavalue'], $matches)) { // it's a formula placement
101 $formulas[] = array(
102 'coord' => $coord,
103 'cell' => $cell,
104 'expression' => $matches[1],
105 );
106 }
107 }
108 $lastpos = array(max($lastpos[0], $cell['pos'][0]), max($lastpos[1], $cell['pos'][1]));
109 }
110
111 // Replace field placeholders with actual values.
112 $tangent_increment = array(SHEETNODE_EXPANSION_VERTICAL => 0, SHEETNODE_EXPANSION_HORIZONTAL => 1);
113 if (!empty($fields)) foreach ($fields as $field_name => $field) {
114 unset($socialcalc['sheet']['cells'][$field['coord']]);
115 $newcell = $field['cell'];
116 $pos = $newcell['pos'];
117 foreach ($this->view->result as $result) {
118 $newcell['pos'] = $pos;
119 $value = $field['info']->theme($result);
120 $newcell['datavalue'] = $value;
121 $newcell['datatype'] = is_numeric($value) ? 'v' : 't';
122 $newcell['valuetype'] = is_numeric($value) ? 'n' : 'th';
123 $newcells[socialcalc_cr_to_coord($pos[0], $pos[1])] = $newcell;
124 $pos[$normal]++;
125 }
126 $fields[$field_name]['endpos'] = array($pos[0]-$tangent_increment[$tangent], $pos[1]-$tangent_increment[$normal]);
127 $lastpos = array(max($lastpos[0], $pos[0]), max($lastpos[1], $pos[1]));
128 }
129
130 // Replace formula expansions with actual values.
131 if (!empty($formula_expansions) && !empty($fields)) foreach ($formula_expansions as $fe) {
132 unset($socialcalc['sheet']['cells'][$fe['coord']]);
133 $newcell = $fe['cell'];
134 $pos = $newcell['pos'];
135 $matches = array();
136 $count = preg_match_all('/@(.*?)@|\$(.*?)\$/', $fe['expression'], $matches);
137 foreach ($this->view->result as $j => $result) {
138 $expression = $fe['expression'];
139 $newcell['pos'] = $pos;
140 for ($i=0; $i<$count; $i++) {
141 $replace = NULL;
142 $match = !empty($matches[1][$i]) ? $matches[1][$i] : $matches[2][$i];
143 $field_name = $this->get_real_field_name($match);
144 if (!$field_name) {
145 // Not a field: try a cell coordinate.
146 if (preg_match('/^\$?\w\w?\$?\d+$/', $match)) {
147 $refpos = socialcalc_coord_to_cr($match);
148 foreach ($fields as $field) {
149 if ($refpos == $field['cell']['pos']) {
150 $replace = socialcalc_cr_to_coord(
151 $field['cell']['pos'][0]+($j*$tangent_increment[$tangent]),
152 $field['cell']['pos'][1]+($j*$tangent_increment[$normal])
153 );
154 break;
155 }
156 }
157 }
158 } else if ($matches[0][$i][0] == '@' && isset($fields[$field_name])) {
159 $replace = socialcalc_cr_to_coord(
160 $fields[$field_name]['cell']['pos'][0]+($j*$tangent_increment[$tangent]),
161 $fields[$field_name]['cell']['pos'][1]+($j*$tangent_increment[$normal])
162 );
163 }
164 else if ($matches[0][$i][0] == '$') {
165 $replace = $result->{$this->view->field[$field_name]->field_alias};
166 }
167 if (!is_null($replace)) {
168 $expression = str_replace($matches[0][$i], $replace, $expression);
169 }
170 }
171 $newcell['formula'] = $expression;
172 $newcell['datatype'] = 'f';
173 $newcell['valuetype'] = 'n';
174 $newcell['datavalue'] = 0;
175 $newcells[socialcalc_cr_to_coord($pos[0], $pos[1])] = $newcell;
176 $pos[$normal]++;
177 }
178 $fields[] = array(
179 'cell' => $fe['cell'],
180 'coord' => $fe['coord'],
181 'endpos' => array($pos[0]-$tangent_increment[$tangent], $pos[1]-$tangent_increment[$normal]),
182 );
183 $lastpos = array(max($lastpos[0], $pos[0]), max($lastpos[1], $pos[1]));
184 }
185
186 // Replace formula placeholders with actual values.
187 if (!empty($formulas)) foreach ($formulas as $formula) {
188 $newcell = $formula['cell'];
189 $expression = $formula['expression'];
190 $matches = array();
191 $count = (int)preg_match_all('/@(.*?)@/', $expression, $matches);
192 for ($i=0; $i<$count; $i++) {
193 $field_name = $this->get_real_field_name($matches[1][$i]);
194 if (!$field_name || !isset($fields[$field_name])) {
195 // Not a field: try a cell coordinate.
196 if (preg_match('/^\$?\w\w?\$?\d+$/', $matches[1][$i])) {
197 $replace = '@'.$matches[1][$i].'@';
198 $references[$formula['coord']][] = socialcalc_coord_to_cr(str_replace('$', '', $matches[1][$i]));
199 }
200 else {
201 $replace = NULL;
202 }
203 }
204 else {
205 $field = $fields[$field_name];
206 $replace = socialcalc_cr_to_coord($field['cell']['pos'][0], $field['cell']['pos'][1]) .':'. socialcalc_cr_to_coord($field['endpos'][0], $field['endpos'][1]);
207 }
208 if ($replace) {
209 $expression = str_replace($matches[0][$i], $replace, $expression);
210 }
211 }
212 $newcell['formula'] = $expression;
213 $newcell['datatype'] = 'f';
214 $newcell['valuetype'] = 'n';
215 $newcell['datavalue'] = 0;
216 $socialcalc['sheet']['cells'][$formula['coord']] = $newcell;
217 }
218
219 // Adjust positions of all cells based on expanded values.
220 if (!empty($fields)) foreach ($socialcalc['sheet']['cells'] as $coord => $cell) {
221 $pos = $cell['pos'];
222 foreach ($fields as $field) {
223 if ($pos[$tangent] == $field['cell']['pos'][$tangent] && $pos[$normal] > $field['cell']['pos'][$normal]) {
224 $pos[$normal] += $field['endpos'][$normal] - $field['cell']['pos'][$normal];
225 unset($socialcalc['sheet']['cells'][$coord]);
226 $cell['pos'] = $pos;
227 $newcoord = socialcalc_cr_to_coord($pos[0], $pos[1]);
228 $socialcalc['sheet']['cells'][$newcoord] = $cell;
229 if (isset($references[$coord])) {
230 $references[$newcoord] = $references[$coord];
231 unset($references[$coord]);
232 }
233 $lastpos = array(max($lastpos[0], $pos[0]), max($lastpos[1], $pos[1]));
234 break;
235 }
236 }
237 }
238
239 // Adjust references in formulas based on expanded values.
240 if (!empty($fields) && !empty($references)) foreach ($references as $coord => $positions) {
241 $cell = $socialcalc['sheet']['cells'][$coord];
242 $expression = $cell['formula'];
243 $matches = array();
244 $count = (int)preg_match_all('/@\$?\w\w?\$?\d+@/', $expression, $matches);
245 for ($i=0; $i<$count; $i++) {
246 $pos = $positions[$i];
247 foreach ($fields as $field) {
248 if ($pos[$tangent] == $field['cell']['pos'][$tangent]) {
249 if ($pos[$normal] == $field['cell']['pos'][$normal]) { // referencing a field cell
250 $refrange = socialcalc_cr_to_coord($field['cell']['pos'][0], $field['cell']['pos'][1]) .':'. socialcalc_cr_to_coord($field['endpos'][0], $field['endpos'][1]);
251 $expression = str_replace($matches[0][$i], $refrange, $expression);
252 break;
253 } else if ($pos[$normal] > $field['cell']['pos'][$normal]) { // referencing a cell below a field
254 $pos[$normal] += $field['endpos'][$normal] - $field['cell']['pos'][$normal];
255 $refcoord = socialcalc_cr_to_coord($pos[0], $pos[1], TRUE);
256 $expression = preg_replace('/@(\$?)\w\w?(\$?)\d+@/', '${1}'.$refcoord[0].'${2}'.$refcoord[1], $expression, 1);
257 break;
258 }
259 }
260 }
261 }
262 $cell['formula'] = $expression;
263 $socialcalc['sheet']['cells'][$coord] = $cell;
264 }
265
266 // Replace values inside the sheet.
267 if (!empty($newcells)) {
268 $socialcalc['sheet']['cells'] += $newcells;
269 }
270 $socialcalc['sheet']['attribs']['lastcol'] = $lastpos[0];
271 $socialcalc['sheet']['attribs']['lastrow'] = $lastpos[1];
272
273 // Adjust starting cell in editor.
274 $pos = $socialcalc['edit']['ecell']['pos'];
275 if (!empty($fields)) foreach ($fields as $field) {
276 if ($pos[$tangent] == $field['cell']['pos'][$tangent] && $pos[$normal] > $field['cell']['pos'][$normal]) {
277 $pos[$normal] += $field['endpos'][$normal] - $field['cell']['pos'][$normal];
278 $ecell_changed = TRUE;
279 break;
280 }
281 }
282 $socialcalc['edit']['ecell']['pos'] = $pos;
283 $socialcalc['edit']['ecell']['coord'] = socialcalc_cr_to_coord($pos[0], $pos[1]);
284
285 // Adjust row and column panes.
286 $panes = $tangent == SHEETNODE_EXPANSION_VERTICAL ? 'rowpanes' : 'colpanes';
287 foreach ($socialcalc['edit'][$panes] as $i => $pane) {
288 $delta = 0;
289 if (!empty($fields)) foreach ($fields as $field) {
290 if ($pane['last'] > $field['cell']['pos'][$normal]) {
291 $delta = max($delta, $field['endpos'][$normal] - $field['cell']['pos'][$normal]);
292 }
293 }
294 if (@$ecell_changed) {
295 $socialcalc['edit'][$panes][$i]['first'] += $delta;
296 $socialcalc['edit'][$panes][$i]['last'] += $delta;
297 }
298 }
299 }
300 else {
301 // Hand-make default SocialCalc structure based on views results.
302 $pos = array(1,1);
303 foreach ($this->view->field as $field => $info) {
304 if ($info->options['exclude']) continue;
305 $cell['pos'] = $pos;
306 $cell['datavalue'] = $info->label();
307 $cell['datatype'] = 't';
308 $cell['valuetype'] = 't';
309
310 $sheet['cells'][socialcalc_cr_to_coord($pos[0], $pos[1])] = $cell;
311 $pos[$tangent]++;
312 }
313 $pos[$normal] = 2;
314 foreach ($this->view->result as $result) {
315 $pos[$tangent] = 1;
316 foreach ($this->view->field as $field => $info) {
317 if ($info->options['exclude']) continue;
318 $cell['pos'] = $pos;
319 $value = $info->theme($result);
320 $cell['datavalue'] = $value;
321 $cell['datatype'] = is_numeric($value) ? 'v' : 't';
322 $cell['valuetype'] = is_numeric($value) ? 'n' : 'th';
323
324 $sheet['cells'][socialcalc_cr_to_coord($pos[0], $pos[1])] = $cell;
325 $pos[$tangent]++;
326 }
327 $pos[$normal]++;
328 }
329 $sheet['attribs']['lastcol'] = $pos[0]-1;
330 $sheet['attribs']['lastrow'] = $pos[1]-1;
331
332 if ($tangent == SHEETNODE_EXPANSION_VERTICAL) {
333 $edit['rowpanes'] = array(
334 0 => array('first' => 1, 'last' => 1),
335 1 => array('first' => 2, 'last' => $sheet['attribs']['lastrow']),
336 );
337 $edit['colpanes'] = array(
338 0 => array('first' => 1, 'last' => $sheet['attribs']['lastcol']),
339 );
340 }
341 else {
342 $edit['colpanes'] = array(
343 0 => array('first' => 1, 'last' => 1),
344 1 => array('first' => 2, 'last' => $sheet['attribs']['lastcol']),
345 );
346 $edit['rowpanes'] = array(
347 0 => array('first' => 1, 'last' => $sheet['attribs']['lastrow']),
348 );
349 }
350 $edit['ecell'] = array(
351 'coord' => 'A1',
352 );
353
354 // Inject the Sheetnode code.
355 $socialcalc = array(
356 'sheet' => $sheet,
357 'edit' => $edit,
358 'audit' => socialcalc_default_audit($sheet),
359 );
360 }
361 return $socialcalc;
362 }
363
364 function render() {
365 if (!empty($this->view->live_preview)) {
366 drupal_set_message(t('Spreadsheet preview is not supported.'), 'warning');
367 return;
368 }
369
370 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/socialcalc/socialcalcconstants.js');
371 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/socialcalc/socialcalc-3.js');
372 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/socialcalc/socialcalctableeditor.js');
373 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/socialcalc/formatnumber2.js');
374 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/socialcalc/formula1.js');
375 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/socialcalc/socialcalcpopup.js');
376 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/socialcalc/socialcalcspreadsheetcontrol.js');
377 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/socialcalc/socialcalcviewer.js');
378 drupal_add_js(drupal_get_path('module', 'sheetnode') . '/sheetnode.js');
379 drupal_add_css(drupal_get_path('module', 'sheetnode') . '/socialcalc/socialcalc.css');
380 drupal_add_css(drupal_get_path('module', 'sheetnode') . '/sheetnode.css');
381
382 $value = socialcalc_save($this->render_sheet());
383 $context = array('entity-name' => 'view', 'oid' => isset($this->view->vid) ? $this->view->vid : $this->view->name);
384 module_invoke_all('sheetnode_plugins', $value, FALSE, $context);
385
386 drupal_add_js(array('sheetnode' => array(
387 'value' => $value,
388 'imagePrefix' => base_path() . drupal_get_path('module', 'sheetnode') . '/socialcalc/images/sc-',
389 'containerElement' => 'sheetview',
390 'saveElement' => FALSE,
391 'viewMode' => variable_get('sheetnode_view_mode', SHEETNODE_VIEW_FIDDLE),
392 'showToolbar' => variable_get('sheetnode_view_toolbar', FALSE),
393 'permissions' => array(
394 'edit sheetnode settings' => user_access('edit sheetnode settings'),
395 ),
396 'context' => $context,
397 )), 'setting');
398 drupal_add_js('Drupal.behaviors.sheetnode = function(context) { Drupal.sheetnode.start(context); }', 'inline');
399 return '<div class="sheetview" id="sheetview"></div>';
400 }
401
402 function get_real_field_name($field_name) {
403 if (isset($this->view->field[$field_name])) return $field_name;
404 foreach ($this->view->field as $field => $info) {
405 if ($info->field_alias == $field_name) return $field;
406 if (strcasecmp($info->label(), $field_name) == 0) return $field;
407 }
408 return FALSE;
409 }
410
411 function attach_to($display_id, $path, $title) {
412 $url_options = array('html' => true);
413 $input = $this->view->get_exposed_input();
414 if ($input) {
415 $url_options['query'] = $input;
416 }
417 $image = theme('image', $this->feed_image);
418 $this->view->feed_icon .= l($image, $this->view->get_url(NULL, $path), $url_options);
419 }
420 }
421
422 class sheetnode_feed_plugin_style extends sheetnode_plugin_style {
423 function option_definition() {
424 $options = parent::option_definition();
425 $options['inherit'] = array('default' => TRUE);
426 return $options;
427 }
428
429 function options_form(&$form, &$form_values) {
430 parent::options_form($form, $form_values);
431 $form['inherit'] = array(
432 '#type' => 'checkbox',
433 '#title' => t('Inherit spreadsheet settings'),
434 '#default_value' => $this->options['inherit'],
435 '#description' => t('If this feed is attached to a display that has a Spreadsheet style,
436 this option lets you inherit the settings of that style, including the spreadsheet formatting.
437 The settings below are then ignored.'),
438 );
439 }
440
441 function inherit_options() {
442 if ($this->options['inherit']) {
443 $displays = array_filter($this->display->handler->get_option('displays'));
444 if (!empty($displays)) foreach ($displays as $display) {
445 $handler = $this->view->display[$display]->handler;
446 if ($handler->get_option('style_plugin') == 'sheet') {
447 $options = $handler->get_option('style_options');
448 break;
449 }
450 }
451 if (isset($options)) foreach ($options as $key => $option) {
452 $this->options[$key] = $option;
453 }
454 }
455 }
456 }
457
