<?php

/**
 * Firetoys
 *
 * @author Matthew Robertson
 *
 * 
 *  
 * 
 */
 
/**
 * Inputs:
 *
 * shopify_token - string
 * country - string
 * attributes - comma separated string
 * key - string
 * key_value - comma separated list of string values
 */

/*
// Check whether we are testing PHPs directly in the browser
if ($direct)
{
	// Parameters will be in query string
	$shopify_token = $_GET['shopify_token'];
	$country = $_GET['country'];
	$attributes = $_GET['attributes'];
	$key = $_GET['key'];
	$keyValues = $_GET['key_value'];
}
else
{
	// Get POSTed parameters 
	$shopify_token = $_POST['shopify_token'];
	$country = $_POST['country'];
	$attributes = $_POST['attributes'];
	$key = $_POST['key'];
	$keyValues = $_POST['key_value'];
}
*/

// Get parameter if there is one
$parameter = "";
$inputParameters = Array();
if (sizeof($argv) > 1)
	parse_str($argv[1], $inputParameters);

// Initial load should be run with command line parameter 'runType=firstRun' 

if (array_key_exists('runType', $inputParameters) && ($inputParameters['runType'] == 'firstRun'))
{
	if (array_key_exists('startDate', $inputParameters))
		$startDate = $inputParameters['startDate'];
	else 
	{
		echo "startDate required for firstRun \r\n";
		return;
	}

	echo "*** This is running as an initial load, which may take a long time, and will re-initialise the database tables\r\n"
		. "*** Waiting 5 seconds - Ctrl-C to cancel\r\n";
	for($i = 0; $i < 5; $i++)
	{
		sleep (1);
		echo "* ";
	}
	$firstRun = true;
}
else 
	$firstRun = false;
//echo "*" . $firstRun . "*\r\n";


require_once ('.config.inc.php'); 
//require_once ('ShopifyGraphQLUtilities.php');



function initialiseSqlArray($tableColumns, $topLevelKey)
{
	foreach ($tableColumns as $key => $valueArray)
	{
		if (!($key == $topLevelKey))
		{
			if (($valueArray['type'] == "S"))
				$valuesArrayForId[$key] = "'" . $valueArray['default'] . "'";
			else
				$valuesArrayForId[$key] = $valueArray['default'];
		}
	}
//echo $topLevelKey . "\r\n" . print_r($tableColumns, true) . "\r\n\n";
	
	return $valuesArrayForId;
}



// Column names, type (string, boolean, date or number), default value
define('ORDERS_TABLE_COLUMNS', Array(
'Id' => Array('type' => 'S', 'default' => 'null'),
'Number' => Array('type' => 'N', 'default' => 0),
'CustomerId' => Array('type' => 'S', 'default' => ''),
'CustomerFirstName' => Array('type' => 'S',  'default' => ''),
'CustomerLastName' => Array('type' => 'S',  'default' => ''),
'Currency' => Array('type' => 'S',  'default' => ''),
'Email' => Array('type' => 'S',  'default' => ''),
'Name' => Array('type' => 'S',  'default' => ''),
'TotalDiscounts' => Array('type' => 'N', 'default' => 0),
'TotalOrderItemsPrice' => Array('type' => 'N', 'default' => 0),
'TotalPrice' => Array('type' => 'N', 'default' => 0),
'SubtotalPrice' => Array('type' => 'N', 'default' => 0),
'TotalTax' => Array('type' => 'N', 'default' => 0),
'TotalWeight' => Array('type' => 'N', 'default' => 0),
'BuyerAcceptsMarketing' => Array('type' => 'B', 'default' => 'false'),
'TaxesIncluded' => Array('type' => 'B', 'default' => 'false'),
'SourceName' => Array('type' => 'S',  'default' => ''),
'LocationId' => Array('type' => 'S',  'default' => ''),
'Tags' => Array('type' => 'S',  'default' => ''),
'FulfillmentStatus' => Array('type' => 'S',  'default' => ''),
'FinancialStatus' => Array('type' => 'S',  'default' => ''),
'BillingAddressZip' => Array('type' => 'S',  'default' => ''),
'BillingAddressCountryCode' => Array('type' => 'S',  'default' => ''),
'BillingAddressProvinceCode' => Array('type' => 'S',  'default' => ''),
'ShippingAddressZip' => Array('type' => 'S',  'default' => ''),
'ShippingAddressCountryCode' => Array('type' => 'S',  'default' => ''),
'ShippingAddressProvinceCode' => Array('type' => 'S',  'default' => ''),
'ShippingAddressDefault' => Array('type' => 'B', 'default' => 'false'),
'AppliedDiscountTitle' => Array('type' => 'S',  'default' => ''),
'AppliedDiscountDescription' => Array('type' => 'S',  'default' => ''),
'AppliedDiscountValue' => Array('type' => 'N',  'default' => 0),
'AppliedDiscountValueType' => Array('type' => 'S',  'default' => ''),
'AppliedDiscountAmount' => Array('type' => 'N',  'default' => 0),
'ProcessedAt' => Array('type' => 'D',  'default' => 'null'),
'CreatedAt' => Array('type' => 'D',  'default' => 'null'),
'ClosedAt' => Array('type' => 'D',  'default' => 'null'),
'CancelledAt' => Array('type' => 'D',  'default' => 'null'),
'UpdatedAt' => Array('type' => 'D',  'default' => 'null'),
'TotalShippingPriceSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalShippingPriceSetShopMoneyCurrencyCode' => Array('type' => 'S',  'default' => ''),
'TotalShippingPriceSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalPriceSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalPriceSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalTaxSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalTaxSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CheckoutId' => Array('type' => 'S',  'default' => ''),
'Test' => Array('type' => 'B', 'default' => 'false'),
'TotalOutstanding' => Array('type' => 'N', 'default' => 0),
'PresentmentCurrency' => Array('type' => 'S',  'default' => ''),
'TotalLineItemsSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalLineItemsSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalDiscountsSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalDiscountsSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalSubtotalSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'TotalSubtotalSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'OrigTotalAdditionalFeesSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'OrigTotalAdditionalFeesSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurTotalDiscountsSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurTotalDiscountsSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurSubtotalPriceSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurSubtotalPriceSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurrentTotalTaxPriceSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurrentTotalTaxPriceSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurTotalAdditionalFeesSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurTotalAdditionalFeesSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurrentTotalPriceSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurrentTotalPriceSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'OriginalTotalDutiesSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'OriginalTotalDutiesSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurrentTotalDutiesSetShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'CurrentTotalDutiesSetPresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'PaymentGatewayNames' => Array('type' => 'S',  'default' => '')
));

$sqlColumnsArray = Array();
foreach (array_keys(ORDERS_TABLE_COLUMNS) as $key)
{
	$sqlColumnsArray[] = $key;
}
define ('ORDERS_TABLE_COLUMNS_STRING', implode(",", $sqlColumnsArray));
define ('ORDERS_TABLE_DEFAULT_VALUES', initialiseSqlArray(ORDERS_TABLE_COLUMNS, "Id"));
define ('ORDERS_TABLE_NAME', 'ft_shopify_orders');


// Column names, type (string, boolean, date or number), default value
define('ORDERS_ITEMS_TABLE_COLUMNS', Array(
'ItemId' => Array('type' => 'S', 'default' => 'null'),
'OrderId' => Array('type' => 'S', 'default' => 'null'),
'OrderUpdatedAt' => Array('type' => 'D', 'default' => 'null'),
'ProductId' => Array('type' => 'S', 'default' => ''),
'ItemVariantId' => Array('type' => 'S', 'default' => ''),
'Name' => Array('type' => 'S', 'default' => ''),
'ItemQuantity' => Array('type' => 'N', 'default' => 0),
'ItemPrice' => Array('type' => 'N', 'default' => 0),
'ItemGrams' => Array('type' => 'N', 'default' => 0),
'SKU' => Array('type' => 'S', 'default' => ''),
'VariantTitle' => Array('type' => 'S', 'default' => ''),
'Vendor' => Array('type' => 'S', 'default' => ''),
'FulfillmentsService' => Array('type' => 'S', 'default' => ''),
'RequiresShipping' => Array('type' => 'B', 'default' => 'false'),
'Taxable' => Array('type' => 'B', 'default' => 'false'),
'GiftCard' => Array('type' => 'B', 'default' => 'false'),
'FulfillableQuantity' => Array('type' => 'N', 'default' => 0),
'TotalDiscount' => Array('type' => 'N', 'default' => 0),
'FulfillmentStatus' => Array('type' => 'S', 'default' => ''),
'AppliedDiscountTitle' => Array('type' => 'S', 'default' => ''),
'AppliedDiscountDescription' => Array('type' => 'S', 'default' => ''),
'AppliedDiscountValue' => Array('type' => 'N', 'default' => 0),
'AppliedDiscountValueType' => Array('type' => 'S', 'default' => ''),
'AppliedDiscountAmount' => Array('type' => 'N', 'default' => 0)
));

$sqlColumnsArray = Array();
foreach (array_keys(ORDERS_ITEMS_TABLE_COLUMNS) as $key)
{
	$sqlColumnsArray[] = $key;
}
define ('ORDERS_ITEMS_TABLE_COLUMNS_STRING', implode(",", $sqlColumnsArray));
define ('ORDERS_ITEMS_TABLE_DEFAULT_VALUES', initialiseSqlArray(ORDERS_ITEMS_TABLE_COLUMNS, "ItemId"));
define ('ORDERS_ITEMS_TABLE_NAME', 'ft_shopify_orders_items');


// Column names, type (string, boolean, date or number), default value
define('ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_COLUMNS', Array(
'ItemId' => Array('type' => 'S', 'default' => 'null'),
'LineNumber' => Array('type' => 'N', 'default' => 0),
'OrderId' => Array('type' => 'S', 'default' => 'null'),
'Amount' => Array('type' => 'N', 'default' => 0),
'ApplicationIndex' => Array('type' => 'N', 'default' => 0),
'ShopMoneyAmount' => Array('type' => 'N', 'default' => 0),
'ShopMoneyCurrencyCode'  => Array('type' => 'S', 'default' => ''),
'PresentmentMoneyAmount' => Array('type' => 'N', 'default' => 0),
'PresentmentMoneyCurrencyCode' => Array('type' => 'N', 'default' => 0),
'OrderUpdatedAt' => Array('type' => 'D', 'default' => 'null'),
));

$sqlColumnsArray = Array();
foreach (array_keys(ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_COLUMNS) as $key)
{
	$sqlColumnsArray[] = $key;
}
define ('ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_COLUMNS_STRING', implode(",", $sqlColumnsArray));
define ('ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_DEFAULT_VALUES', initialiseSqlArray(ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_COLUMNS, "ItemId"));
define ('ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_NAME', 'ft_shopify_order_item_discount_allocations');


// Column names, type (string, boolean, date or number), default value
define('INVENTORY_LEVELS_TABLE_COLUMNS', Array(
'InventoryItemId' => Array('type' => 'S', 'default' => 'null'),
'LocationId' => Array('type' => 'S', 'default' => 'null'),
'Available' => Array('type' => 'B', 'default' => 'false'),
'UpdatedAt' => Array('type' => 'D', 'default' => 'null'),
));

