$sql = 'SELECT'
. ' T1.orders_date AS orders_date'
. ' T1.customers_id AS customers_id'
. ' T1.billaddress_company AS billaddress_company'
. ' T1.billaddress_street AS billaddress_street'
. ' T1.billaddress_suburb AS billaddress_suburb'
. ' T1.billaddress_city AS billaddress_city'
. ' T1.billaddress_state AS billaddress_state'
. ' T1.billaddress_country_name AS billaddress_country_name'
. ' T1.billaddress_postcode AS billaddress_postcode'
. ' T1.shipaddress_company AS shipaddress_company'
. ' T1.shipaddress_street AS shipaddress_street'
. ' T1.shipaddress_suburb AS shipaddress_suburb'
. ' T1.shipaddress_city AS shipaddress_city'
. ' T1.shipaddress_state AS shipaddress_state'
. ' T1.shipaddress_country_name AS shipaddress_country_name'
. ' T1.shipaddress_postcode AS shipaddress_postcode'
. ' T1.duedate AS duedate'
. ' T1.servicedate AS servicedate'
. ' T1.fob AS fob'
. ' T1.ponumber AS ponumber'
. ' T1.tax1total AS tax1total'
. ' T1.tax2total AS tax2total'
. ' T1.exchangerate AS exchangerate'
. ' T1.purchaseorder_status AS purchaseorder_status'
. ' T1.orders_payflag AS orders_payflag'
. ' T1.orders_shipflag AS orders_shipflag'
. ' T1.payment_id AS payment_id'
. ' T1.shipping_id AS shipping_id'
. ' T1.shipping_no AS shipping_no'
. ' T1.shipping_cost AS shipping_cost'
. ' T1.orders_date_ended AS orders_date_ended'
. ' T1.orders_back AS orders_back'
. ' T1.products_score AS products_score'
. ' T1.products_content AS products_content'
. ' T1.orders_comments AS orders_comments'
. ' T1.update_date_time AS update_date_time'
. ' T1.orders_year AS orders_year'
. ' T3.customers_id AS customers_id'
. ' T3.customers_gander AS customers_gander'
. ' T3.customers_firstname AS customers_firstname'
. ' T3.customers_middlename AS customers_middlename'
. ' T3.customers_lastname AS customers_lastname'
. ' T3.customers_company AS customers_company'
. ' T3.shipping_id AS shipping_id'
. ' T3.customers_telephone AS customers_telephone'
. ' T3.customers_email AS customers_email'
. ' T3.customers_fax AS customers_fax'
. ' T3.customers_credit AS customers_credit'
. ' T3.customers_type AS customers_type'
. ' T3.customers_role AS customers_role'
. ' T2.orderTotal AS orderTotal'
. ' T4.tax1rate AS tax1rate'
. ' T4.tax2rate AS tax2rate'
. ' T4.istax1exempt AS istax1exempt'
. ' T4.istax2exempt AS istax2exempt'
. ' T4.ispiggybackrate AS ispiggybackrate'
. " CASE WHEN istax1exempt = '0'"
. ' THEN tax1rate'
. " ELSE '0'"
. ' END AS tax1'
. " CASE WHEN istax2exempt = '0'"
. ' THEN tax2rate'
. " ELSE '0'"
. ' END AS tax2'
. " CASE WHEN istax1exempt = '0'"
. ' THEN (tax1rate * orderTotal)'
. " ELSE '0' END AS gst"
. " CASE WHEN istax2exempt = '0'"
. " THEN CASE WHEN (ispiggybackrate = '1' AND istax1exempt = '0')"
. ' THEN (tax2rate * ((1 + tax1rate)* orderTotal))'
. ' ELSE (tax2rate * orderTotal)'
. ' END'
. " ELSE '0'"
. ' END AS ont'
. " CASE WHEN istax1exempt = '0' AND istax2exempt = '0'"
. " THEN CASE WHEN ispiggybackrate = '0'"
. ' THEN ((tax1rate + tax2rate) * orderTotal)'
. ' ELSE ((tax1rate * orderTotal) + tax2rate *((1+tax1rate )* orderTotal))'
. ' END'
. " WHEN istax1exempt = '0' AND istax2exempt = '1'"
. ' THEN (tax1rate * orderTotal)'
. " WHEN istax1exempt = '1' AND istax2exempt = '0'"
. ' THEN (tax2rate * orderTotal)'
. " WHEN istax1exempt = '1' AND istax2exempt = '1'"
. " THEN '0'"
. ' END AS taxtotal'
. ' FROM orders AS T1'
. ' LEFT JOIN'
. ' ('
. ' SELECT'
. ' orders_year'
. ' orders_num'
. ' SUM(final_price * orders_products.orders_quantity) AS orderTotal'
. ' FROM orders_products'
. ' WHERE CONCAT(orders_year, orders_num) ='
. $pOrderId
. ' ) AS T2'
. ' ON (T1.orders_num = T2.orders_num AND T1.orders_year = T2.orders_year)'
. ' LEFT JOIN customers AS T3'
. ' ON T1.customers_id = T3.customers_id'
. ' LEFT JOIN tax_rate AS T4'
. ' ON T1.shipaddress_state = T4.zone_id'
. ' WHERE CONCAT(T1.orders_year, T1.orders_num) ='
. $pOrderId;
return $sql;