The Lowlandpiper; february 19th 2011
This is my addition to the code contained in the file \administrator\components\com_cbjuice\admin.cbjuice.php which will insert records for CBSubs and Virtumart into the relevant tables from a csv file at the same time as the standard CBJuice code inserts records in Users, CB users and associated tables
The version of CBJuice that this is based on is Version: 1.7 beta4, NOT the latest CBJuice2; the following is no use to you if you're using CB 1.4 or Joomla 1.6, though you could use it as a guide to writing your own version;
This is probably not for the faint-hearted; back-up your tables!
Although the csv file could contain all the records you need, I prefer to send small batches, made up of the same kinds of records (eg all of the same plan); that way I can compose a suitable email for CBJuice to send for each batch (and any errors are less drastic)
BTW, it's a good idea to keep a notepad file of the email messages so that you dont have to keep re-typing them.
Notes
1. You should save a safe copy of the cbjuice file somewhere before editing the live version. It goes without saying, I know ...
2. I am using an amended version of Angek's CBselectfield drop down for State and Country fields; hence the cb_selectfield variables; you will need to adjust these to suit your configuration for these fields
3. I have fields in my csv file for a 'parent plan'[cbsubs_regplan_id] and 'childplan' [cbsubs_csplan_id] (these are the id numbers of the relevant plans) and for expiry date {expiry_date].
The parent plan is the subscription plan that users are to be subscribed to; the csplan is a child plan. These fields were filled in the original Access database that the existing membership was drawn from. More subtle combinations of plans could be incorporated into the code, I guess.
4. I have not included any checks to see whether CBSubs plans actually exist that have the id's in the csv file
5. I do not use Juga; if you do, you should check whether you need to make changes to the code that handles those updates.
6. The line numbers quoted may differ slightly depending on the version of CBJuice you are using and any edits you have made.
/*First, we need to edit line 450 which checks that the fields in our csv are in the CB table;
if($testindex<0 and $testincomprofiler<0 ){
those fields relevant to CBSubs [which are not in the CB field list] have to be excluded from this check: so the new line 450 reads:
*/
if($testindex<0 and $testincomprofiler<0 and (!$thisvar="expiry_date" ) and (!$thisvar="cbsubs_regplan_id") and (!$thisvar="cbsubs_csplan_id" )){
/*Now we need to get the values of these fields into variables: admin.cbjuice.php starts this process at line 623:
$lastname=defaultNameFields($eachuser,$headervariables,"lastname",$username);
$cf=checkFields("firstname",$headervariables);
imediately BEFORE this set of code lines I have added the following: some of the CB fields have different names to their equivalent VM fields, so we just grab them all and store them in variables here:
*/
$cbsubs_regplan_id=defaultNameFields($eachuser,$headervariables,"cbsubs_regplan_id","not available");
$cbsubs_csplan_id=defaultNameFields($eachuser,$headervariables,"cbsubs_csplan_id","not available");
$expiry_date=defaultNameFields($eachuser,$headervariables,"expiry_date","not available");
$address_1=defaultNameFields($eachuser,$headervariables,"location","not available");
$address_2=defaultNameFields($eachuser,$headervariables,"address","not available");
$city=defaultNameFields($eachuser,$headervariables,"city","not available");
$zip=defaultNameFields($eachuser,$headervariables,"zipcode","not available");
$phone=defaultNameFields($eachuser,$headervariables,"phone","not available");
$cb_selectcountry=defaultNameFields($eachuser,$headervariables,"cb_selectcountry","not available");
$cb_selectstate=defaultNameFields($eachuser,$headervariables,"cb_selectstate","not available");
/*if your CB config uses text fields here you will want to substitute $state and $country for these last two variables ; these variables are used to send data to VM which uses drop-downs, so there's an issue to resolve either way;
Now we go to the insert function call; this is around line 707, depending on how many new lines you added above:
if($addmode){
$error = insertuser($auditlist,$cbJoomlaVersion,$new_aro_group_name,$new_aro_group_id,$username, $password, $name, $email,
$registerdate, $new_id, $new_aro_id, $new_comprofiler_id, $lastname, $firstname, $comprofilernames,$comprofilervalues,$JugaRun,$JugaIdsForUser);
*/
//We need to include the CBSubs fields and all our variables, so we replce these lines with:
if($addmode){
$error = insertuser($auditlist,$cbJoomlaVersion,$new_aro_group_name,$new_aro_group_id,$username, $password, $name, $email,$address_1,$address_2,$city,$state,$zip,$phone,$country,
$registerdate, $new_id, $new_aro_id, $new_comprofiler_id, $lastname, $firstname, $comprofilernames,$comprofilervalues,$JugaRun,$JugaIdsForUser,$cbsubs_regplan_id,$cbsubs_csplan_id,$expiry_date);
//Now for the insert function itself: it is around line 954:
function insertuser ($auditlist,$cbJoomlaVersion,$new_group_name,$new_group_id,$username, $password, $name, $email,$address_1,$address_2,$city,$state,$zip,$phone,$country, $registerdate, $new_id, $new_aro_id, $new_comprofiler_id, $lastname, $firstname,
$comprofilernames, $comprofilervalues,$JugaRun,$JugaIdsForUser,$cbsubs_regplan_id,$cbsubs_csplan_id,$expiry_date) {
//again, I have added the new variables to the params. Note that $comprofilernames & $comprofilervalues contain all the CB fields, including my extra select fields; you only need the $state and $country ones to send that data to VM.
/*now for the CBsubs inserts: somewhere around line 1097 you will see
if($database->getErrorNum()){
print(" SQL Error in Juga_u2g".$database->stderr(true)."
");
return;
}
}
}
}
*/
/*after this we add the new code: first check that there is a plan:
This is preventing the code from running at all; it needs reconsidering; disabled for now:
if(!$cbsubs_regplan_id="not available"){
then some variables;
[you might want to adjust this; cbjuice has set $registerdate to the current time at line 277]*/
$start_date=$registerdate;
/*[assuming you are inserting Active subs]*/
$status="A";
$parent_plan=0;
$parent_subscription=0;
//then the lines that call the insertcbsubs function: this assumes that $cbsubs_regplan_id exists in the CB plans table; a universal version of this code would need to check for this;
$parent_subscription = insertcbsubs($new_comprofiler_id,$cbsubs_regplan_id,$parent_plan,$parent_subscription,$status,$start_date,$expiry_date);
/*note that this insertcbsubs function will return the id of the new subscription record as $parent_subscription;
now for the child plan:
first we set some variables:*/
$plan_id=$cbsubs_csplan_id;
$parent_plan=$cbsubs_regplan_id;
/*now check whether there is a child plan and call the insertcbsubs function again:
This is preventing the code from running at all; it needs reconsidering
if($plan_id>0 and (!plan_id="not available")){*/
insertcbsubs($new_comprofiler_id, $cbsubs_csplan_id,$parent_plan,$parent_subscription,$status,$start_date,$expiry_date);
/* }
} :brackets for if cluases now disbaled */
/*I understand that if you use the CB-VM plug-in Virtumart will be updated; however here is the code to insert the appropriate records in the various VM tables if you need it:
I use a drop-down field for CB country and state; hence the $CB_selectcountry etc; replace these with $country & $state if you have text fields in CB, but VM wont like it unless you have adjusted the Vm drop-down field structure.
That's another matter altogether ...
If you are adding users with different shop permissions (auth_group, shopper_group) you may want to put these in your csv file too; you can then grab the values when you grab the others [at lin 623 see above]; you will also need to remove these variables from the check function at line 450
*/
$query = "INSERT INTO #__vm_user_info (user_info_id,address_type, Address_type_name, user_email, user_id, first_name, last_name, address_1, address_2, city, zip, country, state, phone_1,perms,cdate,mdate) VALUES ('$encpassword', 'BT', '-default-', '$email', '$new_comprofiler_id', '$firstname', '$lastname', '$address_1', '$address_2', '$city', '$zip', '$cb_selectcountry', '$cb_selectstate', '$phone', 'Members Shopper', '$timestamp', '$timestamp')";
$database->setQuery($query);
$database->query();
if($database->getErrorNum()){
print(" SQL Error in V|M".$database->stderr(true)."
");
return;
}
/*shopper group* you will need to substitute a suitable value for `shopper_group_id` depending on your shop config; I have used the hashed password as cuctomer number*/
$query = "INSERT INTO #__vm_shopper_vendor_xref (`user_id` ,`vendor_id` ,`shopper_group_id` , `customer_number`) VALUES ('$new_comprofiler_id', '1', '6', '$encpassword')";
$database->setQuery($query);
$database->query();
if($database->getErrorNum()){
print(" SQL Error in V|M".$database->stderr(true)."
");
return;
/*auth_user group: you'll need to adjust the group_id here acc. to your shop config. and user details; '5' for me is a 'Gold Level' shopper group for society members.
the groups available are in vm_auth_group table */
$query = "INSERT INTO #__vm_auth_user_group (`user_id`,`group_id` ) VALUES ('$new_comprofiler_id', '5')";
$database->setQuery($query);
$database->query();
if($database->getErrorNum()){
print(" SQL Error in V|M".$database->stderr(true)."
");
return;
}
}
// Insert vendor relationship; vendor_id=1; adjust if necessary
$query = "INSERT INTO #__vm_auth_user_vendor (user_id,vendor_id) VALUES ('$new_comprofiler_id', '1')";
$database->setQuery($query);
$database->query();
if($database->getErrorNum()){
print(" SQL Error in V|M".$database->stderr(true)."
");
return;
}
//now for the vital insertcbsubs function that does the work: I have used the start_date variable for both the registered date and the lastupdateddate
function insertcbsubs($new_comprofiler_id, $planid, $parent_plan, $parent_subscription, $status, $start_date, $expiry_date){
$query = "INSERT INTO #__cbsubs_subscriptions (user_id, status, plan_id, parent_plan, parent_subscription, subscription_date, last_renewed_date, expiry_date) VALUES
('$new_comprofiler_id', '$status', '$planid', '$parent_plan', '$parent_subscription', '$start_date', '$start_date', '$expiry_date')";
if (defined('JPATH_ADMINISTRATOR')){
$database = &JFactory::getDbo();
$my = &JFactory::getUser();
}else {
global $database,$my;
}
$database->setQuery($query);
$database->query();
if($database->getErrorNum()){
print(" SQL Error in CBsubs".$database->stderr(true)."
");
return;
}
//get the new planid for parentplan_subscription_id if this is the first call:
if ($parent_subscription=0){
$query = "SELECT `id` FROM #__cbsubs_subscriptions WHERE `user_id` ='$new_comprofiler_id'";
$database->setQuery($query);
$database->query();
$planid=$database->loadResult();
if($database->getErrorNum()){
print(" SQL Error in CBsubs".$database->stderr(true)."
");
return;
}
}
//update the plans table
$query = "update #__cbsubs_plans set `confirmed` =`confirmed`+1,`approved`=`approved`+1 WHERE `id` ='$planid' ";
$database->setQuery($query);
$database->query();
if($database->getErrorNum()){
print(" SQL Error in CBsubs".$database->stderr(true)."
");
return;
}
return $planid;
}
This code works for me and my set-up, but you use it at your own risk; I cannot guarantee that it has covered all possibilities; indeed, I'm sure that it hasn't; still, if you've got this far, you can probably make your own adjustments where necessary; I dont need to tell you to back up your database tables before testing do I ...