$sqlColumnsArray = Array();
foreach (array_keys(INVENTORY_LEVELS_TABLE_COLUMNS) as $key)
{
	$sqlColumnsArray[] = $key;
}
define ('INVENTORY_LEVELS_TABLE_COLUMNS_STRING', implode(",", $sqlColumnsArray));
define ('INVENTORY_LEVELS_TABLE_DEFAULT_VALUES', initialiseSqlArray(INVENTORY_LEVELS_TABLE_COLUMNS, "InventoryItemId"));
define ('INVENTORY_LEVELS_TABLE_NAME', 'ft_shopify_inventory_levels');


// Column names, type (string, boolean, date or number), default value
define('REFUNDS_TABLE_COLUMNS', Array(
'Id'  => Array('type' => 'S', 'default' => ''),
'OrderId'  => Array('type' => 'S', 'default' => ''),
'OrderUpdatedAt' => Array('type' => 'D', 'default' => 'null'),
'Note'  => Array('type' => 'S', 'default' => ''),
'Restock' => Array('type' => 'B', 'default' => 'false'),
'UserId'  => Array('type' => 'S', 'default' => ''),
'CreatedAt' => Array('type' => 'D', 'default' => 'null'),
'ProcessedAt' => Array('type' => 'D', 'default' => 'null')
));

$sqlColumnsArray = Array();
foreach (array_keys(REFUNDS_TABLE_COLUMNS) as $key)
{
	$sqlColumnsArray[] = $key;
}
define ('REFUNDS_TABLE_COLUMNS_STRING', implode(",", $sqlColumnsArray));
define ('REFUNDS_TABLE_DEFAULT_VALUES', initialiseSqlArray(REFUNDS_TABLE_COLUMNS, "Id"));
define ('REFUNDS_TABLE_NAME', 'ft_shopify_refunds');


// Column names, type (string, boolean, date or number), default value
define('REFUNDS_ITEMS_TABLE_COLUMNS', Array(
'ItemId'  => Array('type' => 'S', 'default' => ''),
'OrderId'  => Array('type' => 'S', 'default' => ''),
'RefundId'  => Array('type' => 'S', 'default' => ''),
'LocationId'  => Array('type' => 'S', 'default' => ''),
'ProductId'  => Array('type' => 'S', 'default' => ''),
'ItemVariantId'  => Array('type' => 'S', 'default' => ''),
'Name'  => Array('type' => 'S', 'default' => ''),
'ItemQuantity' => Array('type' => 'N', 'default' => 0),
'ItemPrice' => Array('type' => 'N', 'default' => 0),
'ItemGrams' => Array('type' => 'N', 'default' => 0),
'SKU'  => Array('type' => 'S', 'default' => ''),
'VariantTitle'  => Array('type' => 'S', 'default' => ''),
'Properties'  => Array('type' => 'S', 'default' => ''),
'Vendor'  => Array('type' => 'S', 'default' => ''),
'FulFillmentsService'  => Array('type' => 'S', 'default' => ''),
'RequiresShipping' => Array('type' => 'B', 'default' => 'false'),
'Taxable' => Array('type' => 'B', 'default' => 'false'),
'GiftCard' => Array('type' => 'B', 'default' => 'false'),
'FulfillableQuantity' => Array('type' => 'N', 'default' => 0),
'TotalDiscount' => Array('type' => 'N', 'default' => 0),
'FulfillmentStatus'  => Array('type' => 'S', 'default' => ''),
'RefundSubtotal' => Array('type' => 'N', 'default' => 0),
'RefundQuantity' => Array('type' => 'N', 'default' => 0),
'RefundRestockType'  => Array('type' => 'S', 'default' => ''),
'RefundSubtotalPresentmentAmount' => Array('type' => 'N', 'default' => 0),
'RefundSubtotalPresentmentCurrencyCode' => Array('type' => 'N', 'default' => 0),
'RefundSubtotalShopAmount' => Array('type' => 'N', 'default' => 0),
'RefundSubtotalShopCurrencyCode'  => Array('type' => 'S', 'default' => ''),
'RefundTotalTax' => Array('type' => 'N', 'default' => 0),
'RefundTotalTaxPresentmentAmount' => Array('type' => 'N', 'default' => 0),
'RefundTotalTaxPresentmentCurrencyCode'  => Array('type' => 'S', 'default' => ''),
'RefundTotalTaxShopAmount' => Array('type' => 'N', 'default' => 0),
'RefundTotalTaxShopCurrencyCode'  => Array('type' => 'S', 'default' => ''),
'AppliedDiscountTitle'  => Array('type' => 'S', 'default' => ''),
'AppliedDiscountDescription'  => Array('type' => 'S', 'default' => ''),
'AppliedDiscountValue' => Array('type' => 'N', 'default' => 0),
'AppliedDiscountValueType'  => Array('type' => 'S', 'default' => ''),
'AppliedDiscountAmount' => Array('type' => 'N', 'default' => 0)
));

$sqlColumnsArray = Array();
foreach (array_keys(REFUNDS_ITEMS_TABLE_COLUMNS) as $key)
{
	$sqlColumnsArray[] = $key;
}
define ('REFUNDS_ITEMS_TABLE_COLUMNS_STRING', implode(",", $sqlColumnsArray));
define ('REFUNDS_ITEMS_TABLE_DEFAULT_VALUES', initialiseSqlArray(REFUNDS_ITEMS_TABLE_COLUMNS, "ItemId"));
define ('REFUNDS_ITEMS_TABLE_NAME', 'ft_shopify_refunds_items');



// Other constants
define ('STORE_LIST', Array('UK', 'EU', 'US', 'WS'));
define ('STORE_DETAILS', Array(
'UK' => Array('storeName' => FIRETOYS_BETA_STORE_NAME, 'shopifyToken' => FIRETOYS_BETA_ACCESS_TOKEN, 'locationId' => 'gid://shopify/Location/87785079089'),
'EU' => Array('storeName' => FIRETOYS_EU_STORE_NAME, 'shopifyToken' => FIRETOYS_EU_ACCESS_TOKEN, 'locationId' => 'gid://shopify/Location/102064259393'),
'US' => Array('storeName' => FIRETOYS_US_STORE_NAME, 'shopifyToken' => FIRETOYS_US_ACCESS_TOKEN, 'locationId' => 'gid://shopify/Location/67801972917'),
'WS' => Array('storeName' => FIRETOYS_WHOLESALE_STORE_NAME, 'shopifyToken' => FIRETOYS_WHOLESALE_ACCESS_TOKEN, 'locationId' => 'gid://shopify/Location/92100591905')
));
define ('DISCOUNT_TYPES', Array('AutomaticDiscountApplication', 'DiscountCodeApplication', 'ManualDiscountApplication', 'ScriptDiscountApplication'));


// Utility variables
$file = basename(__FILE__);
$error = false;

/*
// Bulk get inventory data
$queryStringQuery = 'mutation {
  bulkOperationRunQuery(
   query: """
		{
		inventoryItems {
			edges {
				node {
					id
					sku
					updatedAt
					inventoryLevels(query: "updated_at:>\'2025-06-09\'") {
						edges {
						  node{
							location {
							  id
							  name
							}
							updatedAt
							quantities(
								names: ["available","reserved","committed"]
							) {
								name
								quantity
							  }
						  }
						}
					}
				}
			}
			pageInfo {
			  endCursor
			  hasNextPage
			}
		}
    }
    """
  ) {
    bulkOperation {
      id
      status
    }
    userErrors {
      field
      message
    }
  }
}';
*/

/*
// Bulk get unfulfilled order details (EU-HW)
$queryStringQuery = 'mutation {
  bulkOperationRunQuery(
   query: """
	{
	  orders(query:"id:>6083908993345 AND (fulfillment_status:>fulfilled OR fulfillment_status:<fulfilled)") {
		edges {
		  node {
			id
			name
			tags
			cancelledAt
			fulfillmentOrders(first:1 query:"assigned_location_id:98587148609")
			{
			  edges
			  {
				node 
				{
				  id
				  lineItems
				  {
					edges 
					{
					  node 
					  {
						sku
						remainingQuantity
					  }
					}
					pageInfo 
					{
					  endCursor
					  hasNextPage
					}
				  }
				}
			  }
			}
		  }
		}
		}
    }
    """
  ) {
    bulkOperation {
      id
      status
    }
    userErrors {
      field
      message
    }
  }
}';
*/

/*
// Bulk get unfulfilled order details (non EU)
//	  orders(query:"(updated_at:>\'2025-06-14\') AND (fulfillment_status:>fulfilled OR fulfillment_status:<fulfilled)") {
$queryStringQuery = 'mutation {
  bulkOperationRunQuery(
   query: """
	{
	  orders(query:"updated_at:>\'2025-06-14\'") {
		edges {
		  node {
			id
			name
			tags
			cancelledAt
			displayFulfillmentStatus
			lineItems 
			{
			  edges 
			  {
				node 
				{
				  id
				  variant 
				  {
					id
					sku
				  }
				  currentQuantity
				}
			  }
			}
			fulfillmentOrders(first:1)
			{
			  edges
			  {
				node 
				{
				  id
				  lineItems(first:250)
				  {
					edges 
					{
					  node 
					  {
						sku
						remainingQuantity
					  }
					}
					pageInfo 
					{
					  endCursor
					  hasNextPage
					}
				  }
				}
			  }
			}
		  }
		}
      }
	}
    """
  ) {
    bulkOperation {
      id
      status
    }
    userErrors {
      field
      message
    }
  }
}';
*/

/*
$queryStringQuery = 'mutation {
  bulkOperationRunQuery(
   query: """
   {productVariants(query:"updated_at:>\'2024-10-09\'") {edges {node {id sku barcode displayName inventoryItem{measurement{weight{value,unit}}} product{title  } } } }}
    """
  ) {
    bulkOperation {
      id
      status
    }
    userErrors {
      field
      message
    }
  }
}';
*/




/*
removed sections

after email
        refunds(first: 10) {
          id
          note
          order {
            id
            updatedAt
          }
          refundLineItems(first: 10) {
            edges {
              node {
                id
                lineItem {
                  id
                }
              }
            }
          }
          createdAt
          updatedAt
        }



*/

/*
(In case needed)
			fulfillmentOrders
			{
			  edges{
				node {
				  id
				  assignedLocation {
					location {
					  id
					  name
					  fulfillmentService {
						id
						serviceName
					  }
					}
				  }
				  lineItems
				  {
					edges {
					  node {
						id
						variant {
						  sku
						  inventoryItem {
							id
							inventoryLevel (locationId: "%s") {
							  quantities (names: ["available"]){
								name
								quantity
							  }
							  location {
								id
							  }
							}
							measurement {
							  weight {
								value
							  }
							}
							updatedAt
						  }
						}
						remainingQuantity
					  }
					}
				  }
				}
			  }
			}
			
			
			
			
*/


