/
Reconciliation Product Cron

Reconciliation Product Cron

Owned by harshita.shinde
Last updated: Dec 22, 2023
8 min read

 

Step 1: Get the list of all connections against the integration type of Woocommerce

SELECT DISTINCT connectionId AS connectionIds FROM channels WHERE integrationType =:integrationType AND channels.status='0'

 

Step : 2 Check for each connection if the app automation stepId is active.

SELECT ie_appinstall_connection.id AS connectionId, ie_appinstall_connection.inputFields, ie_app_automation_steps.id AS appAutomationStepId, ie_app_automation_steps.appId, ie_app_automation_steps_config.isActive, ie_app_automation_steps_config.latestSyncTimeStamp AS latestSyncTimeStamp, ie_appinstall_connection.isActive AS appIsActive FROM ie_appinstall_connection LEFT JOIN ie_app_installation ON ie_app_installation.appInstallationId = ie_appinstall_connection.appInstallationId LEFT JOIN ie_app ON ie_app.appId = ie_app_installation.appId LEFT JOIN ie_app_automation_steps ON ie_app_automation_steps.appId = ie_app.appId LEFT JOIN ie_app_automation_steps_config ON ie_app_automation_steps_config.connectionId = ie_appinstall_connection.id AND ie_app_automation_steps_config.appAutomationStepId = ie_app_automation_steps.id WHERE ie_appinstall_connection.id IN (:connectionId) AND ie_app_automation_steps_config.isActive = '1';

Step 3: Iterate through each connection whose automation stepId is active and fetch channel Data

SELECT accounts.account_slug AS accountSlug, channels.connectionId AS connectionId, channels.externalChannelId AS externalChannelId, warehouses.externalWarehouseId AS externalWareHouseId FROM channels LEFT JOIN external_wms_channels ON channels.id = external_wms_channels.channel_id LEFT JOIN warehouses ON warehouses.id = external_wms_channels.warehouse_id LEFT JOIN accounts ON channels.account_id = accounts.id LEFT JOIN channel_account_mapping ON channel_account_mapping.external_wms_channel_id = external_wms_channels.id WHERE channels.connectionId =: connectionId AND channel_account_mapping.enrollmentStatus = 'ACTIVE';

  • Once the channelData is Fetch for every connection push the channelData for the respective connection to the taskQueue.

Step 3: Call the Get All Product API of Woocommerce with pagination where per_page we will extract 10 products until we get empty or less than per_page records in the response.

Get ALL Product Woocommerce API:

We will get the “seller-woocommerce-domain” from inputFields, along with consumerkey and consumerSecret.

GET https://{{seller-woocommerce-domain}}/wp-json/wc/v3/products
Authorization: ConsumerKey and ConsumerSecret.


Response:

