How to restrict import order by date ?

Hi team,
I would like to know if your UB DataMigration Pro permit to include a param to restrict import orders by date (in command line ssh mode) ?
My goal is to transfert only the last 2 years ORDERS of my magento 1.9 website to the new Magento 2.4 (because i had very old and unecessary orders in my magento 1.9 database who surcharge the db for nothing)
If no param exist to limit the orders with a specific date, please can you tell me what file i can modify to add SQL filter on query whith something like this :

WHERE date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 2 YEAR)

Thanks a lot for your help on this point.
Florent

5 answers

Profile photo of Mall Staff 156270.00 $tone November 5, 2020
Public

Hi Florent,

please can you tell me what file i can modify to add SQL filter on query whith something like this :

You can perform the migration that way at your end following these steps:

1. Open the PHP file at: pub/ub-tool/protected/controllers/Step7Controller.php
and find the code lines:

if ($this->runMode == UBMigrate::RUN_MODE_DELTA) {
$lastMigrationTime = UBMigrate::getLastMigrationTime($this->stepIndex, 'sales_flat_order');
if (!empty($condition)) {
$condition .= " AND updated_at >= '{$lastMigrationTime}'";
} else {
$condition = "updated_at >= '{$lastMigrationTime}'";
}
}
//get data by limit and offset
$salesOrders = UBMigrate::getListObjects('Mage1SalesOrder', $condition, $offset3, $this->limit, "entity_id ASC");

and replace it with: 

if ($this->runMode == UBMigrate::RUN_MODE_DELTA) {
$lastMigrationTime = UBMigrate::getLastMigrationTime($this->stepIndex, 'sales_flat_order');
if (!empty($condition)) {
$condition .= " AND updated_at >= '{$lastMigrationTime}'";
} else {
$condition = "updated_at >= '{$lastMigrationTime}'";
}
}

//NOTE: You can append additional filter by datetimes as you want into the $condition variables here.

//get data by limit and offset
$salesOrders = UBMigrate::getListObjects('Mage1SalesOrder', $condition, $offset3, $this->limit, "entity_id ASC");

I hope that helps. 
Regards,
Mall.

#1
Profile photo of BIGAND JEROME 490.00 $tone November 5, 2020
Public

Hi Mall,
Thanks a lot for your prompt reply, sorry but not sure to understand well.
Can you confirm if I can modify the code like this, and can you confirm I need to use a AND clause here and not WHERE clause ? can I use ` to delimit field name ?

if ($this->runMode == UBMigrate::RUN_MODE_DELTA) {
$lastMigrationTime = UBMigrate::getLastMigrationTime($this->stepIndex, 'sales_flat_order');
if (!empty($condition)) {
$condition .= " AND updated_at >= '{$lastMigrationTime}'";
} else {
$condition = "updated_at >= '{$lastMigrationTime}'";
}
}

// ADD CONDITION HERE to exlude too old orders (morethan 2 year) and canceled orders:
$condition .= " AND `state` != 'canceled' AND `created_at` BETWEEN DATE_FORMAT(DATE_SUB( NOW() , INTERVAL 2 YEAR),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')";

//get data by limit and offset
$salesOrders = UBMigrate::getListObjects('Mage1SalesOrder', $condition, $offset3, $this->limit, "entity_id ASC");

Also, can you tell me if I need to add these conditions 2 times at line 250 (for table orders) and 291 (for table quotes) to also not integrate the old quotes during the migration process ?

#2
Profile photo of Mall Staff 156270.00 $tone November 5, 2020
Public

Hi there,

Can you confirm if I can modify the code like this, and can you confirm I need to use a AND clause here and not WHERE clause ? can I use ` to delimit field name ?

You need to use the ‘ AND ‘ to joins condition strings.

Also, can you tell me if I need to add these conditions 2 times at line 250 (for table orders) and 291 (for table quotes) to also not integrate the old quotes during the migration process ?

Yes, you can apply the same way for the quotes.

Regards,
Mall.
 

#3
Profile photo of BIGAND JEROME 490.00 $tone November 5, 2020
Public

Does this exact string seems correct for you (with ` to delimit field name ):

$condition .= " AND `state` != 'canceled' AND `created_at` BETWEEN DATE_FORMAT(DATE_SUB( NOW() , INTERVAL 2 YEAR),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')";
#4
Profile photo of Mall Staff 156270.00 $tone November 5, 2020
Public

Hi there,

$condition .= " AND `state` != 'canceled' AND `created_at` BETWEEN DATE_FORMAT(DATE_SUB( NOW() , INTERVAL 2 YEAR),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')";

Yes, you can use that code to append as my first replies to get your goals:

My goal is to transfert only the last 2 years ORDERS

Regards,
Mall.

#5

Please login or Register to Submit Answer

Written By

Comments