¢þT01Waitron Name Search Report1show waitrons with names matching search criteria~select id,name, if(active = 1, 'Yes', 'No') active from waitron where name like '%[likename]%' order by active desc, name;Tests[likename,value,Waitron name like wnames.id,dropdown,Select Waitron Please dtest,date,Date`id,Waitron ID,[align:right;total:count] name,Waitron Name active,Active? db_profile_name,Shop¦þwnameswnames waiter names3select id,name label from waitron where active = 1;TestsBid,ID label,waitron name for drop downs db_profile_name,Database§ÿRT1 Test subquery subquery testÐselect w.name wname,tods.invoice invoice,tods.stamp stamp,tods.name name from tillordersdetails tods,waitron w where w.id = '[wnames.id]' and tods.stamp > '[startdate] 06:00:00' and tods.waitron_id = w.id;TestsUwnames.id,dropdown,Select Waitron Please startdate,date,Select Start Date for reportJwname,Waitron Name invoice,Invoice ID stamp, Date & Time name, PLU Itemþ 5test for sorting3 columns from menuselect m.id,mc.name cname, concat(m.name,'
(',st.name,')') name, orgprice, st.name stype, m.print_destination from menu m, menu_categories mc, stocktypes st where m.name like '%[namelike]%' and m.type = st.id and m.useflag = 1 and m.menu_category = mc.id;Testsnamelike,value,PLU name like›id,Id,align:right;total:avg; cname,Category,total:count; name,Name orgprice,Price,prefix:R ;number:yes;total:sum;align:right; print_destination,Printerstype{þ replistrep listRep List;select distinct folder from tillclients where folder != ''; REP REPORTS folder,repÌþ 13 rollup-no-sumrollup no sum testsselect m.id,mc.name cname,m.name,m.code from menu m, menu_categories mc where mc.id = m.menu_category limit 100;Testsid,Id name,item code,PLUcname[þ14* Per user turnoverShow summary of sales per userYselect w.name wname, round(sum(qty)*sum(price),2) total, sum(qty) qty, tods.name, round(sum(qty) * sum(price) / sum(qty) , 2) ahs from tillordersdetails tods, waitron w where tods.waitron_id = w.id and date(tods.stamp) > '[sdate] 06:00:00' and date(tods.stamp) < '[edate] 06:00:00' and tods.plu > 0 group by tods.name limit 10; SALES*sdate,date,Start Date edate,date,End Date…name,PLU Name qty,QTY,align:right; total,Sales total,align:right;total:sum;prefix:R ;number:yes; ahs,Average Head Spend,total:avg;wnameóþ15* Address ListCustomer address listMselect name,ph_address,ph_address1,ph_address2,ph_postcode from tillclients;CUSTOMER LISTS_name,Name ph_address,Address ph_address1,Address1 ph_address2,Address2 ph_postcode,Address3Çþ15 * Phone ListCustomer phone list>select clientgroup,name,telephone,cellphone from tillclients;CUSTOMER LISTSFclientgroup,Group name,Name telephone,Telephone cellphone,Cellphoneœþ15 * Email ListCustomer email list0select clientgroup,name,email from tillclients;CUSTOMER LISTS)clientgroup,Group name,Name email,emailRþ21* Unit Price StatsLast, Min, Avg Unit PricelSELECT st.name stype, sc.name cname, si.name siname, CASE si.units WHEN 1 THEN 'kg' WHEN 2 THEN 'litre' WHEN 3 THEN 'each' END unit, SUM(org_qty) qty, ROUND(SUBSTRING_INDEX(GROUP_CONCAT(pu.price ORDER BY pu.date DESC), ',', 1),2) lastprice, ROUND(MAX(pu.price) , 2) maximumprice, ROUND(MIN(pu.price) , 2) minprice, ROUND(AVG(pu.price) , 2) avgprice FROM purchases pu, stockitems si, stocktypes st, stockcats sc WHERE si.id = pu.stockitems_id AND si.type = st.id AND si.stockcats_id = sc.id AND si.stockitem = 1 GROUP BY si.id ORDER BY st.name,sc.name, si.name; PURCHASESœstype,C.Center cname,Category siname,Item unit,Units qty,Total Qty lastprice,Last Price maximumprice,Max Price minprice,Min Price avgprice,Avg Price£þallitemsallitemscount of all stockitemsBselect count(id) cnt,'count all stockitems' label from stockitems;Tests!label,waitron name for drop downsjþ totaltest totaltestcount of all stockitems|select [allitems.cnt] allitemz, count(stockitems.id)/[allitems.cnt]*100 allitemz2, if (stockcats.name is not null,stockcats.name,'N/a') name, concat(count(stockitems.id), ' of [allitems.cnt]') cnt, stocktypes.name sname from stocktypes,stockitems left join stockcats on stockcats.id = stockitems.stockcats_id where stocktypes.id = stockitems.type group by stockcats.id;Tests%allitems.cnt,dropdown,Count all itemz€sname,C.Center,total:count; name,Category,total:count; cnt,Items,total:sum; allitemz2,allcnt2,sufix:%;total:sum;number:yes; sname‘þ24 Per DB RoolupTest for Rollup per DBselect name,id from waitron;Tests(name,user,total:count; id,id,total:sum;db_profile_nameàþ 31* Per CategorySales summary per CategoryOselect menucat.name catname, menu.name, sum(tods.price * tods.qty) total from tillordersdetails tods,tillinvoices inv, menu,menu_categories menucat where tods.invoice = inv.id and (inv.voided is null or inv.voided = 0 or inv.voided = 2) and menu.id = tods.plu and menucat.id = menu.menu_category group by tods.name ; SALESGname,PLU Name,total:count; total,Sales,total:sum;number:yes;prefix:R catnameãþ!33Vehicle SpendingVehicle Fuel Spenditureøselect vh_nr, MIN(vh_km) kmstart, MAX(vh_km) kmend, round(SUM(qty*price),2) totalvalue, MAX(vh_km) - MIN(vh_km) distance, SUM(qty) litres, ROUND( AVG(price), 2) avgprice, ROUND( ( SUM(qty * price) ) / (MAX(vh_km) - MIN(vh_km)) , 2 ) pusage, ROUND( SUM(qty) / ( MAX(vh_km) - MIN(vh_km) ) * 100 , 2 ) qtyusage from purchases where vh_nr != '' and date >= '[datestart]' and date <= '[dateend]' group by vh_nr having MAX(vh_km) - MIN(vh_km) > 0; VEHICLES0datestart,date,Start Date dateend,date,End Datetvh_nr,Vehicle Reg,total:count; kmstart,Start (km),align:right;sufix: km; kmend,End (km),align:right;sufix: km; distance,Distance (km),align:right;sufix: km;total:sum; litres,Fuel Litres,align:right;sufix: litres; avgprice,Average Price,align:right;prefix:R ; totalvalue,Total Value,align:right;prefix:R ;total:sum; qtyusage,Litres / 100km,align:right;sufix: litres;iþ"2INVOICES FOR PERIODFOOD INVOICES ÿSELECT `purchases`.`date` , `suppliers`.`name` AS sname , `purchases`.`invoice` , `purchases`.`qty` , `purchases`.`price` , `purchases`.`org_price` , `purchases`.`discount` , `stockitems`.`name` , `stockitems`.`type` , `stocktypes`.`name` AS stname FROM `purchases` INNER JOIN `suppliers` ON (`purchases`.`supplier_id` = `suppliers`.`id`) INNER JOIN `stockitems` ON (`purchases`.`stockitems_id` = `stockitems`.`id`) INNER JOIN `stocktypes` ON (`stocktypes`.`id` = `stockitems`.`type`) WHERE purchases.f_status = 1 AND purchases.cnote = 0 AND stockitems.type = 1 AND purchases.date >= '[sdate]' AND purchases.date <= '[edate]'; PURCHASES+sdate,date,Start Date edate,date,End Dateþdate,Invoice Date, sname,Supplier Name, invoice,Invoice Nr, stname,Department, qty,QTY,align:right, price,Price,number:yes;align:right, org_price,Total,number:yes;total:sum;align:right, discount,Discount Total,prefix:R, name,Product Name,Žþ#35ORDERS NOT YET INVOICEDSHOWS ALL OPEN ORDERSâSELECT waitron.name AS Waitron_Name, tilltableops.table_id AS Table_id, tillordersdetails.split AS Split, tillordersdetails.qty AS Qty, tillordersdetails.name AS PLU_Name FROM tillordersdetails JOIN waitron ON waitron.id = tillordersdetails.waitron_id JOIN tillorders ON tillorders.id = tillordersdetails.orders_id JOIN tilltableops ON tillorders.tableops_id = tilltableops.id WHERE invoice IS NULL ; OPEN ORDERSWWaitron_Name, POS User, Table_id, Table, Split, Split, Qty, Qty, PLU_Name, PLU NameTable_idþ$36Payment MethodsïSELECT dci.`tillinvoices_id` as till_invoice, ti.id as till_id, dci.`date` as date, dci.`time` as time, w.`name` as waiter, dci.`dcinv_turnover_total` as turnover, ti.`discount` as discount, ti.`ccard` as card, ti.`account` as account, ti.`cheque` as cheque, ti.`eft` as eft, ti.`cash` as cash FROM dcashinvoices dci JOIN tillinvoices ti ON dci.`tillinvoices_id` = ti.`id` JOIN waitron w ON ti.`waitron_id` = w.`id` WHERE dci.`tillinvoices_id` = ti.`id` ;Tests0datestart,date,Start Date dateend,date,End DateÅtill_invoice, invoice, till_id, inv_id, date, date, time, time, waiter, waiter, turnover, turnover, discount,discount, card, card, account, account, cheque, cheque, eft, eft, cash, cash,