{ "id": 66, "name": "New-ArrivalBag", "slug": "new-arrivalbag", "permalink": "https://woo-casually-quality-runaway.wpcomstaging.com/product/new-arrivalbag/", "date_created": "2023-12-06T20:02:30", "date_created_gmt": "2023-12-06T14:32:30", "date_modified": "2023-12-06T20:02:30", "date_modified_gmt": "2023-12-06T14:32:30", "type": "simple", "status": "publish", "featured": false, "catalog_visibility": "visible", "description": "<p><img class=\"alignnone size-medium wp-image-67\" src=\"https://woo-casually-quality-runaway.wpcomstaging.com/wp-content/uploads/2023/12/fashionbag-300x300.jpg\" alt=\"\" width=\"300\" height=\"300\" /></p>\n", "short_description": "<p>New fashionable bags in store<br />\n[contact-form]</p>\n", "sku": "Newbag", "price": "1800", "regular_price": "1800", "sale_price": "", "date_on_sale_from": null, "date_on_sale_from_gmt": null, "date_on_sale_to": null, "date_on_sale_to_gmt": null, "on_sale": false, "purchasable": true, "total_sales": 0, "virtual": false, "downloadable": false, "downloads": [], "download_limit": -1, "download_expiry": -1, "external_url": "", "button_text": "", "tax_status": "taxable", "tax_class": "", "manage_stock": false, "stock_quantity": null, "backorders": "no", "backorders_allowed": false, "backordered": false, "low_stock_amount": null, "sold_individually": false, "weight": "0.5", "dimensions": { "length": "12", "width": "13", "height": "14" }, "shipping_required": true, "shipping_taxable": true, "shipping_class": "", "shipping_class_id": 0, "reviews_allowed": true, "average_rating": "0.00", "rating_count": 0, "upsell_ids": [], "cross_sell_ids": [], "parent_id": 0, "purchase_note": "", "categories": [ { "id": 1375, "name": "Women", "slug": "women" } ], "tags": [], "images": [ { "id": 67, "date_created": "2023-12-07T01:30:00", "date_created_gmt": "2023-12-06T14:30:00", "date_modified": "2023-12-07T01:30:00", "date_modified_gmt": "2023-12-06T14:30:00", "src": "https://woo-casually-quality-runaway.wpcomstaging.com/wp-content/uploads/2023/12/fashionbag.jpg", "name": "fashionbag", "alt": "" } ], "attributes": [], "default_attributes": [], "variations": [], "grouped_products": [], "menu_order": 0, "price_html": "<span class=\"woocommerce-Price-amount amount\"><bdi><span class=\"woocommerce-Price-currencySymbol\">&#36;</span>1,800.00</bdi></span>", "related_ids": [ 25, 27, 26, 24 ], "meta_data": [ { "id": 346, "key": "_wpcom_is_markdown", "value": "1" }, { "id": 347, "key": "_last_editor_used_jetpack", "value": "classic-editor" }, { "id": 355, "key": "_product_addons", "value": [] }, { "id": 356, "key": "_product_addons_exclude_global", "value": "0" }, { "id": 372, "key": "group_of_quantity", "value": "" }, { "id": 373, "key": "minimum_allowed_quantity", "value": "" }, { "id": 374, "key": "maximum_allowed_quantity", "value": "" }, { "id": 375, "key": "minmax_do_not_count", "value": "no" }, { "id": 376, "key": "minmax_cart_exclude", "value": "no" }, { "id": 377, "key": "minmax_category_group_of_exclude", "value": "no" }, { "id": 385, "key": "_wpas_done_all", "value": "1" } ], "stock_status": "instock", "has_options": false, "post_password": "", "bundled_by": [], "bundle_stock_status": "instock", "bundle_stock_quantity": null, "bundle_virtual": false, "bundle_layout": "", "bundle_add_to_cart_form_location": "", "bundle_editable_in_cart": false, "bundle_sold_individually_context": "", "bundle_item_grouping": "", "bundle_min_size": "", "bundle_max_size": "", "bundle_price": [], "bundled_items": [], "bundle_sell_ids": [], "jetpack_publicize_connections": [], "jetpack_sharing_enabled": true, "jetpack_likes_enabled": true, "brands": [], "_links": { "self": [ { "href": "https://woo-casually-quality-runaway.wpcomstaging.com/wp-json/wc/v3/products/66" } ], "collection": [ { "href": "https://woo-casually-quality-runaway.wpcomstaging.com/wp-json/wc/v3/products" } ] } }

  • We will check the attributes.variation= true and variations list is not empty if this is the case then we call the get variant API for each variantion present in variantion List.

Variant API

https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products/77/variantions/84

 

Response:

{ "id": 78, "date_created": "2023-12-21T11:03:24", "date_created_gmt": "2023-12-21T05:33:24", "date_modified": "2023-12-21T11:09:51", "date_modified_gmt": "2023-12-21T05:39:51", "description": "", "permalink": "https://woo-briskly-different-dinosaur.wpcomstaging.com/product/jacket-new/?attribute_size=M", "sku": "jacketcheckM", "price": "230", "regular_price": "234", "sale_price": "230", "date_on_sale_from": null, "date_on_sale_from_gmt": null, "date_on_sale_to": null, "date_on_sale_to_gmt": null, "on_sale": true, "status": "publish", "purchasable": true, "virtual": false, "downloadable": false, "downloads": [], "download_limit": -1, "download_expiry": -1, "tax_status": "taxable", "tax_class": "", "manage_stock": true, "stock_quantity": 20, "stock_status": "instock", "backorders": "no", "backorders_allowed": false, "backordered": false, "low_stock_amount": null, "weight": "1", "dimensions": { "length": "12", "width": "13", "height": "14" }, "shipping_class": "", "shipping_class_id": 0, "image": null, "attributes": [ { "id": 0, "name": "size", "slug": "size", "option": "M" } ], "menu_order": 1, "meta_data": [ { "id": 734, "key": "_gift_card_template_default_use_image", "value": "product" }, { "id": 740, "key": "min_max_rules", "value": "no" }, { "id": 741, "key": "variation_group_of_quantity", "value": "" }, { "id": 742, "key": "variation_minimum_allowed_quantity", "value": "" }, { "id": 743, "key": "variation_maximum_allowed_quantity", "value": "" }, { "id": 744, "key": "variation_minmax_do_not_count", "value": "no" }, { "id": 745, "key": "variation_minmax_cart_exclude", "value": "no" }, { "id": 746, "key": "variation_minmax_category_group_of_exclude", "value": "no" } ], "name": "M", "parent_id": 77, "_links": { "self": [ { "href": "https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products/77/variations/78" } ], "collection": [ { "href": "https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products/77/variations" } ], "up": [ { "href": "https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products/77" } ] } }

  • Upon receiving the response from the "PI, along with channel data, we will pass this information to a PubSub Topic.

  • In the MiddleLayer pub-sub listener,, we will map the response with eshopbox identifiers to create a Product.

