Search

Showing posts with label Magento2. Show all posts
Showing posts with label Magento2. Show all posts

Jan 7, 2025

Backup and update table data via the csv file in magento 2

1. Backup data table to csv file (using datagrip)

catalog_product_entity.csv

entity_id,sku,quickbooks_entity,quickbooks_sync_token

86304,15100_SVBKB_9,30,0

86307,15100_SVBKB_8,31,0

86310,15100_SVBKB_7,32,0

86313,15100_SVBKB_6,33,0

86316,15100_SVBKB_12,34,0

86319,15100_SVBKB_11,35,0


2. Put catalog_product_entity.csv to magento/pub/catalog_product_entity.csv


3. Create file restore.php in magento/pub/restore.php

<?php
use \Magento\Framework\App\Bootstrap;

include('../app/bootstrap.php');

$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

// Get connection
$host = '127.0.0.1';
$dbname = 'dddd';
$username = 'aaaa';
$password = 'ssss';

$tableName = 'catalog_product_entity';
$filePath = 'catalog_product_entity.csv';

// Connect to MariaDB
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Open the CSV file
$csvFile = fopen($filePath, 'r');
fgetcsv($csvFile);

$sql = "UPDATE $tableName SET quickbooks_entity = ?, quickbooks_sync_token = ? WHERE entity_id = ?";
$stmt = $conn->prepare($sql);

while (($data = fgetcsv($csvFile, null, ',')) !== FALSE) {
/**
* entity_id,sku,quickbooks_entity,quickbooks_sync_token
* 0: entity_id
* 2: quickbooks_entity
* 3: quickbooks_sync_token
*/
$entity_id = $data[0];
$quickbooks_entity = $data[2];
$quickbooks_sync_token = $data[3];

if ($entity_id && $quickbooks_entity) {
$stmt->execute([$quickbooks_entity, $quickbooks_sync_token, $entity_id]);
echo "Updated entity_id = $entity_id, quickbooks_entity = $quickbooks_entity, quickbooks_sync_token = $quickbooks_sync_token \n";
}
}

fclose($csvFile);

$conn = null;
echo "\n\n $filePath imported successfully!";



4. Run `php restore.php` in terminal



Sep 20, 2024

The Stripe card is not being saved for the user after placing an order in Admin Magento

Step by step:

1. Go to admin magento 2. Navigate to Sales > Orders. 

2. Click "Create new order" button. Select an existing customer.

3. Add products. Fill in the Shipping and Billing addresses.

4. Add a new payment method (credit card). Check "Save card on customer". 

5. Complete the order by submitting it.

6. On the frontend, log in as the customer from step 2.

7. Navigate to My Account > Stored Payment Methods.

8. Actual result: The new card is not saved in the "Stored Payment Methods" section.

9. Expected result: The new card should be saved in the "Stored Payment Methods" section. 

Environment: Magento EE 2.4.6-p3 + stripe/module-payments 4.0.10


FIXED: StripeIntegration/Payments/Helper/Generic.php

