Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-12T15:01:58.360Z
Answers.Text: 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 :)
Answers.UserDisplayName: foamcow
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-12T15:02:47.063Z
Answers.Text: "But initially it seemed ..." <- famous last words ;)
Answers.UserDisplayName: Paul
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-12T15:07:53.417Z
Answers.Text: Haha, yes. Though I'm sure it would have taken me just as long to figure out all the foibles doing it with PHP.
Answers.UserDisplayName: foamcow
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-12T15:56:31.063Z
Answers.Text: I will not lie ... had to lookup "foibles"
Answers.UserDisplayName: Paul
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-12T14:59:01.523Z
Answers.LastActivityDate: 2020-03-12T14:59:01.523Z
Answers.UserDisplayName: Paul
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-12T14:59:31.820Z
Answers.LastActivityDate: 2020-03-14T10:47:17.993Z
Answers.LastEditDate: 2020-03-14T10:47:17.993Z
Answers.UserDisplayName: foamcow
Body: <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>
  
Tags: <craft3><import>
Title: Importing voucher codes from Espresso Store to Verbb Gift Vouchers
ThreadText: # 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)
  
  ```
  
  
  ---
  
  