SQLize Online / PHPize Online  /  SQLtest Online

CREATE VIEW ocp_transaction_ReceiptData AS SELECT MasterTransaction_0.IDNumber AS ReceiptNo, MasterTransaction_0.ID, MasterTransaction_0.eftposReceiptNumber, coalesce(sum(CASE WHEN to_items_1.to_itemType_code = 'SURCHARGE' THEN to_items_1.paymentAmount ELSE 0 END), 0.0) AS surchargeAmount, coalesce(sum(CASE WHEN to_items_1.to_itemType_code != 'SURCHARGE' THEN to_items_1.paymentAmount ELSE 0 END), 0.0) AS NetAmount, MasterTransaction_0.createdAt AS CreatedAt, MasterTransaction_0.totalAmount AS TotalAmount, to_counter_2.description AS CounterDesciption, to_agent_3.agentID AS AgentID, to_counter_2.code AS CounterID, MasterTransaction_0.to_agent_ID FROM ((((ocp_transaction_MasterTransaction AS MasterTransaction_0 LEFT JOIN ocp_transaction_PaymentItem AS to_items_1 ON (to_items_1.to_masterTransaction_ID = MasterTransaction_0.ID)) LEFT JOIN ocp_config_business_counter_Counter AS to_counter_2 ON MasterTransaction_0.to_counter_code = to_counter_2.code) LEFT JOIN ocp_config_user_management_Agent AS to_agent_3 ON MasterTransaction_0.to_agent_ID = to_agent_3.ID) LEFT JOIN ocp_transaction_Receipt AS to_receipt_4 ON (to_receipt_4.to_transaction_ID = MasterTransaction_0.ID)) GROUP BY MasterTransaction_0.ID, to_counter_2.description, to_agent_3.agentID, to_counter_2.code, to_receipt_4.content;