Step 5 : Process the products fetched from Woocommerce store

Check if product already exists in EshopBox Workspace:

 

Get Product list by identifiers:

GET https://{workspace}.myeshopbox.com/product-engine/api/v1/products/sku

In the response will receieve the event if sku is present in Eshopbox and will also receive ESIN for the corresponding SKU.

 

Case 1: If the product is present mark it available.

Mark Product Availability:

POST https://{workspace}.myeshopbox.com/product-engine/api/v2/productListing

Request Body

{ "esin" : "0SGAT12SG25F", "channelCode" : "CH1234", "availability" : true }

Case 2: If product is not present:

Check the configuration of product sync for that connection:
If enableProductCreation = 1 then create product

Create a Product:

POST https://{workspace}.myeshopbox.com/product-engine/api/v1/products

 

Mapping for creating product in WMS

Eshopbox Keys

Woocommerce Identifier

Eshopbox Keys

Woocommerce Identifier

sku

Newbag

ean

 

additionalNames

 "sku": "Newbag",

brand

 brands[]

description

 "short_description": "\u003cp\u003eNew fashionable bags in store\u003cbr /\u003e\n[contact-form]\u003c/p\u003e\n",

mrp

"sale_price": "1800",

unitprice

regular_price

dimensionwidth

dimensions.width

dimensionheight

dimensions.height

dimensionlenght

dimensions.length

weight

"weight": "0.5"

taxcode

as per settings

type

"type": "simple",

 

 

  • Match SKU: Woocommerce sku matches with one of the products in eshopbox, the eshopbox product should be marked as available

  • No Match SKU : Woocommerce sku will not match with one of the product in Eshopbox ,will create a new draft product

  • Ignore: In case of duplicacy or missing SKU

 

Update a Product:
We have a reconciliation cron for update product with parameters as modified_before and modified_after
to recieve an update of the product

https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products?after=2023-12-21T12:03:12&before=2023-12-23T17:03:12

Response

