Export Gift Certificates Into Orders?


I have a client who would like to be able to include a column for gift certificate codes when they take a csv export of their orders.

I've added additional columns to the export sheet before by editing the app/schemas/exim/orders.php file. for example I added a column for the subtotal discount by adding the lines:

'Subtotal Discount' => array(
            'db_field' => 'subtotal_discount'

This is straight forward because subtotal discount already exists within the orders table in the database.

With gift certificates the gift certificate code is stored in the cscart_gift_certificates table and the information about when a gift certificate has been used is in the cscart_gift_certificates_log table. So I can't simply use the method above to download them.

I've tried creating an addon which will add these values to the orders table, the code for which is as follows.

<?xml version="1.0"?>

Export Gift Certificate
allows you to export gift certificate details in order export

ALTER TABLE `cscart_orders` ADD `certificate` VARCHAR(50) AFTER `subtotal_discount` ;
ALTER TABLE `cscart_orders` ADD `cert_id` VARCHAR(50) AFTER `subtotal_discount` ;
INSERT INTO `cscart_orders` (`cert_id`) SELECT `gift_cert_id` FROM `cscart_gift_certificates_log` WHERE `cscart_gift_certificates_log`(`order_id`) = `cscart_orders` (`order_id`);
INSERT INTO `cscart_orders` (`certificate`) SELECT `gift_cert_code` FROM `cscart_gift_certificates` WHERE `cscart_gift_certificates`(`gift_cert_id`) = `cscart_orders` (`cert_id`);
 ALTER TABLE `cscart_orders` DROP `certificate`;
ALTER TABLE `cscart_orders` DROP `cert_id`;

when i install it however it gives the following error shown in the image attached. [attachment=11839:example.JPG]

I know my insert into lines must be wrong. Could someone please suggest how I should be doing this?


H all, for accounting purposes I'm also looking for a way to export the use of gift certificates and the amount used per order. Would anyone know of an add-on or so?