Nickolai and augustnyr,
I think that would be a rather extensive hack, to try to pull in all the purchase info into the CB user profile, I actually thought about it before I did what is in place now. I think the ultimate solution is if someone could build a report module on to Virtuemart and have reports that could do this.
If all you need is to keep track of who's paid/unpaid, this will do all you need, and is very portable because anyone who has proper access to the site can run the report themselves, and view them if they have capability to open excel spreadsheets. I have coaches that log in and do it.
Here is basically what needs to be done:
1. Configured CB registration fields on my site to have the fields I need to capture for the particular sport in season. These are basically the same fields that are on our manual registration form that is distributed to schools and the parks and rec. dept.
2. Installed Virtuemart (VM), and the VM CB plugin, which puts the VM payment fields underneath the CB fields during registration.
3. Installed JamboReport Component, and built a custom report that MERGES the CB fields with the Payment fields from VM, and outputs them to MS Excel Spreadsheet. I would rather do it this way in my situation because I need to be able to take the spreadsheet and reformat for reports out of Access database. I published the component on the site as a link from the main menu for registered users only and called it 'Baseball Payments'
First, go to this topic and it shows how to install jamboreport and the virtuemart report.
http://forum.joomla.org/index.php?topic=46426.msg249905Replace the report.php in components/com_jamboreport/reports/vm_csv3 directory with this code below, and then modify the database fields (cb_xxxxx) to match yours from CB registration fields. Pay careful attention to the join clauses, they are what actually do the merge, you probably should not need to change this.
The payment status field shows me as pending or completed on the report. We take paypal or check/money order, and when the paypal IPN hits, it works well, and when we receive the check in the mail, we just go in and update the order as complete and it is reflected on this report.
The names after each 'AS' is what is displayed on the first line of the excel spreadsheet for each column, you can call them whatever you want, just don't use spaces.
Under the line
// Set Conditions for Report
you can put in whatever you want to be able to pull only certain registrations. I have to use this because we take registrations for different sports.
Another file in the same directory, metadata.xml, which needs to be changed so the screen has the correct report name and description, in the 'name' and 'description' tags:
Name of Report Description of Report Here's the report.php code (and attached in a text file):
/**
* @version $Id $
* @package JamboWorks
* @subpackage JamboReport
* @copyright 2005 JamboWorks LLC. All rights reserved.
* @license GNU/GPL
*/
/**
* Reporting class
* @package JamboWorks
* @subpackage JamboReport
*/
class vm_csv3_Report extends JamboReport {
/** @var string The report id (folder name); */
var $id = 'vm_csv3';
/**
* Runs the report
* @param array An array of system variables
*/
function run( &$vars ) {
// set the default ordering
if (empty( $vars['cb_playerlastname'] )) {
$vars['cb_playerlastname'] = 'search_term';
}
$database = &$this->getDBO();
$qb = jwDatabaseHelper::getQuery();
// SELECT FIELDS FROM COMPROFILER TABLE
$qb->select( 'r.cb_ParentFirstName AS ParentFirstName' );
$qb->select( 'r.cb_ParentLastName AS ParentLastName' );
// GET EMAIL ADDRESS FROM USER TABLE
$qb->select( 'u.email AS Email' );
// GET ORDER TYPE FROM VM ORDER PAYMENT TABLE
$qb->select( 'p.payment_method_id AS PmtMethod' );
// SELECT FIELDS FROM VM ORDERS TABLE
$qb->select( 'o.order_id AS OrderNo' );
$qb->select( "FROM_UNIXTIME(o.cdate,'%c-%d-%y') AS Orderdate" );
$qb->select( 'o.order_total AS Amount' );
$qb->select( 'o.order_status AS Status' );
$qb->select( 'o.customer_note AS OrderNote' );
$qb->from( '#__vm_orders AS o' );
$qb->select( 'r.cb_address AS Address' );
$qb->select( 'r.cb_city AS City' );
$qb->select( 'r.cb_state AS State' );
$qb->select( 'r.cb_zip AS Zip' );
$qb->select( 'r.cb_homephone AS HomePhone' );
$qb->select( 'r.cb_workphone AS WorkPhone' );
$qb->select( 'r.cb_emergencyphone AS EmerPhone' );
$qb->select( 'r.cb_playerfirstname AS PlayerFirstName' );
$qb->select( 'r.cb_playerlastname AS PlayerLastName' );
$qb->select( 'r.cb_playergender AS Gender' );
$qb->select( 'r.cb_homeschool AS HomeSchool' );
$qb->select( 'r.cb_playerschool AS School' );
$qb->select( 'r.cb_grade AS Grade_Spring_07' );
$qb->select( 'r.cb_age AS Baseball_Teeball_Age_as_of_05_01_07' );
$qb->select( 'r.cb_softball AS Softball_Age_as_of_01_01_07' );
$qb->select( 'r.cb_basebracket AS Baseball_Teeball_League' );
$qb->select( 'r.cb_leaguesoftball AS Softball_League' );
$qb->select( 'r.cb_year AS Year' );
$qb->select( 'r.cb_sport AS Sport' );
$qb->select( 'r.cb_jerseybaseball AS JerseySize' );
$qb->select( 'r.cb_pantsbaseball AS PantsShortsSize' );
$qb->select( 'r.cb_disability AS Disability' );
$qb->select( 'r.cb_birthcert AS BirthCertOnFile' );
$qb->select( 'r.cb_volunteer AS Volunteer' );
$qb->select( 'r.cb_prevouscoachteam AS PreviousTeam' );
$qb->select( 'r.cb_requestedcoach AS Coach' );
$qb->select( 'r.cb_playerbirthdate AS Birthdate' );
$qb->select( 'r.cb_sameteam AS SameTeam' );
// MERGE DATA
$qb->join( 'LEFT', '#__comprofiler AS r ON r.user_id = o.user_id' );
$qb->join( 'LEFT', '#__users AS u ON r.user_id = u.id' );
$qb->join( 'LEFT', '#__vm_order_payment AS p ON o.order_id = p.order_id' );
// Set Conditions for Report
$qb->where( "r.cb_sport = 'Baseball'" );
$qb->where( "r.cb_year = '2007'" );
$qb->where( "o.order_status != 'R'" );
// SET DEFAULT ORDER FOR REPORT
$qb->order( 'cdate' );
// DO QUERY ON DATABASE and GENERATE EXCEL CSV FILE
$query = $qb->toString();
$database->setQuery( $query, 0, 1 );
$heading = $database->loadAssocList();
//echo $query;
echo $database->getErrorMsg();
$database->setQuery( $query );
$rows = $database->loadRowList();
array_unshift( $rows, array_keys( $heading[0] ) );
$buffer = trim( arrayToCSV( $rows ) );
header( "Content-type: application/octet-stream" );
header( "Content-Length: ".strlen( $buffer ) );
header( "Content-disposition: attachment; filename=GSA_Baseball_Registrations-".date("Y-m-d").".csv" );
header( "Pragma: no-cache" );
header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
header( "Expires: 0" );
echo $buffer;
exit( 0 );
}
}
?>
