select
o.increment_id as orderId,
convert_tz(o.created_at, '+0:00', '+7:00') as created_at,
o.customer_email as email,
o.status as status,
CONCAT(sab.street ,' ', sab.city,' ', sab.region ,' ', sab.country_id) AS address,
CONCAT(sab.lastname,' ', sab.firstname) as customerName,
sab.telephone AS phone,
o.pickup_location,
i.name AS productName,
pe.entity_id as productId,
pe.sku AS sku,
thumbnail.value AS thumbnail,
IFNULL(price.value, i.original_price) AS originalPrice,
i.original_price AS price,
GREATEST(i.qty_ordered, IFNULL(i.qty_backordered, 0)) - i.qty_canceled - i.qty_shipped - i.qty_refunded AS qty
from fhs_sales_flat_order o
JOIN fhs_sales_flat_order_item i           ON i.order_id = o.entity_id
LEFT JOIN fhs_catalog_product_entity pe         ON i.product_id = pe.entity_id
LEFT JOIN fhs_catalog_product_entity_varchar thumbnail ON pe.entity_id = thumbnail.entity_id AND thumbnail.attribute_id = 87
LEFT JOIN fhs_catalog_product_entity_varchar publisher ON pe.entity_id = publisher.entity_id AND publisher.attribute_id = 142
LEFT JOIN fhs_catalog_product_entity_decimal price ON pe.entity_id = price.entity_id AND price.attribute_id = 75
JOIN fhs_sales_flat_order_address sab      ON o.billing_address_id = sab.entity_id
where o.increment_id = '100869217'
and o.status = "pending" and o.state = "new"
and o.pickup_location is not null
and (NOT ((i.product_type = 'simple') AND (i.product_options LIKE '%bundle_selection_attributes%')));