// Pastèque is free software: you can redistribute it and/or modify // it under the terms of the GNU General Public License as published by // the Free Software Foundation, either version 3 of the License, or // (at your option) any later version. // // Pastèque is distributed in the hope that it will be useful, // but WITHOUT ANY WARRANTY; without even the implied warranty of // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the // GNU General Public License for more details. // // You should have received a copy of the GNU General Public License // along with Pastèque. If not, see <http://www.gnu.org/licenses/>. namespace BaseSales; $sql = "SELECT CASHREGISTERS.NAME, CLOSEDCASH.DATESTART, " . "CLOSEDCASH.DATEEND, TICKETS.TICKETID, RECEIPTS.DATENEW, " . "PRODUCTS.NAME AS PRD_NAME, CATEGORIES.NAME AS CAT_NAME, " . "TICKETLINES.UNITS, (TICKETLINES.PRICE * TICKETLINES.UNITS * (1 - TICKETLINES.DISCOUNTRATE)) AS SELL " . "FROM TICKETS " . "LEFT JOIN RECEIPTS ON TICKETS.ID = RECEIPTS.ID " . "LEFT JOIN CLOSEDCASH ON RECEIPTS.MONEY = CLOSEDCASH.MONEY " . "LEFT JOIN CASHREGISTERS ON CLOSEDCASH.CASHREGISTER_ID = CASHREGISTERS.ID " . "LEFT JOIN TICKETLINES ON TICKETLINES.TICKET = TICKETS.ID " . "LEFT JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID " . "LEFT JOIN CATEGORIES ON PRODUCTS.CATEGORY = CATEGORIES.ID " . "WHERE CLOSEDCASH.DATESTART > :start AND CLOSEDCASH.DATEEND < :stop " . "ORDER BY CLOSEDCASH.DATESTART DESC, TICKETS.TICKETID DESC, " . "TICKETLINES.LINE DESC"; $fields = array("NAME", "DATESTART", "DATEEND", "TICKETID", "DATENEW", "PRD_NAME", "CAT_NAME", "UNITS", "SELL"); $headers = array(\i18n("CashRegister.label"), \i18n("Session.openDate"), \i18n("Session.closeDate"), \i18n("Ticket.number"), \i18n("Ticket.date"), \i18n("Product name", PLUGIN_NAME), \i18n("Category name", PLUGIN_NAME), \i18n("Units", PLUGIN_NAME), \i18n("Sell", PLUGIN_NAME)); $report = new \Pasteque\Report(PLUGIN_NAME, "sales_report", \i18n("Sales report", PLUGIN_NAME), $sql, $headers, $fields); $report->addInput("start", \i18n("Session.openDate"), \Pasteque\DB::DATE); $report->setDefaultInput("start", time() - time() % 86400 - 86400); $report->addInput("stop", \i18n("Session.closeDate"), \Pasteque\DB::DATE); $report->setDefaultinput("stop", time() - time() % 86400 + 86400); $report->addFilter("DATESTART", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATESTART", "\\i18nDatetime"); $report->addFilter("DATEEND", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATEEND", "\\i18nDatetime"); $report->addFilter("DATENEW", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATENEW", "\\i18nDatetime"); $report->setVisualFilter("SELL", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("SELL", "\\i18nFlt", \Pasteque\Report::DISP_CSV); \Pasteque\register_report($report);
// GNU General Public License for more details. // // You should have received a copy of the GNU General Public License // along with Pastèque. If not, see <http://www.gnu.org/licenses/>. namespace BaseSales; $sql = "SELECT " . "PRODUCTS.REFERENCE, " . "PRODUCTS.NAME, " . "PRODUCTS.CATEGORY, " . "CATEGORIES.NAME AS CATNAME, " . "SUM(TICKETLINES.UNITS) AS UNITS, " . "(SUM(TICKETLINES.UNITS) * TICKETLINES.PRICE * (1 - TICKETLINES.DISCOUNTRATE)) AS TOTAL, " . "(SUM(TICKETLINES.UNITS) * ((TICKETLINES.PRICE * (1 - TICKETLINES.DISCOUNTRATE)) - PRODUCTS.PRICEBUY)) AS MARGIN, " . "(SUM(TICKETLINES.UNITS) * (TICKETLINES.PRICE * (1 - TICKETLINES.DISCOUNTRATE) * (SELECT SUM(1 + TAXES.RATE) FROM TAXES WHERE PRODUCTS.TAXCAT = TAXES.CATEGORY AND TAXES.VALIDFROM < RECEIPTS.DATENEW ORDER BY TAXES.VALIDFROM DESC LIMIT 0,1))) AS TAXEDTOTAL " . "FROM CLOSEDCASH " . "JOIN RECEIPTS ON RECEIPTS.MONEY = CLOSEDCASH.MONEY " . "JOIN TICKETS ON TICKETS.ID = RECEIPTS.ID " . "JOIN TICKETLINES ON TICKETLINES.TICKET = RECEIPTS.ID " . "JOIN PRODUCTS ON PRODUCTS.ID = TICKETLINES.PRODUCT " . "JOIN CATEGORIES ON CATEGORIES.ID = PRODUCTS.CATEGORY " . "WHERE " . "CLOSEDCASH.DATESTART > :start AND CLOSEDCASH.DATEEND < :stop " . "GROUP BY PRODUCTS.REFERENCE, PRODUCTS.NAME, PRODUCTS.CATEGORY " . "ORDER BY CATEGORIES.NAME, PRODUCTS.NAME"; $fields = array("REFERENCE", "NAME", "UNITS", "TOTAL", "TAXEDTOTAL", "MARGIN"); $headers = array(\i18n("Product.reference"), \i18n("Product.label"), \i18n("Quantity"), \i18n("Total w/o VAT", PLUGIN_NAME), \i18n("Total", PLUGIN_NAME), \i18n("Margin", PLUGIN_NAME)); $report = new \Pasteque\Report(PLUGIN_NAME, "sales_by_category_report", \i18n("Sales by category", PLUGIN_NAME), $sql, $headers, $fields); $report->addInput("start", \i18n("Session.openDate"), \Pasteque\DB::DATE); $report->setDefaultInput("start", time() - time() % 86400 - 7 * 86400); $report->addInput("stop", \i18n("Session.closeDate"), \Pasteque\DB::DATE); $report->setDefaultinput("stop", time() - time() % 86400 + 86400); $report->setGrouping("CATNAME"); $report->addSubTotal("TOTAL", \Pasteque\Report::TOTAL_SUM); $report->addSubTotal("TAXEDTOTAL", \Pasteque\Report::TOTAL_SUM); $report->addSubTotal("MARGIN", \Pasteque\Report::TOTAL_SUM); $report->addFilter("DATESTART", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATESTART", "\\i18nDatetime"); $report->addFilter("DATEEND", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATEEND", "\\i18nDatetime"); $report->setVisualFilter("UNITS", "\\i18nFlt", \Pasteque\Report::DISP_USER); $report->setVisualFilter("UNITS", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->setVisualFilter("TOTAL", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("TOTAL", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->setVisualFilter("TAXEDTOTAL", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("TAXEDTOTAL", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->setVisualFilter("MARGIN", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("MARGIN", "\\i18nFlt", \Pasteque\Report::DISP_CSV); \Pasteque\register_report($report);
// // You should have received a copy of the GNU General Public License // along with Pastèque. If not, see <http://www.gnu.org/licenses/>. namespace BaseRestaurant; $sql = "SELECT TICKETS.CUSTCOUNT, " . "MIN(RECEIPTS.DATENEW) AS STARTDATE, " . "MAX(RECEIPTS.DATENEW) AS ENDDATE, COUNT(TICKETS.TICKETID) AS COUNT, " . "(COUNT(TICKETS.TICKETID) / TICKETS.CUSTCOUNT) AS TABLES ," . "AVG(TAXLINES.BASE) AS AVGSUBPRICE, " . "AVG(TAXLINES.BASE + TAXLINES.AMOUNT) AS AVGPRICE " . "FROM TICKETS, RECEIPTS, TAXLINES WHERE RECEIPTS.ID = TICKETS.ID " . "AND RECEIPTS.ID = TAXLINES.RECEIPT " . "AND RECEIPTS.DATENEW > :start " . "AND RECEIPTS.DATENEW < :stop " . "GROUP BY TICKETS.CUSTCOUNT " . "ORDER BY TICKETS.CUSTCOUNT"; $fields = array("CUSTCOUNT", "STARTDATE", "ENDDATE", "COUNT", "TABLES", "AVGSUBPRICE", "AVGPRICE"); $headers = array(\i18n("Custcount", PLUGIN_NAME), \i18n("Session.openDate"), \i18n("Session.closeDate"), \i18n("Number", PLUGIN_NAME), \i18n("Tables", PLUGIN_NAME), \i18n("Average price w/o tax", PLUGIN_NAME), \i18n("Average price", PLUGIN_NAME)); $report = new \Pasteque\Report(PLUGIN_NAME, "place_sales_report", \i18n("Place sales", PLUGIN_NAME), $sql, $headers, $fields); $report->addInput("start", \i18n("Session.openDate"), \Pasteque\DB::DATE); $report->setDefaultInput("start", time() - time() % 86400 - 7 * 86400); $report->addInput("stop", \i18n("Session.closeDate"), \Pasteque\DB::DATE); $report->setDefaultinput("stop", time() - time() % 86400 + 86400); $report->addFilter("DATESTART", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATESTART", "\\i18nDatetime"); $report->addFilter("DATEEND", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATEEND", "\\i18nDatetime"); $report->setVisualFilter("AVGSUBPRICE", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("AVGSUBPRICE", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->setVisualFilter("AVGPRICE", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("AVGPRICE", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->setVisualFilter("TABLES", "\\i18nInt", \Pasteque\Report::DISP_CSV | \Pasteque\Report::DISP_USER); $report->addTotal("TABLES", \Pasteque\Report::TOTAL_SUM); // sum of count / sum of tables $report->addTotal("CUSTCOUNT", \Pasteque\Report::TOTAL_SUM); $report->addTotal("CUSTCOUNT", \Pasteque\Report::TOTAL_AVG); $report->addPonderate("CUSTCOUNT", "TABLES"); // COUNT = SUM(TABLE * CUSTCOUNT) $report->addTotal("AVGSUBPRICE", \Pasteque\Report::TOTAL_AVG); $report->addTotal("AVGPRICE", \Pasteque\Report::TOTAL_AVG); \Pasteque\register_report($report);
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the // GNU General Public License for more details. // // You should have received a copy of the GNU General Public License // along with Pastèque. If not, see <http://www.gnu.org/licenses/>. namespace BaseSales; $sql = "SELECT " . "CASHREGISTERS.NAME AS CASHREGISTER, " . "TAXES.NAME, SUM(TAXLINES.BASE) AS BASE, " . "SUM(TAXLINES.AMOUNT) AS AMOUNT, " . "DATESTART, DATEEND " . "FROM RECEIPTS " . "LEFT JOIN CLOSEDCASH ON RECEIPTS.MONEY = CLOSEDCASH.MONEY " . "LEFT JOIN TAXLINES ON TAXLINES.RECEIPT = RECEIPTS.ID " . "LEFT JOIN TAXES ON TAXLINES.TAXID = TAXES.ID " . "LEFT JOIN CASHREGISTERS ON CLOSEDCASH.CASHREGISTER_ID = CASHREGISTERS.ID " . "WHERE CLOSEDCASH.DATESTART > :start AND CLOSEDCASH.DATEEND < :stop " . "GROUP BY CLOSEDCASH.MONEY, CASHREGISTERS.NAME, TAXES.NAME " . "ORDER BY CLOSEDCASH.DATESTART ASC, TAXES.NAME ASC"; $fields = array("CASHREGISTER", "DATESTART", "DATEEND", "NAME", "BASE", "AMOUNT"); $headers = array(\i18n("CashRegister.label"), \i18n("Session.openDate"), \i18n("Session.closeDate"), \i18n("Tax name", PLUGIN_NAME), \i18n("Tax base", PLUGIN_NAME), \i18n("Tax amount", PLUGIN_NAME)); $report = new \Pasteque\Report(PLUGIN_NAME, "taxes_report", \i18n("Taxes report", PLUGIN_NAME), $sql, $headers, $fields); $report->addInput("start", \i18n("Session.openDate"), \Pasteque\DB::DATE); $report->setDefaultInput("start", time() - time() % 86400 - 30 * 86400); $report->addInput("stop", \i18n("Session.closeDate"), \Pasteque\DB::DATE); $report->setDefaultinput("stop", time() - time() % 86400 + 86400); $report->setGrouping("CASHREGISTER"); $report->addSubtotal("BASE", \Pasteque\Report::TOTAL_SUM); $report->addTotal("BASE", \Pasteque\Report::TOTAL_SUM); $report->addSubtotal("AMOUNT", \Pasteque\Report::TOTAL_SUM); $report->addTotal("AMOUNT", \Pasteque\Report::TOTAL_SUM); $report->addFilter("DATESTART", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATESTART", "\\i18nDatetime"); $report->addFilter("DATEEND", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATEEND", "\\i18nDatetime"); $report->setVisualFilter("BASE", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("BASE", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->setVisualFilter("AMOUNT", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("AMOUNT", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->SetVisualFilter("BASE", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->SetVisualFilter("BASE", "\\i18nFlt", \Pasteque\Report::DISP_CSV); \Pasteque\register_report($report);
// it under the terms of the GNU General Public License as published by // the Free Software Foundation, either version 3 of the License, or // (at your option) any later version. // // Pastèque is distributed in the hope that it will be useful, // but WITHOUT ANY WARRANTY; without even the implied warranty of // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the // GNU General Public License for more details. // // You should have received a copy of the GNU General Public License // along with Pastèque. If not, see <http://www.gnu.org/licenses/>. namespace BaseCustomers; $sql = "SELECT RECEIPTS.DATENEW, TICKETS.TICKETID, " . "PRODUCTS.NAME AS PNAME, TICKETS.TICKETTYPE, " . "SUM(TICKETLINES.UNITS) AS UNITS, " . "ROUND(SUM(TICKETLINES.UNITS * TICKETLINES.PRICE*(1-TICKETLINES.DISCOUNTRATE)),2) AS SUBTOTAL, " . "ROUND(SUM(TICKETLINES.UNITS * TICKETLINES.PRICE)*(1+TAXES.RATE)*(1-TICKETLINES.DISCOUNTRATE),2) AS TOTAL, " . "ROUND(SUM(TICKETLINES.UNITS * TICKETLINES.PRICE)*(TAXES.RATE)*(1-TICKETLINES.DISCOUNTRATE),2) AS TAXESTOTAL, " . "TAXCATEGORIES.NAME AS TAXNAME, " . "PAYMENTS.PAYMENT AS MODE " . "FROM TICKETLINES " . "LEFT JOIN TICKETS ON TICKETLINES.TICKET = TICKETS.ID " . "LEFT JOIN RECEIPTS ON TICKETS.ID = RECEIPTS.ID " . "LEFT JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID " . "LEFT JOIN PAYMENTS ON TICKETS.ID = PAYMENTS.RECEIPT " . "LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID " . "LEFT JOIN TAXCATEGORIES ON TICKETLINES.TAXID = TAXCATEGORIES.ID " . "LEFT JOIN TAXES ON TAXCATEGORIES.ID = TAXES.CATEGORY " . "WHERE (PAYMENTS.PAYMENT = 'prepaid' OR PRODUCTS.CATEGORY = '-1') " . "AND RECEIPTS.DATENEW > :start AND RECEIPTS.DATENEW < :stop " . "AND CUSTOMERS.ID = :id " . "GROUP BY RECEIPTS.DATENEW, TICKETS.TICKETID, PRODUCTS.NAME, " . "TICKETS.TICKETTYPE " . "ORDER BY RECEIPTS.DATENEW, PRODUCTS.CATEGORY"; $fields = array("PNAME", "DATENEW", "TICKETID", "MODE", "UNITS", "SUBTOTAL", "TAXNAME", "TAXESTOTAL", "TOTAL"); $headers = array(\i18n("Product.label"), \i18n("Date"), \i18n("Ticket number"), \i18n("Mode"), \i18n("Quantity"), \i18n("Subtotal"), \i18n("TaxRate"), \i18n("TaxTotal"), \i18n("Total")); $report = new \Pasteque\Report(PLUGIN_NAME, "customers_prepaid_diary", \i18n("Customer's prepaid diary", PLUGIN_NAME), $sql, $headers, $fields); $report->addInput("start", \i18n("Session.openDate"), \Pasteque\DB::DATE); $report->setDefaultInput("start", time() - 604800); $report->addInput("stop", \i18n("Session.closeDate"), \Pasteque\DB::DATE); $report->setDefaultInput("stop", time()); $report->addInput("id", "", "hidden"); $report->addFilter("DATENEW", "\\Pasteque\\stdtimefstr"); $report->addFilter("DATENEW", "\\i18nDatetime"); $report->setVisualFilter("SUBTOTAL", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("SUBTOTAL", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->setVisualFilter("TAXESTOTAL", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("TAXESTOTAL", "\\i18nFlt", \Pasteque\Report::DISP_CSV); $report->setVisualFilter("TOTAL", "\\i18nCurr", \Pasteque\Report::DISP_USER); $report->setVisualFilter("TOTAL", "\\i18nFlt", \Pasteque\Report::DISP_CSV); \Pasteque\register_report($report);