Andrew - 2016-07-14

Example MySQL report query. Top 10,000 sales over 365 days example. Performed in MySQL Workbench or other tool. Report may take awhile to pull. On older AMD dual-core it took between 1 to 2 minutes to look over ~2,900 products on ~35,000 ticketline rows, when reporting on every product (%%).
When reporting the '%card%' example as below. It only took 10.343 seconds to report the 23 products found with 'card' in its name.

#---- START PERIOD options ----
SET @NumberOfDays = 365; #Edit with number of days you wish to report.
#SET @StartDate = '2010-12-31 00:00:00'; #Edit with your Start date and time (yyyy-mm-dd hr:min:sec).
#SET @EndDate = '2015-12-31 00:00:00'; #Edit with your End date and time (yyyy-mm-dd hr:min:sec).
#---- END PERIOD options ----
#
#---- START CUSTOM options ----
SET @ProductName = '%card%'; #Edit with part (or whole) of product name. Case insensitive.
#SET @Supplier = '%<entry key="Supplier">%Cola%</entry>%'; #Edit with supplier. Place Supplier in Properties field >>
#SET @Type = '%<entry key="Type">%Shirt%</entry>%'; #Edit with type. Place Type in Properties field >>
/* Start Properties (Attribute) field example >>
<properties>
    <entry key="Supplier">Coca-Cola</entry>
    <entry key="Type">Adult T-Shirt</entry>
</properties>
*/ #<< End Properties (Attribute) field example
#---- END CUSTOM options ----
SELECT
    PRODUCTS.REFERENCE,
    PRODUCTS.NAME,
    PRODUCTS.PRICEBUY,
    PRODUCTS.PRICESELL,
    STOCKCURRENT.UNITS AS 'HAVE',
    SUM(TICKETLINES.UNITS) AS 'SOLD',
    SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) AS 'SUBTOTAL',
    SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) AS 'TAXES',
    SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) + SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) AS 'GROSSTOTAL',
    SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) - SUM(PRODUCTS.PRICEBUY * TICKETLINES.UNITS) AS 'PROFIT'
FROM
    TICKETLINES
        LEFT OUTER JOIN
    PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID
        LEFT OUTER JOIN
    TICKETS ON TICKETS.ID = TICKETLINES.TICKET
        LEFT OUTER JOIN
    RECEIPTS ON RECEIPTS.ID = TICKETS.ID
        LEFT OUTER JOIN
    STOCKCURRENT ON PRODUCTS.ID = STOCKCURRENT.PRODUCT,
    TAXES
WHERE
    RECEIPTS.ID = TICKETS.ID
        AND TICKETS.ID = TICKETLINES.TICKET
        AND TICKETLINES.PRODUCT = PRODUCTS.ID
        AND TICKETLINES.TAXID = TAXES.ID
        #--- START PERIOD option implementation. Use either "@NumberOfDays" or "@StartDate AND @EndDate" ----
        AND RECEIPTS.DATENEW BETWEEN DATE_SUB(NOW(),INTERVAL @NumberOfDays DAY) AND NOW()
        #AND RECEIPTS.DATENEW BETWEEN @StartDate AND @EndDate
        #--- END PERIOD option implementation ----
        #--- START CUSTOM option implementation. Use any single or multiple options ----
        AND PRODUCTS.NAME LIKE @ProductName
        #AND CONVERT(PRODUCTS.ATTRIBUTES USING latin1) LIKE @Supplier
        #AND CONVERT(PRODUCTS.ATTRIBUTES USING latin1) LIKE @Type
        #--- END CUSTOM option implementation.
GROUP BY PRODUCTS.REFERENCE , PRODUCTS.NAME , PRODUCTS.PRICEBUY , PRODUCTS.PRICESELL
ORDER BY GROSSTOTAL DESC
LIMIT 10000 #Edit this limit to return as many products as you wish. Top 10,000 Sales?

result example:

REFERENCE, NAME, PRICEBUY, PRICESELL, HAVE, SOLD, SUBTOTAL, TAXES, GROSSTOTAL, PROFIT
 

Last edit: Andrew 2016-07-15