$orderUpdatedRangeModel = '
	orders(query: "%s") {
		edges {
		  node {
			name
			id
			customer {
			  id
			  firstName
			  lastName
			}
			currencyCode
			email

			refunds {
				id
			}

			lineItems
			{
			  edges {
				node {
				  id
				  fulfillmentService {
					id
					handle
					serviceName
				  }
				  variant {
					id
					displayName
					title
					sku
					barcode
					price
					product {
					  id
					  title
					}
					inventoryItem {
					  id
					  inventoryLevel (locationId: "%s") {
						quantities (names: ["available"]){
						  name
						  quantity
						}
    					location {
						  id
						}
					  }
					  measurement {
						weight {
						  value
						}
					  }
					  updatedAt
					}
				  }
				  nonFulfillableQuantity
				  currentQuantity
				  quantity
				  vendor
				  requiresShipping
				  taxable
				  isGiftCard
				  fulfillmentStatus
				  discountedUnitPriceSet {
					presentmentMoney {
					  amount
					  currencyCode
					}
					shopMoney {
					  amount
					  currencyCode
					}
				  }
				  totalDiscountSet {
					presentmentMoney {
					  amount
					  currencyCode
					}
					shopMoney {
					  amount
					  currencyCode
					}
				  }
				  discountAllocations {
					allocatedAmountSet {
						presentmentMoney {
						  amount
						  currencyCode
					  }
						shopMoney {
						  amount
						  currencyCode
					 }
					}
					discountApplication {
					  allocationMethod
					  index
					  targetType
					  targetSelection
					  __typename
					  ... on ManualDiscountApplication {
						title
						description
					  }
					  ... on DiscountCodeApplication {
						code
					  }
					  value {
						... on MoneyV2 {
						  currencyCode
						  amount
						}
						... on PricingPercentageValue {
						  percentage
						}
					  __typename
					  }
					}
				  }
				  
				}
			  }
			}
			
			totalWeight
			customerAcceptsMarketing
			taxesIncluded
			sourceName
			tags
			displayFulfillmentStatus
			displayFinancialStatus
			billingAddress {
			  zip
			  countryCodeV2
			  provinceCode
			}
			shippingAddress {
			  zip
			  countryCodeV2
			  provinceCode
			}
			processedAt
			createdAt
			closedAt
			cancelledAt
			updatedAt
			test
			presentmentCurrencyCode
			paymentGatewayNames
			totalShippingPriceSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			totalPriceSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			currentSubtotalPriceSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			totalTaxSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			totalOutstandingSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			totalDiscountsSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			originalTotalAdditionalFeesSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			currentTotalDiscountsSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			subtotalPriceSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			currentTotalTaxSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			currentTotalAdditionalFeesSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			currentTotalPriceSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			originalTotalDutiesSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			currentTotalDutiesSet {
			  presentmentMoney {
				amount
				currencyCode
			  }
			  shopMoney {
				amount
				currencyCode
			  }
			}
			discountApplications {
			  edges {
				node {
				  allocationMethod
				  index
				  targetType
				  targetSelection
				  __typename
				  ... on AutomaticDiscountApplication {
					title
				  }
				  ... on DiscountCodeApplication {
					code
				  }
				  ... on ManualDiscountApplication {
					title
					description
				  }
				  ... on ScriptDiscountApplication {
					title
				  }
				  value {
					... on MoneyV2 {
					  currencyCode
					  amount
					}
					... on PricingPercentageValue {
					  percentage
					}
					__typename
				  }
				}
			  }
			}
		  }
		}
	}

';


/*
// Refund query 
$refundDetailsNonBulk = 'query GetRefundDetails {
  orders(query: "%s") {
	edges {
	  node {
		id
		updatedAt
		restockable
        refunds (first:10) {
		  edges {
			node {
				id
				note
				createdAt
				order {
					id
					updatedAt
					restockable
				}
				quantity
				createdAt
				updatedAt
				  refundLineItems(first: 250 %s) {
					  edges {
						node {
						  lineItem {
							id
							fulfillmentService {
							  id
							  handle
							  serviceName
							}
							variant {
							  id
							  displayName
							  title
							  sku
							  barcode
							  price
							  product {
								id
								title
							  }
							}
							nonFulfillableQuantity
							quantity
							restockType
							vendor
							requiresShipping
							taxable
							isGiftCard
							fulfillmentStatus
							totalDiscountSet {
							  presentmentMoney {
								amount
								currencyCode
							  }
							  shopMoney {
								amount
								currencyCode
							  }
							}
							discountAllocations {
							  allocatedAmountSet {
								presentmentMoney {
								  amount
								  currencyCode
								}
								shopMoney {
								  amount
								  currencyCode
								}
							  }
							  discountApplication {
								allocationMethod
								index
								targetType
								targetSelection
								__typename
								... on ManualDiscountApplication {
								  title
								  description
								}
								... on DiscountCodeApplication {
								  code
								}
								value {
								  ... on MoneyV2 {
									currencyCode
									amount
								  }
								  ... on PricingPercentageValue {
									percentage
								  }
								  __typename
								}
							  }
							}
						  }
						}
					  }
					pageInfo 
					{
					  endCursor
					  hasNextPage
					}
				  }
			}
		  }
        }
	  }
	}
  }
}
';
*/

$refundDetailsNonBulk = 'query GetRefundDetails {
    refund(id:"%s") {
	  id
	  note
	  order {
		id
		updatedAt
		restockable
	  }
	  refundLineItems(first: 250 %s) {
		  edges {
			node {
			  lineItem {
				id
				fulfillmentService {
				  id
				  handle
				  serviceName
				}
				variant {
				  id
				  displayName
				  title
				  sku
				  barcode
				  price
				  product {
					id
					title
				  }
				}
				nonFulfillableQuantity
				quantity
				restockable
				vendor
				requiresShipping
				taxable
				isGiftCard
				fulfillmentStatus
				totalDiscountSet {
				  presentmentMoney {
					amount
					currencyCode
				  }
				  shopMoney {
					amount
					currencyCode
				  }
				}
				discountAllocations {
				  allocatedAmountSet {
					presentmentMoney {
					  amount
					  currencyCode
					}
					shopMoney {
					  amount
					  currencyCode
					}
				  }
				  discountApplication {
					allocationMethod
					index
					targetType
					targetSelection
					__typename
					... on ManualDiscountApplication {
					  title
					  description
					}
					... on DiscountCodeApplication {
					  code
					}
					value {
					  ... on MoneyV2 {
						currencyCode
						amount
					  }
					  ... on PricingPercentageValue {
						percentage
					  }
					  __typename
					}
				  }
				}
			  }
			  subtotalSet {
			    presentmentMoney {
				  amount
				  currencyCode
			    }
			    shopMoney {
				  amount
				  currencyCode
			    }
			  }
			  totalTaxSet {
			    presentmentMoney {
				  amount
				  currencyCode
			    }
			    shopMoney {
				  amount
				  currencyCode
			    }
			  }
			  restockType
			}
		  }
		pageInfo 
		{
		  endCursor
		  hasNextPage
		}
      }
	  createdAt
	}
}
';


// Non bulk Orders query model
$orderNonBulkDetails = 'query getNonBulkOrderDetails
{
	orders(first: 250
			query: "%s"
			%s) {
		edges {
		  node {
			id
			number
		  }
		}
		pageInfo 
		{
		  endCursor
		  hasNextPage
		}
	}
}
';


$queryStringQueryModel = 'mutation {
  bulkOperationRunQuery(
   query: """
   {
%s}  
"""
  ) {
    bulkOperation {
      id
      status
    }
    userErrors {
      field
      message
    }
  }
}';

//return;

// Bulk operation get result
define ('QUERY_STRING_GET_RESULTS', 'query {
  currentBulkOperation {
    id
    status
    errorCode
    createdAt
    completedAt
    objectCount
    fileSize
    url
    partialDataUrl
  }
}');


// Bulk operation cancel
$queryStringCancel = 'mutation bulkOperationCancel($id: ID!) {
  bulkOperationCancel(id: $id) {
    bulkOperation {
      id
    }
    userErrors {
      field
      message
    }
  }
}';

/*
	$storeName = STORE_DETAILS["UK"]['storeName'];
	$shopifyToken = STORE_DETAILS["UK"]['shopifyToken'];
	$inventoryLocationId = STORE_DETAILS["UK"]['locationId'];
	// Run query
	$ch = curl_init("https://" . $storeName . ".myshopify.com/admin/api/2025-07/graphql.json");
	$paramArray ["query"] = $queryStringCancel;
	$paramArray ["variables"] = Array();
	$paramArray ["variables"]["id"] = "gid://shopify/BulkOperation/7906409054589";
	$paramString = json_encode($paramArray);

//echo print_r($paramArray, true) . "\r\n\n";
//return true;
	$resultsString = runMyGraphQLCall($ch, $shopifyToken, $paramString);
	echo $resultsString . "\r\n";
	return;
*/


// This will contain either the end of the date range being processed,
// or the timestamp at the start of this run of the program
$updateToDate = null;
$runTime = "'" . gmdate("Y/m/d H:i:s") . "'";

// Connect to database
$con = sqlConnect(DATABASE_SERVER,DATABASE_USER,DATABASE_PASSWORD,DATABASE_NAME);
if (is_null($con))
	return;

if ($firstRun)
{
	// On a firstRun we run the GraphQL query for one month at a time,
	// from the startDate to now
	// This array will contain a date range for each month
	$queryClauseArray = Array();

	// Prepare the query clauses
	$today = time();
	$queryClauseModel = "(created_at:>'%sT00:00:00Z') AND (created_at:<'%sT23:59:59Z')";
	$queryClauseModelFinal = "created_at:>'%sT00:00:00Z'";

	$start = strtotime($startDate);
	while ($start < $today)
	{
		// Interval will be from current value of start to the end of the month
		$end = strtotime(date("Y-m-t", $start));
		
		// Final interval will have no end date specified
		if (($end > $today) || (date("Y-m-d", $end) == date("Y-m-d", $today)))
			$queryClause = sprintf($queryClauseModelFinal, date("Y-m-d", $start));
		else
			$queryClause = sprintf($queryClauseModel, date("Y-m-d", $start), date("Y-m-d", $end));

		// Store query intervals in an array
		$queryClauseArray[] = $queryClause;
		
		// Next start value is one day ahead (ie first of the next month
		$start = strtotime(date("Y-m-d", $end + 86400));
	}
}
else
{
	// For routine runs this array will contain a single start date
	$queryClauseArray = Array();

	$sqlDateString = sqlGetLatestUpdateDate($con, 'Orders');
	$latestOrdersUpdateDate = str_replace(" ", "T", $sqlDateString) . "Z";
//echo $latestOrdersUpdateDate . "\r\n\n";
	$queryClauseArray[0] = sprintf("updated_at:>'%s'", $latestOrdersUpdateDate);
}

if (!startSqlTransaction($con))
	return;
	
if ($firstRun)
{
	if (!(sqlEmptyTable($con, ORDERS_TABLE_NAME)
		&& sqlEmptyTable($con, ORDERS_ITEMS_TABLE_NAME)
		&& sqlEmptyTable($con, INVENTORY_LEVELS_TABLE_NAME)
		&& sqlEmptyTable($con, REFUNDS_TABLE_NAME)
		&& sqlEmptyTable($con, REFUNDS_ITEMS_TABLE_NAME)
		&& sqlEmptyTable($con, ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_NAME)))
		return;
}

