FloreantPOS order reports

floreant_reports.sql

/* makes a small report of all tickets with details on orders */
SELECT DISTINCT 
  TICKET.ID, 
  CREATE_DATE, 
  TICKET.TOTAL_PRICE, 
  TICKET.PAID_AMOUNT, 
  CAST( VOIDED AS UNSIGNED) AS VOIDED, 
  PAYMENT_TYPE, (SELECT GROUP_CONCAT( CATEGORY_NAME, ".", ITEM_NAME,": ", ITEM_PRICE SEPARATOR ', ')  
                 FROM TICKET_ITEM  
                 WHERE TICKET_ITEM.TICKET_ID = TICKET.ID 
                 LIMIT 1) AS DETAIL 
FROM TICKET 
INNER JOIN TRANSACTIONS 
ON TRANSACTIONS.TICKET_ID = TICKET.ID 
INNER JOIN TICKET_ITEM 
ON TICKET_ITEM.TICKET_ID = TICKET.ID 
WHERE TICKET.CREATE_DATE > "2017-01-01";

/* To get a CSV file save the query under name.sql i.e. sales.sql then from command line run */
mysql posdb -u {user} -p{pwd} < sales.sql > out.csv
/* Yes no space between -p and password */