PDA

View Full Version : Grid formulas

chrisgotter
19 Sep 2016, 5:59 PM
ok, I am writing a grid which is essentially a timeline ex:

activity

started
ended
duration

07:00
07:30
00:30

go to work
07:30
08:00
00:30

work
08:00
16:00
08:00

go home
16:00
16:30
00:30

eat
16:30
17:15
00:45

watch tv
17:15
23:00
05:45

sleep
23:00
07:00
08:00

EXCEL format:

activity

started
ended
duration

07:00
=B3

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

go to work
07:30
=B4

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

work
08:00
=B5

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

go home
16:00
=B6

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

eat
16:30
=B7

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

watch tv
17:15
=B8

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

sleep
23:00
=B2

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

looking past how sad my personal life is, does anyone have any ideas for how to make this happen?
As you can see the only values which are actually important are the "started times".
Ideally, I would like my store to just be a list of those (the activities are arbitrary in reality I just included them for context).
I implemented a system of 2way data-binding myself which syncs up the values on a single row, but I still need to ensure that C2 = B3 and that SUM(d:d) = 24:00

// Handler receives array of itmes and selector in parentheses
Ext.ComponentQuery.pseudos.nth = function(components, selector) {
if (selector.indexOf(',') == -1) {
return components[selector - 1];

} else if (selector === '') {
return components;
} else {
var retain = selector.split(',');
var result = [];
for (var i of retain) {
result.append(components[i - 1])
}
return result;
}
}
var startChange = function(field) {
//if (field.hasFocus) {
var row = field.ownerCt;
var start = row.down('field:nth(2)');
var end = row.down('field:nth(3)');
var diff = row.down('field:nth(4)');
end.setValue(start.getValue() + diff.getValue());
//}
}

var endChange = function(field) {
//if (field.hasFocus) {
var row = field.ownerCt;
var start = row.down('field:nth(2)');
var end = row.down('field:nth(3)');
var diff = row.down('field:nth(4)');
diff.setValue(end.getValue() - start.getValue());
//}
}

var diffChange = startChange;

Ext.application({
name: 'Fiddle',

launch: function() {
Ext.create('Ext.data.Store', {
storeId: 'testStore',
fields: ['name', 'start', 'end', 'diff'],
data: [{
"name": "Lisa",
"start": 2,
"end": 23,
"diff": 21
}, {
"name": "Bart",
"start": 1,
"end": 5,
"diff": 4
}, {
"name": "Homer",
"start": 11,
"end": 16,
"diff": 5
}, {
"name": "Marge",
"start": 44,
"end": 49,
"diff": 5
}]
});

var rowEditing = Ext.create('Ext.grid.plugin.RowEditing', {
clicksToEdit: 1
});

Ext.create('Ext.grid.Panel', {

title: 'Test',
store: Ext.data.StoreManager.lookup('testStore'),

columns: [{
text: 'Name',
dataIndex: 'name',
flex: 1,
}, {
text: 'Start',
dataIndex: 'start',

editor: {
xtype: 'numberfield',
listeners: {
change: startChange
}
},
flex: 1
}, {
text: 'End',
dataIndex: 'end',
editor: {
xtype: 'numberfield',
listeners: {
change: endChange
}
},
flex: 1
}, {
text: 'Difference',
dataIndex: 'diff',
editor: {
xtype: 'numberfield',
listeners: {
change: diffChange
}
},
flex: 1
}],
selType: 'rowmodel',
plugins: [rowEditing],
width: 400,
renderTo: Ext.getBody()
})
}
});

Gary Schlosberg
20 Sep 2016, 7:01 AM