for ($queryClauseIndex = 0; $queryClauseIndex < sizeof($queryClauseArray); $queryClauseIndex++)
{
	$error = false;
	
	// 
	if (($lessThanPos = strpos($queryClauseArray[$queryClauseIndex], "<")) === false)
	{
		$thisClauseEndDate = $runTime;
	}
	else
	{
		if (($endBracketPos = strpos($queryClauseArray[$queryClauseIndex], ")", $lessThanPos)) === false)
			echo "Failed to extract end date of range" . $queryClauseArray[$queryClauseIndex] . "\r\n";
		else
		{
			$thisClauseEndDate = substr($queryClauseArray[$queryClauseIndex], $lessThanPos + 1, $endBracketPos - $lessThanPos - 1);
		}
	}

	// Each date range is run in a transaction
	if (!startSqlTransaction($con))
		$error = true;

	if (!$error)
	{
		// If we are in firstRun and this is the first date range then we need to empty the tables
		if ($firstRun && ($queryClauseIndex == 0))
		{
			if (!(sqlEmptyTable($con, ORDERS_TABLE_NAME)
				&& sqlEmptyTable($con, ORDERS_ITEMS_TABLE_NAME)
				&& sqlEmptyTable($con, INVENTORY_LEVELS_TABLE_NAME)
				&& sqlEmptyTable($con, REFUNDS_TABLE_NAME)
				&& sqlEmptyTable($con, REFUNDS_ITEMS_TABLE_NAME)))
				$error = true;
		}
	}

	if (!$error)
	{
		echo "\r\nDate range: " . $queryClauseArray[$queryClauseIndex] . "\r\n";

		// Specify Orders query string
		$orderRangeQuery = sprintf($orderUpdatedRangeModel, $queryClauseArray[$queryClauseIndex], "%s", "%s");
		$queryStringOrderRangeQuery = sprintf($queryStringQueryModel, $orderRangeQuery);

		// Get order details for each of the stores
		foreach (STORE_LIST as $store)
		{
			echo "\r\n" . date("H:i:s") . " ***** " . $store . " store *****\r\n";
			if (!runOrderQueries($queryStringOrderRangeQuery, $store, $con, $firstRun, $orderNonBulkDetails, $queryClauseArray[$queryClauseIndex], $refundDetailsNonBulk))
			{
				$error = true;
				break;
			}
		}
	}

	// All being well we commit the transaction, otherwise roll back
	if ($error)
	{
		if (mysqli_rollback($con))
		{
			echo "\r\n" . date("H:i:s") . " Current changes rolled back - database is back to previous date range\r\n";
		}
		else
		{
			echo "\r\n" . date("H:i:s") . " Error rolling back transaction: " . mysqli_error($con) . "\r\nDatabase state indeterminate\r\n";
		}
	}
	else
	{
		if (mysqli_commit($con))
		{
			if ($queryClauseIndex == (sizeof($queryClauseArray) - 1))
			{
				echo "\r\n" . date("H:i:s") . " Current changes committed - database is up to date\r\n";
			}
			else
				echo "\r\n" . date("H:i:s") . " Current changes committed - database is up to current date range\r\n";
		}
		else
		{
			echo "\r\n" . date("H:i:s") . " Error committing transaction: " . mysqli_error($con) . "\r\nDatabase state indeterminate";
			$error = true;
		}
	}
	
	if (!$error)
	{
		$updateToDate = $thisClauseEndDate;
	}

	// If there has been any error, leave the for loop
	if ($error)
		break;
}

if ($updateToDate != null)
{
//echo $updateToDate . "\r\n";
	if (!sqlInsertLatestUpdateDate($con, "Orders", $updateToDate))
		echo "All data inserts worked, but latest update date failed\r\n";
}	

return;

function runOrderQueries ($queryStringOrderRangeQuery, $store, $con, $firstRun, $orderNonBulkDetails, $queryFilter, $refundDetailsNonBulk)
{	
	
	// Store
	$storeName = STORE_DETAILS[$store]['storeName'];
	$shopifyToken = STORE_DETAILS[$store]['shopifyToken'];
	$inventoryLocationId = STORE_DETAILS[$store]['locationId'];

echo date("H:i:s") . " Run main Orders query\r\n";
	// Run bulk query
	$ch = curl_init("https://" . $storeName . ".myshopify.com/admin/api/2025-07/graphql.json");
	$paramArray ["query"] = sprintf($queryStringOrderRangeQuery, $inventoryLocationId, $inventoryLocationId);
	$paramString = json_encode($paramArray);

//echo print_r($paramArray, true) . "\r\n\n";
//return true;
	$resultsString = runMyGraphQLCall($ch, $shopifyToken, $paramString);

	// Check query created OK
	if (($bulkQueryId = checkQueryCreation($resultsString)) == "")
		return false;
	
echo date("H:i:s") . " Run Orders non-bulk query\r\n";
	// Get non bulk data while waiting for bulk query to return 
	$nonBulkReturnArray = Array();
	if (!getNonBulkOrderDetails ($orderNonBulkDetails, $storeName, $shopifyToken, $queryFilter, $nonBulkReturnArray))
	{
		return false;
	}
	
echo date("H:i:s") . " Waiting for bulk query to complete\r\n";
	// Wait for query to complete
	if (($returnedDataUrl = waitForQueryToComplete($storeName, $shopifyToken, $bulkQueryId)) == "Error")
		return false;
	elseif (($returnedDataUrl = waitForQueryToComplete($storeName, $shopifyToken, $bulkQueryId)) == "")
		return true;

echo date("H:i:s") . " Getting main query returns\r\n";
	// Get returned data
	$refundsIdsArray = Array();
	if (!getReturnedOrderData($returnedDataUrl, $sqlAllOrdersValuesString, $sqlAllOrdersItemsValuesString, 
						$sqlAllOrderItemDiscountAllocationsValuesString, $sqlAllInventoryLevelsValuesString, 
						$nonBulkReturnArray, $refundsIdsArray))
		return false;
//echo $store . " Refund ids:\r\n" . print_r($refundsIdsArray, true) . "\r\n\n";

echo date("H:i:s") . " Run Refunds non-bulk and Rest queries\r\n";
	// Get refunds data
	$refundsSqlArray = Array();
	$refundsItemsSqlArray = Array();
	foreach ($refundsIdsArray As $orderId => $refundIdsArray)
	{
//echo "1 " . $orderId . "\r\n";
		// Most of the data comes from GraphQL ...
		foreach ($refundIdsArray as $key => $thisRefundId)
		{
//echo "2 " . $orderId . "\r\n";
//echo date("H:i:s") . " Run Refund non-bulk query\r\n";
			if (!getReturnedRefundData($thisRefundId, $storeName, $shopifyToken, $refundDetailsNonBulk, 
								$refundsSqlArray, $refundsItemsSqlArray))
				return false;
//echo "3 " . $orderId . "\r\n";
		}
		
//echo "4 " . $orderId . "\r\n";
		// ... but some (processed_at) can only be got from REST
//echo date("H:i:s") . " Run Refund Rest query\r\n";
		if (!getReturnedRefundRestData($orderId, $storeName, $shopifyToken, $refundsSqlArray))
			return false;
	}
	
	// Set up SQL strings for uploading refunds data
//echo print_r($refundsSqlArray, true) . "\r\n\n";
//echo print_r($refundsItemsSqlArray, true) . "\r\n\n";
	$sqlAllRefundsValuesString = setUpSqlValuesString($refundsSqlArray);
	$sqlAllRefundsItemsValuesString = setUpSqlValuesString($refundsItemsSqlArray);
//echo $sqlAllRefundsValuesString . "\r\n\n";

	// Write rows to Orders table
	if (strlen($sqlAllOrdersValuesString) == 0)
	{
		echo "nothing to insert\r\n";
		return true;
	}
	
	// Run Insert query
	if (!sqlInsertOrUpdate($con, ORDERS_TABLE_NAME, ORDERS_TABLE_COLUMNS_STRING, $sqlAllOrdersValuesString))
		return false;

	if (!sqlInsertOrUpdate($con, ORDERS_ITEMS_TABLE_NAME, ORDERS_ITEMS_TABLE_COLUMNS_STRING, $sqlAllOrdersItemsValuesString))
		return false;

//echo $sqlAllOrderItemDiscountAllocationsValuesString . "\r\n\n";	
	if (!sqlInsertOrUpdate($con, ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_NAME, ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_COLUMNS_STRING, $sqlAllOrderItemDiscountAllocationsValuesString))
		return false;
	
	if (!sqlInsertOrUpdate($con, INVENTORY_LEVELS_TABLE_NAME, INVENTORY_LEVELS_TABLE_COLUMNS_STRING, $sqlAllInventoryLevelsValuesString))
		return false;

	if (!(is_null($sqlAllRefundsValuesString) || ($sqlAllRefundsValuesString == '')))
		if (!sqlInsertOrUpdate($con, REFUNDS_TABLE_NAME, REFUNDS_TABLE_COLUMNS_STRING, $sqlAllRefundsValuesString))
			return false;

	if (!(is_null($sqlAllRefundsItemsValuesString) || ($sqlAllRefundsItemsValuesString == '')))
		if (!sqlInsertOrUpdate($con, REFUNDS_ITEMS_TABLE_NAME, REFUNDS_ITEMS_TABLE_COLUMNS_STRING, $sqlAllRefundsItemsValuesString))
			return false;

	// All OK
	return true;
}


function checkQueryCreation($resultsString)
{
	// Check for failure to run query
	if (substr($resultsString, 0, 6) == "Error:")
	{
		echo "Failed to run query:\r\n" . $resultsString . "\r\n\n";
		return "";
	}
	
	$resultsArray = json_decode($resultsString, true);
//echo "UK:\r\n" . print_r($resultsArray, true) . "\r\n";

	// Check for error running query
	if (array_key_exists("data", $resultsArray) && is_array($resultsArray['data'])
				&& array_key_exists('bulkOperationRunQuery', $resultsArray["data"]) && is_array($resultsArray['data']['bulkOperationRunQuery'])
				&& array_key_exists('bulkOperation', $resultsArray["data"]['bulkOperationRunQuery']) && is_array($resultsArray['data']['bulkOperationRunQuery']['bulkOperation'])
				&& array_key_exists('status', $resultsArray["data"]['bulkOperationRunQuery']['bulkOperation']))
	{
		$bulkQueryId = $resultsArray["data"]['bulkOperationRunQuery']['bulkOperation']['id'];
		$bulkQueryStatus = $resultsArray["data"]['bulkOperationRunQuery']['bulkOperation']['status'];
		if ($bulkQueryStatus != "CREATED")
		{
			echo "Query not created:\r\nStatus: " . $status . "\r\n";
			if (sizeof($resultsArray["data"]['bulkOperationRunQuery']['userErrors']) > 0)
				echo "Additional information: " . print_r($resultsArray["data"]['bulkOperationRunQuery']['userErrors'], true) . "\r\n\n";
			return "";
		}
	}
	else
	{
		echo "Query creation returned error:\r\n" . json_encode($resultsArray["data"]['bulkOperationRunQuery']['userErrors']) . "\r\n\n";
		return "";
	}
	
	return $bulkQueryId;
}

