<?php /*

 Composr
 Copyright (c) Christopher Graham, 2004-2024

 See docs/LICENSE.md for full licensing information.


 NOTE TO PROGRAMMERS:
   Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
   **** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****

*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  Christopher Graham
 * @package    ecommerce
 */

require_code('crud_module');

/**
 * Module page class.
 */
class Module_admin_ecommerce_reports
{
    /**
     * Find details of the module.
     *
     * @return ?array Map of module info (null: module is disabled)
     */
    public function info() : ?array
    {
        $info = [];
        $info['author'] = 'Chris Graham';
        $info['organisation'] = 'Composr';
        $info['hacked_by'] = null;
        $info['hack_version'] = null;
        $info['version'] = 1;
        $info['locked'] = false;
        $info['min_cms_version'] = 11.0;
        $info['addon'] = 'ecommerce';
        return $info;
    }

    /**
     * Find entry-points available within this module.
     *
     * @param  boolean $check_perms Whether to check permissions
     * @param  ?MEMBER $member_id The member to check permissions as (null: current user)
     * @param  boolean $support_crosslinks Whether to allow cross links to other modules (identifiable via a full-page-link rather than a screen-name)
     * @param  boolean $be_deferential Whether to avoid any entry-point (or even return null to disable the page in the Sitemap) if we know another module, or page_group, is going to link to that entry-point. Note that "!" and "browse" entry points are automatically merged with container page nodes (likely called by page-groupings) as appropriate.
     * @return ?array A map of entry points (screen-name=>language-code/string or screen-name=>[language-code/string, icon-theme-image]) (null: disabled)
     */
    public function get_entry_points(bool $check_perms = true, ?int $member_id = null, bool $support_crosslinks = true, bool $be_deferential = false) : ?array
    {
        if (!addon_installed('ecommerce')) {
            return null;
        }

        if (get_value('unofficial_ecommerce') !== '1') {
            if (get_forum_type() != 'cns') {
                return null;
            }
        }

        $ret = [
            'browse' => ['ECOMMERCE', 'menu/adminzone/audit/ecommerce/ecommerce'],
            'sales' => ['ECOM_PRODUCTS_MANAGE_SALES', 'menu/adminzone/audit/ecommerce/sales'],
            'logs' => ['TRANSACTION_LOGS', 'menu/adminzone/audit/ecommerce/transactions_log'],
            'trigger' => ['MANUAL_TRANSACTION', 'menu/rich_content/ecommerce/purchase'],
            'profit_loss' => ['PROFIT_LOSS', 'menu/adminzone/audit/ecommerce/profit_loss'],
            'cash_flow' => ['CASH_FLOW', 'menu/adminzone/audit/ecommerce/cash_flow'],
            'view_subscriptions' => ['VIEW_SUBSCRIPTIONS', 'menu/adminzone/audit/ecommerce/subscriptions'],
        ];

        if ($support_crosslinks) {
            $ret['_SEARCH:admin_invoices:browse'] = ['INVOICES', 'menu/adminzone/audit/ecommerce/invoices'];
            if (addon_installed('shopping')) {
                require_lang('shopping');
                $ret['_SEARCH:admin_shopping:browse'] = ['ORDERS', 'menu/rich_content/ecommerce/orders'];
            }
        }

        return $ret;
    }

    public $title;

    /**
     * Module pre-run function. Allows us to know metadata for <head> before we start streaming output.
     *
     * @return ?Tempcode Tempcode indicating some kind of exceptional output (null: none)
     */
    public function pre_run() : ?object
    {
        $error_msg = new Tempcode();
        if (!addon_installed__messaged('ecommerce', $error_msg)) {
            return $error_msg;
        }

        $type = get_param_string('type', 'browse');

        require_code('ecommerce');
        require_css('ecommerce');

        if ($type == 'browse') {
            $this->title = get_screen_title('ECOMMERCE');
        }

        if ($type == 'sales' || $type == 'delete_sales_entry') {
            breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')]]);
            $this->title = get_screen_title('ECOM_PRODUCTS_MANAGE_SALES');
        }

        if ($type == 'logs') {
            breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')]]);
            $this->title = get_screen_title('TRANSACTION_LOGS');
        }

        if ($type == 'export_transactions') {
            $this->title = get_screen_title('EXPORT_TRANSACTIONS');
        }

        if ($type == 'receipt') {
            breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')], ['_SELF:_SELF:logs', do_lang_tempcode('TRANSACTION_LOGS')]]);
            $this->title = get_screen_title('RECEIPT');
        }

        if (($type != 'logs') && ($type != 'sales') && ($type != 'receipt')) {
            set_helper_panel_tutorial('tut_ecommerce');
            set_helper_panel_text(comcode_lang_string('DOC_ECOMMERCE'));
        }

        if ($type == 'cash_flow') {
            breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')]]);

            $this->title = get_screen_title('CASH_FLOW');
        }

        if ($type == 'profit_loss') {
            breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')]]);

            $this->title = get_screen_title('PROFIT_LOSS');
        }

        if ($type == 'trigger') {
            breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')]]);

            $this->title = get_screen_title('MANUAL_TRANSACTION');
        }

        if ($type == '_trigger') {
            breadcrumb_set_self(do_lang_tempcode('DONE'));
            $type_code = get_param_string('type_code', null);
            if ($type_code === null) {
                breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')], ['_SELF:_SELF:trigger', do_lang_tempcode('MANUAL_TRANSACTION')]]);
            } else {
                breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')], ['_SELF:_SELF:trigger', do_lang_tempcode('MANUAL_TRANSACTION')], ['_SELF:_SELF:trigger:type_code=' . $type_code, do_lang_tempcode('PRODUCT')]]);
            }

            $this->title = get_screen_title('MANUAL_TRANSACTION');
        }

        if ($type == 'view_subscriptions') {
            breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')]]);
            $this->title = get_screen_title('VIEW_SUBSCRIPTIONS');
        }

        if ($type == 'cancel_subscription') {
            breadcrumb_set_parents([['_SELF:_SELF:browse', do_lang_tempcode('ECOMMERCE')], ['_SELF:_SELF:view_subscriptions', do_lang_tempcode('VIEW_SUBSCRIPTIONS')]]);
            $this->title = get_screen_title('CANCEL_MANUAL_SUBSCRIPTION');
        }

        return null;
    }

    /**
     * Execute the module.
     *
     * @return Tempcode The result of execution
     */
    public function run() : object
    {
        require_code('ecommerce2');

        if (get_value('unofficial_ecommerce') !== '1') {
            if (get_forum_type() != 'cns') {
                warn_exit(do_lang_tempcode('NO_CNS'));
            }
        }

        if (get_forum_type() == 'cns') {
            cns_require_all_forum_stuff();
        }

        $type = get_param_string('type', 'browse');

        if ($type == 'browse') {
            return $this->browse();
        }
        if ($type == 'sales') {
            return $this->sales();
        }
        if ($type == 'delete_sales_entry') {
            return $this->delete_sales_entry();
        }
        if ($type == 'export_sales') {
            return $this->export_sales();
        }
        if ($type == 'logs') {
            return $this->logs();
        }
        if ($type == 'export_transactions') {
            return $this->export_transactions();
        }
        if ($type == 'export_subscriptions') {
            return $this->export_subscriptions();
        }
        if ($type == 'receipt') {
            return $this->receipt();
        }
        if ($type == 'cash_flow') {
            return $this->cash_flow();
        }
        if ($type == 'profit_loss') {
            return $this->profit_loss();
        }
        if ($type == 'trigger') {
            return $this->trigger();
        }
        if ($type == '_trigger') {
            return $this->_trigger();
        }
        if ($type == 'view_subscriptions') {
            return $this->view_subscriptions();
        }
        if ($type == 'cancel_subscription') {
            return $this->cancel_subscription();
        }

        return new Tempcode();
    }

