Shopify Connector

SELECT * FROM Products

SELECT * FROM Products WITH Id=1111111111111

SELECT * FROM Products WITH(ids='1111111111111,2222222222222,3333333333333')

INSERT INTO Products 
(
	 Title
	,Status
	,BodyHtml
	,UrlHandle
	,Vendor
	,ProductType
	,Variants
	,Options
	,Tags
	,Metafields
	,Images	
	)
VALUES	
('Ice Cream'
,'draft'
,'<strong>Very yummy ice cream!</strong>'
,'ice-cream' 
,'Burton'
,'Snowboard' 

,'[
	{"price":10.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":100},
	{"price":10.5, "option1":"Chocolate","option2":"Medium","sku":"ICE-CHO-MED","inventory_quantity":100},
	{"price":11.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":210}
  ]'

--you must set variants and use atlease one value from the below list in option1, option2 or option3 in any variant entry else it will fail.  
,'[
	{"name":"Color","values":["Chocolate","Vanilla"]}, 
	{"name":"Size","values":["Small","Medium"]}
  ]' 

,'["Frozen","Seasonal","Dad''s Fav"]'

--adding metadata (custom fields) - metadata fields must be created before setting it 
--below are 2 system fields for SEO Title / SEO Description (you dont need to create them unlike custom metadata). These values appears on SEO section
,'[
	{"key":"title_tag","value":"Yum Ice Cream SEO Title", "namespace":"global","type":"single_line_text_field"}, 
	{"key":"description_tag","value":"Yum Ice Cream SEO description", "namespace":"global","type":"single_line_text_field"} 
  ]'