function waitForQueryToComplete($storeName, $shopifyToken, $bulkQueryId)
{
	// Wait for query to complete
	$queryReturned = false;
	$errorReturned = false;
	$count = 0;
	while (!$queryReturned && ($count < 300))
	{
		$ch = curl_init("https://" . $storeName . ".myshopify.com/admin/api/2025-01/graphql.json");
		$paramArray ["query"] = QUERY_STRING_GET_RESULTS;
		$paramString = json_encode($paramArray);
		$resultsString = runMyGraphQLCall($ch, $shopifyToken, $paramString);
		if (substr($resultsString, 0, 6) == "Error:")
		{
			echo $storeName . ":\r\n" . $resultsString . "\r\n\n";
			return;
		}
		else
		{
			$resultArray = json_decode($resultsString, true);
//echo print_r($resultArray, true) . "\r\n\n";
			if (array_key_exists("data", $resultArray) && is_array($resultArray['data'])
						&& array_key_exists('currentBulkOperation', $resultArray["data"]) && is_array($resultArray['data']['currentBulkOperation'])
						&& array_key_exists('status', $resultArray["data"]['currentBulkOperation']))
			{
//echo $resultArray["data"]['currentBulkOperation']['status'] . "\r\n";
				if ($resultArray["data"]['currentBulkOperation']['status'] == 'COMPLETED')
				{
					$queryReturned = true;
					$returnedDataUrl = $resultArray["data"]['currentBulkOperation']['url'];
					break;
				}
				else if ($resultArray["data"]['currentBulkOperation']['status'] == 'FAILED')
				{
					$errorReturned = true;
					$returnedError = $resultArray["data"]['currentBulkOperation']['errorCode'];
					break;
				}
			}
						
		}
		
		$count++;
		
		sleep(1);
	}

	if ($queryReturned)
	{
//		echo "URL: " . $returnedDataUrl . "\r\n\n";
		return $returnedDataUrl;
	}
	else if ($errorReturned)
	{
		echo "Query failed: " . $returnedError . "\r\n\n";
		return "Error";
	}
	else
	{
		echo "Timeout, id = " . $bulkQueryId . "\r\n\n";
		return "Error";
	}
}

function getNonBulkOrderDetails ($orderNonBulkDetails, $storeName, $shopifyToken, $queryFilter, &$nonBulkReturnArray)
{
	$continuation = true;
	$afterClause = "";
	
	while ($continuation)
	{
		$continuation = false;
		$ch = curl_init("https://" . $storeName . ".myshopify.com/admin/api/2025-07/graphql.json");
		$paramArray ["query"] = sprintf($orderNonBulkDetails, $queryFilter, $afterClause);
//		$paramArray ["variables"] = Array();
//		$paramArray ["variables"]["queryFilter"] = $queryFilter;
//		$paramArray ["variables"]["afterClause"] = $afterClause;
//echo print_r($paramArray, true) . "\r\n\n";
//return false;
		$paramString = json_encode($paramArray);
		$resultsString = runMyGraphQLCall($ch, $shopifyToken, $paramString);
		if (substr($resultsString, 0, 6) == "Error:")
		{
			echo $storeName . ":\r\n" . $resultsString . "\r\n\n";
			return false;
		}
		$resultsArray = json_decode($resultsString, true);
		$edges = $resultsArray['data']['orders']['edges'];
		for ($i = 0; $i < sizeof($edges); $i++)
		{
			$id = getShopifyIdNumber($edges[$i]['node']['id']);
			$number = $edges[$i]['node']['number'];
			
			$nonBulkReturnArray[$id] = Array();
			$nonBulkReturnArray[$id]['Number'] = $number;
		}
		
		if ($continuation = $resultsArray['data']['orders']['pageInfo']['hasNextPage'])
			$afterClause = 'after: "' . $resultsArray['data']['orders']['pageInfo']['endCursor'] . '"';
	}
	
	return true;
}

function getReturnedRefundData($refundId, $storeName, $shopifyToken, $refundDetailsNonBulk, &$refundsSqlArray, &$refundsItemsSqlArray)
{
	$continuation = true;
	$afterClause = "";
	$refundIdNumber = getShopifyIdNumber($refundId);
	$refundsSqlArray[$refundIdNumber] = REFUNDS_TABLE_DEFAULT_VALUES;

	while ($continuation)
	{
		$continuation = false;
		// Run non bulk query
		$ch = curl_init("https://" . $storeName . ".myshopify.com/admin/api/2025-07/graphql.json");
		$paramArray ["query"] = sprintf($refundDetailsNonBulk, $refundId, $afterClause);
		$paramString = json_encode($paramArray);
//echo print_r($paramArray, true) . "\r\n\n";

		$resultsString = runMyGraphQLCall($ch, $shopifyToken, $paramString);
		if (substr($resultsString, 0, 6) == "Error:")
		{
			echo $storeName . ":\r\n" . $resultsString . "\r\n\n";
			return false;
		}
		
		$resultsArray = json_decode($resultsString, true);
//echo print_r($resultsArray, true) . "\r\n\n";
		
		$refund = $resultsArray['data']['refund'];
		if (!is_array($refund))
			return true;
		
		$orderId = "";
		foreach ($refund As $key => $value)
		{
//echo $key . "\r\n";
			switch ($key)
			{
				case "id":
					break;
				case "note":
					$refundsSqlArray[$refundIdNumber]["Note"] = normaliseStringValue($value, 256);
					break;
				case "order":
					$orderId = getShopifyIdNumber($value["id"]);
					$refundsSqlArray[$refundIdNumber]["OrderId"] = "'" . $orderId . "'";
					$refundsSqlArray[$refundIdNumber]["OrderUpdatedAt"] = "'" . formatDate($value["updatedAt"]). "'";
					$refundsSqlArray[$refundIdNumber]["Restock"] = ($value["restockable"] ? 'true' : 'false');
					break;
				case "refundLineItems":
					getReturnedRefundItemsData($value["edges"], $refundsItemsSqlArray, $orderId, $refundId);
					break;
				case "createdAt":
					$refundsSqlArray[$refundIdNumber]["CreatedAt"] = "'" . formatDate($value). "'";
					break;
				default:
					break;
			}
		}
		
		if ($continuation = $resultsArray['data']['refund']['refundLineItems']['pageInfo']['hasNextPage'])
			$afterClause = 'after: "' . $resultsArray['data']['refund']['refundLineItems']['pageInfo']['endCursor'] . '"';
	}
//echo "\r\n";
	
	return true;
}

function getReturnedRefundRestData ($orderId, $storeName, $shopifyToken, &$refundsSqlArray)
{
//echo "5 " . $orderId . "\r\n";
	// Limit REST return
	$fields = "?fields=id,processed_at";

	// Make REST call
//echo "\r\n\n***********\r\n";
//echo "https://" . $storeName . ".myshopify.com/admin/api/2025-07/orders/" . $orderId . "/refunds.json" . $fields . "\r\n";
	$ch = curl_init("https://" . $storeName . ".myshopify.com/admin/api/2025-07/orders/" . $orderId . "/refunds.json" . $fields);
	$resultsString = runRESTCall($ch, $shopifyToken);
	if (substr($resultsString, 0, 6) == "Error:")
	{
		echo $storeName . ":\r\n" . $resultsString . "\r\n\n";
		return false;
	}

	// 
	$restReturn = json_decode($resultsString, true);

//echo "\r\n\n***********\r\n";
//echo print_r($restReturn, true) . "\r\n\r\n";
//echo "\r\n\n***********\r\n";

	$refundsArray = $restReturn['refunds'];
	foreach ($refundsArray as $key => $value)
	{
		$refundIdNumber = $value['id'];
		$refundsSqlArray[$refundIdNumber]['ProcessedAt'] = "'" . $value['processed_at'] . "'";
	}

	// All OK
	return true;
}

function getReturnedRefundItemsData ($valuesArray, &$refundsItemsSqlArray, $orderId, $refundId)
{
	for ($i = 0; $i < count($valuesArray); $i++)
	{
		foreach ($valuesArray[$i]["node"] as $key => $value)
		{
			switch ($key)
			{
				case "lineItem":
					$refundItemId = getReturnedRefundLineItemsData($value, $refundsItemsSqlArray, $orderId, $refundId);
					break;
				case "subtotalSet":
					$setValuesArray = getSetValues($value);
					$refundsItemsSqlArray[$refundItemId]['RefundSubtotalShopAmount'] = $setValuesArray['ShopAmount'];
					$refundsItemsSqlArray[$refundItemId]['RefundSubtotalShopCurrencyCode'] = "'" . $setValuesArray['ShopCurrencyCode'] . "'";
					$refundsItemsSqlArray[$refundItemId]['RefundSubtotalPresentmentAmount'] = $setValuesArray['PresentmentAmount'];
					$refundsItemsSqlArray[$refundItemId]['RefundSubtotalPresentmentCurrencyCode'] = "'" . $setValuesArray['PresentmentCurrencyCode'] . "'";
					$refundsItemsSqlArray[$refundItemId]['RefundSubtotal'] = $setValuesArray['ShopAmount'];
					break;
				case "totalTaxSet":
					$refundsItemsSqlArray[$refundItemId]['RefundTotalTaxShopAmount'] = $setValuesArray['ShopAmount'];
					$refundsItemsSqlArray[$refundItemId]['RefundTotalTaxShopCurrencyCode'] = "'" . $setValuesArray['ShopCurrencyCode'] . "'";
					$refundsItemsSqlArray[$refundItemId]['RefundTotalTaxPresentmentAmount'] = $setValuesArray['PresentmentAmount'];
					$refundsItemsSqlArray[$refundItemId]['RefundTotalTaxPresentmentCurrencyCode'] = "'" . $setValuesArray['PresentmentCurrencyCode'] . "'";
					$refundsItemsSqlArray[$refundItemId]['RefundTotalTax'] = $setValuesArray['ShopAmount'];
					break;
				case "restockType":
					$refundsItemsSqlArray[$refundItemId]['RefundRestockType'] = "'" . $value . "'";
					break;
					
				default:
					break;
			}
		}
	}
}
		
function getReturnedRefundLineItemsData ($valuesArray, &$refundsItemsSqlArray, $orderId, $refundId)
{
	foreach ($valuesArray as $key => $value)
	{
		switch ($key)
		{
			case "id":
				$refundItemId = getShopifyIdNumber($value);
				$refundsItemsSqlArray[$refundItemId] = REFUNDS_ITEMS_TABLE_DEFAULT_VALUES;
				$refundsItemsSqlArray[$refundItemId]["OrderId"] = "'" . $orderId . "'";
				$refundsItemsSqlArray[$refundItemId]["RefundId"] = "'" . getShopifyIdNumber($refundId) . "'";
				break;
			case "fulfillmentService";
				$refundsItemsSqlArray[$refundItemId]["FulFillmentsService"] = "'" . $value["handle"] . "'";
				break;
			case "variant";
				if (!is_array($value) || !(array_key_exists("id", $value)) || ($value["id"] == null))
					break;
				$itemVariantId = getShopifyIdNumber($value["id"]);
				if (!is_null($itemVariantId) && $itemVariantId != "")
				{						
					$refundsItemsSqlArray[$refundItemId]["ItemVariantId"] = "'" . $itemVariantId . "'";
					$refundsItemsSqlArray[$refundItemId]["Name"] = normaliseStringValue($value["displayName"], 256);
					$refundsItemsSqlArray[$refundItemId]["ItemPrice"] = $value["price"];
					$refundsItemsSqlArray[$refundItemId]["SKU"] = "'" . $value["sku"] . "'";
					$refundsItemsSqlArray[$refundItemId]["VariantTitle"] = normaliseStringValue($value["title"], 256);

					$refundsItemsSqlArray[$refundItemId]["ProductId"] = "'" . getShopifyIdNumber($value["product"]["id"]) . "'";
				}
				break;
			case "nonFulfillableQuantity";
				$refundsItemsSqlArray[$refundItemId]["FulfillableQuantity"] -= $value;
				break;
			case "quantity";
				$refundsItemsSqlArray[$refundItemId]["ItemQuantity"] = $value;
				$refundsItemsSqlArray[$refundItemId]["FulfillableQuantity"] += $value;
				break;
			case "vendor":
				$refundsItemsSqlArray[$refundItemId]["Vendor"] = normaliseStringValue($value, 64);
				break;
			case "requiresShipping":
				$refundsItemsSqlArray[$refundItemId]['RequiresShipping'] = ($value ? 'true' : 'false');
				break;
			case "taxable":
				$refundsItemsSqlArray[$refundItemId]['Taxable'] = ($value ? 'true' : 'false');
				break;
			case "isGiftCard":
				$refundsItemsSqlArray[$refundItemId]['GiftCard'] = ($value ? 'true' : 'false');
				break;
			case "fulfillmentStatus":
				$refundsItemsSqlArray[$refundItemId]['FulfillmentStatus'] = "'" . $value . "'";
				break;
			case "discountAllocations":
				copyDiscountAllocationsDataToArray($refundItemId, $orderId, $value, $refundsItemsSqlArray);
				break;
			case "totalDiscountSet":
				$setValuesArray = getSetValues($value);
				$refundsItemsSqlArray[$refundItemId]['TotalDiscount'] = $setValuesArray['ShopAmount'];
				break;
				
			default:
				break;
		}
	}
	
	return $refundItemId;
}

