1

I'm doing some messing with MySQL to import some Espresso Store voucher codes into a Craft Commerce site as Verbb Gift Vouchers.

I've got a long query that basically reads the data from the EE table(s) and creates rows in giftvoucher_codes table. It also creates a corresponding row in the elements and elements_sites tables.

Everything seems to join up and get created but I still don't see the imported vouchers in the Gift Vouchers area of Craft's control panel.

I've tried clearing caches with no luck.

What am I missing? What else needs to be updated to get them to show up?

1

I think you'd be better off creating a quick module with a controller method to import your voucher codes, to make sure Craft (and Verbb Gift) create all the necessary table rows for the vouchers to work (code is untested, but just to get an idea):

use verbb\giftvoucher\elements\Voucher;

public function actionCopyVouchers()
{
    $esVouchers = Craft::$app->db->createCommand()->select('*')->from('es_vouchers')->all();

    foreach ($esVouchers as $esVoucher) {
        $voucher = new Voucher();
        $voucher->field = $esVoucher->field;
        $voucher->anotherField = $esVoucher->anotherField;
        // ... Whatever your voucher needs ...

        Craft::$app->getElements()->saveElement($voucher);
    }
}

4
  • You're probably right. But initially it seemed I could just do a straight insert into the voucher code table. Getting the data requires doing some joins and a bit of fiddling to figure out the balance left on a voucher - which I still need to do. I'm invested in the SQL route now :) – foamcow Mar 12 '20 at 15:01
  • 1
    "But initially it seemed ..." <- famous last words ;) – Paul Mar 12 '20 at 15:02
  • Haha, yes. Though I'm sure it would have taken me just as long to figure out all the foibles doing it with PHP. – foamcow Mar 12 '20 at 15:07
  • I will not lie ... had to lookup "foibles" – Paul Mar 12 '20 at 15:56
1

Found the solution.

It's also necessary to create a row in the content table.

Here's the query in case anyone would find it useful. Bear in mind I've hard coded the voucher type id as 7461 and the field layout ID for the gift voucher 34.

# Query 1 Get all giftcards created from 1st Dec 2018


# Remove stored function
DROP FUNCTION IF EXISTS createGiftCardElement;

# Stored procedure to create a giftcard element
DELIMITER $$
CREATE FUNCTION createGiftCardElement(
    created_date DATETIME
) RETURNS INT
BEGIN
    DECLARE lastElementId INT;
    SET lastElementId = 0;
    SET @thisTime = created_date;
    INSERT INTO elements VALUES(null,null,null,34,'verbb\\giftvoucher\\elements\\Code',1,0,@thisTime,@thisTime,null,UUID());
    SELECT LAST_INSERT_ID() INTO lastElementId;
    INSERT INTO elements_sites VALUES(null,lastElementId,1,null,null,1,@thisTime,@thisTime,UUID());
    INSERT INTO content(`id`,`elementId`,`siteId`,`title`,`dateCreated`,`dateUpdated`,`uid`) VALUES(null,lastElementId,1,null,@thisTime,@thisTime,UUID());
    RETURN lastElementId;
END$$
DELIMITER ;

# Insert a giftcard into new giftcards table
INSERT INTO giftvoucher_codes (
    id,
    voucherId,
    orderId,
    lineItemId,
    codeKey,
    originalAmount,
    currentAmount,
    expiryDate,
    dateCreated,
    dateUpdated,
    uid
)
SELECT
    createGiftCardElement(),
    7461,
    null,
    null,
    gc.code,
    gc.initial_value,
    gc.initial_value,
    expiry_date,
    created_date,
    created_date,
    UUID()
FROM exp_store_giftcard gc
WHERE created_date > '2018-12-01 00:00:00'
GROUP BY gc.code
ORDER BY created_date asc


#########################################################



# QUERY 2 update voucher balance

DROP FUNCTION IF EXISTS voucherSpend;


DELIMITER $$
CREATE FUNCTION voucherSpend(
    codeKey VARCHAR(255)
)
RETURNS DECIMAL(12,2)
BEGIN
    DECLARE spend DECIMAL(12,2);
    SET spend = 0;

    SELECT
    SUM(esgt.amount) into spend
    FROM exp_store_giftcard esg
    RIGHT JOIN exp_store_giftcard_transactions esgt ON esgt.giftcard_id = esg.id
    WHERE esg.code = codeKey AND esg.created_date > '2018-12-01 00:00:00'
    and void_date IS NULL
    GROUP BY esg.id;

    RETURN spend;
END$$
DELIMITER ;


UPDATE giftvoucher_codes gvc
SET gvc.currentAmount = gvc.originalAmount - voucherSpend(gvc.codeKey)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.