public function paymentConfirmationPagination($id)
{
$page = $id != null ? $id : 1;
$startFrom = self::ITEM_PER_PAGE * ($page - 1);
$sql = "SELECT * FROM payment_confirmations ORDER BY payment_confirmation_id LIMIT {$startFrom}, " . self::ITEM_PER_PAGE;
$this->payment_confirmations = parent::connect()->prepare($sql);
try {
$this->payment_confirmations->execute();
} catch (PDOException $e) {
die($e->getMessage());
}
return $this->payment_confirmations;
}
public function CommentPagination($id)
{
$page = $id != null ? $id : 1;
$startFrom = self::ITEM_PER_PAGE * ($page - 1);
$sql = "SELECT * FROM comments ORDER BY comment_id DESC LIMIT {$startFrom}, " . self::ITEM_PER_PAGE;
$this->comments = parent::connect()->prepare($sql);
try {
$this->comments->execute();
} catch (PDOException $e) {
die($e->getMessage());
}
return $this->comments;
}
public function createOrderDetail()
{
$sql = 'INSERT INTO order_details(order_id, product_id, quantity)
SELECT t2.order_id, t1.product_id, t1.quantity FROM carts t1
INNER JOIN orders t2 ON t2.customer_id = t1.customer_id
WHERE t1.customer_id = :customer_id and t2.customer_id = :customer_id';
$this->order_details = parent::connect()->prepare($sql);
$data = array('customer_id' => isset($_SESSION['idCustomer']) ? $_SESSION['idCustomer'] : null);
try {
$this->order_details->execute($data);
} catch (PDOException $e) {
die($e->getMessage);
}
}
public function totalCart()
{
$sql = 'SELECT SUM(total_price) as total_cart FROM carts WHERE customer_id = :customer_id';
$this->carts = parent::connect()->prepare($sql);
$data = array('customer_id' => isset($_SESSION['idCustomer']) ? $_SESSION['idCustomer'] : null);
try {
$this->carts->execute($data);
$results = $this->carts->fetchAll(\PDO::FETCH_ASSOC);
foreach ($results as $row) {
$data = array('total_cart' => $row['total_cart']);
}
} catch (PDOException $e) {
die($e->getMessage());
}
return $data['total_cart'];
}
public function grandTotalSalesReportByDate()
{
$req = $this->app->request();
$sql = "SELECT SUM(total) as grand_total FROM orders WHERE status != 'In Process' AND created_at BETWEEN DATE(:createdAt1) AND DATE(:createdAt2) + INTERVAL 1 DAY";
$this->orders = parent::connect()->prepare($sql);
$data = array('createdAt1' => $req->post('createdAt1'), 'createdAt2' => $req->post('createdAt2'));
try {
$this->orders->execute($data);
foreach ($this->orders as $row) {
$results = $row['grand_total'];
}
} catch (PDOException $e) {
die($e->getMessage());
}
return $results;
}