SQL coding

Code: Select all

<?php
/*
 * THE QUERIES IN THIS FILE ARE RESPONSIBLE FOR INTERACTING WITH YOUR DATABASE.
 * PLEASE ALTER THE QUERIES BELOW TO MAKE THEM COMPATIBLE WITH YOUR SYSTEM.
 */

/*
 * Orders Query
 * 
 * There are no input variables for this query.
 * 
 * It needs to return all the orders which are ready to ship. The criteria are 
 * different depending on the shopping cart system used, usually it can be
 * determined by the status of the orders and/or the fact whether payment was 
 * successfully received.
 * 
 * The query needs to return all fields indicated below. If some of the fields
 * are not present in the shopping cart system then it's best to return an empty
 * string (eg SELECT '' as 'order_notes').
 * 
 * The above also applies to fields which are optional and might not be present
 * for every order.
 * 
 * Optional fields include:
 * discount
 * coupon_code
 * order_notes
 * customer_email
 * customer_telephone
 * shipping_line2
 * shipping company
 * shipping_county
 * billing_line2
 * billing company
 * billing_county
 */
$ordersQuery = "SELECT 
    o.`subtotal` AS 'subtotal',
    o.`tax` AS 'tax',
    o.`shipping` AS 'shipping',
    o.`total` AS 'total',
    o.`discount` AS 'discount',
    o.`coupon_code` AS 'coupon_code',
    o.`order_date` AS 'order_date',
    o.`shipping_method` AS 'shipping_method',
    o.`currency_code` AS 'currency_code',
    o.`order_ref` AS 'order_ref',
    o.`order_notes` AS 'order_notes',
    c.`customer_ref` AS 'customer_ref',
    c.`first_name` AS 'customer_first_name',
    c.`last_name` AS 'customer_last_name',
    c.`email` AS 'customer_email',
    c.`telephone` AS 'customer_telephone',
    sa.`first_name` AS 'shipping_first_name',
    sa.`last_name` AS 'shipping_last_name',
    sa.`line1` AS 'shipping_line1',
    sa.`line2` AS 'shipping_line2',
    sa.`company` AS 'shipping_company',
    sa.`city` AS 'shipping_city',
    sa.`county` AS 'shipping_county',
    sa.`postcode` AS 'shipping_postcode',
    sa.`country` AS 'shipping_country',
    ba.`first_name` AS 'billing_first_name',
    ba.`last_name` AS 'billing_last_name',
    ba.`line1` AS 'billing_line1',
    ba.`line2` AS 'billing_line2',
    ba.`company` AS 'billing_company',
    ba.`city` AS 'billing_city',
    ba.`county` AS 'billing_county',
    ba.`postcode` AS 'billing_postcode',
    ba.`country` AS 'billing_country'
    FROM orders o
    INNER JOIN customers c ON o.customer_id=c.customer_id
    INNER JOIN addresses sa ON c.shipping_address_id=sa.address_id
    INNER JOIN addresses ba ON c.billing_address_id=ba.address_id
    WHERE o.status='ready to ship'";

/*
 * Order Items Query
 * 
 * This query is executed for every order returned by the Orders Query.
 * 
 * It should return all items that belong to the order $ordeRref. The
 * $ordeRref variable will contain the order_ref returned by the Orders Query.
 * 
 * Required fields are sku and quantity. Other fields also have to be present in
 * the output of the query, but may be left empty (see example in the comments 
 * to the Orders Query about empty fields). If the optional fields are left
 * empty the values for name and totals will be taken from the StoreFeeder
 * product database, which might not be accurate, as the prices and names might
 * be different on each channel. Therefore, it is strongly advised that all the
 * field of the Order Items Query are populated with the correct data.
 * 
 */
$orderItemsQuery = "SELECT 
`sku` AS 'sku',
`name` AS 'name',
`quantity` as 'quantity',
`line_subtotal` as 'line_subtotal',
`line_tax` as 'line_tax',
`line_total` as 'line_total'
FROM order_items WHERE order_id=(SELECT order_id FROM orders WHERE order_ref='$orderRef')";

/*
 * Inventory Update Query
 * 
 * This query is expected to update the inventory level of a product in the
 * shopping cart system's database.
 * 
 * The input variables are:
 * $sku - holds the sku of the product which should have inventory updated
 * $inventory - the inventory level that should be set for the above product
 */
$inventoryUpdateQuery = "UPDATE products SET inventory='$inventory' WHERE sku='$sku'";

/*
 * Shipping Update Query
 * 
 * This query is responsible for marking an order sa dispatched in the shopping
 * cart system.
 * 
 * The input variables are:
 * $orderRef - the reference number for the order that was shipped (the same 
 * that was returned in the Orders Query)
 * $carrierName - the name of the carrier used to dispatch the order
 * $trackingNumber - the tracking number of the parcel (or multiple tracking
 * numbers if the order was dispatched in multiple parcels)
 * 
 * In most cases all that is required of this query is to update the status of
 * the order, howerver, more steps might be required depending on the shopping
 * cart system used.
 * 
 * If the shopping cart system is not design to keep carrier names or tracking
 * number info, you can just ignore the input variables and not use them in the
 * query.
 */
$shippingUpdateQuery = "UPDATE orders SET carrier_name='$carrierName', tracking_number='$trackingNumber', status='shipped' WHERE order_ref='$orderRef'";

/*
 * Product Download Query
 * 
 * This query is responsible for fetching all products from your website.
 * 
 * It should return all products that you want to send to StoreFeeder.
 * 
 * Required fields are sku, name and price_ex_vat. Other fields also have to be present in
 * the output of the query, but may be left empty (see example in the comments 
 * to the Orders Query about empty fields).
 * 
 * Note that you can 'hardcode' some of the fields, e.g. you could enter:
 * 
 * ... 'Default Warehouse' as 'warehouse'...
 * if you want all your products to be assigned to the 'Default Warehouse' inside StoreFeeder
 * 
 */
$productDownloadQuery = "SELECT 
`product.[sku]` AS 'sku',
` product_description.name` AS 'name',
`` as 'description',
`product.[price]` as 'price_ex_vat',
`vat_percent` as 'vat_percent',
`` AS 'rrp',
`` as 'cost',
`` as 'weight',
`` AS 'mpn',
`` as 'supplier',
`` as 'warehouse',
`` as 'inventory',
`` as 'ean',
`` as 'upc',
`` as 'image_url',
`` as 'website_url'
FROM product INNER JOIN product_description ON product.product_id = product_description.product_id";

?>