Importing customer data into OpenCart web shops

Recently, I helped Loveknitting to import customer information into a OpenCart-based web shop. The database structure used by OpenCart is quite intuitive so figuring out what information that was needed was relatively easy. Customer's password were not imported so the customers had to use the web interface to get a new password when they log in. The same goes for address information. OpenCart version 1.5.1 was used.

Looking at the database tables used by OpenCart, there is one which is used for important customer data (table cutomer) that needs to be populated.

mysql> DESCRIBE customer;
+-------------------+-------------+------+-----+---------------------+----------------+
| Field             | Type        | Null | Key | Default             | Extra          |
+-------------------+-------------+------+-----+---------------------+----------------+
| customer_id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| store_id          | int(11)     | NO   |     | 0                   |                |
| firstname         | varchar(32) | NO   |     |                     |                |
| lastname          | varchar(32) | NO   |     |                     |                |
| email             | varchar(96) | NO   |     |                     |                |
| telephone         | varchar(32) | NO   |     |                     |                |
| fax               | varchar(32) | NO   |     |                     |                |
| password          | varchar(40) | NO   |     |                     |                |
| cart              | text        | YES  |     | NULL                |                |
| wishlist          | text        | YES  |     | NULL                |                |
| newsletter        | tinyint(1)  | NO   |     | 0                   |                |
| address_id        | int(11)     | NO   |     | 0                   |                |
| customer_group_id | int(11)     | NO   |     | NULL                |                |
| ip                | varchar(15) | NO   |     | 0                   |                |
| status            | tinyint(1)  | NO   |     | NULL                |                |
| approved          | tinyint(1)  | NO   |     | NULL                |                |
| date_added        | datetime    | NO   |     | 0000-00-00 00:00:00 |                |
+-------------------+-------------+------+-----+---------------------+----------------+

The password field was set to a dummy value so the customer needed to reset it when logging in, there is an existing web interface for that functionality (to get a new password via email). The SQL statement needed to construct entries for one dummy customer follows. It seem to be too expensive to buy a module extension to OpenCart for, e.g. 25 dollars, to import data on this simple format.

INSERT INTO customer(
      firstname,lastname,email,
      telephone,fax,password,newsletter,status,approved,date_added)
   VALUES(
      'Peter','Jansson','peter.jansson@the.net',
      '+46-(0)18-4951199','','dummy',1,1,1,NOW());

A trivial character search and replace method in C++

A very long time ago I was searching for ways to replace individual characters in a string and some of the methods was summarized in a small test program. I found that test program in an old folder on my computer which I almost forgot. So, I share that little code with the world. It follows.

#include <algorithm>
#include <string>
#include <iostream>

using namespace std;

bool is_punct( const char& c )
{
 return ispunct( c );
}

bool is_l( const char& c )
{
 return 'l'==c;
}

struct mycomp
{
 bool operator()(const char& o)
 {
  return ispunct( o );
 }
};

int main()
{
 // Remove all characters that "ispunct" using a function.
 string s = "hello, world!";
 s.erase( remove_if( s.begin(), s.end(), &is_punct ), s.end() );
 cout << s << '\n';

 // Remove all characters that "ispunct" using a functor.
 s = "hello, world!";
 s.erase( remove_if( s.begin(), s.end(), mycomp() ), s.end() );
 cout << s << '\n';

 // Replace all 'l' with 'L' using a function.
 s = "hello, world!";
 replace_if( s.begin(), s.end(), &is_l, 'L' );
 cout << s << '\n';
}

Creating a histogram in SQL using event data

Sometimes you may have gathered some data on event-by-event basis. For instance, the data may look like this when pulses from a radioactive decay have been processed by a peak sensing analog to digital converter (ADC):

2
2
2
8
8
8
8
8
8
8
8
8
100
100
250
250

A simple table in a relational database may be created to store this data:

CREATE TABLE samples( channel INTEGER NOT NULL );

BEGIN TRANSACTION;
INSERT INTO samples VALUES(2);
INSERT INTO samples VALUES(2);
INSERT INTO samples VALUES(2);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(100);
INSERT INTO samples VALUES(100);
INSERT INTO samples VALUES(250);
INSERT INTO samples VALUES(250);
COMMIT;

I found the following SQL statement somewhere on the net (if you find it, please let me know where so that I can pay the appropriate tribute to its source). It is a demonstration of how to create a 1024 channel histogram with the above data. I hope you find it useful.

SELECT bin, COUNT(1) AS cnt
FROM (SELECT CAST((channel-mn) / (1.0*range/1024) AS INTEGER) + 1 AS bin
      FROM (SELECT MIN(channel) AS mn, MAX(channel)-MIN(channel)+1 AS range
            FROM samples) AS R
         CROSS JOIN
           (SELECT * FROM samples) AS S) AS RS
GROUP BY bin;

Using SQLite as database engine, I get the following output after executing the above SQL statements.

1|3
25|9
404|2
1020|2