Virtuemart Orders Report By Category

Attached is a custom add-on report for InteraMind Reports.

This report finds all of the orders within a date range and outputs the persons name, product information and the Root/Base Category from each product they ordered.

This is a handy report and allows for us to easily import these customers name & email into our preferred email marketing software.

For Example if I made an order with 2 items

Computers -> CPU -> Intel i7x
Software -> Business -> Quickbooks 2012


The resulting csv would look like 

19 June 2012, CodePyro, info@codepyro.com ,Computers
19 June 2012, CodePyro, info@codepyro.com ,Software

Download the Virtuemart Orders Report By Category InteraMind Reports

Export 100K+ records to Excel using ColdFusion

Lately I have been building a contact manager in coldfusion 8 for a customer with hundreds of thousands of records. They needed to be able to import, search, sort & export their contacts from excel .xls files. Using Ben Nadel's POI utility is awesome for excel files with hundreds of rows. When you start to deal with more than that it becomes very troublesome, lots of timeout errors, 500 jRun memory stack issues etc. I need to add a part 2 of this post for the Import and how I got around those problems.

If you are able to, use MsSQL DTS packages instead to do the import/export (much more efficient). In a shared environment this is impossible, we have to do it asynchronously (*challenge accepted!*).

First of all I tried to use a cfcontentvariable to save the string of outputted text to write the file using cffile. Even with 1000 records this process ran too long and failed. After some googling I found Raymond Camden's blog about writing large amounts of data to a file which added a nice solution (Read It NOW). In this article he compares and explains several different ways of writing data. I chose to use the method #2 fileOpen, because its really FAST!! (well alot more than cffile).

Ill attach the example in full, but here are some excerpts that show the key pieces.

On the first run, the export creates the initial file, adds the header html table that is used to write the .XLS file.

<cfset filepath=ExpandPath( "tmp/Contacts Export.xls" )/>

<cfset contentLine=""/>
<cfif isFirstRun  eq true >
  <!--- write the header and inital X records to the xls file ---> 
  
  <!---ensure that on the first run the file is Overwritten--->
  <cfif FileExists(filepath)>
    <cfset FileDelete(filepath)/>
  </cfif>
  
  <!---open the file object--->
  <cfset fileObj = fileOpen(filepath,"write")/>
  
  <!---create the xls header--->
  <cfset contentHeader ='<table border="1"><thead align="center"><th>FirstName</th><th>LastName</th><th>email</th><th>City</th><th>State</th><th>categoryList</th><th>entity</th><th>phone</th><th>type</th><th>resource</th></thead>'>
  <cfset fileWriteLine(fileObj, contentHeader)>
  <cfset fileClose(fileObj)>
</cfif> 

After the Header is written, 10,000 records are then appended as a string and then written to the file. Once this process is completed AND there are more records to process, the page is automatically refreshed either by an html button or javascript window.location. Each iteration appends 10K records to the excel file. Depending upon the server, it may be able to handle 20K or more.

<!---re-open up the file for writing the rest of the contacts--->
<cfset fileObj = fileOpen(filepath,"append")/> 

<!---create the string of data from the query--->
<cfloop query="qcontacts" startrow="#url.recordsProcessed#"  endrow="#maxRows#"  >
  <cfset contentLine &=  '<tr align="left"><td>#FirstName#</td><td>#LastName#</td><td>#email#</td></tr>'/>
</cfloop>
<!---write this bulk entry into the xls file--->
<cfset fileWriteLine(fileObj, contentLine)>
<cfset fileClose(fileObj)>

This process works pretty well for hundreds of thousands of records, but not millions. It takes about 3-4 minutes to write 200K records to an excel sheet. Not ideal and not the most elegant, but it works. If you have improvements Please Feel Free to Share!!


Full Source example
*note this is a hacked up version from the project; it has not been tested.

Export Lists from Outlook to csv

Open up the contacts
Click to highlight a list
Choose File->Save As
Change the Save As Type to "Text Only .txt"

Save the file
Then open excel
Next goto File->Open Choose your file

You should get a popup "Text Import Wizard" asking you about delimited, just leave the default.
- Choose Next
- make sure Tab is checked
- Click Finish

Next choose File->Save As
Save your file as a csv

