<p>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):</p>

<pre><code>use verbb\giftvoucher\elements\Voucher;

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

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

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

</code></pre>

CC BY-SA 4.0
2020-03-12T15:01:58.360Z
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
CC BY-SA 4.0
2020-03-12T15:02:47.063Z
"But initially it seemed ..." <- famous last words ;)
Paul
CC BY-SA 4.0
2020-03-12T15:07:53.417Z
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
CC BY-SA 4.0
2020-03-12T15:56:31.063Z
I will not lie ... had to lookup "foibles"
Paul
CC BY-SA 4.0
2020-03-12T14:59:01.523Z
2020-03-12T14:59:01.523Z
Paul
<p>Found the solution.</p>

<p>It's also necessary to create a row in the <code>content</code> table.</p>

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

<pre><code># 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 &gt; '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 &gt; '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)
</code></pre>

CC BY-SA 4.0
2020-03-12T14:59:31.820Z
2020-03-14T10:47:17.993Z
2020-03-14T10:47:17.993Z
foamcow
<p>I'm doing some messing with MySQL to import some Espresso Store voucher codes into a Craft Commerce site as Verbb Gift Vouchers.</p>

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

<p>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.</p>

<p>I've tried clearing caches with no luck.</p>

<p>What am I missing? What else needs to be updated to get them to show up?</p>

<craft3><import>
Importing voucher codes from Espresso Store to Verbb Gift Vouchers
# Importing voucher codes from Espresso Store to Verbb Gift Vouchers

(Asked by: username_0 on 2020-03-12)

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?

**Tags:** craft3, import

---

## 2 answers

### Answer by username_1 on 2020-03-12. Score: 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);
    }
}

> **Comments:**
> **username_0**: 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 :)  
> **username_1**: "But initially it seemed ..." <- famous last words ;)  
> **username_0**: Haha, yes. Though I'm sure it would have taken me just as long to figure out all the foibles doing it with PHP.  
> **username_1**: I will not lie ... had to lookup "foibles"  

---

### Answer by username_0 on 2020-03-12. Score: 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)


---

