<?php
/**
 * -------------------------------------------------------------------------
 *
 * The database module for selecting order entries.
 *
 * -------------------------------------------------------------------------
 *
 * This module is always called using getByUrl() method implemented below.
 * The initiator of that call is run() method of the main module of your
 * application.
 *
 * The second method implemented below is getOrders() which will be called
 * from the template file "Themes/default/home.tpl" to retrieve and display
 * all the orders submitted by customers.
 *
 * -------------------------------------------------------------------------
 *
 * @package    MimimiFramework
 * @subpackage Examples / Shopping UI with Vue.js
 * @license    GPL-2.0
 *             https://opensource.org/license/gpl-2-0/
 * @copyright  2022 MiMiMi Community
 *             https://mimimi.software/
 *
 * -------------------------------------------------------------------------
 */

    mimimiInclude ( 'ModuleWithTable.php' );
    class MyMimimiOrders extends MimimiModuleWithTable {

        /**
         * -----------------------------------------------------------------
         *
         * Specify a name of the database table to store orders.
         *
         * -----------------------------------------------------------------
         *
         * @access public
         * @var    string
         *
         * -----------------------------------------------------------------
         */

        public $table = 'orders';

        /**
         * -----------------------------------------------------------------
         *
         * Define a database table structure.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    array
         *
         * -----------------------------------------------------------------
         */

        protected $tableFields = [
                      '`id`           BIGINT(20)    NOT NULL  AUTO_INCREMENT  COMMENT "order row system identifier"',
                      '`customer_id`  BIGINT(20)    NOT NULL                  COMMENT "customer ID if an authorized person was present"',
                      '`customer`     VARCHAR(60)   NOT NULL                  COMMENT "username provided at the time of order"',
                      '`address`      VARCHAR(120)  NOT NULL                  COMMENT "delivery address specified when placing the order"',
                      '`product_id`   BIGINT(20)    NOT NULL                  COMMENT "product identifier"',
                      '`vendor`       VARCHAR(60)   NOT NULL                  COMMENT "product vendor at the time of order"',
                      '`product`      VARCHAR(60)   NOT NULL                  COMMENT "product name at the time of order"',
                      '`cost`         FLOAT(11,2)   NOT NULL                  COMMENT "product price at the time of order"',
                      '`count`        INT(11)       NOT NULL                  COMMENT "product quantity"',
                      '`url`          VARCHAR(255)  NOT NULL                  COMMENT "order URL relative to your.site/order"',
                      '`date`         TIMESTAMP     NOT NULL                  COMMENT "order creation date"',
                      '`closed`       TIMESTAMP     NULL                      COMMENT "order closing date"'
                  ];

        /**
         * -----------------------------------------------------------------
         *
         * Define a list of table keys to speed up the database operations
         * related to orders.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    array
         *
         * -----------------------------------------------------------------
         */

        protected $tableKeys = [
                      'PRIMARY KEY ( `id`          )',
                      'KEY         ( `customer_id` )',
                      'KEY         ( `product_id`  )',
                      'KEY         ( `customer`    )',
                      'KEY         ( `vendor`      )',
                      'KEY         ( `product`     )',
                      'KEY         ( `url`         )',
                      'KEY         ( `date`        )'
                  ];

        /**
         * -----------------------------------------------------------------
         *
         * Retrieves order entries by order URL.
         *
         * -----------------------------------------------------------------
         *
         * Please note that "t1" below is an alias for the database table
         * "orders". Note also that the filter used below will actually
         * result in the following MySQL query:
         *
         *     SELECT   t1.*
         *     FROM     orders AS t1
         *     WHERE    t1.url = $url
         *     ORDER BY t1.id ASC
         *     LIMIT    1000000000
         *
         * I would like to point out that this table implements a non-standard
         * order numbering scheme. The order number is equated to ID of the
         * first row in the list of ordered items, so when retrieving an
         * order, its rows are sorted by "t1.id" column.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string      $url  The relative URL of the order you are looking for.
         * @return  array|bool        ARRAY on success. Each element is an array, like a row obtained from a database table.
         *                            FALSE on failure. This means no entries were found.
         *
         * -----------------------------------------------------------------
         */

        public function getByUrl ( $url ) {
            $filter = [
                'select'  => [ 't1.*'   => TRUE  ],
                /* where */    't1.url' => $url,
                'orderby' => [ 't1.id'  => 'asc' ]
            ];
            return $this->select ( $filter, 0, 1000000000 );
        }

        /**
         * -----------------------------------------------------------------
         *
         * Retrieves all orders.
         *
         * -----------------------------------------------------------------
         *
         * These orders are sorted by their identifier. Mote that "t1" below
         * is an alias for the database table "orders". Note also that the
         * filter used below will actually result in the following MySQL
         * query:
         *
         *     SELECT   t1.id                   AS number ,
         *              t1.customer_id                    ,
         *              t1.customer                       ,
         *              t1.address                        ,
         *              SUM(t1.cost * t1.count) AS cost   ,
         *              SUM(t1.count)           AS count  ,
         *              t1.url                            ,
         *              t1.date                           ,
         *              t1.closed
         *     FROM     orders AS t1
         *     GROUP BY t1.url ASC
         *     ORDER BY number ASC
         *     LIMIT    1000000000
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @return  array|bool  ARRAY on success. Each element is an array, like a row obtained from a database table.
         *                      FALSE on failure. This means no entries were found.
         *
         * -----------------------------------------------------------------
         */

        public function getOrders ( ) {
            $filter = [
                'select'  => [ 't1.id'                           => 'number' ,
                               't1.customer_id'                  => TRUE     ,
                               't1.customer'                     => TRUE     ,
                               't1.address'                      => TRUE     ,
                               'SUM(`t1`.`count` * `t1`.`cost`)' => 'cost'   ,
                               'SUM(`t1`.`count`)'               => 'count'  ,
                               't1.url'                          => TRUE     ,
                               't1.date'                         => TRUE     ,
                               't1.closed'                       => TRUE     ],
                'groupby' => [ 't1.url' => 'asc' ],
                'orderby' => [ 'number' => 'asc' ]
            ];
            return $this->select ( $filter, 0, 1000000000 );
        }

        /**
         * -----------------------------------------------------------------
         *
         * Specify demo rows that will be used as default order entries
         * if the database does not have a table named "orders". In this
         * case, all demo rows will be automatically added to the newly
         * created table.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    array
         *
         * -----------------------------------------------------------------
         */

        protected $demoRows = [
            [
                'url'         => 'demo-1',
                'date'        => '2024-09-01 10:21:32',
                'closed'      => '2024-09-05 16:27:38',
                'id'          => 1,
                'customer_id' => 1,
                'customer'    => 'Demo Customer',
                'address'     => 'Demo Town street, house 123',
                'product_id'  => 8,
                'vendor'      => 'Nike Shoes',
                'product'     => 'Renew Ride',
                'count'       => 2,
                'cost'        => 60.97
            ], [
                'url'         => 'demo-1',
                'date'        => '2024-09-01 10:21:32',
                'closed'      => '2024-09-05 16:27:38',
                'id'          => 2,
                'customer_id' => 1,
                'customer'    => 'Demo Customer',
                'address'     => 'Demo Town street, house 123',
                'product_id'  => 1,
                'vendor'      => 'Nike Shoes',
                'product'     => 'Air Zoom Pegasus 36',
                'count'       => 4,
                'cost'        => 108.97
            ], [
                'url'         => 'demo-1',
                'date'        => '2024-09-01 10:21:32',
                'closed'      => '2024-09-05 16:27:38',
                'id'          => 3,
                'customer_id' => 1,
                'customer'    => 'Demo Customer',
                'address'     => 'Demo Town street, house 123',
                'product_id'  => 10,
                'vendor'      => 'Nike Shoes',
                'product'     => 'Zoom Fly 3 Premium',
                'count'       => 1,
                'cost'        => 160.0
            ],

            [
                'url'         => 'demo-2',
                'date'        => '2024-09-03 15:10:03',
                'id'          => 4,
                'customer_id' => 0,
                'customer'    => 'Young Visitor',
                'address'     => 'Happy Town street, house 5',
                'product_id'  => 1,
                'vendor'      => 'Nike Shoes',
                'product'     => 'Air Zoom Pegasus 36',
                'count'       => 1,
                'cost'        => 108.97
            ], [
                'url'         => 'demo-2',
                'date'        => '2024-09-03 15:10:03',
                'id'          => 5,
                'customer_id' => 0,
                'customer'    => 'Young Visitor',
                'address'     => 'Happy Town street, house 5',
                'product_id'  => 0,
                'vendor'      => 'Lavager',
                'product'     => 'Instant Shine Sponge SILVER',
                'count'       => 1,
                'cost'        => 4.35
            ]
        ];
    };