Virtuemart Google base export with Child Products

The default Google Base export in CSVI for joomla and virutmart is set as a "Product export" which does not include any child products. To include Child products you need to first change the type to "Multiple prices export".

Unfortunately though the multi prices export doesn't have all the fields for the google base export.

So I modified some files in the model to get the export working as needed. Download the files and put them in the administrator\components\com_csvimproved\model folder.

CSVIMultiPricesMods.zip

Zipcodes to go radius search MySQL table

Recently I have finished a customized zip radius search and used the one from Zipcodes to go. Unfortunately they only have csv, xls, and mdb formats for the database. To make use of their data you need to import it into mysql, but first you must create the table which phpmyadmin won't do automagically (please tell me im wrong by the way).

First you must create the table.

I tried to be as lazy as possible and tried to export the mdb to mssql then to mysql but it didn't really work and took waayy too long so i ended up having to write out the create table anyway.


SQL to create the gold table
Note: Remove the ID field until after you import all the data from the csv, otherwise the field lengths wont match!

CREATE TABLE IF NOT EXISTS `zipcodes_full` (
 `ID` int(12) unsigned NOT NULL AUTO_INCREMENT,
 `ZIP_CODE` varchar(255) NOT NULL DEFAULT '' ,
 `CITY_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_ABBREV` varchar(255) NOT NULL DEFAULT '' ,
 `STATE_FIPS` varchar(255) NOT NULL DEFAULT '' ,
 `COUNTY_FIPS` varchar(255) NOT NULL DEFAULT '' ,
 `COUNTY_NAME` varchar(255) NOT NULL DEFAULT '' ,
 `TOTAL_COUNTY_POPULATION` varchar(255) NOT NULL DEFAULT '' ,
 `TOTAL_COUNTY_HOUSING_UNITS` varchar(255) NOT NULL DEFAULT '' ,
 `LATITUDE` varchar(255) NOT NULL DEFAULT '' ,
 `LONGITUDE` varchar(255) NOT NULL DEFAULT '' ,
 `TIMEZONE` varchar(255) NOT NULL DEFAULT '' ,
 `DAYLIGHT_SAVINGS` varchar(255) NOT NULL DEFAULT '' ,
 `Returns` varchar(255) NOT NULL DEFAULT '' ,
 `$ AVG AGI` varchar(255) NOT NULL DEFAULT '' ,
 `Refunds` varchar(255) NOT NULL DEFAULT '' ,
 `$ AVG Refund` varchar(255) NOT NULL DEFAULT '' ,
 `Single` varchar(255) NOT NULL DEFAULT '' ,
 `Married Joint` varchar(255) NOT NULL DEFAULT '' ,
 `Head of Household` varchar(255) NOT NULL DEFAULT '' ,
 `<30` varchar(255) NOT NULL DEFAULT '' ,
 `30_44` varchar(255) NOT NULL DEFAULT '' ,
 `45_60` varchar(255) NOT NULL DEFAULT '' ,
 `>60` varchar(255) NOT NULL DEFAULT '' ,
 `TV_Designated Market Areas` varchar(255) NOT NULL DEFAULT '' ,
   PRIMARY KEY (`id`)
)  

Second:

Importing the data is a freaking pain through phpmyadmin. I ended up using the commandline on my local machine and now i am trying to use BigDump php to migrate it to the server.

Use the below script in the mysql commandline to import the csv into the db table. It worked wonderfully 4.6 some seconds. Great Now how do i get it to the server....

Note i dont have mysql mapped in my environment variables in windows so i needed to copy the csv file into the mysql bin directory.

load data local infile '5-DIGIT-ZIP-GOLD.csv' into table zipcodes_full fields terminated by ','  ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

Excel format cells with quotes

Here is how to format a text cell so it will have either double or single quotes. I needed to import a csv file with text fields formatted with double quotes. There are probably some more reasons why you might need to do this.

• Edit the cell or column you need to format
• choose format cell
• Select Custom from the bottom
• use the formula \"@\"

The @ symbol represents the text in the field. You can wrap any text field with any character but make sure to escape it.

User Port: upgrades

Been working with the userport extension for joomla and importing 2500 users for a site. This import utility is amazing and saves a ton of time GO GET IT!