public function assignPaymentData($payment, $data)
{
$this->resetPaymentData($payment);

if ($this->isMultiShipping())
{
$payment->setAdditionalInformation("payment_location", "Multishipping checkout");
}
else if ($this->isAdmin())
{
$payment->setAdditionalInformation("payment_location", "Admin area");

/**
* The card is not being saved for the user after placing an order in Magento
* $data = {"payment_method":"pm_1Q0xfP2MVsduPvSkD8t4bjfw","save_payment_method":"on"}
*/
if (isset($data['save_payment_method'])) {
$payment->setAdditionalInformation("save_payment_method", $data['save_payment_method']);
}
}

Jul 25, 2024

Magento 2: Export Gift cards data - download csv file

Required task: 

Export Giftcards with the below header:

ID, code, website, created, end, active, status, balance, payment_method, email (creator email), email (sent to), order id

Solution 1: Use only SQL

SELECT gc.giftcardaccount_id AS ID,
gc.code,
gc.website_id AS website,
gc.date_created AS `created`,
gc.date_expires AS `end`,
gc.status AS `active`,

CASE
WHEN gc.state = 0 THEN 'AVAILABLE'
WHEN gc.state = 1 THEN 'USED'
WHEN gc.state = 2 THEN 'REDEEMED'
WHEN gc.state = 3 THEN 'EXPIRED'
END AS `status`,
gc.balance,

CASE
WHEN gch.additional_info LIKE 'Order #%'
THEN TRIM(BOTH 'Order #' FROM
SUBSTRING_INDEX(SUBSTRING_INDEX(gch.additional_info, 'Order #', -1), '.', 1))
ELSE null
END AS order_id,

IF(soi.price = 0, 'reward_points', sop.method) AS payment_method,
soi.sender_email,
soi.recipient_email,
gch.additional_info AS info

FROM magento_giftcardaccount AS gc

LEFT JOIN magento_giftcardaccount_history AS gch
ON gc.giftcardaccount_id = gch.giftcardaccount_id AND gch.action = '0'

LEFT JOIN
sales_order AS so
ON TRIM(BOTH 'Order #' FROM
SUBSTRING_INDEX(SUBSTRING_INDEX(gch.additional_info, 'Order #', -1), '.', 1)) = so.increment_id

LEFT JOIN sales_order_payment AS sop
ON so.entity_id = sop.parent_id

LEFT JOIN (
SELECT soi.order_id,
MAX(CASE WHEN qio.code = 'giftcard_sender_email' THEN qio.value END) AS sender_email,
MAX(CASE WHEN qio.code = 'giftcard_recipient_email' THEN qio.value END) AS recipient_email,
soi.price
FROM sales_order_item AS soi
LEFT JOIN quote_item_option AS qio
ON soi.quote_item_id = qio.item_id
WHERE soi.product_type = 'giftcard'
GROUP BY soi.order_id) as soi
ON soi.order_id = so.entity_id

order by ID desc;


Solution 2: Create "Download Giftcards CSV" button on admin page

Go to Admin > Report > Download Giftcards CSV
Create below files in app/code folder:

 

Lexim/GiftcardReport/registration.php
<?php

\Magento\Framework\Component\ComponentRegistrar::register(
\Magento\Framework\Component\ComponentRegistrar::MODULE,
'Lexim_GiftcardReport',
__DIR__
);


Lexim/GiftcardReport/etc/module.xml
<?xml version="1.0"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
<module name="Lexim_GiftcardReport" setup_version="1.0.0">
<sequence>
<module name="Magento_GiftCard" />
</sequence>
</module>
</config>


Lexim/GiftcardReport/etc/adminhtml/menu.xml
<?xml version="1.0"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Backend:etc/menu.xsd">

<menu>
<add id="Lexim_GiftcardReport::giftcard_menu"
title="Gift Cards"
translate="title"
module="Lexim_GiftcardReport"
sortOrder="100"
parent="Magento_Reports::report"
resource="Lexim_GiftcardReport::giftcard_menu"/>


<add id="Lexim_GiftcardReport::csv_export"
title="Download Gift Cards CSV" translate="title"
module="Lexim_GiftcardReport"
sortOrder="10"
parent="Lexim_GiftcardReport::giftcard_menu"
resource="Lexim_GiftcardReport::csv_export"
action="giftcardreport/csv/export"/>

</menu>

</config>


Lexim/GiftcardReport/etc/adminhtml/routes.xml
<?xml version="1.0"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:App/etc/routes.xsd">
    <router id="admin">
<route id="giftcardreport" frontName="giftcardreport">
<module name="Lexim_GiftcardReport" />
</route>
</router>
</config>


Lexim/GiftcardReport/etc/acl.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Acl/etc/acl.xsd">

<acl>
<resources>
<resource id="Magento_Backend::admin">
<resource id="Magento_Reports::report">

<resource id="Lexim_GiftcardReport::giftcard_menu"
                        title="Gift Cards" sortOrder="100">
<resource id="Lexim_GiftcardReport::csv_export"
                            title="Download Gift Cards CSV" sortOrder="10"/>
</resource>

</resource>
</resource>
</resources>
</acl>

</config>


Lexim/GiftcardReport/Controller/Adminhtml/Index.php
<?php

namespace Lexim\GiftcardReport\Controller\Adminhtml;

use Magento\Backend\App\Action;
use Laminas\Log\Writer\Stream;
use Laminas\Log\Logger;

abstract class Index extends Action
{

const LOGGER_NAME = 'Lexim_GiftcardReport';

/** @var Logger */
protected $logger;

/**
* @param Action\Context $context
*/
public function __construct(
\Magento\Backend\App\Action\Context $context,
) {
parent::__construct($context);

$writer = new Stream(BP . '/var/log/' . self::LOGGER_NAME . '.log');
$this->logger = new Logger();
$this->logger->addWriter($writer);
}
}



Lexim/GiftcardReport/Controller/Adminhtml/Csv/Export.php
<?php

namespace Lexim\GiftcardReport\Controller\Adminhtml\Csv;

use Magento\Framework\App\Action\HttpGetActionInterface;
use Magento\Backend\App\Action\Context;
use Magento\Framework\App\Response\Http\FileFactory;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Filesystem\Io\File;

class Export extends \Lexim\GiftcardReport\Controller\Adminhtml\Index
    implements HttpGetActionInterface
{
/** @var FileFactory */
protected $fileFactory;

/** @var ResourceConnection */
protected $resourceConnection;

/** @var File */
protected $file;

/**
* @param Context $context
* @param FileFactory $fileFactory
* @param ResourceConnection $resourceConnection
* @param File $file
*/
public function __construct(
Context $context,
FileFactory $fileFactory,
ResourceConnection $resourceConnection,
File $file
) {
parent::__construct($context);
$this->fileFactory = $fileFactory;
$this->resourceConnection = $resourceConnection;
$this->file = $file;
}

/**
* @throws \Exception
*/
public function execute()
{
try {
// Format the date and time
$currentDateTime = date('YFd-H\hi');
$fileName = 'GiftCards.' . $currentDateTime . '.csv';
$directory = BP . '/var/export/';
$filePath = $directory . $fileName;

// Ensure the directory exists
if (!$this->file->fileExists($directory)) {
$this->file->mkdir($directory, 0775);
}

$connection = $this->resourceConnection->getConnection();

// Optimized SQL query
$select = $connection->select()
->from(['gc' => 'magento_giftcardaccount'], [
'ID' => 'gc.giftcardaccount_id',
'gc.code',
'website' => 'gc.website_id',
'created' => 'gc.date_created',
'end' => 'gc.date_expires',
'active' => 'gc.status',
'status' => new \Zend_Db_Expr("
CASE
WHEN gc.state = 0 THEN 'AVAILABLE'
WHEN gc.state = 1 THEN 'USED'
WHEN gc.state = 2 THEN 'REDEEMED'
WHEN gc.state = 3 THEN 'EXPIRED'
END
"),
'gc.balance',
'order_id' => new \Zend_Db_Expr("
IF(gch.additional_info LIKE 'Order #%',
TRIM(BOTH 'Order #' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(gch.additional_info, 'Order #', -1), '.', 1)), NULL)
"),
'payment_method' => new \Zend_Db_Expr("
IF(soi.price = 0, 'reward_points', sop.method)
"),
'soi.sender_email',
'soi.recipient_email',
'gch.additional_info AS info'
])
->joinLeft(
['gch' => 'magento_giftcardaccount_history'],
'gc.giftcardaccount_id = gch.giftcardaccount_id
AND gch.action = \'0\'',
[]
)
->joinLeft(
['so' => 'sales_order'],
'TRIM(BOTH \'Order #\' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(gch.additional_info, \'Order #\', -1), \'.\', 1)) = so.increment_id',
[]
)
->joinLeft(
['sop' => 'sales_order_payment'],
'so.entity_id = sop.parent_id',
[]
)
->joinLeft(['soi' => new \Zend_Db_Expr("
(SELECT soi.order_id,
MAX(CASE WHEN qio.code = 'giftcard_sender_email'
THEN qio.value END) AS sender_email,
MAX(CASE WHEN qio.code = 'giftcard_recipient_email'
THEN qio.value END) AS recipient_email,
soi.price
FROM sales_order_item AS soi
LEFT JOIN quote_item_option AS qio
ON soi.quote_item_id = qio.item_id
WHERE soi.product_type = 'giftcard'
GROUP BY soi.order_id)
")], 'soi.order_id = so.entity_id', [])
->order('ID DESC');

$data = $connection->fetchAll($select);

$file = fopen($filePath, 'w+');
fputcsv($file, array_keys($data[0])); // add headers
foreach ($data as $row) {
fputcsv($file, $row);
}
fclose($file);

$response = $this->fileFactory->create(
$fileName,
[
'type' => 'filename',
'value' => $filePath,
'rm' => true
],
\Magento\Framework\App\Filesystem\DirectoryList::VAR_DIR,
'application/octet-stream'
);

return $response;

} catch (\Exception $e) {
$this->logger->crit('Error downloading and exporting gift card data: '
. $e->getMessage());
throw new \Exception($e);
}
}
}


May 14, 2024

Magento 2.4 product stock status not change after salable quantity become zero

 From Niraj Patel issue: 

I tried the below scenario in Magento 2.4.0 version

Anyone please try the below scenario in your Magento 2.4 version ?

I created one configurable product and it's two simple children with 100 qty.

Before Order place: I went to Admin Product Grid there are two columns Quantity and Salable Quantity. One child product has 100 Quantity and Salable Quantity are 100

After Placed order: In Admin product grid, same child product has salable quantity is 0 and quantity column shows 100 quantity and when I went product edit page it still shows in stock status even no backorders and Manages stock set to yes and I did reindex.

Expected Result: when the product becomes zero salable quantity it should automatically change stock status out of stock

Actual Result: when the product becomes zero salable quantity it does not change stock status from in stock to `out of stock

Anyone faces above issue in your Magento 2.4 version ?

____________________

My SOLUTION: 
Create Lexim_InventoryConfigurableProduct extension


etc/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Inventory\Model\SourceItem\Command\DecrementSourceItemQty">
        <plugin name="update_parent_configurable_product_stock_status_in_legacy_stock" disabled="true"/>
        <plugin name="update_parent_configurable_product_stock_status_in_legacy_stock_fix" type="Lexim\InventoryConfigurableProduct\Plugin\InventoryApi\UpdateParentStockStatusInLegacyStockPlugin"/>
    </type>
</config>



Plugin\InventoryApi\UpdateParentStockStatusInLegacyStockPlugin.php

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */

namespace Lexim\InventoryConfigurableProduct\Plugin\InventoryApi;

use Magento\Framework\Exception\NoSuchEntityException;
use Magento\Inventory\Model\SourceItem\Command\DecrementSourceItemQty;
use Magento\InventoryApi\Api\Data\SourceItemInterface;
use Magento\InventoryCatalogApi\Api\DefaultSourceProviderInterface;
use Magento\InventoryCatalogApi\Model\GetProductIdsBySkusInterface;
use Magento\ConfigurableProduct\Model\Inventory\ChangeParentStockStatus;

/**
 * Apply a fix for update configurable product stock status in legacy stock
 * after decrement quantity of child stock item
 */
class UpdateParentStockStatusInLegacyStockPlugin
{
    /**
     * @var DefaultSourceProviderInterface
     */
    private $defaultSourceProvider;

    /**
     * @var ChangeParentStockStatus
     */
    private $changeParentStockStatus;

    /**
     * @var GetProductIdsBySkusInterface
     */
    private $getProductIdsBySkus;

    /**
     * @param DefaultSourceProviderInterface $defaultSourceProvider
     * @param GetProductIdsBySkusInterface $getProductIdsBySkus
     * @param ChangeParentStockStatus $changeParentStockStatus
     */
    public function __construct(
        DefaultSourceProviderInterface $defaultSourceProvider,
        GetProductIdsBySkusInterface $getProductIdsBySkus,
        ChangeParentStockStatus $changeParentStockStatus
    ) {
        $this->defaultSourceProvider = $defaultSourceProvider;
        $this->getProductIdsBySkus = $getProductIdsBySkus;
        $this->changeParentStockStatus = $changeParentStockStatus;
    }

    /**
     * Make configurable product out of stock if all its children out of stock
     *
     * @param DecrementSourceItemQty $subject
     * @param void $result
     * @param SourceItemInterface[] $sourceItemDecrementData
     * @return void
     * @throws NoSuchEntityException
     */
    public function afterExecute(DecrementSourceItemQty $subject, $result, array $sourceItemDecrementData): void
    {
        $productIds = [];
        $sourceItems = array_column($sourceItemDecrementData, 'source_item');

        /** @var SourceItemInterface $sourceItem */
        foreach ($sourceItems as $sourceItem) {
            $sku = $sourceItem->getSku();
            if ($sourceItem->getSourceCode() === $this->defaultSourceProvider->getCode()
                && $productId = ($this->getProductIdsBySkus->execute([$sku])[$sku] ?? null)
            ) {
                $productIds[] = (int) $productId;
            }
        }

        if ($productIds) {
            $this->changeParentStockStatus->execute($productIds);
        }
    }
}



registration.php

<?php
\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'Lexim_InventoryConfigurableProduct',
    __DIR__
);



module.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Lexim_InventoryConfigurableProduct" setup_version="1.0.0" />
</config>