//
// 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 AdvancedReports;
$db = \Pasteque\DB::get();
// Cash session request
$sqls[] = "SELECT " . "CASHREGISTERS.NAME, CLOSEDCASH.HOSTSEQUENCE, CLOSEDCASH.MONEY, " . "CLOSEDCASH.DATESTART, CLOSEDCASH.DATEEND, " . "CLOSEDCASH.OPENCASH, CLOSEDCASH.CLOSECASH, CLOSEDCASH.EXPECTEDCASH, " . "COUNT(DISTINCT(RECEIPTS.ID)) AS TICKETS, " . "SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) AS SALES, " . "SUM(TICKETLINES.PRICE * TICKETLINES.UNITS * (1 + TAXES.RATE)) " . "AS SALESVAT " . "FROM CLOSEDCASH " . "LEFT JOIN CASHREGISTERS ON " . "CLOSEDCASH.CASHREGISTER_ID = CASHREGISTERS.ID " . "LEFT JOIN RECEIPTS ON RECEIPTS.MONEY = CLOSEDCASH.MONEY " . "LEFT JOIN TICKETLINES ON TICKETLINES.TICKET = RECEIPTS.ID " . "LEFT JOIN TAXES ON TICKETLINES.TAXID = TAXES.ID " . "WHERE CLOSEDCASH.DATESTART > :start " . "AND CLOSEDCASH.DATESTART <= :stop " . "GROUP BY CLOSEDCASH.MONEY, CASHREGISTERS.NAME, HOSTSEQUENCE, " . "DATESTART, DATEEND, OPENCASH, CLOSECASH " . "ORDER BY CLOSEDCASH.DATESTART DESC";
// Payments request
$sqls[] = "SELECT " . "CLOSEDCASH.MONEY, PAYMENTS.PAYMENT AS __KEY__, " . "SUM(PAYMENTS.TOTAL) AS __VALUE__ " . "FROM " . "CLOSEDCASH " . "LEFT JOIN RECEIPTS ON RECEIPTS.MONEY = CLOSEDCASH.MONEY " . "LEFT JOIN PAYMENTS ON PAYMENTS.RECEIPT = RECEIPTS.ID " . "WHERE CLOSEDCASH.DATESTART > :start " . "AND CLOSEDCASH.DATESTART <= :stop " . "GROUP BY CLOSEDCASH.MONEY, __KEY__ " . "ORDER BY CLOSEDCASH.DATESTART DESC";
// Taxes request
$sqls[] = "SELECT " . "CLOSEDCASH.MONEY, TAXES.NAME as __KEY__, " . $db->concat($db->concat("SUM(TAXLINES.BASE)", "'/'"), "SUM(TAXLINES.AMOUNT)") . " AS __VALUE__ " . "FROM CLOSEDCASH " . "LEFT JOIN RECEIPTS ON RECEIPTS.MONEY = CLOSEDCASH.MONEY " . "LEFT JOIN TAXLINES ON TAXLINES.RECEIPT = RECEIPTS.ID " . "LEFT JOIN TAXES ON TAXLINES.TAXID = TAXES.ID " . "WHERE CLOSEDCASH.DATESTART > :start " . "AND CLOSEDCASH.DATESTART < :stop " . "GROUP BY CLOSEDCASH.MONEY, TAXES.NAME " . "ORDER BY CLOSEDCASH.DATESTART DESC";
$fields = array("NAME", "HOSTSEQUENCE", "DATESTART", "DATEEND", "OPENCASH", "CLOSECASH", "EXPECTEDCASH", "TICKETS", "SALES", "SALESVAT");
$mergeFields = array("MONEY");
$headers = array(\i18n("CashRegister.label"), \i18n("Session"), \i18n("Session.openDate"), \i18n("Session.closeDate"), \i18n("Session.openCash"), \i18n("Session.closeCash"), \i18n("Session.expectedCash"), \i18n("Tickets", PLUGIN_NAME), \i18n("Sales", PLUGIN_NAME), \i18n("Sales with VAT", PLUGIN_NAME));
$report = new \Pasteque\MergedReport(PLUGIN_NAME, "ztickets", \i18n("Z tickets", PLUGIN_NAME), $sqls, $headers, $fields, $mergeFields);
$report->addInput("start", \i18n("Start date"), \Pasteque\DB::DATE);
$report->setDefaultInput("start", time() - time() % 86400 - 7 * 86400);
$report->addInput("stop", \i18n("Stop date"), \Pasteque\DB::DATE);
$report->setDefaultinput("stop", time() - time() % 86400 + 86400);
function cashMatch($val, $values)
{
if ($val != $values['CLOSECASH']) {
return "<span style=\"color:#b00;\">" . \i18nCurr($val) . "</span>";
}
return $val;
}
$report->addFilter("DATESTART", "\\Pasteque\\stdtimefstr");
$report->addFilter("DATESTART", "\\i18nDatetime");
$report->addFilter("DATEEND", "\\Pasteque\\stdtimefstr");
$report->addFilter("DATEEND", "\\i18nDatetime");
// 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;
$sqls = array();
$sqls[] = "SELECT AVERAGE.NAME, AVERAGE.DATESTART, AVERAGE.DATEEND, " . "AVERAGE.TICKETS, AVERAGE.AVERAGE, " . "REALCS.TICKETAMOUNT AS REALCS, THEOCS.AMOUNT AS THEOCS, THEOCS.SUBAMOUNT AS THEOSCS " . "FROM " . "" . "(SELECT LIST.MONEY, NAME, DATESTART, DATEEND, COUNT(LIST.TICKET) AS TICKETS, AVG(LIST.TICKETAMOUNT) AS AVERAGE " . "FROM " . "(SELECT CLOSEDCASH.MONEY, NAME, DATESTART, DATEEND, " . "SUM(PAYMENTS.TOTAL) AS TICKETAMOUNT, RECEIPTS.ID AS TICKET " . "FROM PAYMENTS " . "LEFT JOIN RECEIPTS ON RECEIPTS.ID = PAYMENTS.RECEIPT " . "LEFT JOIN CLOSEDCASH ON CLOSEDCASH.MONEY = RECEIPTS.MONEY " . "LEFT JOIN CASHREGISTERS ON CLOSEDCASH.CASHREGISTER_ID = CASHREGISTERS.ID " . "GROUP BY TICKET, CLOSEDCASH.MONEY, NAME, DATESTART, DATEEND) " . "AS LIST " . "GROUP BY LIST.MONEY, LIST.NAME, LIST.DATESTART, LIST.DATEEND " . ") AS AVERAGE " . "" . "LEFT JOIN " . "(SELECT CLOSEDCASH.MONEY, NAME, DATESTART, DATEEND, SUM(PAYMENTS.TOTAL) AS TICKETAMOUNT " . "FROM PAYMENTS " . "LEFT JOIN RECEIPTS ON RECEIPTS.ID = PAYMENTS.RECEIPT " . "LEFT JOIN CLOSEDCASH ON CLOSEDCASH.MONEY = RECEIPTS.MONEY " . "LEFT JOIN CASHREGISTERS ON CLOSEDCASH.CASHREGISTER_ID = CASHREGISTERS.ID " . "WHERE PAYMENTS.PAYMENT IN ('cash', 'magcard', 'cheque', 'paperin') " . "GROUP BY CLOSEDCASH.MONEY, NAME, DATESTART, DATEEND) AS REALCS " . "ON AVERAGE.MONEY = REALCS.MONEY " . "" . "LEFT JOIN " . "(SELECT CLOSEDCASH.MONEY, CASHREGISTERS.NAME, DATESTART, DATEEND, " . "SUM(TICKETLINES.PRICE * (1 + TAXES.RATE) * TICKETLINES.UNITS * (1 - TICKETLINES.DISCOUNTRATE)) AS AMOUNT, " . "SUM(TICKETLINES.PRICE * TICKETLINES.UNITS * (1 - TICKETLINES.DISCOUNTRATE)) AS SUBAMOUNT " . "FROM TICKETLINES " . "LEFT JOIN TAXES ON TICKETLINES.TAXID = TAXES.ID " . "LEFT JOIN RECEIPTS ON TICKETLINES.TICKET = RECEIPTS.ID " . "LEFT JOIN CLOSEDCASH ON CLOSEDCASH.MONEY = RECEIPTS.MONEY " . "LEFT JOIN CASHREGISTERS ON CLOSEDCASH.CASHREGISTER_ID = CASHREGISTERS.ID " . "LEFT JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID " . "WHERE PRODUCTS.CATEGORY != '-1' " . "GROUP BY CLOSEDCASH.MONEY, CASHREGISTERS.NAME) AS THEOCS " . "ON AVERAGE.MONEY = THEOCS.MONEY " . "" . "WHERE AVERAGE.DATESTART > :start AND AVERAGE.DATEEND < :stop " . "ORDER BY NAME ASC, DATESTART ASC";
$sqls[] = "SELECT CASHREGISTERS.NAME, CLOSEDCASH.DATESTART, " . "CLOSEDCASH.DATEEND," . "TAXES.NAME as __KEY__, SUM(TAXLINES.AMOUNT) AS __VALUE__ " . "FROM CLOSEDCASH " . "LEFT JOIN CASHREGISTERS ON CLOSEDCASH.CASHREGISTER_ID = CASHREGISTERS.ID " . "LEFT JOIN RECEIPTS ON RECEIPTS.MONEY = CLOSEDCASH.MONEY " . "LEFT JOIN TICKETS ON TICKETS.ID = RECEIPTS.ID " . "LEFT JOIN TAXLINES ON TAXLINES.RECEIPT = TICKETS.ID " . "LEFT JOIN TAXES ON TAXLINES.TAXID = TAXES.ID " . "WHERE CLOSEDCASH.DATESTART > :start AND CLOSEDCASH.DATEEND < :stop " . "GROUP BY CLOSEDCASH.MONEY, CASHREGISTERS.NAME, " . "CLOSEDCASH.DATESTART, CLOSEDCASH.DATEEND, __KEY__ " . "ORDER BY CASHREGISTERS.NAME ASC, CLOSEDCASH.DATESTART ASC";
$fields = array("NAME", "DATESTART", "DATEEND", "TICKETS", "AVERAGE", "REALCS", "THEOCS", "THEOSCS");
$mergeFields = array("NAME", "DATESTART", "DATEEND");
$headers = array(\i18n("CashRegister.label"), \i18n("Session.openDate"), \i18n("Session.closeDate"), \i18n("Tickets", PLUGIN_NAME), \i18n("Average", PLUGIN_NAME), \i18n("Real CS", PLUGIN_NAME), \i18n("Theo CS", PLUGIN_NAME), \i18n("Theo SCS", PLUGIN_NAME));
$report = new \Pasteque\MergedReport(PLUGIN_NAME, "cs_report", \i18n("Consolidated sales report", PLUGIN_NAME), $sqls, $headers, $fields, $mergeFields);
$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("NAME");
$report->addSubtotal("AVERAGE", \Pasteque\Report::TOTAL_AVG);
$report->addSubtotal("REALCS", \Pasteque\Report::TOTAL_SUM);
$report->addSubtotal("THEOCS", \Pasteque\Report::TOTAL_SUM);
$report->addSubtotal("THEOSCS", \Pasteque\Report::TOTAL_SUM);
$report->addSubtotal("TICKETS", \Pasteque\Report::TOTAL_SUM);
$report->addMergedSubtotal(0, \Pasteque\Report::TOTAL_SUM);
$report->addTotal("AVERAGE", \Pasteque\Report::TOTAL_AVG);
$report->addTotal("REALCS", \Pasteque\Report::TOTAL_SUM);
$report->addTotal("THEOCS", \Pasteque\Report::TOTAL_SUM);
$report->addTotal("THEOSCS", \Pasteque\Report::TOTAL_SUM);
