opensourcepos/app/Models/Reports/Summary_report.php

<?php

namespace App\Models\Reports;

use App\Traits\Models\Reports\ReportDateFilter;
use App\Traits\Models\Reports\SaleTypeFilter;
use CodeIgniter\Database\BaseBuilder;
use Config\OSPOS;

abstract class Summary_report extends Report
{
    use ReportDateFilter;
    use SaleTypeFilter;

    private function __common_select(array $inputs, &$builder): void
    {
        $config = config(OSPOS::class)->settings;
        $where = $this->buildDateWhereClause($inputs);

        $decimals = totals_decimals();

        $sale_price = 'CASE WHEN sales_items.discount_type = ' . PERCENT
            . " THEN sales_items.quantity_purchased * sales_items.item_unit_price - ROUND(sales_items.quantity_purchased * sales_items.item_unit_price * sales_items.discount / 100, $decimals) "
            . 'ELSE sales_items.quantity_purchased * (sales_items.item_unit_price - sales_items.discount) END';

        $sale_cost = 'SUM(sales_items.item_cost_price * sales_items.quantity_purchased)';
        $sales_tax = "IFNULL(SUM(sales_items_taxes.tax), 0)";

        $cash_adjustment = 'IFNULL(SUM(payments.sale_cash_adjustment), 0)';

        if ($config['tax_included']) {
            $sale_total = "ROUND(SUM($sale_price), $decimals) + $cash_adjustment";
            $sale_subtotal = "$sale_total - $sales_tax";
        } else {
            $sale_subtotal = "ROUND(SUM($sale_price), $decimals) + $cash_adjustment";
            $sale_total = "ROUND(SUM($sale_price), $decimals) + $sales_tax + $cash_adjustment";
        }

        // Create a temporary table to contain all the sum of taxes per sale item
        $this->db->query(
            'CREATE TEMPORARY TABLE IF NOT EXISTS ' . $this->db->prefixTable('sales_items_taxes_temp') .
                ' (INDEX(sale_id), INDEX(item_id)) ENGINE=MEMORY
            (
                SELECT sales_items_taxes.sale_id AS sale_id,
                    sales_items_taxes.item_id AS item_id,
                    sales_items_taxes.line AS line,
                    SUM(ROUND(sales_items_taxes.item_tax_amount,' . $decimals . ')) AS tax
                FROM ' . $this->db->prefixTable('sales_items_taxes') . ' AS sales_items_taxes
                INNER JOIN ' . $this->db->prefixTable('sales') . ' AS sales
                    ON sales.sale_id = sales_items_taxes.sale_id
                INNER JOIN ' . $this->db->prefixTable('sales_items') . ' AS sales_items
                    ON sales_items.sale_id = sales_items_taxes.sale_id AND sales_items.line = sales_items_taxes.line
                WHERE ' . $where . '
                GROUP BY sale_id, item_id, line
            )'
        );

        $this->db->query(
            'CREATE TEMPORARY TABLE IF NOT EXISTS ' . $this->db->prefixTable('sales_payments_temp') .
                ' (PRIMARY KEY(sale_id), INDEX(sale_id))
            (
                SELECT payments.sale_id AS sale_id,
                    SUM(CASE WHEN payments.cash_adjustment = 0 THEN payments.payment_amount ELSE 0 END) AS sale_payment_amount,
                    SUM(CASE WHEN payments.cash_adjustment = 1 THEN payments.payment_amount ELSE 0 END) AS sale_cash_adjustment,
                    SUM(payments.cash_refund) AS sale_cash_refund,
                    GROUP_CONCAT(CONCAT(payments.payment_type, " ", (payments.payment_amount - payments.cash_refund)) SEPARATOR ", ") AS payment_type
                FROM ' . $this->db->prefixTable('sales_payments') . ' AS payments
                INNER JOIN ' . $this->db->prefixTable('sales') . ' AS sales
                    ON sales.sale_id = payments.sale_id
                WHERE ' . $where . '
                GROUP BY sale_id
            )'
        );

        $builder->select("
                IFNULL($sale_subtotal, $sale_total) AS subtotal,
                $sales_tax AS tax,
                IFNULL($sale_total, $sale_subtotal) AS total,
                $sale_cost AS cost,
                (IFNULL($sale_subtotal, $sale_total) - $sale_cost) AS profit
        ");
    }

    /**
     * @param BaseBuilder $builder
     * @return void
     */
    private function __common_from(BaseBuilder &$builder): void    // TODO: hungarian notation
    {
        $builder->join('sales AS sales', 'sales_items.sale_id = sales.sale_id', 'inner');
        $builder->join(
            'sales_items_taxes_temp AS sales_items_taxes',
            'sales_items.sale_id = sales_items_taxes.sale_id AND sales_items.item_id = sales_items_taxes.item_id AND sales_items.line = sales_items_taxes.line',
            'left outer'
        );
        $builder->join('sales_payments_temp AS payments', 'sales.sale_id = payments.sale_id', 'LEFT OUTER');
    }

    /**
     * @param array $inputs
     * @param $builder
     * @return void
     */
    private function __common_where(array $inputs, &$builder): void
    {
        $config = config(OSPOS::class)->settings;

        $this->applyDateFilter($builder, $inputs);

        if ($inputs['location_id'] != 'all') {
            $builder->where('sales_items.item_location', $inputs['location_id']);
        }

        $this->applySaleTypeFilter($builder, $inputs['sale_type']);
    }

    abstract protected function _get_data_columns(): array;

    /**
     * @param array $inputs
     * @param BaseBuilder $builder
     * @return void
     */
    protected function _select(array $inputs, BaseBuilder &$builder): void
    {
        $this->__common_select($inputs, $builder);
    }    // TODO: hungarian notation

    /**
     * @param BaseBuilder $builder
     * @return void
     */
    protected function _from(BaseBuilder &$builder): void
    {
        $this->__common_from($builder);
    }    // TODO: hungarian notation TODO: Do we need to pass &$builder to the __common_from()?

    /**
     * @param array $inputs
     * @param BaseBuilder $builder
     * @return void
     */
    protected function _where(array $inputs, BaseBuilder &$builder): void
    {
        $this->__common_where($inputs, $builder);
    }    // TODO: hungarian notation

    /**
     * @param BaseBuilder $builder
     * @return void
     */
    protected function _group_order(BaseBuilder &$builder): void {}    // TODO: hungarian notation

    /**
     * Public interface implementing the base abstract class,
     * in general it should not be extended unless there is a valid reason
     * like a non sale report (e.g. expenses)
     */

    public function getDataColumns(): array
    {
        return $this->_get_data_columns();
    }

    /**
     * @param array $inputs
     * @return array
     */
    public function getData(array $inputs): array
    {
        $builder = $this->db->table('sales_items AS sales_items');

        $this->_select($inputs, $builder);
        $this->_from($builder);
        $this->_where($inputs, $builder);
        $this->_group_order($builder);

        return $builder->get()->getResultArray();
    }

    /**
     * @param array $inputs
     * @return array
     */
    public function getSummaryData(array $inputs): array
    {
        $builder = $this->db->table('sales_items AS sales_items');

        $this->__common_select($inputs, $builder);
        $this->__common_from($builder);
        $this->_where($inputs, $builder);

        return $builder->get()->getRowArray();
    }
}