remote_work/src/lib/models/basket.php

<?php
/**
 * Created by PhpStorm.
 * User: Alex Savenko
 * Date: 12.01.2017
 * Time: 12:59
 */
namespace models;
use Phalcon\Forms\Element\Select;
class basket extends \db
{
    /**
     * @param   int     $user_id
     * @param   bool    $letter
     * @return  array
     */
    public function addUser($user_id, $letter) {
        return $this->get(
            '
                INSERT INTO
                    public.basket
                        (
                          user_id,
                          letter,
                          last_mod
                        )
                        VALUES
                        (
                          :user_id,
                          :letter,
                          CURRENT_TIMESTAMP
                        )
                        RETURNING user_id
            ',
            [
                'user_id'       => $user_id,
                'letter'        => $letter
            ],
            -1
        );
    }
    /**
     * @param   int     $user_id
     * @param   bool    $letter
     * @return  bool
     */
    public function updateUser($user_id, $letter) {
        return $this->exec(
            '
                UPDATE
                    public.basket
                SET
                    letter   = :letter,
                    last_mod = CURRENT_TIMESTAMP
                WHERE
                    user_id  = :user_id
                RETURNING user_id
            ',
            [
                'user_id'       => $user_id,
                'letter'        => $letter
            ]
        );
    }
    /**
     * @param   int     $user_id
     * @return  bool
     */
    public function deleteUser($user_id) {
        return $this->exec(
            '
                DELETE 
                FROM
                    public.basket
                WHERE
                    user_id = :user_id
            ',
            [
                'user_id' => $user_id
            ]
        );
    }
    /**
     * @param   int     $user_id
     * @param   int     $item_id
     * @param   int     $count_items
     * @return  array
     */
    public function addItem($user_id, $item_id, $count_items) {
        return $this->get(
            '
                    INSERT INTO
                        public.basket_items
                            (
                              user_id,
                              item_id,
                              count
                            )
                            VALUES
                            (
                              :user_id,
                              :item_id,
                              :count
                            )
                            RETURNING id
                ',
            [
                'user_id'   => $user_id,
                'item_id'   => $item_id,
                'count'     => $count_items
            ],
            -1
        );
    }
    /**
     * @param   int     $user_id
     * @param   int     $item_id
     * @param   int     $count_items
     * @return  array
     */
    public function updateItem($user_id, $item_id, $count_items) {
        return $this->get(
            '
                    UPDATE
                        public.basket_items
                    SET
                        item_id = :item_id,
                        count   = :count
                    WHERE
                        user_id = :user_id
                        AND
                        item_id = :item_id
                    RETURNING id
                ',
            [
                'user_id'   => $user_id,
                'item_id'   => $item_id,
                'count'     => $count_items
            ],
            -1
        );
    }
    /**
     * @param   int     $user_id
     * @return  bool
     */
    public function deleteAllItems($user_id) {
        return $this->exec(
            '
                DELETE 
                FROM
                    public.basket_items
                WHERE
                    user_id = :user_id
            ',
            [
                'user_id' => $user_id
            ]
        );
    }
    /**
     * @param   int     $user_id
     * @return  bool
     */
    public function checkUserExist($user_id) {
        $query = $this->get(
            '
                SELECT *
                FROM public.basket
                WHERE
                  user_id = :user_id
                LIMIT 1
            ',
            [
                'user_id' => $user_id
            ],
            -1
        );
        if (!empty($query[0]['id'])) {
            return true;
        }
        else {
            return false;
        }
    }
    /**
     * @param   int     $user_id
     * @param   int     $item_id
     * @return  bool
     */
    public function checkItemExist($user_id, $item_id) {
        $query = $this->get(
            '
                SELECT *
                FROM public.basket_items
                WHERE
                  user_id = :user_id
                  AND
                  item_id = :item_id
                LIMIT 1
            ',
            [
                'user_id' => $user_id,
                'item_id' => $item_id
            ],
            -1
        );
        if (!empty($query[0]['id'])) {
            return true;
        }
        else {
            return false;
        }
    }
    /**
     * @param   int     $user_id
     * @param   array   $items
     * @param   bool    $letter
     * @return  string
     */
    public function addData($user_id, $items, $letter) {
        $query_basket =  $this->addUser($user_id, $letter);
        foreach ($items as $item) {
            $this->addItem($user_id, $item['item_id'], $item['count_items']);
        }
        return 'User #'.$query_basket[0]['user_id'].' added';
    }
    /**
     * @param   int     $user_id
     * @param   array   $items
     * @param   bool    $letter
     * @return  string
     */
    public function updateData($user_id, $items, $letter) {
        //update user last mod
        if (!empty($items)) {
            $query_basket =  $this->updateUser($user_id, $letter);
        }
        else {
            $query_basket =  $this->deleteUser($user_id, $letter);
        }
        //clear deprecated items
        $this->deleteAllItems($user_id);
        foreach ($items as $item) {
            //add items 1 by 1
            $this->addItem($user_id, $item['item_id'], $item['count_items']);
        }
        return 'User #'.$query_basket[0]['user_id'].' updated';
    }
    /**
     * @param   int     $user_id
     * @param   array   $items
     * @param   bool    $letter
     */
    public function updateBasket($user_id, $items, $letter) {
        if ($this->checkUserExist($user_id)) {
            $this->updateData($user_id, $items, $letter);
        }
        else {
            $this->addData($user_id, $items, $letter);
        }
    }
    /**
     * @param   int     $user_id
     */
    public function deleteBasket($user_id) {
        $this->deleteUser($user_id);
        $this->deleteAllItems($user_id);
    }
    /**
     * @return array
     */
    public function getAbandonedUsers() {
        return $this->get(
            "
                SELECT *
                FROM public.basket
                WHERE
                  last_mod < CURRENT_TIMESTAMP - INTERVAL '1 day'
                AND
                  letter = FALSE
            ",
            [
            ]
        );
    }
}