    /**
     * The do-next manager for before audit management.
     *
     * @return Tempcode The UI
     */
    public function browse() : object
    {
        require_code('templates_donext');
        return do_next_manager(
            $this->title,
            new Tempcode(),
            [
                ['menu/rich_content/ecommerce/purchase', ['_SELF', ['type' => 'trigger'], '_SELF'], do_lang('MANUAL_TRANSACTION')],
                ['menu/adminzone/audit/ecommerce/sales', ['_SELF', ['type' => 'sales'], '_SELF'], do_lang('ECOM_PRODUCTS_MANAGE_SALES')],
                ['menu/adminzone/audit/ecommerce/transactions_log', ['_SELF', ['type' => 'logs'], '_SELF'], do_lang('TRANSACTION_LOGS')],
                ['menu/adminzone/audit/ecommerce/cash_flow', ['_SELF', ['type' => 'cash_flow'], '_SELF'], do_lang('CASH_FLOW')],
                ['menu/adminzone/audit/ecommerce/profit_loss', ['_SELF', ['type' => 'profit_loss'], '_SELF'], do_lang('PROFIT_LOSS')],
                ['menu/adminzone/audit/ecommerce/subscriptions', ['_SELF', ['type' => 'view_subscriptions'], '_SELF'], do_lang('VIEW_SUBSCRIPTIONS')],
                addon_installed('shopping') ? ['menu/rich_content/ecommerce/orders', ['admin_shopping', ['type' => 'browse'], get_module_zone('admin_shopping')], do_lang('shopping:ORDERS')] : null,
                ['menu/adminzone/audit/ecommerce/invoices', ['admin_invoices', ['type' => 'browse'], get_module_zone('admin_invoices')], do_lang('INVOICES')],
            ],
            do_lang('ECOMMERCE')
        );
    }

    /**
     * The UI to take details on a manually triggered transaction.
     *
     * @return Tempcode The UI
     */
    public function trigger() : object
    {
        require_code('currency');

        require_code('form_templates');
        $fields = new Tempcode();

        url_default_parameters__enable();

        $text = do_lang_tempcode('MANUAL_TRANSACTION_TEXT');

        // Choose product
        $type_code = get_param_string('type_code', null);
        if ($type_code === null) {
            $products = find_all_products();
            $list = new Tempcode();
            foreach ($products as $type_code => $details) {
                if (!is_string($type_code)) {
                    $type_code = strval($type_code);
                }

                $label = escape_html($details['item_name']);
                $label .= ' (' . escape_html($type_code);

                if ($details['price'] !== null) {
                    $shipping_cost = calculate_shipping_cost($details, $details['shipping_cost'], $details['product_weight'], $details['product_length'], $details['product_width'], $details['product_height']);

                    $currency = isset($details['currency']) ? $details['currency'] : get_option('currency');
                    $label .= ', ' . currency_convert($details['price'] + $shipping_cost, $currency, $currency, CURRENCY_DISPLAY_WITH_CURRENCY_SIMPLEST);
                }

                $label .= ')';

                $list->attach(form_input_list_entry($type_code, $type_code === get_param_string('type_code', null), protect_from_escaping($label)));
            }
            $fields->attach(form_input_huge_list(do_lang_tempcode('PRODUCT'), '', 'type_code', $list, null, true));

            $submit_name = do_lang_tempcode('CHOOSE');

            url_default_parameters__disable();

            return do_template('FORM_SCREEN', [
                '_GUID' => 'a2fe914c23e378c493f6e1dad0dc11eb',
                'TITLE' => $this->title,
                'SUBMIT_ICON' => 'buttons/proceed',
                'SUBMIT_NAME' => $submit_name,
                'FIELDS' => $fields,
                'TEXT' => $text,
                'URL' => get_self_url(),
                'GET' => true,
                'HIDDEN' => '',
            ]);
        }

        $post_url = build_url(['page' => '_SELF', 'type' => '_trigger', 'redirect' => protect_url_parameter(get_param_string('redirect', null, INPUT_FILTER_URL_INTERNAL))], '_SELF');

        $submit_name = do_lang_tempcode('MANUAL_TRANSACTION');

        list($details, $product_object) = find_product_details($type_code);

        $currency = isset($details['currency']) ? $details['currency'] : get_option('currency');

        // To work out key
        if (post_param_integer('got_purchase_key_dependencies', 0) == 0) {
            list($needed_fields, $needed_hidden, $needed_text, $js_function_calls) = get_needed_fields($type_code, true, true);

            if ($needed_fields !== null) { // Only do step if we actually have fields - create intermediary step. get_self_url ensures first product-choose step choice is propagated.
                $submit_name = do_lang_tempcode('PROCEED');

                $extra_hidden = new Tempcode();
                $extra_hidden->attach(form_input_hidden('got_purchase_key_dependencies', '1'));
                $extra_hidden->attach(form_input_hidden('csrf_token_preserve', '1'));
                $extra_hidden->attach($needed_hidden);

                if ($needed_text !== null) {
                    $text = $needed_text;
                }

                url_default_parameters__disable();

                return do_template('FORM_SCREEN', [
                    '_GUID' => '90ee397ac24dcf0b3a0176da9e9c9741',
                    'TITLE' => $this->title,
                    'SUBMIT_ICON' => 'buttons/proceed',
                    'SUBMIT_NAME' => $submit_name,
                    'FIELDS' => $needed_fields,
                    'TEXT' => $text,
                    'JS_FUNCTION_CALLS' => $js_function_calls,
                    'URL' => get_self_url(),
                    'HIDDEN' => $extra_hidden,
                ]);
            }
        }

        $hidden = new Tempcode();

        // Remaining fields, customised for product chosen
        $default_purchase_id = get_param_string('id', null);
        if ($default_purchase_id === null) {
            if (method_exists($product_object, 'process_needed_fields')) {
                list($default_purchase_id) = $product_object->process_needed_fields($type_code, true);
                $hidden->attach(form_input_hidden('purchase_id', $default_purchase_id));
            } else {
                $fields->attach(form_input_username(do_lang_tempcode('USERNAME'), do_lang('DESCRIPTION_MANUAL_PURCHASE_USERNAME'), 'purchase_id_username', $GLOBALS['FORUM_DRIVER']->get_username(get_member()), false));
            }
        } else {
            $fields->attach(form_input_codename(do_lang_tempcode('PURCHASE_ID'), do_lang('DESCRIPTION_MANUAL_PURCHASE_ID'), 'purchase_id', $default_purchase_id, false));
        }

        list($details) = find_product_details($type_code);
        if ($details['type'] == PRODUCT_SUBSCRIPTION) {
            $fields->attach(form_input_date(do_lang_tempcode('EXPIRY_DATE'), do_lang_tempcode('DESCRIPTION_CUSTOM_EXPIRY_DATE'), 'cexpiry', false, false, false, null, 100));
        }

        $fields->attach(do_template('FORM_SCREEN_FIELD_SPACER', ['_GUID' => 'f4e52dff9353fb767afbe0be9808591c', 'SECTION_HIDDEN' => ($details['price'] !== null), 'TITLE' => do_lang_tempcode('ADVANCED')]));
        $fields->attach(form_input_float(do_lang_tempcode('PRICE'), do_lang_tempcode('DESCRIPTION_MONEY_PRICE', escape_html($currency), ecommerce_get_currency_symbol()), 'price', null, ($details['price'] === null)));

        $hidden->attach(form_input_hidden('type_code', $type_code));
        $hidden->attach(form_input_hidden('csrf_token_preserve', '1'));
        $hidden->attach(build_keep_post_fields());

        url_default_parameters__disable();

        return do_template('FORM_SCREEN', [
            '_GUID' => '990d955cb14b6681685ec9e1d1448d9d',
            'TITLE' => $this->title,
            'SUBMIT_ICON' => 'menu/rich_content/ecommerce/purchase',
            'SUBMIT_NAME' => $submit_name,
            'FIELDS' => $fields,
            'TEXT' => $text,
            'URL' => $post_url,
            'HIDDEN' => $hidden,
        ]);
    }

