[Jmoffat]

Hi 

 

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"?>
<addon scheme="2.0">
<id>gc_export</id>
<name>Export Gift Certificate</name>
<description>allows you to export gift certificate details in order export</description>
<version>1.0</version>
<priority>100500</priority>
<status>active</status>
<queries>
<item for="install">ALTER TABLE `cscart_orders` ADD `certificate` VARCHAR(50) AFTER `subtotal_discount` ;</item>
<item for="install">ALTER TABLE `cscart_orders` ADD `cert_id` VARCHAR(50) AFTER `subtotal_discount` ;</item>
<item for="install">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`);</item>
<item for="install">INSERT INTO `cscart_orders` (`certificate`) SELECT `gift_cert_code` FROM `cscart_gift_certificates` WHERE `cscart_gift_certificates`(`gift_cert_id`) = `cscart_orders` (`cert_id`);</item>
<item for="uninstall"> ALTER TABLE `cscart_orders` DROP `certificate`;</item>
<item for="uninstall">ALTER TABLE `cscart_orders` DROP `cert_id`;</item>
</queries>
</addon>

when i install it however it gives the following error shown in the image attached.   example.JPG   34.11KB   0 downloads

 

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

[Flow]

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?