function getReturnedOrderData($returnedDataUrl, 
								&$sqlAllOrdersValuesString, 
								&$sqlAllOrdersItemsValuesString, 
								&$sqlAllOrderItemDiscountAllocationsValuesString,
								&$sqlAllInventoryLevelsValuesString,
								$nonBulkReturnArray,
								&$refundsIdsArray)
{
    // Initialize cURL session
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $returnedDataUrl);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); // Important for Shopify S3 links
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true); // Keep it secure
    curl_setopt($ch, CURLOPT_TIMEOUT, 1200);          // 20-minute timeout for large files

    $jsonlString = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    
    if (curl_errno($ch)) {
        $error_msg = curl_error($ch);
    }
    curl_close($ch);

    // Check if we got a successful response (HTTP 200) and content
    if ($jsonlString !== false && $httpCode === 200) 
    {
        echo date("H:i:s") . " File downloaded successfully \r\n"; 
        // echo $jsonlString;
    }
    else 
    {     
        echo "File download failed. HTTP Status: " . $httpCode . " " . ($error_msg ?? ''); 
        return false;
    }

	$jsonlLineArray = preg_split("/\r\n|\n|\r/", $jsonlString); 
	$ordersSqlArray = Array();
	$ordersItemsSqlArray = Array();
	$orderItemDiscountAllocationsSqlArray = Array();
	$inventoryLevelsSqlArray = Array();
	foreach($jsonlLineArray as $jsonLine)
	{
//echo "A\r\n";		
//echo print_r(json_decode($jsonLine, true), true) . "\r\n\n";
		if (strlen($jsonLine) == 0)
			continue;
//echo "B\r\n";		
		
		$jsonLineArray = json_decode($jsonLine, true);
//if (!array_key_exists("id", $jsonLineArray))
//{
//	echo print_r($jsonLineArray, true) . "\r\n\n";
//	return false;
//}
		if (array_key_exists("__parentId", $jsonLineArray))
			$parentId = $jsonLineArray['__parentId'];
		else 
			$parentId = "none";

		if (array_key_exists("id", $jsonLineArray))
		{
			$id = $jsonLineArray['id'];
			strtok($id, "/");
			strtok("/");
			$idType = strtok("/");
			$idNumber = strtok("/");
		}
		else
		{
			if (array_key_exists("__typename", $jsonLineArray))
			{
				if (in_array($jsonLineArray['__typename'], DISCOUNT_TYPES))
					$idType = "DiscountApplication";
				else 
					$idType = "none";
			}
		}
//echo $idType . "   " . $idNumber . "   " . $parentId . "\r\n";

		// Initialise SQL values array
		if ($idType == "Order")
		{
//echo "   1";
			$ordersSqlArray[$idNumber] = ORDERS_TABLE_DEFAULT_VALUES;
		}
		elseif ($idType == "LineItem")
		{
//echo "   2";
			$ordersItemsSqlArray[$idNumber] = ORDERS_ITEMS_TABLE_DEFAULT_VALUES;
			$orderItemDiscountAllocationsSqlArray[$idNumber] = ORDER_ITEM_DISCOUNT_ALLOCATIONS_TABLE_DEFAULT_VALUES;
			if (is_array($jsonLineArray["variant"]))
			{
				$inventoryItemIdNumber = getShopifyIdNumber($jsonLineArray['variant']['inventoryItem']['id']);
				$inventoryLevelsSqlArray[$inventoryItemIdNumber] = INVENTORY_LEVELS_TABLE_DEFAULT_VALUES;
			}
		}
//echo "   3/r/n";
		elseif (($idType == "DiscountCodeApplication") || ($idType == "DiscountCodeApplication"))
		{
			continue;
		}

		if (!copyOrderDataToArrays($jsonLineArray, $idType, $ordersSqlArray, $ordersItemsSqlArray, 
				$orderItemDiscountAllocationsSqlArray, $inventoryLevelsSqlArray, $refundsIdsArray))
			return false;
//echo "   4\r\n";

	}
//echo "C\r\n";	
//echo print_r($ordersSqlArray, true) . "\r\n\n" . print_r($ordersItemsSqlArray, true) . "\r\n\n";
//return false;
	
	foreach ($ordersItemsSqlArray as $itemId => $orderItemValues)
	{
		$orderId = str_replace("'", "", $orderItemValues['OrderId']);
//echo $orderId . "\r\n";
		$totalPriceThisItem = $orderItemValues['ItemQuantity'] * $orderItemValues['ItemPrice'];
		
		$ordersSqlArray[$orderId]['TotalLineItemsSetShopMoneyAmount'] += $totalPriceThisItem;
		$ordersSqlArray[$orderId]['TotalLineItemsSetPresentmentMoneyAmount'] += $totalPriceThisItem;
		$ordersSqlArray[$orderId]['TotalOrderItemsPrice'] += $totalPriceThisItem;
	}
		
	foreach ($ordersSqlArray as $orderId => $valuesArray)
	{
//echo print_r($nonBulkReturnArray, true) . "\r\n\n";
		if (array_key_exists($orderId, $nonBulkReturnArray))
		{
//echo "1";
			$valuesArray['Number'] = $nonBulkReturnArray[$orderId]['Number'];
		}
//echo "2";
	}

	// Set up values strings for SQL insertions
	$sqlAllOrdersValuesString = setUpSqlValuesString($ordersSqlArray);
	$sqlAllOrdersItemsValuesString = setUpSqlValuesString($ordersItemsSqlArray);
	$sqlAllOrderItemDiscountAllocationsValuesString = setUpSqlValuesString($orderItemDiscountAllocationsSqlArray);
	$sqlAllInventoryLevelsValuesString = setUpSqlValuesString($inventoryLevelsSqlArray);
	$sqlAllOrderItemDiscountAllocationsString = setUpSqlValuesString($orderItemDiscountAllocationsSqlArray);

	return true;
}

function copyOrderDataToArrays($jsonLineArray, $idType, &$ordersSqlArray, &$ordersItemsSqlArray, 
			&$orderItemDiscountAllocationsSqlArray, &$inventoryLevelsSqlArray, &$refundsIdsArray)
{
	
	switch ($idType)
	{
		case "Order":
			copyOrdersDataToArray($jsonLineArray, $ordersSqlArray, $refundsIdsArray);
			break;
		case "LineItem":
			copyOrdersItemsDataToArray($jsonLineArray, $ordersItemsSqlArray, $orderItemDiscountAllocationsSqlArray, 
					$inventoryLevelsSqlArray, $ordersSqlArray);
			break;
		case "DiscountApplication":
			$orderId = getShopifyIdNumber($jsonLineArray["__parentId"]);
			copyDiscountApplicationDataToArray($jsonLineArray, $ordersSqlArray[$orderId]);
			break;
	}
	
	return true;
}