    /**
     * The actualiser for a manually triggered transaction.
     *
     * @return Tempcode The result of execution
     */
    public function _trigger() : object
    {
        $type_code = post_param_string('type_code');

        list($details, $product_object) = find_product_details($type_code);

        $purchase_id = post_param_string('purchase_id', null);
        if ($purchase_id === null) {
            $_purchase_id = post_param_string('purchase_id_username', null, INPUT_FILTER_POST_IDENTIFIER);
            if ($_purchase_id !== null) {
                $__purchase_id = $GLOBALS['FORUM_DRIVER']->get_member_from_username($_purchase_id);
                if ($__purchase_id !== null) {
                    $purchase_id = strval($__purchase_id);
                }
            }
        }
        if ($purchase_id === null) {
            if (method_exists($product_object, 'process_needed_fields')) {
                list($purchase_id) = $product_object->process_needed_fields($type_code, true);
            } else {
                $purchase_id = strval(get_member());
            }
        }

        $memo = post_param_string('memo', '');
        $_price = post_param_string('price', '');
        $price = ($_price == '') ? null : float_unformat($_price);
        $custom_expiry = post_param_date('cexpiry');

        $currency = isset($details['currency']) ? $details['currency'] : get_option('currency');

        if ($details['type'] == PRODUCT_INVOICE) {
            $invoice_details = $GLOBALS['SITE_DB']->query_select('ecom_invoices', ['*'], ['id' => intval($purchase_id)], '', 1);
        }

        $shipping_cost = calculate_shipping_cost($details, $details['shipping_cost'], $details['product_weight'], $details['product_length'], $details['product_width'], $details['product_height']);

        if ($price === null) {
            if ($details['type'] == PRODUCT_INVOICE) {
                $price = $invoice_details[0]['i_price'];
            } elseif ($details['price'] !== null) {
                $price = $details['price'];
            } else {
                warn_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
        }

        if ($details['type'] == PRODUCT_INVOICE) {
            // Tax details are locked in in advance for an invoice
            $tax_code = $invoice_details[0]['i_tax_code'];
            $tax_derivation = ($invoice_details[0]['i_tax_derivation'] == '') ? [] : json_decode($invoice_details[0]['i_tax_derivation'], true);
            $tax = $invoice_details[0]['i_tax'];
            $tax_tracking = ($invoice_details[0]['i_tax_tracking'] == '') ? [] : json_decode($invoice_details[0]['i_tax_tracking'], true);
            $shipping_cost = 0.00;
            $shipping_tax = 0.00;
        } else {
            $tax_code = $details['tax_code'];
            list($tax_derivation, $tax, $tax_tracking, $shipping_tax) = calculate_tax_due($details, $tax_code, $price, $shipping_cost);
        }

        $status = 'Completed';
        $reason = '';
        $pending_reason = '';
        require_code('crypt');
        $txn_id = 'man-' . str_replace('-', '', get_secure_v1_guid()); // Must keep under 40 characters
        $parent_txn_id = '';

        $item_name = $details['item_name'];

        if ($details['type'] == PRODUCT_SUBSCRIPTION) {
            if (($purchase_id == '') || (post_param_string('username', '', INPUT_FILTER_POST_IDENTIFIER) != '')) {
                $member_id = get_member();
                $username = post_param_string('username', '', INPUT_FILTER_POST_IDENTIFIER);
                if ($username != '') {
                    $_member_id = $GLOBALS['FORUM_DRIVER']->get_member_from_username($username);
                    if (($_member_id !== null) && (!is_guest($_member_id))) {
                        $member_id = $_member_id;
                    }
                }

                $purchase_id = strval($GLOBALS['SITE_DB']->query_insert('ecom_subscriptions', [
                    's_type_code' => $type_code,
                    's_member_id' => $member_id,
                    's_state' => 'new',
                    's_price' => $details['price'],
                    's_tax_code' => $tax_code,
                    's_tax_derivation' => json_encode($tax_derivation, defined('JSON_PRESERVE_ZERO_FRACTION') ? JSON_PRESERVE_ZERO_FRACTION : 0),
                    's_tax' => $tax,
                    's_tax_tracking' => json_encode($tax_tracking, defined('JSON_PRESERVE_ZERO_FRACTION') ? JSON_PRESERVE_ZERO_FRACTION : 0),
                    's_currency' => $currency,
                    's_purchase_id' => $purchase_id,
                    's_time' => time(),
                    's_auto_fund_source' => '',
                    's_auto_fund_key' => '',
                    's_payment_gateway' => 'manual',
                    's_length' => $details['type_special_details']['length'],
                    's_length_units' => $details['type_special_details']['length_units'],
                ], true));
            }

            $is_subscription = true;

            $s_length = $details['type_special_details']['length'];
            $s_length_units = $details['type_special_details']['length_units']; // y-year, m-month, w-week, d-day

            if ($custom_expiry !== null) {
                $time_period_units = ['y' => 'year', 'm' => 'month', 'w' => 'week', 'd' => 'day'];
                $new_s_time = strtotime('-' . strval($s_length) . ' ' . $time_period_units[$s_length_units], $custom_expiry);
                $GLOBALS['SITE_DB']->query_update('ecom_subscriptions', ['s_time' => $new_s_time], ['id' => $purchase_id]);
            }

            $period = cms_strtolower_ascii(strval($s_length) . ' ' . $s_length_units);
        } else {
            $is_subscription = false;

            if ($purchase_id == '') {
                $member_id = get_member();
                $username = post_param_string('username', '', INPUT_FILTER_POST_IDENTIFIER);
                if ($username != '') {
                    $_member_id = $GLOBALS['FORUM_DRIVER']->get_member_from_username($username);
                    if (($_member_id !== null) && (!is_guest($_member_id))) {
                        $member_id = $_member_id;
                    }
                }

                $purchase_id = strval($member_id);
            }

            $s_length = null;
            $s_length_units = '';

            $period = '';
        }

        $invoicing_breakdown = generate_invoicing_breakdown($type_code, $item_name, $purchase_id, $price, $tax, $shipping_cost, $shipping_tax);

        $GLOBALS['SITE_DB']->query_insert('ecom_trans_expecting', [
            'id' => $txn_id,
            'e_type_code' => $type_code,
            'e_purchase_id' => $purchase_id,
            'e_item_name' => $item_name,
            'e_price' => $price,
            'e_tax_derivation' => json_encode($tax_derivation, defined('JSON_PRESERVE_ZERO_FRACTION') ? JSON_PRESERVE_ZERO_FRACTION : 0),
            'e_tax' => $tax,
            'e_tax_tracking' => json_encode($tax_tracking, defined('JSON_PRESERVE_ZERO_FRACTION') ? JSON_PRESERVE_ZERO_FRACTION : 0),
            'e_shipping' => $shipping_cost,
            'e_currency' => $currency,
            'e_price_points' => 0,
            'e_member_id' => get_member(),
            'e_session_id' => get_session_id(),
            'e_ip_address' => get_ip_address(),
            'e_time' => time(),
            'e_length' => $s_length,
            'e_length_units' => $s_length_units,
            'e_memo' => post_param_string('memo', ''),
            'e_invoicing_breakdown' => json_encode($invoicing_breakdown, defined('JSON_PRESERVE_ZERO_FRACTION') ? JSON_PRESERVE_ZERO_FRACTION : 0),
        ]);
        store_shipping_address($txn_id);

        $transaction_fee = 0.00; // The software does not charge transaction fees ;)

        handle_confirmed_transaction($txn_id, $txn_id, $type_code, $item_name, $purchase_id, $is_subscription, $status, $reason, $price, $tax, $shipping_cost, $transaction_fee, $currency, false, $parent_txn_id, $pending_reason, $memo, $period, get_member(), 'manual', false, true);

        $url = get_param_string('redirect', '', INPUT_FILTER_URL_INTERNAL);
        if ($url != '') {
            return redirect_screen($this->title, $url, do_lang_tempcode('SUCCESS'));
        }

        return inform_screen($this->title, do_lang_tempcode('SUCCESS'));
    }

    /**
     * The UI to view sales.
     *
     * @return Tempcode The UI
     */
    public function sales() : object
    {
        require_code('ecommerce_reports');
        require_code('form_templates');
        require_code('templates_tooltip');

        // Get filter parameters
        $filter_username = get_param_string('filter_username', '', INPUT_FILTER_NONE);
        $filter_txn_id = get_param_string('filter_txn_id', '');
        $_filter_type_code = get_param_string('filter_type_code', '');
        $filter_type_code = explode(',', $_filter_type_code);
        $filter_start = post_param_date('filter_start', true);
        $filter_end = post_param_date('filter_end', true);

        $filters = [];
        if ($filter_username != '') {
            $member_id = $GLOBALS['FORUM_DRIVER']->get_member_from_username($filter_username);
            if ($member_id !== null) {
                $filters['member_id'] = $member_id;
            } else {
                warn_exit(do_lang_tempcode('_MEMBER_NO_EXIST', escape_html($filter_username)));
            }
        }
        if ($filter_txn_id != '') {
            $filters['txn_id'] = $filter_txn_id;
        }
        if ($_filter_type_code != '') {
            $filters['type_code'] = $_filter_type_code;
        }
        if ($filter_start !== null) {
            $filters['start'] = $filter_start;
        }
        if ($filter_end !== null) {
            $filters['end'] = $filter_end;
        }

        // Build the table
        $table = build_sales_table($filters, true, true, 50, true);
        if ($table !== null) {
            list($sales_table, $pagination, $rows) = $table;
        } else {
            $sales_table = do_lang_tempcode('NO_ENTRIES');
            $pagination = new Tempcode();
            $rows = [];
        }

        // Start building fields for the filter box
        push_field_encapsulation(FIELD_ENCAPSULATION_RAW);

        // Product types
        $_products = $GLOBALS['SITE_DB']->query_select('ecom_sales s LEFT JOIN ' . get_table_prefix() . 'ecom_transactions t ON t.id=s.txn_id', ['DISTINCT t.t_type_code'], [], '');
        $products = new Tempcode();
        $__products = [];
        foreach ($_products as $p) {
            list($details, $product_object) = find_product_details($p['t_type_code']);
            if ($details !== null) {
                $item_name = $details['item_name'];
            } else {
                $item_name = $p['t_type_code'];
            }
            $__products[] = ['value' => $p['t_type_code'], 'caption' => $item_name];
        }
        sort_maps_by($__products, 'caption');
        foreach ($__products as $p) {
            $products->attach(form_input_list_entry($p['value'], (($_filter_type_code != '') && (in_array($p['value'], $filter_type_code))), $p['caption']));
        }

        $filters_row_a = [
            [
                'PARAM' => 'filter_txn_id',
                'LABEL' => do_lang_tempcode('TRANSACTION'),
                'FIELD' => form_input_line(do_lang_tempcode('TRANSACTION'), new Tempcode(), 'filter_txn_id', $filter_txn_id, false),
            ],
            [
                'PARAM' => 'filter_type_code',
                'LABEL' => do_lang_tempcode('PRODUCT'),
                'FIELD' => form_input_multi_list(do_lang_tempcode('PRODUCT'), new Tempcode(), 'filter_type_code', $products),
            ],
        ];
        $filters_row_b = [
            [
                'PARAM' => 'filter_username',
                'LABEL' => do_lang_tempcode('CUSTOMER'),
                'FIELD' => form_input_username(do_lang_tempcode('CUSTOMER'), new Tempcode(), 'filter_username', $filter_username, false),
            ],
            [
                'PARAM' => 'filter_start',
                'LABEL' => do_lang_tempcode('ST_START_PERIOD'),
                'FIELD' => form_input_date(do_lang_tempcode('ST_START_PERIOD'), do_lang_tempcode('ST_START_PERIOD_DESCRIPTION'), 'filter_start', false, ($filter_start === null), true, $filter_start),
            ],
            [
                'PARAM' => 'filter_end',
                'LABEL' => do_lang_tempcode('ST_END_PERIOD'),
                'FIELD' => form_input_date(do_lang_tempcode('ST_END_PERIOD'), do_lang_tempcode('ST_END_PERIOD_DESCRIPTION'), 'filter_end', false,  ($filter_end === null), true, $filter_end),
            ],
        ];

        $url = build_url(['page' => '_SELF', 'type' => 'sales'], '_SELF');

        pop_field_encapsulation();

        // Export button
        $form = new Tempcode();
        if (count($rows) > 0) {
            $export_url = build_url(['page' => '_SELF', 'type' => 'export_sales'], '_SELF', [], true);
            $form->attach(do_template('BUTTON_SCREEN', ['_GUID' => 'c56fc0abd8cf7ea763a0244f7e118e8a', 'IMMEDIATE' => false, 'URL' => $export_url, 'TITLE' => do_lang_tempcode('EXPORT'), 'IMG' => 'admin/export_spreadsheet', 'HIDDEN' => new Tempcode()]));
        }
        $sales_table->attach($form);

        return do_template('ECOM_SALES_SCREEN', [
            '_GUID' => '44039e850ea60fd7dfe3be5a29974261',
            'TITLE' => $this->title,
            'CONTENT' => $sales_table,
            'PAGINATION' => $pagination,
            'FILTERS_ROW_A' => $filters_row_a,
            'FILTERS_ROW_B' => $filters_row_b,
            'URL' => $url,
        ]);
    }

    /**
     * The actualiser to delete a purchase.
     *
     * @return Tempcode The UI
     */
    public function delete_sales_entry() : object
    {
        $this->_delete_sales_entry(get_param_integer('id'));

        // Show it worked / Refresh
        $url = build_url(['page' => '_SELF', 'type' => 'sales'], '_SELF');
        return redirect_screen($this->title, $url, do_lang_tempcode('SUCCESS'));
    }

    /**
     * Delete a sales entry (presumably as a purchase is being reversed).
     *
     * @param  integer $id The sales ID
     */
    public function _delete_sales_entry(int $id)
    {
        $GLOBALS['SITE_DB']->query_delete('ecom_sales', ['id' => $id], '', 1);
    }

    /**
     * The UI to view all eCommerce transactions ordered by date.
     *
     * @return Tempcode The UI
     */
    public function logs() : object
    {
        // Do not display any UI if there are no records
        $has_records = $GLOBALS['SITE_DB']->query_select_value_if_there('ecom_transactions', 'id', []);
        if ($has_records === null) {
            inform_exit(do_lang_tempcode('NO_ENTRIES'));
        }

        $start = get_param_integer('start', 0);
        $max = get_param_integer('max', 50);
        $sortables = ['t_time' => do_lang_tempcode('DATE'), 't_price' => do_lang_tempcode('PRICE')];
        $test = explode(' ', get_param_string('sort', 't_time DESC', INPUT_FILTER_GET_COMPLEX), 2);
        if (count($test) == 1) {
            $test[1] = 'DESC';
        }
        list($sortable, $sort_order) = $test;
        if (((cms_strtoupper_ascii($sort_order) != 'ASC') && (cms_strtoupper_ascii($sort_order) != 'DESC')) || (!array_key_exists($sortable, $sortables))) {
            log_hack_attack_and_exit('ORDERBY_HACK');
        }

        // Build WHERE query
        $where = '1=1';
        $filter_username = get_param_string('filter_username', '', INPUT_FILTER_NONE);
        $filter_txn_id = get_param_string('filter_txn_id', '');
        $filter_purchase_id = get_param_string('filter_purchase_id', '');
        $_filter_status = get_param_string('filter_status', '');
        $filter_status = explode(',', $_filter_status);
        $_filter_type_code = get_param_string('filter_type_code', '');
        $filter_type_code = explode(',', $_filter_type_code);
        $filter_start = post_param_date('filter_start', true);
        $filter_end = post_param_date('filter_end', true);
        if ($filter_username != '') {
            $member_id = $GLOBALS['FORUM_DRIVER']->get_member_from_username($filter_username);
            if ($member_id !== null) {
                $where .= ' AND t_member_id=' . strval($member_id);
            } else {
                warn_exit(do_lang_tempcode('_MEMBER_NO_EXIST', escape_html($filter_username)));
            }
        }
        if ($filter_txn_id != '') {
            $where .= ' AND (id LIKE \'' .  db_encode_like('%' . $filter_txn_id . '%') . '\' OR t_parent_txn_id LIKE \'' . db_encode_like('%' . $filter_txn_id . '%') . '\')';
        }
        if ($filter_purchase_id != '') {
            $where .= ' AND ' . db_string_equal_to('t_purchase_id', $filter_purchase_id);
        }
        if ($_filter_status != '') {
            $where .= ' AND (';
            foreach ($filter_status as $key => $status) {
                if ($key > 0) {
                    $where .= ' OR ';
                }
                $where .= db_string_equal_to('t_status', $status);
            }
            $where .= ')';
        }
        if ($_filter_type_code != '') {
            $where .= ' AND (';
            foreach ($filter_type_code as $key => $product) {
                if ($key > 0) {
                    $where .= ' OR ';
                }
                $where .= db_string_equal_to('t_type_code', $product);
            }
            $where .= ')';
        }
        if ($filter_start !== null) {
            $where .= ' AND t_time>=' . strval($filter_start);
        }
        if ($filter_end !== null) {
            $where .= ' AND t_time<=' . strval($filter_end);
        }

        $max_rows = $GLOBALS['SITE_DB']->query_value_if_there('SELECT COUNT(*) FROM ' . get_table_prefix() . 'ecom_transactions WHERE ' . $where);
        $rows = $GLOBALS['SITE_DB']->query('SELECT * FROM ' . get_table_prefix() . 'ecom_transactions WHERE ' . $where . ' ORDER BY ' . $sortable . ' ' . $sort_order, $max, $start);

        require_code('form_templates');
        require_code('templates_results_table');
        require_code('templates_tooltip');
        require_code('templates_map_table');

        $result_entries = new Tempcode();
        $header_row = results_header_row([
            do_lang('DATE'),
            do_lang('TRANSACTION'),
            do_lang('CUSTOMER'),
            do_lang('RELATED_MEMBER'),
            do_lang('PRODUCT'),
            do_lang('PURCHASE_ID'),
            do_lang('PRICE'),
            do_lang(get_option('tax_system')),
            do_lang('SHIPPING'),
            do_lang('TRANSACTION_FEES'),
            do_lang('STATUS'),
        ], $sortables, 'sort', $sortable . ' ' . $sort_order);
        foreach ($rows as $transaction_row) {
            $date = get_timezoned_date_time($transaction_row['t_time'], false);

            if ($transaction_row['t_status'] != 'Completed') {
                $trigger_url = build_url(['page' => '_SELF', 'type' => 'trigger', 'type_code' => $transaction_row['t_type_code'], 'id' => $transaction_row['t_purchase_id']], '_SELF');
                $status = do_template('ECOM_TRANSACTION_LOGS_MANUAL_TRIGGER', [
                    '_GUID' => '5e770b9b30db88032bcc56efe8e3dc23',
                    'STATUS' => get_transaction_status_string($transaction_row['t_status']),
                    'TRIGGER_URL' => $trigger_url,
                ]);
            } else {
                $status = make_string_tempcode(escape_html(get_transaction_status_string($transaction_row['t_status'])));
            }


            list($details, $product_object) = find_product_details($transaction_row['t_type_code']);
            if ($details !== null) {
                $item_name = make_string_tempcode(escape_html($details['item_name']));
                $product_url = ecom_details_url($transaction_row['t_type_code'], (in_array($details['type'], [PRODUCT_SUBSCRIPTION, PRODUCT_PURCHASE, PRODUCT_OTHER]) ? $transaction_row['id'] : $transaction_row['t_purchase_id']));
                if ($product_url !== null) {
                    $product_link = hyperlink($product_url, tooltip($item_name, $transaction_row['t_type_code'], true), false, false);
                } else {
                    $product_link = tooltip($item_name, $transaction_row['t_type_code'], true);
                }
            } else {
                $item_name = do_lang_tempcode('UNKNOWN_EM');
                $product_link = tooltip($item_name, $transaction_row['t_type_code'], true);
            }

            // Find member links, if possible
            $customer_link = $GLOBALS['FORUM_DRIVER']->member_profile_hyperlink($transaction_row['t_member_id'], '', false);
            $member_id = null;
            if ($product_object !== null) {
                $member_id = method_exists($product_object, 'member_for') ? $product_object->member_for($transaction_row['t_type_code'], $transaction_row['t_purchase_id']) : null;
            }
            if ($member_id !== null) {
                $member_link = $GLOBALS['FORUM_DRIVER']->member_profile_hyperlink($member_id, '', false);
            } else {
                $member_link = do_lang_tempcode('NA_EM');
            }

            $receipt_url = build_url(['page' => '_SELF', 'type' => 'receipt', 'id' => $transaction_row['id'], 'wide_high' => 1], '_SELF');
            $price_linker = hyperlink($receipt_url, ecommerce_get_currency_symbol($transaction_row['t_currency']) . escape_html(float_format($transaction_row['t_price'])), true, false, do_lang('RECEIPT'));

            $tax = ecommerce_get_currency_symbol($transaction_row['t_currency']) . escape_html(float_format($transaction_row['t_tax']));

            $transaction_tooltip_map = [];
            if ($transaction_row['t_parent_txn_id'] != '') {
                $transaction_tooltip_map['PARENT_TRANSACTION'] = $transaction_row['t_parent_txn_id'];
            }
            if ($transaction_row['t_reason'] != '') {
                $transaction_tooltip_map['REASON'] = $transaction_row['t_reason'];
            }
            if ($transaction_row['t_pending_reason'] != '') {
                $transaction_tooltip_map['REASON_FOR_PENDING'] = $transaction_row['t_pending_reason'];
            }
            if ($transaction_row['t_memo'] != '') {
                $transaction_tooltip_map['NOTES'] = $transaction_row['t_memo'];
            }
            $_transaction_fields = new Tempcode();
            foreach ($transaction_tooltip_map as $key => $val) {
                if ($val != '') {
                    $_transaction_fields->attach(map_table_field(do_lang_tempcode($key), $val, true));
                }
            }
            $transaction_table = do_template('MAP_TABLE', ['_GUID' => '71ebfad2c0e8f284650496b3646fcd4d', 'FIELDS' => $_transaction_fields, 'RESPONSIVE' => true]);

            $result_entries->attach(results_entry([
                escape_html($date),
                ((count($transaction_tooltip_map) > 0) ? tooltip($transaction_row['id'], $transaction_table, true) : $transaction_row['id']),
                $customer_link,
                $member_link,
                $product_link,
                escape_html($transaction_row['t_purchase_id']),
                $price_linker,
                $tax,
                ecommerce_get_currency_symbol($transaction_row['t_currency']) . escape_html(float_format($transaction_row['t_shipping'])),
                ecommerce_get_currency_symbol($transaction_row['t_currency']) . escape_html(float_format($transaction_row['t_transaction_fee'])),
                $status,
            ], false));
        }

        $results_table = results_table(do_lang('TRANSACTION_LOGS'), $start, 'start', $max, 'max', $max_rows, $header_row, $result_entries, $sortables, $sortable, $sort_order, 'sort');

        // Export button
        $form = new Tempcode();
        if (count($rows) > 0) {
            $export_url = build_url(['page' => '_SELF', 'type' => 'export_transactions'], '_SELF', [], true);
            $form->attach(do_template('BUTTON_SCREEN', ['_GUID' => 'dffe0df601b504c08ac59de95ef3a50f', 'IMMEDIATE' => false, 'URL' => $export_url, 'TITLE' => do_lang_tempcode('EXPORT'), 'IMG' => 'admin/export_spreadsheet', 'HIDDEN' => new Tempcode()]));
        }

        // Start building fields for the filter box
        push_field_encapsulation(FIELD_ENCAPSULATION_RAW);

        // Transaction state
        $t_statuses = new Tempcode();
        $t_statuses->attach(form_input_list_entry('SCancelled', (($_filter_status != '') && in_array('SCancelled', $filter_status)), get_transaction_status_string('SCancelled')));
        $t_statuses->attach(form_input_list_entry('SModified', (($_filter_status != '') && in_array('SModified', $filter_status)), get_transaction_status_string('SModified')));
        $t_statuses->attach(form_input_list_entry('Completed', (($_filter_status != '') && in_array('Completed', $filter_status)), get_transaction_status_string('Completed')));
        $t_statuses->attach(form_input_list_entry('Pending', (($_filter_status != '') && in_array('Pending', $filter_status)), get_transaction_status_string('Pending')));

        // Product types
        $products = new Tempcode();
        $product_rows = $GLOBALS['SITE_DB']->query_select('ecom_transactions', ['DISTINCT t_type_code'], []);
        $__products = [];
        foreach ($product_rows as $p) {
            list($details, $product_object) = find_product_details($p['t_type_code']);
            if ($details !== null) {
                $item_name = $details['item_name'];
            } else {
                $item_name = $p['t_type_code'];
            }
            $__products[] = ['value' => $p['t_type_code'], 'caption' => $item_name];
        }
        sort_maps_by($__products, 'caption');
        foreach ($__products as $p) {
            $products->attach(form_input_list_entry($p['value'], (($_filter_type_code != '') && (in_array($p['value'], $filter_type_code))), $p['caption']));
        }

        $filters_row_a = [
            [
                'PARAM' => 'filter_txn_id',
                'LABEL' => do_lang_tempcode('TRANSACTION'),
                'FIELD' => form_input_line(do_lang_tempcode('TRANSACTION'), new Tempcode(), 'filter_txn_id', $filter_txn_id, false),
            ],
            [
                'PARAM' => 'filter_type_code',
                'LABEL' => do_lang_tempcode('PRODUCT'),
                'FIELD' => form_input_multi_list(do_lang_tempcode('PRODUCT'), new Tempcode(), 'filter_type_code', $products),
            ],
            [
                'PARAM' => 'filter_purchase_id',
                'LABEL' => do_lang_tempcode('PURCHASE_ID'),
                'FIELD' => form_input_line(do_lang_tempcode('PURCHASE_ID'), new Tempcode(), 'filter_purchase_id', $filter_purchase_id, false),
            ],
            [
                'PARAM' => 'filter_state',
                'LABEL' => do_lang_tempcode('STATUS'),
                'FIELD' => form_input_multi_list(do_lang_tempcode('STATUS'), new Tempcode(), 'filter_status', $t_statuses),
            ],
        ];
        $filters_row_b = [
            [
                'PARAM' => 'filter_username',
                'LABEL' => do_lang_tempcode('CUSTOMER'),
                'FIELD' => form_input_username(do_lang_tempcode('CUSTOMER'), new Tempcode(), 'filter_username', $filter_username, false),
            ],
            [
                'PARAM' => 'filter_start',
                'LABEL' => do_lang_tempcode('ST_START_PERIOD'),
                'FIELD' => form_input_date(do_lang_tempcode('ST_START_PERIOD'), do_lang_tempcode('ST_START_PERIOD_DESCRIPTION'), 'filter_start', false, ($filter_start === null), true, $filter_start),
            ],
            [
                'PARAM' => 'filter_end',
                'LABEL' => do_lang_tempcode('ST_END_PERIOD'),
                'FIELD' => form_input_date(do_lang_tempcode('ST_END_PERIOD'), do_lang_tempcode('ST_END_PERIOD_DESCRIPTION'), 'filter_end', false,  ($filter_end === null), true, $filter_end),
            ],
        ];

        $url = build_url(['page' => '_SELF', 'type' => 'logs'], '_SELF');

        $tpl = do_template('RESULTS_TABLE_SCREEN', [
            '_GUID' => '04cd636d046a8b6667f5415929a84278',
            'TITLE' => $this->title,
            'TEXT' => do_lang_tempcode('TRANSACTION_LOGS_TEXT', escape_html(static_evaluate_tempcode(build_url(['page' => '_SELF', 'type' => 'sales'], '_SELF')))),
            'RESULTS_TABLE' => $results_table,
            'FORM' => $form,
            'FILTERS_ROW_A' => $filters_row_a,
            'FILTERS_ROW_B' => $filters_row_b,
            'URL' => $url,
            'FILTERS_HIDDEN' => new Tempcode(),
        ]);

        pop_field_encapsulation();

        require_code('templates_internalise_screen');
        return internalise_own_screen($tpl);
    }

    /**
     * Actualiser to build sales spreadsheet from the selected filters.
     *
     * @return Tempcode The result of execution
     */
    public function export_sales() : object
    {
        $filter_username = get_param_string('filter_username', '', INPUT_FILTER_NONE);
        $filter_txn_id = get_param_string('filter_txn_id', '');
        $_filter_type_code = get_param_string('filter_type_code', '');
        $filter_start = post_param_date('filter_start', true);
        $filter_end = post_param_date('filter_end', true);

        require_code('tasks');
        return call_user_func_array__long_task(do_lang('EXPORT_TRANSACTIONS'), $this->title, 'export_ecom_sales', [$filter_username, $filter_txn_id, $_filter_type_code, $filter_start, $filter_end]);
    }

    /**
     * Actualiser to build logs spreadsheet from the selected filters.
     *
     * @return Tempcode The result of execution
     */
    public function export_transactions() : object
    {
        $filter_username = get_param_string('filter_username', '', INPUT_FILTER_NONE);
        $filter_txn_id = get_param_string('filter_txn_id', '');
        $filter_purchase_id = get_param_string('filter_purchase_id', '');
        $_filter_status = get_param_string('filter_status', '');
        $_filter_type_code = get_param_string('filter_type_code', '');
        $filter_start = post_param_date('filter_start', true);
        $filter_end = post_param_date('filter_end', true);

        require_code('tasks');
        return call_user_func_array__long_task(do_lang('EXPORT_TRANSACTIONS'), $this->title, 'export_ecom_transactions', [$filter_username, $filter_txn_id, $filter_purchase_id, $_filter_status, $_filter_type_code, $filter_start, $filter_end]);
    }

    /**
     * Export a spreadsheet showing all subscriptions.
     *
     * @return Tempcode The result of execution
     */
    public function export_subscriptions() : object
    {
        // Filter parameters
        $filter_manual = get_param_integer('filter_manual', 1);
        $filter_username = get_param_string('filter_username', '', INPUT_FILTER_NONE);
        $_filter_state = get_param_string('filter_state', '');
        $_filter_type_code = get_param_string('filter_type_code', '');

        require_code('tasks');
        return call_user_func_array__long_task(do_lang('EXPORT_SUBSCRIPTIONS'), $this->title, 'export_ecom_subscriptions', [$filter_manual, $filter_username, $_filter_state, $_filter_type_code]);
    }

    /**
     * Show a receipt for a transaction.
     *
     * @return Tempcode The result of execution
     */
    public function receipt() : object
    {
        $GLOBALS['SCREEN_TEMPLATE_CALLED'] = '';

        $txn_id = get_param_string('id');
        $receipt = display_receipt($txn_id);
        return $receipt;
    }

    /**
     * An interface for choosing between dates.
     *
     * @param  Tempcode $title The title to display
     * @return Tempcode The result of execution
     */
    public function _get_between(object $title) : object
    {
        require_code('form_templates');

        $fields = new Tempcode();
        $month_start = [0, 0, intval(date('m')), 1, intval(date('Y'))];
        $fields->attach(form_input_date(do_lang_tempcode('FROM'), '', 'from', true, false, false, $month_start, -10));
        $fields->attach(form_input_date(do_lang_tempcode('TO'), '', 'to', true, false, false, time(), -10));

        return do_template('FORM_SCREEN', [
            '_GUID' => '92888622a3ed6b7edbd4d1e5e2b35986',
            'GET' => true,
            'SKIP_WEBSTANDARDS' => true,
            'TITLE' => $title,
            'FIELDS' => $fields,
            'TEXT' => '',
            'HIDDEN' => '',
            'URL' => get_self_url(false, false, [], false, true),
            'SUBMIT_ICON' => 'buttons/proceed',
            'SUBMIT_NAME' => do_lang_tempcode('PROCEED'),
        ]);
    }

    /**
     * Get transaction summaries.
     *
     * @param  TIME $from Start of time range
     * @param  TIME $to End of time range
     * @param  boolean $unpaid_invoices_count Whether to count unpaid invoices into this. This means any invoicing in transactions will be ignored, and instead invoicing will be read directly.
     * @return array A template-ready list of maps of summary for multiple transaction types
     */
    public function get_types(int $from, int $to, bool $unpaid_invoices_count = false) : array
    {
        require_code('currency');

        $types = [
            // Calculations
            'OPENING' => ['TYPE' => do_lang_tempcode('OPENING_BALANCE'), 'AMOUNT' => 0.00, 'SPECIAL' => true],
        ];
        $types_first = [
            // Ones that are always positive
            'INTEREST_PLUS' => ['TYPE' => do_lang_tempcode('M_INTEREST_PLUS'), 'AMOUNT' => 0.00, 'SPECIAL' => false],
            'OTHER_PLUS' => ['TYPE' => do_lang_tempcode('M_OTHER_PLUS'), 'AMOUNT' => 0.00, 'SPECIAL' => false],
        ];
        $types += $types_first;
        $types_last = [];
        $products = find_all_products();
        $skip = ['OTHER', 'INTEREST']; // These are actually switched to separate positive/negative accounts
        $go_last = ['WAGE', 'TAX_GENERAL']; // Ones that are negative (user expected to put in negative values)
        foreach ($products as $type_code => $details) {
            $item_name = $details['item_name'];

            if (is_integer($type_code)) {
                $type_code = strval($type_code);
            }

            $this->amend_type_code($type_code, $item_name);

            if ((!isset($types[$type_code])) && (!in_array($type_code, $skip))) {
                $account = ['TYPE' => $item_name, 'AMOUNT' => 0.00, 'SPECIAL' => false];
                if (in_array($type_code, $go_last)) {
                    $types_last[$type_code] = $account;
                } else {
                    $types[$type_code] = $account;
                }
            }
        }
        $types_last += [
            // Inversions of positive ones
            'OTHER_MINUS' => ['TYPE' => do_lang_tempcode('M_OTHER_MINUS'), 'AMOUNT' => 0.00, 'SPECIAL' => false],
            'INTEREST_MINUS' => ['TYPE' => do_lang_tempcode('M_INTEREST_MINUS'), 'AMOUNT' => 0.00, 'SPECIAL' => false],

            // Derived
            'FEES' => ['TYPE' => do_lang_tempcode('TRANSACTION_FEES'), 'AMOUNT' => 0.00, 'SPECIAL' => false],
            'TAX_SALES' => ['TYPE' => do_lang_tempcode(get_option('tax_system')), 'AMOUNT' => 0.00, 'SPECIAL' => false], // This is kind of funky, as it doesn't offset against anything and should be accounted for separately. We do not actually display this in practice, we just gather it as useful data.
        ];
        $types += $types_last;
        $types += [
            // Calculations
            'CLOSING' => ['TYPE' => do_lang_tempcode('CLOSING_BALANCE'), 'AMOUNT' => 0.00, 'SPECIAL' => true],
            'PROFIT' => ['TYPE' => do_lang_tempcode('NET_PROFIT'), 'AMOUNT' => 0.00, 'SPECIAL' => true],
        ];

        require_code('currency');

        $sql = 'SELECT * FROM ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'ecom_transactions WHERE t_time<' . strval($to) . ' AND ' . db_string_equal_to('t_status', 'Completed') . ' ORDER BY t_time';
        $transactions = $GLOBALS['SITE_DB']->query($sql);
        foreach ($transactions as $transaction) {
            $normalised_price = currency_convert($transaction['t_price'], $transaction['t_currency'], get_option('currency')); // FUDGE: Not ideal because exchange rates change, but we don't normally trade multiple currencies anyway
            $normalised_transaction_fee = currency_convert($transaction['t_transaction_fee'], $transaction['t_currency'], get_option('currency')); // FUDGE: Not ideal because exchange rates change, but we don't normally trade multiple currencies anyway
            $normalised_tax = currency_convert($transaction['t_tax'], $transaction['t_currency'], get_option('currency')); // FUDGE: Not ideal because exchange rates change, but we don't normally trade multiple currencies anyway

            // Put figures into opening/closing amounts...
            $types['CLOSING']['AMOUNT'] += $normalised_price - $normalised_transaction_fee;
            if ($transaction['t_time'] < $from) {
                $types['OPENING']['AMOUNT'] += $normalised_price - $normalised_transaction_fee;
                continue;
            }

            // It's in the time window, store under correct account...

            $types['FEES']['AMOUNT'] -= $normalised_transaction_fee;

            $types['TAX_SALES']['AMOUNT'] -= $normalised_tax; // Funky, see prior comment for TAX_SALES

            $type_code = $transaction['t_type_code'];
            $this->amend_type_code($type_code, $item_name);

            if (($transaction['t_type_code'] == 'OTHER') && ($normalised_price < 0.00)) {
                $types[$type_code][($normalised_price < 0.0) ? 'OTHER_MINUS' : 'OTHER_PLUS']['AMOUNT'] += $normalised_price;
            } elseif ($transaction['t_type_code'] == 'INTEREST') {
                $types[$type_code][($normalised_price < 0.0) ? 'INTEREST_MINUS' : 'INTEREST_PLUS']['AMOUNT'] += $normalised_price;
            } else {
                if (!array_key_exists($type_code, $types)) {
                    $types[$type_code] = ['TYPE' => $type_code, 'AMOUNT' => 0.00, 'SPECIAL' => false]; // In case product no longer exists
                }
                $types[$type_code]['AMOUNT'] += $normalised_price;
            }
        }

        if ($unpaid_invoices_count) {
            $invoices = $GLOBALS['SITE_DB']->query('SELECT * FROM ' . $GLOBALS['SITE_DB']->get_table_prefix() . 'ecom_invoices WHERE ' . db_string_equal_to('i_state', 'new') . ' AND i_time<' . strval($to) . ' ORDER BY i_time');
            foreach ($invoices as $invoice) {
                $type_code = $invoice['i_type_code'];

                //$types['FEES']['AMOUNT'] -= blah;   We cannot know the fee ahead of time so cannot account for it

                $types['TAX_SALES']['AMOUNT'] -= $invoice['i_tax']; // Funky, see prior comment for TAX_SALES

                $types['CLOSING']['AMOUNT'] += $invoice['i_price'];
                if ($invoice['i_time'] < $from) {
                    $types['OPENING']['AMOUNT'] += $invoice['i_price'];
                    continue;
                }

                // It's in the time window, store under correct account...

                $types[$type_code]['AMOUNT'] += $invoice['i_price'];
            }
        }

        foreach ($products as $type_code => $details) {
            if (is_integer($type_code)) {
                $type_code = strval($type_code);
            }

            $this->amend_type_code($type_code, $item_name);

            if ((!array_key_exists($type_code, $types_first)) && (!array_key_exists($type_code, $types_last)) && (isset($types[$type_code])) && ($types[$type_code]['AMOUNT'] == 0.0)) {
                unset($types[$type_code]);
            }
        }

        // Work out profit...

        $types['PROFIT']['AMOUNT'] = $types['CLOSING']['AMOUNT'] - $types['OPENING']['AMOUNT'] - $types['TAX_GENERAL']['AMOUNT']/*before corporation tax, so we add this back in (it's a negative figure)*/;

        // Make sure CLOSING and PROFIT go last...

        $temp = $types['CLOSING'];
        unset($types['CLOSING']);
        $types['CLOSING'] = $temp;

        $temp = $types['PROFIT'];
        unset($types['PROFIT']);
        $types['PROFIT'] = $temp;

        // Type conversion...

        foreach ($types as $type_code => $details) {
            $types[$type_code]['AMOUNT'] = float_format($types[$type_code]['AMOUNT']);
        }

        return $types;
    }

    /**
     * Alter type codes (e.g. merging) to make things more reasonable for charts.
     *
     * @param  string $type_code Type code
     * @param  string $item_name Item name
     */
    protected function amend_type_code(string &$type_code, string &$item_name)
    {
        if (addon_installed('shopping')) {
            if (preg_match('#^CART_ORDER_\d+$#', $type_code) != 0) {
                $type_code = 'CART_ORDER';
                $item_name = do_lang('shopping:ORDERS');
            }
        }
    }

    /**
     * Show a cash flow diagram.
     *
     * @return Tempcode The result of execution
     */
    public function cash_flow() : object
    {
        $d = [post_param_date('from', true), post_param_date('to', true)];
        if ($d[0] === null) {
            return $this->_get_between($this->title);
        }
        list($from, $to) = $d;

        $types = $this->get_types($from, $to);
        unset($types['PROFIT']);
        unset($types['TAX_SALES']); // Not considered an income or a cost, as should be accounted for separately

        return do_template('ECOM_CASH_FLOW_SCREEN', ['_GUID' => 'a042e16418417f46c24818890679f38a', 'TITLE' => $this->title, 'TYPES' => $types]);
    }

    /**
     * Show a profit/loss account.
     *
     * @return Tempcode The result of execution
     */
    public function profit_loss() : object
    {
        $d = [post_param_date('from', true), post_param_date('to', true)];
        if ($d[0] === null) {
            return $this->_get_between($this->title);
        }
        list($from, $to) = $d;

        $types = $this->get_types($from, $to, true);
        unset($types['OPENING']);
        unset($types['CLOSING']);
        unset($types['TAX_SALES']); // Not considered an income or a cost, as should be accounted for separately

        return do_template('ECOM_CASH_FLOW_SCREEN', ['_GUID' => '255681ec95e90e36e085d14cf984b725', 'TITLE' => $this->title, 'TYPES' => $types]);
    }

    /**
     * Show subscriptions with the ability to filter or export them.
     *
     * @return Tempcode The result of execution
     */
    public function view_subscriptions() : object
    {
        // Do not display any UI if there are no records
        $has_records = $GLOBALS['SITE_DB']->query_select_value_if_there('ecom_subscriptions', 'id', []);
        if (!$has_records) {
            inform_exit(do_lang_tempcode('NO_ENTRIES'));
        }

        disable_php_memory_limit();

        // Pagination
        $start = get_param_integer('start', 0);
        $max = get_param_integer('max', 50);

        // Sorting
        $sortables = ['s_type_code' => do_lang('PRODUCT'), 's_state' => do_lang('STATUS'), 's_time' => do_lang('DATE_TIME')];
        $test = explode(' ', get_param_string('sort', 's_type_code ASC', INPUT_FILTER_GET_COMPLEX), 2);
        if (count($test) == 1) {
            $test[1] = 'DESC';
        }
        list($sortable, $sort_order) = $test;
        if (((cms_strtoupper_ascii($sort_order) != 'ASC') && (cms_strtoupper_ascii($sort_order) != 'DESC')) || (!array_key_exists($sortable, $sortables))) {
            log_hack_attack_and_exit('ORDERBY_HACK');
        }

        // Filter parameters
        $filter_manual = get_param_integer('filter_manual', 0);
        $filter_username = get_param_string('filter_username', '', INPUT_FILTER_NONE);
        $_filter_state = get_param_string('filter_state', '');
        $filter_state = explode(',', $_filter_state);
        $_filter_type_code = get_param_string('filter_type_code', '');
        $filter_type_code = explode(',', $_filter_type_code);

        // Build WHERE query
        $where = [];
        $end = '';
        if ($filter_manual == 1) {
            $where['s_payment_gateway'] = 'manual';
        }
        if ($filter_username != '') {
            $member_id = $GLOBALS['FORUM_DRIVER']->get_member_from_username($filter_username);
            if ($member_id !== null) {
                $where['s_member_id'] = $member_id;
            } else {
                warn_exit(do_lang_tempcode('_MEMBER_NO_EXIST', escape_html($filter_username)));
            }
        }
        if ($_filter_state != '') {
            $end .= ' AND (';
            foreach ($filter_state as $key => $state) {
                if ($key > 0) {
                    $end .= ' OR ';
                }
                $end .= db_string_equal_to('s_state', $state);
            }
            $end .= ')';
        }
        if ($_filter_type_code != '') {
            $end .= ' AND (';
            foreach ($filter_type_code as $key => $product) {
                if ($key > 0) {
                    $end .= ' OR ';
                }
                $end .= db_string_equal_to('s_type_code', $product);
            }
            $end .= ')';
        }

        // Construct the table
        $max_rows = $GLOBALS['SITE_DB']->query_select_value('ecom_subscriptions', 'COUNT(*)', $where, $end);

        $end .= ' ORDER BY ' . $sortable . ' ' . $sort_order . ', s_time DESC'; // Use s_time as secondary ordering
        $subscriptions = $GLOBALS['SITE_DB']->query_select('ecom_subscriptions', ['*'], $where, $end, $max, $start);

        require_code('form_templates');
        require_code('templates_results_table');
        require_code('templates_tooltip');

        $map = [do_lang('IDENTIFIER'), do_lang('DATE_TIME'), do_lang('MEMBER'), do_lang('PRODUCT'), do_lang('EXPIRY_DATE'), do_lang('ACTIONS')];
        $header_row = results_header_row($map, $sortables, 'sort', $sortable . ' ' . $sort_order);

        $result = new Tempcode();
        foreach ($subscriptions as $subs) {
            list($details) = find_product_details($subs['s_type_code']);

            if ($details !== null) {
                $item_name = $details['item_name'];
                $s_length = $details['type_special_details']['length'];
                $s_length_units = $details['type_special_details']['length_units']; // y-year, m-month, w-week, d-day
                $time_period_units = ['y' => 'year', 'm' => 'month', 'w' => 'week', 'd' => 'day'];
                $expiry_time = strtotime('+' . strval($s_length) . ' ' . $time_period_units[$s_length_units], $subs['s_time']);
                $expiry_date = make_string_tempcode(get_timezoned_date_time($expiry_time, false));
            } else {
                $item_name = do_lang_tempcode('UNKNOWN_EM');
                $expiry_date = do_lang_tempcode('UNKNOWN_EM');
            }
            if (($subs['s_state'] != 'cancelled') && (ecommerce_test_mode() || ($subs['s_payment_gateway'] == 'manual'))) {
                $cancel_url = hyperlink(build_url(['page' => '_SELF', 'type' => 'cancel_subscription', 'subscription_id' => $subs['id']], '_SELF'), do_lang('SUBSCRIPTION_CANCEL'), false, true);
            } else {
                $cancel_url = new Tempcode();
            }

            $map = [
                strval($subs['id']),
                get_timezoned_date_time($subs['s_time'], false),
                results_table_member_cell($subs['s_member_id']),
                tooltip($item_name, $subs['s_type_code'], true),
                $expiry_date,
                $cancel_url
            ];
            $result->attach(results_entry($map, true));
        }

        $results_table = results_table(do_lang_tempcode('VIEW_SUBSCRIPTIONS'), $start, 'start', $max, 'max', $max_rows, $header_row, $result, $sortables, $sortable, $sort_order, 'sort');

        // Export button
        $form = new Tempcode();
        $export_url = build_url(['page' => '_SELF', 'type' => 'export_subscriptions'], '_SELF', [], true);
        $form->attach(do_template('BUTTON_SCREEN', ['_GUID' => '38f0e331de0db2449c23378f9f41080e', 'IMMEDIATE' => false, 'URL' => $export_url, 'TITLE' => do_lang_tempcode('EXPORT'), 'IMG' => 'admin/export_spreadsheet', 'HIDDEN' => new Tempcode()]));

        // Start building fields for the filter box
        push_field_encapsulation(FIELD_ENCAPSULATION_RAW);

        // Subscription state
        $s_states = new Tempcode();
        $s_states->attach(form_input_list_entry('active', (($_filter_state != '') && in_array('active', $filter_state)), do_lang_tempcode('FILTER_STATE_ACTIVE')));
        $s_states->attach(form_input_list_entry('cancelled', (($_filter_state != '') && in_array('cancelled', $filter_state)), do_lang_tempcode('FILTER_STATE_CANCELLED')));
        $s_states->attach(form_input_list_entry('new', (($_filter_state != '') && in_array('new', $filter_state)), do_lang_tempcode('FILTER_STATE_NEW')));
        $s_states->attach(form_input_list_entry('pending', (($_filter_state != '') && in_array('pending', $filter_state)), do_lang_tempcode('FILTER_STATE_PENDING')));

        // Product types
        $products = new Tempcode();
        $product_rows = $GLOBALS['SITE_DB']->query_select('ecom_subscriptions', ['DISTINCT s_type_code'], [], 'ORDER BY s_type_code');
        $__products = [];
        foreach ($product_rows as $p) {
            list($details, $product_object) = find_product_details($p['s_type_code']);
            if ($details !== null) {
                $item_name = $details['item_name'];
            } else {
                $item_name = $p['s_type_code'];
            }
            $__products[] = ['value' => $p['s_type_code'], 'caption' => $item_name];
        }
        sort_maps_by($__products, 'caption');
        foreach ($__products as $p) {
            $products->attach(form_input_list_entry($p['value'], (($_filter_type_code != '') && (in_array($p['value'], $filter_type_code))), $p['caption']));
        }

        $filters_row_a = [
            [
                'PARAM' => 'filter_manual',
                'LABEL' => do_lang_tempcode('MANUAL_SUBSCRIPTIONS'),
                'FIELD' => form_input_tick(do_lang_tempcode('MANUAL_SUBSCRIPTIONS'), new Tempcode(), 'filter_manual', ($filter_manual == 1)),
            ],
            [
                'PARAM' => 'filter_state',
                'LABEL' => do_lang_tempcode('STATUS'),
                'FIELD' => form_input_multi_list(do_lang_tempcode('STATUS'), new Tempcode(), 'filter_state', $s_states),
            ],
            [
                'PARAM' => 'filter_type_code',
                'LABEL' => do_lang_tempcode('PRODUCT'),
                'FIELD' => form_input_multi_list(do_lang_tempcode('PRODUCT'), new Tempcode(), 'filter_type_code', $products),
            ],
        ];
        $filters_row_b = [
            [
                'PARAM' => 'filter_username',
                'LABEL' => do_lang_tempcode('MEMBER'),
                'FIELD' => form_input_username(do_lang_tempcode('MEMBER'), new Tempcode(), 'filter_username', $filter_username, false),
            ],
        ];

        $url = build_url(['page' => '_SELF', 'type' => 'view_subscriptions'], '_SELF');

        $tpl = do_template('RESULTS_TABLE_SCREEN', [
            '_GUID' => '5b3d7b61edb6e369c51ecdacffbcf0e2',
            'TITLE' => $this->title,
            'RESULTS_TABLE' => $results_table,
            'FORM' => $form,
            'FILTERS_HIDDEN' => new Tempcode(),
            'FILTERS_ROW_A' => $filters_row_a,
            'FILTERS_ROW_B' => $filters_row_b,
            'URL' => $url,
        ]);

        pop_field_encapsulation();

        require_code('templates_internalise_screen');
        return internalise_own_screen($tpl);
    }

    /**
     * Cancel a manual subscription.
     *
     * @return Tempcode The result of execution
     */
    public function cancel_subscription() : object
    {
        $id = get_param_integer('subscription_id');
        $subscription = $GLOBALS['SITE_DB']->query_select('ecom_subscriptions', ['s_type_code', 's_member_id'], ['id' => $id], '', 1);
        if (!array_key_exists(0, $subscription)) {
            warn_exit(do_lang_tempcode('MISSING_RESOURCE'));
        }

        list($details) = find_product_details($subscription[0]['s_type_code']);
        $item_name = $details['item_name'];
        $username = $GLOBALS['FORUM_DRIVER']->get_username($subscription[0]['s_member_id']);

        $repost_id = post_param_integer('id', null);
        if (($repost_id !== null) && ($repost_id == $id)) {
            require_code('ecommerce');
            handle_confirmed_transaction(null, strval($id), $subscription[0]['s_type_code'], '', strval($id), true, 'SCancelled', '', 0.00, 0.00, 0.00, 0.00, get_option('currency'), false, '', '', '', '', get_member(), 'manual', false, true); // Runs a cancel
            return inform_screen($this->title, do_lang_tempcode('SUCCESS'));
        }

        // We need to get confirmation via POST, for security/confirmation reasons
        $preview = do_lang_tempcode('CANCEL_MANUAL_SUBSCRIPTION_CONFIRM', escape_html($item_name), escape_html($username));
        $fields = form_input_hidden('id', strval($id));
        $map = ['page' => '_SELF', 'type' => get_param_string('type'), 'subscription_id' => $id];
        $url = build_url($map, '_SELF');
        return do_template('CONFIRM_SCREEN', ['_GUID' => 'b0ad0d49ad65fd2778ad309aeb26a5a5', 'TITLE' => $this->title, 'FIELDS' => $fields, 'URL' => $url, 'PREVIEW' => $preview]);
    }
}