--first image becomes main image if you supply multiple images 
--upload multiple images from URL (set "src")
 , '[	
	{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
	{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
  ]'
--OR upload multiple local image files (set "attachment")
--, '[	
--	 {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
--	 {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
--  ]'  
  
)

UPDATE Products
SET Title='Ice Cream - Updated'
 , Status='draft' --active, archived, draft  
 , BodyHtml='<strong>Very yummy ice cream - updated!</strong>'  
--first image becomes main image if you supply multiple images 
--upload multiple images from URL (set "src")
 , Images='[	
	{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
	{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
  ]'
--OR upload multiple local image files (set "attachment")
--, Images='[	
--	 {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
--	 {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
--  ]'

 , Variants='[
	{"price":20.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":300},
	{"price":21.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":110}
  ]'
, PublishedScope='global' --or web
, Vendor ='IceGlobal'
, ProductType ='Cold Food'
, Tags ='["Frozen","Seasonal","Dad''s Fav"]'
--Update SEO URL
 ,UrlHandle='ice-cream-51' 
--Update SEO title / description
, SEOTitle='Yum Ice Cream SEO Title-update'  
, SEODescription='Yum Ice Cream SEO description-update'  
Where Id=7348335771748

DELETE FROM Products
Where Id=7348335771748

SELECT * FROM ProductVariants

SELECT * FROM ProductVariants Where ProductId='1111111111111'

SELECT * FROM ProductVariants WITH(ids='1111111111111,2222222222222,3333333333333')

INSERT INTO ProductVariants (ProductId, Option1, Option2,SKU,Price,CompareAtPrice,Position,Weight,WeightUnit,ImageId)
Values(7348335771748, 'Chocolate', 'Medium', 'ICE-CHO-MED', 195.5, 200.5, 3, 20.5, 'lb', 31900013854820)

Update ProductVariants 
SET  
	,Option1='Chocolate' 
	,Option2='Large'
	,SKU='ICE-CHO-SML'
	,Price=90.45
	,CompareAtPrice=100.45
	,Position=2
	,Weight=10.5
	,WeightUnit='lb'
	,ImageId=31900013854820  --use available images from Products table
Where Id=42564507992164

DELETE FROM ProductVariants 
WHERE Id=31900013854820

SELECT * FROM Customers

SELECT * FROM Customers Where Id=12345

SELECT * FROM Customers
WITH (ids='1111111111111,2222222222222,3333333333333')

INSERT INTO Customers
(FirstName, LastName, Email, Phone, Password, PasswordConfirmation, SendWelcomeEmail, MultipassIdentifier, Note, Tags, TaxExempt, TaxExemptions, DefaultAddressFirstName, DefaultAddressLastName, DefaultAddressCompany, DefaultAddressLine1, DefaultAddressLine2, DefaultAddressCity, DefaultAddressProvince, DefaultAddressCountry, DefaultAddressZip, DefaultAddressPhone, DefaultAddressName, DefaultAddressProvinceCode, DefaultAddressCountryCode, DefaultAddressCountryName)
VALUES
('John', 'Doe', 'john.doe@gmail.com', '7705553543', 'myNewP@ssword123', 'myNewP@ssword123', 1, null, 'This is a note on the customer account.', null, 0, null, 'John', 'Doe', 'John Doe Corp.', '123 Main Street', null, 'Atlanta', 'Georgia', 'United States', '30135', '7705553543', 'John Doe', 'GA', 'US', 'United States')

INSERT INTO Customers(_rawdoc_)
VALUES('{"customer":{"first_name":"John","last_name":"Doe","email":"a.doe@gmail.com","phone":"7705553111"}}')

INSERT INTO Customers(FirstName, LastName, Email, Phone)
SOURCE('MSSQL'
  ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
  ,'select ''John'' as FirstName, ''Doe'' as LastName, ''a.doe@gmail.com'' as Email, ''7705553111'' as Phone'
)

INSERT INTO Customers
SOURCE('MSSQL'
	,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
	,'select ''{"customer":{"first_name":"Cust1","last_name":"Doe1","email":"a.doe@gmail.com","phone":"7705553111"}}'' as _rawdoc_
	 UNION 
	 select ''{"customer":{"first_name":"Cust2","last_name":"Doe2","email":"b.doe@gmail.com","phone":"7705553222"}}'' as _rawdoc_
	 '
	)

UPDATE Customers SET
  Email = 'john.doe2@gmail.com',
  Phone = '7705553445',
  Note= 'This is a new note that needed to be added later.'
  WHERE Id=1111111111111

UPDATE Customers
SET _rawdoc_='{"customer":{"first_name":"John_new","last_name":"Doe_new","email":"a_new.doe@gmail.com","phone":"7705553111"}}'
WHERE Id=1111111111111

UPDATE Orders SET
  FulfillmentStatus = 'john.doe5@gmail.com',
  Phone = '7705553111',
  Note= 'This is a new note that needed to be added to the order later.'
WHERE Id=1111111111111

UPDATE Customers
SOURCE('MSSQL'
 ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
 ,'select 111 as Id, ''a@a.com''Email , ''SOLD'' as Note,0 as [$$ContineOn404Error]
  UNION
  select 222 as Id, ''b@b.com''Email , ''SOLD'' as Note,0 as [$$ContineOn404Error]
  '
)

DELETE Customers WHERE Id=1111111111111

DELETE Customers WHERE Id=1111111111111 (ContineOn404Error=0)

DELETE FROM Customers
SOURCE('MSSQL'
  ,'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
  ,'select 111 as Id,1 as [$$ContineOn404Error]
    UNION
    select 222 as Id,1 as [$$ContineOn404Error]
   '
)

SELECT * FROM Orders

SELECT * FROM Orders WITH (Status='open') --also try 'any', 'open', 'closed', 'cancelled'

SELECT * FROM Orders Where Id=1111111111111

SELECT * FROM Orders WITH(ids='1111111111111,2222222222222,3333333333333')

DELETE Orders WHERE Id=1111111111111

DELETE Orders WHERE Id=1111111111111 (ContineOn404Error=0)

SELECT * FROM OrderItems

SELECT * FROM OrderItems Where OrderId=1111111111111

SELECT * FROM OrderItems WITH(ids='1111111111111,2222222222222,3333333333333')

INSERT INTO Orders (BillingAddressLine1, BillingAddressLine2, BillingAddressCity, BillingAddressCompany, BillingAddressCountry, BillingAddressFirstName, BillingAddressLastName, BillingAddressPhone, BillingAddressProvince, BillingAddressZip, BillingAddressName, BillingAddressProvinceCode, BillingAddressCountryCode, BuyerAcceptsMarketing, LineItems, CustomerId, Email, EstimatedTaxes, FinancialStatus, FulfillmentStatus, Name, Note, Phone, Currency, PresentmentCurrency, ProcessedAt, ReferringSite, ShippingAddressLine1, ShippingAddressLine2, ShippingAddressCity, ShippingAddressCompany, ShippingAddressCountry, ShippingAddressFirstName, ShippingAddressLastName, ShippingAddressPhone, ShippingAddressProvince, ShippingAddressZip, ShippingAddressName, ShippingAddressProvinceCode, ShippingAddressCountryCode, Tags, TaxesIncluded, TotalWeight, SendReceipt, SendFulfillmentReceipt)
VALUES
('123 Main Street', 'Suite #54', 'Memphis', 'Acme, Inc.', 'United States', 'John', 'Doe', '4045559876', 'Tennessee', '38101', 'John Doe', 'GA', 'US', 1, '[{"title":"Super Strong Glue","price":24.99,"grams":"100","quantity":1,"tax_lines":[{"price":13.5,"rate":0.06,"title":"State tax"}]}]', 5945175474276, 'johndoe2@gmail.com', 1, 'pending', null, '#40294', 'This order needs to be expedited, so register it in the system as so.', '4045559876', 'USD', 'USD', '2023-02-27T11:00:00', 'https://referringsite.com', '123 Main Street', 'Suite #54', 'Memphis', 'Acme, Inc.', 'United States', 'John', 'Doe', '4045559876', 'Tennessee', '38101', 'John Doe', 'GA', 'US', NULL, 1, 20, 1, 1)

select * from InventoryLevels 
		--WITH(location_ids='43512280416356, 44648752676964, ..... upto 300 to 500 more - until you hit URL limit error')

select * from InventoryLevels WITH (inventory_item_ids='43512280416356, 44648752676964')

select * from InventoryLevels WITH (location_ids='43512280416356, 44648752676964')

select * from InventoryLevels WITH (inventory_item_ids='43512280416356, 44648752676964' , location_ids='111100034, 111100055')

UPDATE InventoryLevels 
SET AvailableAdjustment=488, 
    LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
 Action='Adjust' --or set or connect
, ContineOn404Error=0 
)

UPDATE InventoryLevels 
SET LocationId=25801916516
  ,Available=488 
WHERE InventoryItemId=43512276942948
WITH(
 Action='set' --or adjust or connect
, ContineOn404Error=0 
)

--OR--
/*
INSERT INTO InventoryLevels (InventoryItemId,LocationId,Available)
VALUES(43512276942948, 25801916516, 488)
--WITH( ContineOn404Error=0 )
*/

UPDATE InventoryLevels 
SET LocationId=25801916516
WHERE InventoryItemId=43512276942948
WITH(
 Action='connect' --or adjust or set
, ContineOn404Error=0 
)

SELECT * FROM generic_request 
WITH (
    URL='/inventory_levels/set.json'
  --OR Use full URL
  --URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-01/inventory_levels/set.json'
  	
  , RequestMethod='POST'
  , Body='{"location_id":25801916516,"inventory_item_id":43512280416356,"available":42}' --needed if you call PUT, POST
  , Filter='$.inventory_level' --change table name here
  , Headers='Content-Type: application/json'
  , Meta='inventory_item_id:long; location_id:long; available:int; updated_at: datetime'
)

select * from InventoryItems Where Id=43512280416356

select * from InventoryItems Where Id=43512280416356

select * from InventoryItems WITH(Ids='43512280416356, 43512280449124')

UPDATE InventoryItems
SET Cost='25.55'
WHERE Id=43512280416356

SELECT * FROM generic_request 
WITH (
    URL='/graphql.json'
  --OR Use full URL
  --URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-10/graphql.json'
  
  , RequestMethod='POST'
  , Filter='$.data.products.nodes' --change table name here e.g. products
  , Headers='Content-Type: application/json'
  --change table name and columns below here e.g. products... and id, title etc
  -- change pagesize if needed (i.e. max 250)
  , Body='{
 "query" : "<<{
 
 products(first: 250 [$tag$])
 {
    nodes {
      id
      title
      createdAt
    }
    
    pageInfo {
      hasNextPage
      endCursor
    }
    
  }
  
},FUN_JSONENC>>"
}'
	, NextUrlAttributeOrExpr='$.data.products.pageInfo.endCursor' --change table name 
	, NextUrlEndIndicator='false'
	, StopIndicatorAttributeOrExpr='$.data.products.pageInfo.hasNextPage' --change table name 
	, UseConnection='True'
	, EnablePageTokenForBody='True'
	, HasDifferentNextPageInfo='True'
	, NextPageBodyPart='after: \"[$pagetoken$]\"'
	--Use metadata to speed up execution. To get Metadata Run query without Meta clause. 
	-- Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
	--, Meta='id:String(255); title:String(255); createdAt:DateTime; '
)

SELECT * FROM generic_request 
WITH (
    URL='/products.json'
  --OR Use full URL
  --URL='https://MY-STORE-HERE.myshopify.com/admin/api/2023-01/products.json'
  	
  , RequestMethod='GET'
  , Body='{}' --needed if you call PUT, POST
  , Filter='$.products[*]' --change table name here
  , Headers='Content-Type: application/json'
  , PagingMode='ByResponseHeaderRfc5988'
  
  
  --Use metadata to speed up execution. To get Metadata Run query without Meta clause. 
  --Then click View Metadata button found in Botttom Result Grid Toolbar. Get Compact format and paste below
  , Meta='id:String(255); title:String(255); created_at:DateTime; '
)