I ran into a "feature" that I needed. That feature is to generate random passwords for users on update; when the password field is missing. It also needed to email the new password to the users.

Note: you normally don't want to do this because the import utility automatically loads all the users. Therefore you could accidentally change all your users passwords so be careful.

I chose to modify the userport utility because, well lets face it I'm lazy and making code changes is infinitely more fun than deleting and re-importing records yuk.

Zipped version of the full change for a "quick fix". Oh and it belongs in the folder "administrator\components\com_userport\admin.userport.php"

//change #1 around line 80
//added the userlog to the email body; the userlog contains the new email
'{email_body}' => $params->get( 'message_body_when_notifying_existing_user', JText::_( 'DEFAULT_MESSAGE_BODY_WHEN_NOTIFYING_EXISTING_USER' ) ).
implode( "\n- ", $userLog )


//change #2 around line 228
//create random password on update
else if((bool)$params->get( 'overwrite_password_of_existing_user', false ) == true)
{

$userData->password  = JUserHelper::genRandomPassword( 12 );
//add the password to the userlog so it can be emailed
$userLog[] = JText::sprintf( 'INFO_PASSWORD_UPDATED', $userData->password );
if((bool)$params->get( 'given_passwords_are_already_encoded', false ) == true )
$userData->password = JUserHelper::getCryptedPassword( $userData->password);

$user->password = $userData->password;

}

Also fixed a couple of variable warnings caused by missing quotes

//line 446
$title .= JText::_( 'FORM_ADDED_NEW_USERS' ).' ';
//line 450 
$title .= JText::_( 'FORM_UPDATED_EXISTING_USERS').' ';
//line 466
HTML_userport::userportPrintConfiguration( $option, $task, JText::_( 'FORM_CONFIGURATION' ) );
HTML_userport::userportLog( $option, $task, JText::_( 'FORM_RETRIEVED_USERS' ).' '.JText::_( 'FORM_LOG' ), $log );
HTML_userport::userportExport( $option, $task, JText::_( 'FORM_USER_LIST' ), $csvExportString );

I wanted to post this to the project on the project page http://joomlacode.org/gf/project/userport/, or forums but couldn't find a good place on joomlacode.org. Let me know if there is a place for it, i would like to help the community and submit this for the project.

Importing Categories/Products/Sub items with Virtuemart CSVI

Finally after much trial/error/re-dos I finally have a grasp on correctly importing into virtuemart with the CSVI tool. Here is a run down to hopefully make it easier for you aka "me" later.

Note 1: Export your data from your existing store
The default in the CSVI is Carots ^ for field delimiters and tilde ~ to wrap text content.

Step 1:
Go into the CSVI templates and make a template that you can match your data too. Become familiar with the field listings . I ended up using category_path, category_list_order, category_full_image, category_description, category_publish, category_products_per_row, category_flypage.

Category_path -> use this field to setup your structure of parents and children

Make sure your images don't have a path i.e. 'someimg.jpg' not 'images/someimg.jpg' and are copied into the /components/com_virtuemart/shop_image/category/ folder.

Import the categories, then go into virtuemart category list and verify everything is correct

Note 2: make sure your categories are correct!! Every time you re-import your categories it will make new ones instead of overwriting the old ones which also screws up the products linked to the correct category. //BOO!!
Product import will update/overwrite existing products! //YEA!!

Step 2: Prep/import your products.
You need a csv import of products that will be listed under categories/sub-cats. These products will be added directly to your category structure using the category_path field. The Category_id didn't work for me so use the category_path instead. It will be something like 'Computers/hardware'.

Again the images don't need a path and need to be uploaded into /components/com_virtuemart/shop_image/products/

Step 2: Prep/import your sub-products.
Setup a product import with a blank CATEGORY_PATH and have the product_parent_sku setup. This field will need to have the EXACT product_sku of the parent product item.

Note 3: Products with a category path will be listed under the category NOT under the parent product.

Note 4: the product_currency field MUST BE UPPER case i.e. 'USD' otherwise it formats your prices to something random (I didn't bother trying to figure it out it probably uses EUR or something).

Note 5: When importing users, the users must be imported into joomla first then imported into virtuemart