{ "id": 60, "name": "New T-shirt", "slug": "t-shirt", "permalink": "https://woo-briskly-different-dinosaur.wpcomstaging.com/product/t-shirt/", "date_created": "2023-12-19T16:29:31", "date_created_gmt": "2023-12-19T10:59:31", "date_modified": "2023-12-19T18:37:42", "date_modified_gmt": "2023-12-19T13:07:42", "type": "variable", "status": "publish", "featured": false, "catalog_visibility": "visible", "description": "\u003cp\u003eTesting T-shirt\u003c/p\u003e\n", "short_description": "", "sku": "t shirt", "price": "239", "regular_price": "", "sale_price": "", "on_sale": false, "purchasable": true, "total_sales": 0, "virtual": false, "downloadable": false, "downloads": [], "download_limit": -1, "download_expiry": -1, "external_url": "", "button_text": "", "tax_status": "taxable", "tax_class": "", "manage_stock": false, "backorders": "no", "backorders_allowed": false, "backordered": false, "sold_individually": false, "weight": "0.5", "dimensions": { "length": "10", "width": "11", "height": "12" }, "shipping_required": true, "shipping_taxable": true, "shipping_class": "", "shipping_class_id": 0, "reviews_allowed": true, "average_rating": "0.00", "rating_count": 0, "upsell_ids": [], "cross_sell_ids": [], "parent_id": 0, "purchase_note": "", "categories": [ { "id": 1374, "name": "Men", "slug": "men" } ], "tags": [ { "id": 1378, "name": "tshirt", "slug": "tshirt" } ], "images": [ { "id": 61, "date_created": "2023-12-19T21:58:15", "date_created_gmt": "2023-12-19T10:58:15", "date_modified": "2023-12-19T21:58:15", "date_modified_gmt": "2023-12-19T10:58:15", "src": "https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-content/uploads/2023/12/tshirt.jpg", "name": "tshirt", "alt": "" } ], "attributes": [ { "id": 0, "name": "size", "slug": "size", "position": 0, "visible": true, "variation": true, "options": [ "M", "S", "L" ] } ], "default_attributes": [ { "id": 0, "name": "size", "option": "M" } ], "variations": [ 64, 63, 62 ], "grouped_products": [], "menu_order": 0, "price_html": "\u003cspan class\u003d\"woocommerce-Price-amount amount\"\u003e\u003cbdi\u003e\u003cspan class\u003d\"woocommerce-Price-currencySymbol\"\u003e\u0026#36;\u003c/span\u003e239.00\u003c/bdi\u003e\u003c/span\u003e", "related_ids": [ 25, 24, 27, 26 ], "meta_data": [ { "id": 304, "key": "_last_editor_used_jetpack", "value": "classic-editor" }, { "id": 327, "key": "_product_addons", "value": [] }, { "id": 328, "key": "_product_addons_exclude_global", "value": "0" }, { "id": 329, "key": "group_of_quantity", "value": "" }, { "id": 330, "key": "minimum_allowed_quantity", "value": "" }, { "id": 331, "key": "maximum_allowed_quantity", "value": "" }, { "id": 332, "key": "minmax_do_not_count", "value": "no" }, { "id": 333, "key": "minmax_cart_exclude", "value": "no" }, { "id": 334, "key": "minmax_category_group_of_exclude", "value": "no" }, { "id": 342, "key": "_wpas_done_all", "value": "1" }, { "id": 401, "key": "allow_combination", "value": "no" } ], "stock_status": "instock", "has_options": true, "post_password": "", "bundled_by": [], "bundle_stock_status": "instock", "bundle_virtual": false, "bundle_layout": "", "bundle_add_to_cart_form_location": "", "bundle_editable_in_cart": false, "bundle_sold_individually_context": "", "bundle_item_grouping": "", "bundle_min_size": "", "bundle_max_size": "", "bundle_price": [], "bundled_items": [], "bundle_sell_ids": [], "jetpack_publicize_connections": [], "jetpack_sharing_enabled": true, "jetpack_likes_enabled": true, "brands": [ { "id": 1377, "name": "ray", "slug": "ray" } ], "_links": { "self": [ { "href": "https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products/60" } ], "collection": [ { "href": "https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products" } ] } }

 

We will fetch the product map it with keys eshopbox key and against the sku will update the product.

 

Get Product list by identifiers:

GET https://{workspace}.myeshopbox.com/product-engine/api/v1/products/sku

In the response will receieve the event if sku is present in Eshopbox and will also receive ESIN for the corresponding SKU.

 

Update Product API:

PUT : https://{workspace}.myeshopbox.com/product-engine/api/v1/products/{sku}

Request Body:

{ "imageUrl": "https://cdn.filestackcontent.com/hdYluVCqSADCASjjjcuaeC", "mrp": 1499.0, "unitPrice": 799.0, "hsnCode": "6101112", "weight": 167.0, "dimensionLength": 5.0, "dimensionWidth": 5.0, "dimensionHeight": 5.0, "dimensionUnit": "cm" }'



Delete Product:

Delete Product reconicilation cron we pass parameters as date_modified and status = trash to fetch the deleted products in Eshopbox and will mark them as unavailbale.

https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products?modified_after=2023-12-22T16:03:12&status=trash

Response:

[ { "id": 84, "name": "Trouser", "slug": "trouser__trashed", "permalink": "https://woo-briskly-different-dinosaur.wpcomstaging.com/?post_type=product&p=84", "date_created": "2023-12-21T18:40:19", "date_created_gmt": "2023-12-21T13:10:19", "date_modified": "2023-12-22T16:13:01", "date_modified_gmt": "2023-12-22T10:43:01", "type": "simple", "status": "trash", "featured": false, "catalog_visibility": "visible", "description": "<p>Trouser</p>\n", "short_description": "", "sku": "trouser", "price": "99", "regular_price": "100", "sale_price": "99", "date_on_sale_from": null, "date_on_sale_from_gmt": null, "date_on_sale_to": null, "date_on_sale_to_gmt": null, "on_sale": true, "purchasable": true, "total_sales": 1, "virtual": false, "downloadable": false, "downloads": [], "download_limit": -1, "download_expiry": -1, "external_url": "", "button_text": "", "tax_status": "taxable", "tax_class": "", "manage_stock": true, "stock_quantity": 26, "backorders": "no", "backorders_allowed": false, "backordered": false, "low_stock_amount": null, "sold_individually": false, "weight": "", "dimensions": { "length": "", "width": "", "height": "" }, "shipping_required": true, "shipping_taxable": true, "shipping_class": "", "shipping_class_id": 0, "reviews_allowed": true, "average_rating": "0.00", "rating_count": 0, "upsell_ids": [], "cross_sell_ids": [], "parent_id": 0, "purchase_note": "", "categories": [ { "id": 1374, "name": "Men", "slug": "men" } ], "tags": [], "images": [], "attributes": [], "default_attributes": [], "variations": [], "grouped_products": [], "menu_order": 0, "price_html": "<del aria-hidden=\"true\"><span class=\"woocommerce-Price-amount amount\"><bdi><span class=\"woocommerce-Price-currencySymbol\">&#36;</span>100.00</bdi></span></del> <ins><span class=\"woocommerce-Price-amount amount\"><bdi><span class=\"woocommerce-Price-currencySymbol\">&#36;</span>99.00</bdi></span></ins>", "related_ids": [ 60, 25, 24, 27, 77 ], "meta_data": [ { "id": 754, "key": "_last_editor_used_jetpack", "value": "classic-editor" }, { "id": 758, "key": "_product_addons", "value": [] }, { "id": 759, "key": "_product_addons_exclude_global", "value": "0" }, { "id": 775, "key": "group_of_quantity", "value": "" }, { "id": 776, "key": "minimum_allowed_quantity", "value": "" }, { "id": 777, "key": "maximum_allowed_quantity", "value": "" }, { "id": 778, "key": "minmax_do_not_count", "value": "no" }, { "id": 779, "key": "minmax_cart_exclude", "value": "no" }, { "id": 780, "key": "minmax_category_group_of_exclude", "value": "no" }, { "id": 785, "key": "_wpas_done_all", "value": "1" }, { "id": 797, "key": "_wp_trash_meta_status", "value": "publish" }, { "id": 798, "key": "_wp_trash_meta_time", "value": "1703241781" }, { "id": 799, "key": "_wp_desired_post_slug", "value": "trouser" }, { "id": 800, "key": "_wpcom_is_markdown", "value": "1" } ], "stock_status": "instock", "has_options": false, "post_password": "", "bundled_by": [], "bundle_stock_status": "instock", "bundle_stock_quantity": 26, "bundle_virtual": false, "bundle_layout": "", "bundle_add_to_cart_form_location": "", "bundle_editable_in_cart": false, "bundle_sold_individually_context": "", "bundle_item_grouping": "", "bundle_min_size": "", "bundle_max_size": "", "bundle_price": [], "bundled_items": [], "bundle_sell_ids": [], "jetpack_publicize_connections": [], "jetpack_sharing_enabled": true, "jetpack_likes_enabled": true, "brands": [ { "id": 1377, "name": "ray", "slug": "ray" } ], "_links": { "self": [ { "href": "https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products/84" } ], "collection": [ { "href": "https://woo-briskly-different-dinosaur.wpcomstaging.com/wp-json/wc/v3/products" } ] } } ]

In case of product is deleted ,mark it as unavailable:

Mark Product UnAvailability:

POST https://{workspace}.myeshopbox.com/product-engine/api/v2/productListing

Request Body

{ "esin" : "0SGAT12SG25F", "channelCode" : "CH1234", "availability" : false }

 

 

 

Related content