D5, e-Commerce txnid in Views
I did a little experiment recently to see how we could get e-Commerce Transactions into views in D5. The reason I did this was because I'd like to de-couple store crud routines from store admin ui code so that someone could write an alternative admin interface for ecommerce. The current admin interface is clumsy and always begging to be hacked.
For those not familiar with ecommerce, transactions are represented by a few tables, I was just interested in the main one ec_transaction for this experiment.
(Note: yes, usernode does something like this for users.)
Anyway, all I needed was node for every transaction, and a way to link them. As disgusting as it sounds i thought I'd try experimenting with using the node.title for the txnid. Ha ha, yeah, hence the post in Drupal for Evil. Anyway here is the result with some rough edges.
Notes:
-- test_tables(), which defines the table and fields ready for views, is abstract enough to allow many other tables perhaps gathered by hook
-- there is no node type creation in here, but you could do it automatically by asking test_tables about the table names and creating a node type for each table.
-- nodes get created in cron, but instead you could have a hook to allow other modules to trigger creation/deletion (and a cron to clean up).
-- you might go further and have some node rendering functions, to replace the foreign ID in the title
<?php
/*****************
* This is an experimental module that allows a very light-touch
* join between a random table and the node table so that
* it is possible to render the data as a node or in a view.
******************/
// Prevent special content type editing.
// Maybe you would allow it sometimes...
function test_form_alter($form_id, $form_values) {
$info = test_tables();
if (arg(0) == 'admin' && arg(1) == 'content' && arg(2) == 'types' && arg(3)) {
if (isset($info[str_replace('-', '_', arg(3))])) {
drupal_set_message('Sorry, you cannot edit the '. arg(3) .' content type.');
drupal_goto('admin/content/types');
}
}
}
// Make nodes
function test_cron() {
$info = test_tables();
foreach ($info as $t => $fields) {
// ADD MISSING NODES
// because node.title is not unique, you need a sub-select to weed out
// uninteresting node types in the sub SELECT.
$sql = "
SELECT n2.type, notnode.txnid
FROM (
SELECT n1.title, n1.type
FROM {node} AS n1
WHERE n1.type = '". $t ."'
) AS n2
RIGHT OUTER JOIN ". $t ." AS notnode
ON notnode.txnid = n2.title
WHERE n2.type is NULL
";
$result = db_query($sql);
while ($row = db_fetch_array($result)) {
$default = array();
$default['type'] = $t;
$default['comment'] = 1;
$default['status'] = 1;
$default['uid'] = 0;
$node = node_prepare((object)$default);
$node->title = $row['txnid'];
node_save($node);
}
// TODO: REMOVE ORPHAN NODES
}
}
// Get information about foreign tables we want to represent in views.
// hard coded here for speed
function test_tables() {
return array('ec_transaction' => array(
'key' => 'txnid',
'uid',
'ecid',
'type',
'mail',
'payment_method',
'allocation',
'currency',
'gross',
));
}
// Tell views about our tables.
function test_views_tables() {
$info = test_tables();
foreach($info as $t => $fields) {
$tables[$t] = array(
'name' => $t,
'join' => array(
'left' => array(
'table' => 'node',
'field' => 'title',
),
'right' => array(
'field' => $fields['key'],
),
),
);
foreach ($fields as $f) {
$tables[$t]['fields'][$f] = array(
'name' => "$t: $f",
'sortable' => true,
);
}
}
return $tables;
}
?>