function copyOrdersDataToArray($jsonLineArray, &$ordersSqlArray, &$refundsIdsArray)
{
	$fullOrderId = $jsonLineArray['id'];
	$orderId = getShopifyIdNumber($fullOrderId);
	
	foreach ($jsonLineArray as $key => $value)
	{
		if (is_null($value))
			continue;
		$setName = "";
		switch ($key)
		{
			case "name":
				$ordersSqlArray[$orderId]['Name'] = normaliseStringValue($value, 32);
				break;
			case "customer":
				$ordersSqlArray[$orderId]['CustomerId'] = "'" . getShopifyIdNumber($value['id']) . "'";
				$ordersSqlArray[$orderId]['CustomerFirstName'] = normaliseStringValue($value["firstName"], 64);
				$ordersSqlArray[$orderId]['CustomerLastName'] = normaliseStringValue($value["lastName"], 64);
				break;
			case "currencyCode":
				$ordersSqlArray[$orderId]['Currency'] = "'" . $value . "'";
				break;
			case "email":
				$ordersSqlArray[$orderId]['Email'] = "'" . $value . "'";
				break;
			case "totalWeight":
				$ordersSqlArray[$orderId]['TotalWeight'] = $value;
				break;
			case "refunds":
				if (is_array($value) && (count($value) > 0))
				{
					$refundsIdsArray[$orderId] = Array();
					for ($i = 0; $i < count($value); $i++)
					{
						$refundsIdsArray[$orderId][] = $value[$i]['id'];
					}
				}
				break;
			case "customerAcceptsMarketing":
				$ordersSqlArray[$orderId]['BuyerAcceptsMarketing'] = ($value ? 'true' : 'false');
				break;
			case "taxesIncluded":
				$ordersSqlArray[$orderId]['TaxesIncluded'] = ($value ? 'true' : 'false');
				break;
			case "sourceName":
				$ordersSqlArray[$orderId]['SourceName'] = normaliseStringValue($value, 32);
				break;
			case "tags":
				$tagsArray = $value;
				$ordersSqlArray[$orderId]['Tags'] = normaliseStringValue(implode(',', $tagsArray), 128);
				break;
			case "displayFulfillmentStatus":
				$ordersSqlArray[$orderId]['FulfillmentStatus'] = "'" . $value . "'";
				break;
			case "displayFinancialStatus":
				$ordersSqlArray[$orderId]['FinancialStatus'] = "'" . $value . "'";
				break;
			case "billingAddress":
				if (is_array($value))
				{
					if (array_key_exists('zip', $value))
						$ordersSqlArray[$orderId]['BillingAddressZip'] = "'" . $value['zip'] . "'";
					if (array_key_exists('countryCode', $value))
						$ordersSqlArray[$orderId]['BillingAddressCountryCode'] = "'" . $value['countryCode'] . "'";
					if (array_key_exists('provinceCode', $value))
						$ordersSqlArray[$orderId]['BillingAddressProvinceCode'] = "'" . $value['provinceCode'] . "'";
				}
				break;
			case "shippingAddress":
				if (is_array($value))
				{
					if (array_key_exists('zip', $value))
						$ordersSqlArray[$orderId]['ShippingAddressZip'] = "'" . $value['zip'] . "'";
					if (array_key_exists('countryCode', $value))
						$ordersSqlArray[$orderId]['ShippingAddressCountryCode'] = "'" . $value['countryCode'] . "'";
					if (array_key_exists('provinceCode', $value))
						$ordersSqlArray[$orderId]['ShippingAddressProvinceCode'] = "'" . $value['provinceCode'] . "'";
				}
				break;
			case "billingAddressMatchesShippingAddress":
				$ordersSqlArray[$orderId]['ShippingAddressDefault'] = ($value ? 'true' : 'false');
				break;
			case "processedAt":
				$ordersSqlArray[$orderId]['ProcessedAt'] = "'" . formatDate($value) . "'";
				break;
			case "createdAt":
				$ordersSqlArray[$orderId]['CreatedAt'] = "'" . formatDate($value) . "'";
				break;
			case "closedAt":
				$ordersSqlArray[$orderId]['ClosedAt'] = "'" . formatDate($value). "'";
				break;
			case "cancelledAt":
				$ordersSqlArray[$orderId]['CancelledAt'] = "'" . formatDate($value) . "'";
				break;
			case "updatedAt":
				$ordersSqlArray[$orderId]['UpdatedAt'] = "'" . formatDate($value) . "'";
				break;
			case "test":
				$ordersSqlArray[$orderId]['Test'] = ($value ? 'true' : 'false');
				break;
			case "presentmentCurrencyCode":
				$ordersSqlArray[$orderId]['PresentmentCurrency'] = "'" . $value . "'";
				break;
			case "paymentGatewayNames":
				$paymentGatewayNamesArray = $value;
				$ordersSqlArray[$orderId]['PaymentGatewayNames'] = "'" . implode(',', $paymentGatewayNamesArray) . "'";
				break;
				
			case "totalShippingPriceSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['TotalShippingPriceSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['TotalShippingPriceSetShopMoneyCurrencyCode'] = "'" . $setValuesArray['ShopCurrencyCode'] . "'";
				$ordersSqlArray[$orderId]['TotalShippingPriceSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "totalPriceSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['TotalPriceSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['TotalPriceSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
//				$ordersSqlArray[$orderId]['TotalOrderItemsPrice'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['TotalPrice'] = $setValuesArray['ShopAmount'];
				break;
			case "totalTaxSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['TotalTaxSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['TotalTaxSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				$ordersSqlArray[$orderId]['TotalTax'] = $setValuesArray['ShopAmount'];
				break;
			case "totalOutstandingSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['TotalOutstanding'] = $setValuesArray['ShopAmount'];
				break;
			case "subtotalPriceSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['TotalSubtotalSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['TotalSubtotalSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "totalDiscountsSet":
//echo $orderId . ":\r\n" . print_r($value, true) . "\r\n\n";
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['TotalDiscountsSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['TotalDiscountsSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				$ordersSqlArray[$orderId]['TotalDiscounts'] = $setValuesArray['ShopAmount'];
				break;
			case "originalTotalAdditionalFeesSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['OrigTotalAdditionalFeesSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['OrigTotalAdditionalFeesSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "currentTotalDiscountsSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['CurTotalDiscountsSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['CurTotalDiscountsSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "currentSubtotalPriceSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['CurSubtotalPriceSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['CurSubtotalPriceSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				$ordersSqlArray[$orderId]['SubtotalPrice'] = $setValuesArray['ShopAmount'];
				break;
			case "currentTotalTaxSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['CurrentTotalTaxPriceSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['CurrentTotalTaxPriceSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "currentTotalAdditionalFeesSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['CurTotalAdditionalFeesSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['CurTotalAdditionalFeesSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "currentTotalPriceSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['CurrentTotalPriceSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['CurrentTotalPriceSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "originalTotalDutiesSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['OriginalTotalDutiesSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['OriginalTotalDutiesSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "currentTotalDutiesSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['CurrentTotalDutiesSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['CurrentTotalDutiesSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
			case "currentTotalDutiesSet":
				$setValuesArray = getSetValues($value);
				$ordersSqlArray[$orderId]['CurrentTotalDutiesSetShopMoneyAmount'] = $setValuesArray['ShopAmount'];
				$ordersSqlArray[$orderId]['CurrentTotalDutiesSetPresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				break;
		}
	}
}

function copyOrdersItemsDataToArray($jsonLineArray, &$ordersItemsSqlArray, &$orderItemDiscountAllocationsSqlArray, &$inventoryLevelsSqlArray, $ordersSqlArray)
{
	$itemId = getShopifyIdNumber($jsonLineArray['id']);
	$orderId = getShopifyIdNumber($jsonLineArray['__parentId']);
	$ordersItemsSqlArray[$itemId]['OrderId'] = "'" . $orderId . "'";;
	$ordersItemsSqlArray[$itemId]['OrderUpdatedAt'] = $ordersSqlArray[$orderId]['UpdatedAt'];
	$orderItemDiscountAllocationsSqlArray[$itemId]['OrderId'] = "'" . $orderId . "'";;

	$orderItemDiscountAllocationsSqlArray[$itemId]['OrderUpdatedAt'] = $ordersSqlArray[$orderId]['UpdatedAt'];
	
	foreach ($jsonLineArray as $key => $value)
	{
		if (is_null($value))
			continue;
		switch ($key)
		{
			case "variant":
//echo "order: " . $orderId . ", Item: " . $itemId . ", Variant values:\r\n" . print_r($value, true) . "\r\n\n";
				copyVariantDataToArray($itemId, $value, $ordersItemsSqlArray, $inventoryLevelsSqlArray);
				break;
			case "fulfillmentService":
//echo print_r($value, true) . "\r\n\n";
				if (is_array($value) && array_key_exists('handle', $value))
					$ordersItemsSqlArray[$itemId]["FulfillmentsService"] = "'" . $value['handle'] . "'";
				break;
			case "quantity":
				$ordersItemsSqlArray[$itemId]["ItemQuantity"] = $value;
				$ordersItemsSqlArray[$itemId]["FulfillableQuantity"] += $value;
				$quantitySet = true;
				break;
			case "nonFulfillableQuantity":
				$ordersItemsSqlArray[$itemId]["FulfillableQuantity"] -= $value;
				break;
			case "vendor":
				$ordersItemsSqlArray[$itemId]["Vendor"] = normaliseStringValue($value, 64);
				break;
			case "requiresShipping":
				$ordersItemsSqlArray[$itemId]['RequiresShipping'] = ($value ? 'true' : 'false');
				break;
			case "taxable":
				$ordersItemsSqlArray[$itemId]['Taxable'] = ($value ? 'true' : 'false');
				break;
			case "isGiftCard":
				$ordersItemsSqlArray[$itemId]['GiftCard'] = ($value ? 'true' : 'false');
				break;
			case "fulfillmentStatus":
				$ordersItemsSqlArray[$itemId]['FulfillmentStatus'] = "'" . $value . "'";
				break;
			case "discountedUnitPriceSet":
				$setValuesArray = getSetValues($value);
				$ordersItemsSqlArray[$itemId]["ItemPrice"] = $setValuesArray['ShopAmount'];
				break;
			case "discountAllocations":
				if ($value == null || !is_array($value) || $value == ""   )
					break;
				if (count($value) == 0)
					break;
//echo print_r($value, true) . "\r\n\n";
//break;
				
				copyDiscountAllocationsDataToArray($itemId, $value, $ordersItemsSqlArray);
				$discountApplicationArray = $value[0]['discountApplication'];
				$orderItemDiscountAllocationsSqlArray[$itemId]['ApplicationIndex'] = $discountApplicationArray['index'];

				$setValuesArray = getSetValues($value[0]['allocatedAmountSet']);
				$orderItemDiscountAllocationsSqlArray[$itemId]['ShopMoneyAmount'] = $setValuesArray['ShopAmount'];;
				$orderItemDiscountAllocationsSqlArray[$itemId]['ShopMoneyCurrencyCode'] = "'" . $setValuesArray['ShopCurrencyCode'] . "'";
				$orderItemDiscountAllocationsSqlArray[$itemId]['PresentmentMoneyAmount'] = $setValuesArray['PresentmentAmount'];
				$orderItemDiscountAllocationsSqlArray[$itemId]['PresentmentMoneyCurrencyCode'] = "'" . $setValuesArray['PresentmentCurrencyCode'] . "'";
				
				break;
		}
		
//		$ordersSqlArray[$orderId]['TotalLineItemsSetShopMoneyAmount'] = $value;
	}
}

function copyDiscountApplicationDataToArray($applicationArray, &$sqlArray)
{
	if (array_key_exists("code", $applicationArray))
		$sqlArray["AppliedDiscountTitle"] = "'" . $applicationArray["code"] . "'";
	if (array_key_exists("title", $applicationArray))
		$sqlArray["AppliedDiscountTitle"] = "'" . $applicationArray["title"] . "'";
	if (array_key_exists("description", $applicationArray))
		$sqlArray["AppliedDiscountDescription"] = "'" . $applicationArray["description"] . "'";
	elseif (array_key_exists("title", $applicationArray))
		$sqlArray["AppliedDiscountDescription"] = "'" . $applicationArray["title"] . "'";
		
	if (array_key_exists("percentage", $applicationArray["value"]))
	{
		$sqlArray["AppliedDiscountValue"] = "'" . $applicationArray["value"]["percentage"] . "'";
		$sqlArray["AppliedDiscountValueType"] = "'PricingPercentageValue'";
	}
	if (array_key_exists("amount", $applicationArray["value"]))
	{
		$sqlArray["AppliedDiscountValue"] = "'" . $applicationArray["value"]["amount"] . "'";
		$sqlArray["AppliedDiscountValueType"] = "'MoneyV2'";
	}

}

function copyDiscountAllocationsDataToArray($itemId, $value, &$itemsSqlArray)
{
	if (is_null($value) || !(is_array($value)) || (count($value) == 0))
		return;
//echo $itemId . ":\r\n" . print_r($value[0], true) . "\r\n\n";
	
	$itemsSqlArray[$itemId]["TotalDiscount"] = $value[0]["allocatedAmountSet"]["shopMoney"]["amount"];
	$itemsSqlArray[$itemId]["AppliedDiscountAmount"] = $value[0]["allocatedAmountSet"]["shopMoney"]["amount"];

	copyDiscountApplicationDataToArray($value[0]["discountApplication"], $itemsSqlArray[$itemId]);

}

function copyVariantDataToArray($itemId, $variantValue, &$ordersItemsSqlArray, &$inventoryLevelsSqlArray)
{
	$priceSet = false;
	foreach ($variantValue as $key => $value)
	{
		if (is_null($value))
			continue;
		switch ($key)
		{
			case "inventoryItem":
//echo print_r($value, true) . "\r\n\n";
				copyInventoryDataToArray($value, $inventoryLevelsSqlArray);
				break;
			case "product":
				$ordersItemsSqlArray[$itemId]["ProductId"] = "'" . getShopifyIdNumber($value["id"]) . "'";
				break;
			case "id":
				$ordersItemsSqlArray[$itemId]["ItemVariantId"] = "'" . getShopifyIdNumber($value) . "'";
				break;
			case "displayName":
				$ordersItemsSqlArray[$itemId]["Name"] = normaliseStringValue($value, 256);
				break; 
//			case "price":
//				$ordersItemsSqlArray[$itemId]["ItemPrice"] = $value;
//				$priceSet = true;
//				break;
			case "inventoryItem":
				$ordersItemsSqlArray[$itemId]["ItemGrams"] = $value["measurement"]["weight"]["value"] * 1000;
				break;
			case "sku":
				$ordersItemsSqlArray[$itemId]["SKU"] = "'" . $value . "'";
				break;
			case "title":
				$ordersItemsSqlArray[$itemId]["VariantTitle"] = normaliseStringValue($value, 256);
				break;
		}		
	}
}

function copyInventoryDataToArray($inventoryValue, &$inventoryLevelsSqlArray)
{
	$inventoryItemId = getShopifyIdNumber($inventoryValue["id"]);
	foreach ($inventoryValue as $key => $value)
	{
		if (is_null($value))
			continue;
		
		switch ($key)
		{
			case "inventoryLevel":
//echo "inventoryLevel value:\r\n" . print_r($value, true) . "\r\n\n";
				foreach ($value['quantities'] as $quantitiesArray)
				{
//echo "quantities value:\r\n" . print_r($quantitiesArray, true) . "\r\n\n";
					if ($quantitiesArray['name'] == "available")
					{
						$inventoryLevelsSqlArray[$inventoryItemId]['Available'] = $quantitiesArray['quantity'];
						break;
					}
				}

				$inventoryLevelsSqlArray[$inventoryItemId]['LocationId'] = "'" . getShopifyIdNumber($value['location']['id']) . "'";
				
				break;
			case "updatedAt":
//echo "updatedAt value:\r\n" . print_r($value, true) . "\r\n\n";
				$inventoryLevelsSqlArray[$inventoryItemId]['UpdatedAt'] = "'" . formatDate($value) . "'";
				break;
		}
	}
//echo "\r\n";
}

function setUpSqlValuesString ($sqlArray)
{
	$sqlValuesArray = Array();
	foreach ($sqlArray as $keyId => $valuesArray)
	{
		$sqlRowValuesArray = Array();
		$sqlRowValuesArray[] = "'" . $keyId . "'";
//if (!is_array($valuesArray))
//	echo "### " . $keyId . ": " . $valuesArray . " ###";
		foreach ($valuesArray as $key => $value)
		{
			$sqlRowValuesArray[] = $value;
		}
		
		$sqlRowValuesString = "(" . implode(",", $sqlRowValuesArray) . ")\r\n"; 
		$sqlValuesArray[] = $sqlRowValuesString;
	}
	if (count($sqlValuesArray) > 0)
		$sqlValuesString = implode(",", $sqlValuesArray) . "\r\n";
	else
		$sqlValuesString = "";

	return $sqlValuesString;
}

function formatDate($dateString)
{
	return str_replace('Z', '', str_replace('T', ' ', $dateString));
}

function normaliseStringValue($stringValue, $maxLength)
{
	// Make single quotes safe
	$temp1 = str_replace("'", "''", $stringValue);
	
	// Truncate temp1 if necessary - 
	//   if it ia greater, then we truncate it to at most (maxLength - 4) to allow for " ..." on the end
	if (strlen($temp1) > $maxLength)
	{
		// For unlikely event that $temp1 contains single quotes at pos (maxLength - 5) and pos (maxLength - 4)
		if (substr($temp1, $maxLength - 5, 2) == "''")
			$temp2 = substr($temp1, 0, $maxLength - 5);
		else
			$temp2 = substr($temp1, 0, $maxLength - 4);
		
		$normalisedValue = $temp2 . " ...";
	}
	else
		$normalisedValue = $temp1;
	
	return "'" . $normalisedValue . "'";
}

function getShopifyIdNumber($fullId)
{
	if (strrpos($fullId, '/') === false)
		return $fullId;
	else
		return substr($fullId, strrpos($fullId, '/') + 1);
}

function getSetValues ($value)
{
	if (is_null($value))
		return null;
	
	$setValuesArray = Array();
	$setValuesArray["PresentmentAmount"] = $value["presentmentMoney"]["amount"];
	$setValuesArray["PresentmentCurrencyCode"] = $value["presentmentMoney"]["currencyCode"];
	$setValuesArray["ShopAmount"] = $value["shopMoney"]["amount"];
	$setValuesArray["ShopCurrencyCode"] = $value["shopMoney"]["currencyCode"];
	
	return $setValuesArray;
}

function sqlConnect($serverName, $userName, $password, $databaseName)
{
	$error = false;
	
	// Connect to database
	try
	{
		$con=mysqli_connect($serverName, $userName, $password, $databaseName);
	}
	catch(Exception $e)
	{
		echo "Error: Failed to connect to MySQL: " . $e->GetMessage() . "\r\n";
		$error = true;
	}

	if (!$error)
		return $con;
	else
		return null;
}

function sqlInsertLatestUpdateDate($con, $tableGroup, $updateDate, $comment = '')
{
	// $updateDate has already benn put in single quotes
	$insertQuery = "INSERT INTO `ft_shopify_update_dates` (`table_group`, `update_date`, `comment`)
				VALUES ('" . $tableGroup . "', " . $updateDate . ", '" . $comment . "')";

	$returnCode = true;
	try
	{
		$result = mysqli_query($con, $insertQuery);
//echo "query result: *" . $result . "*\r\n";
		if (!$result)
		{
//echo "1\r\n";
			$returnCode = false;
			echo "Error inserting rows: " . mysqli_error($con) . "\r\nQuery: " . $insertQuery . "\r\n";
		}
	}
	catch (Exception $e)
	{
//echo "2\r\n";
		$returnCode = false;
		echo "Error inserting rows: " . $e->getMessage() . "\r\nQuery: " . $insertQuery . "\r\n";
	}
	
	return $returnCode;
}

function sqlGetLatestUpdateDate ($con, $tableGroup)
{
	$selectQuery = "SELECT `update_date` FROM `ft_shopify_update_dates`
	WHERE `entity_id` = (SELECT MAX(`entity_id`) FROM `ft_shopify_update_dates`
						WHERE `table_group` = '" . $tableGroup . "')";

	$returnDate = null;
	try
	{
		$result = mysqli_query($con, $selectQuery);
//echo "query result: *" . $result . "*\r\n";
		if ($result)
		{
			if (mysqli_num_rows($result) > 0)
			{
				$row = mysqli_fetch_array($result);
				$returnDate = $row[0];
			}
			else
			{
				echo "No last update date returned\r\n";
			}
			
		}
		else
		{
//echo "1\r\n";
			echo mysqli_error($con) . "\r\n";
		}
	}
	catch (Exception $e)
	{
//echo "2\r\n";
		echo $e->getMessage() . "\r\n";
	}
	
	return $returnDate;
}

function startSqlTransaction ($con)
{
	$error = false;
	
	// Start SQL transaction
	$result = mysqli_begin_transaction($con);
	
	if (!$result)
	{
		echo "Error starting transaction: " . __FILE__ . "/" . __LINE__ . mysqli_error($con) . "\r\nDatabase unchanged\r\n";
		$error = true;
	}
	
	if (!$error)
		return true;
	else
		return false;
}

function sqlEmptyTable($con, $tableName)
{
	$deleteQuery = "DELETE FROM `" . $tableName . "`";
//echo $deleteQuery . "\r\n\n";

	$returnCode = true;
	try
	{
		$result = mysqli_query($con, $deleteQuery);
//echo "query result: *" . $result . "*\r\n";
		if (!$result)
		{
//echo "1\r\n";
			$returnCode = false;
			echo "Error emptying table: " . mysqli_error($con) . "\r\nQuery: " . $deleteQuery . "\r\n";
		}
	}
	catch (Exception $e)
	{
//echo "2\r\n";
		$returnCode = false;
		echo "Error emptying table: " . $e->getMessage() . "\r\nQuery: " . $deleteQuery . "\r\n";
	}
	
	return $returnCode;
}

function sqlInsertIgnore($con, $tableName, $columnsList, $values)
{
//echo "\r\n\n" . $values . "\r\n\n";
	// Insert updated rows (only if there are some rows to insert)
	$insertQuery = "INSERT IGNORE INTO `" . $tableName . "` (" . $columnsList . ")
				VALUES " . $values;
//echo $insertQuery . "\r\n\n";

	$returnCode = true;
	try
	{
		$result = mysqli_query($con, $insertQuery);
//echo "query result: *" . $result . "*\r\n";
		if (!$result)
		{
//echo "1\r\n";
			$returnCode = false;
			echo "Error inserting rows: " . mysqli_error($con) . "\r\nQuery: " . $insertQuery . "\r\n";
//echo "3" . $columnsList . "\r\n" . $values . "\r\n";
		}
	}
	catch (Exception $e)
	{
//echo "2\r\n";
		$returnCode = false;
		echo "Error inserting rows: " . $e->getMessage() . "\r\nQuery: " . $insertQuery . "\r\n";
//echo "4" . $columnsList . "\r\n" . $values . "\r\n";
	}
	
	return $returnCode;
}

function sqlInsertOrUpdate($con, $tableName, $columnsList, $values)
{
//echo "\r\n\n" . $values . "\r\n\n";
	// Insert updated rows (only if there are some rows to insert)
	$replaceQuery = "REPLACE INTO `" . $tableName . "` (" . $columnsList . ")
				VALUES " . $values;
//echo $replaceQuery . "\r\n\n";

	$returnCode = true;
	try
	{
		$result = mysqli_query($con, $replaceQuery);
//echo "query result: *" . $result . "*\r\n";
		if (!$result)
		{
//echo "1\r\n";
			$returnCode = false;
			echo "Error inserting rows: " . mysqli_error($con) . "\r\nQuery: " . substr($replaceQuery, 0, 5000) . "\r\n";
//echo "1" . $columnsList . "\r\n" . $values . "\r\n";
		}
	}
	catch (Exception $e)
	{
//echo "2\r\n";
		$returnCode = false;
		echo "Error inserting rows: " . $e->getMessage() . "\r\nQuery: " . substr($replaceQuery, 0, 5000) . "\r\n";
//echo "2" . $columnsList . "\r\n" . $values . "\r\n";
	}
	
	return $returnCode;
}

function runMyGraphQLCall($ch, $shopifyToken, $paramString)
{
//echo $paramString . "<BR>";	
	// Set options
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
	curl_setopt($ch, CURLOPT_POSTFIELDS, $paramString);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json", 
					"X-Shopify-Access-Token:  " . $shopifyToken,
							"Content-Length: " . strlen($paramString)));

	// Get types
	$result = curl_exec($ch);
//echo "*" .  print_r($result, true) . "*\r\n\n";	
	
	// Check result - might be Magento error message or curl error
	if (curl_error($ch) != "")
	{
		// Curl error
		return "Error: " . curl_error($ch);
	}

	$resultArray = json_decode($result, true);
	if (array_key_exists("errors", $resultArray))
	{
		return "Error: Shopify error" . json_encode($resultArray["errors"], true);
	}

	if ($result == "")
	{
		// Empty string returned
		return "Error: No data returned";
	}
	else
	{
		// Success!
		return $result;
	}
}

function runRESTCall($ch, $shopifyToken)
{
//echo $param_string . "<BR>";	
	// Set options
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json", 
					"X-Shopify-Access-Token:  " . $shopifyToken));

	// Get types
	$result = curl_exec($ch);
//echo "*" .  print_r($result, true) . "*\r\n";	
	
	// Check result - might be Magento error message or curl error
	if (curl_error($ch) != "")
	{
		// Curl error
		return "Error: " . curl_error($ch);
	}

	if (($msg_pos = stripos($result, '"errors"')) != false)
	{
		return "Error: " . $result;
	}
//echo $msg_pos . "\r\n";
	if ($result == "")
	{
		// Empty string returned
		return "Error: No data returned";
	}
	else
	{
		// Success!
		return $result;
	}
}


?>