<?php

class Stats { 

	public static function get_stats() {
		$db = Registry::get('base')->db;

		// get new customers
		$sql = "SELECT count(id) AS count_customers
		FROM customers
		WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= registration_date";
		$result = $db->query($sql);
		$obj = $result->fetch_object();
		$new_customers = $obj->count_customers;

		// get new orders
		$sql = "SELECT count(id) AS count_orders
		FROM orders
		WHERE order_status=1";
		$result = $db->query($sql);
		$obj = $result->fetch_object();
		$new_orders = $obj->count_orders;

		// get new emails
		$sql = "SELECT count(id) AS count_emails
		FROM tickets
		WHERE state_id=1";
		$result = $db->query($sql);
		$obj = $result->fetch_object();
		$new_emails = $obj->count_emails;

		return array(
			'new_customer' => $new_customers,
			'new_order' => $new_orders,
			'new_email' => $new_emails
		);
	}


	public static function orders($search) {
		$db = Registry::get('base')->db;
		$search = $this->db->real_escape_string($search);
		$sql = "SELECT *
				FROM orders
				WHERE CONCAT_WS('-', order_number, order_revision, customer_name, paypal_transaction_id, paypal_email, paypal_payerid, invoice_number)
				LIKE '%$search%'
				AND order_status != 9
				AND order_status != 10
				AND order_addendum != 'N'
				LIMIT 20";

		$result = $db->query($sql);

		$hits = $result->num_rows;

		if ($hits > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return array('hits' => $hits, 'results' => $return);
		}

		return false;
	}

	public static function items($search, $active=0) {
		$db = Registry::get('base')->db;

		$search = $this->db->real_escape_string($search);
		$sql = "SELECT *
				FROM items
				WHERE";
		if ($active) {
			$sql .= " active=1 AND ";
		}
		$sql .= " CONCAT(number, name, short_description, vendor_item_number, ean_code, seo_keywords)
				LIKE '%$search%'
				LIMIT 20";

		$result = $db->query($sql);

		$hits = $result->num_rows;

		if ($hits > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return array('hits' => $hits, 'results' => $return);
		}

		return false;
	}

	public static function category_items($search, $category_id, $active=0) {
		$db = Registry::get('base')->db;
		$search = $this->db->real_escape_string($search);
		$sql = "SELECT *
				FROM items
				WHERE";
		if ($active) {
			$sql .= " active=1 AND ";
		}
		$category_id = $this->db->real_escape_string($category_id);
		$sql .= " CONCAT(number, name, short_description, vendor_item_number, ean_code)
				LIKE '%$search%' AND
				(
					structure_id = $category_id OR
					structure_id_2 = $category_id OR
					structure_id_3 = $category_id OR
					structure_id_4 = $category_id OR
					structure_id_5 = $category_id OR
					structure_id_6 = $category_id OR
					structure_id_7 = $category_id OR
					structure_id_8 = $category_id OR
					structure_id_9 = $category_id
				)
				LIMIT 20";
		$base_object = Registry::get('base');
		$result = $db->query($sql);

		$hits = $result->num_rows;

		if ($hits > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return array('hits' => $hits, 'results' => $return);
		}

		return false;
	}

	public static function manufacturers($search) {
		$db = Registry::get('base')->db;
		$search = $this->db->real_escape_string($search);
		$sql = "SELECT *
				FROM manufacturers
				WHERE CONCAT(name, address, city)
				LIKE '%$search%'
				LIMIT 20";

		$result = $db->query($sql);

		$hits = $result->num_rows;

		if ($hits > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return array('hits' => $hits, 'results' => $return);
		}

		return false;
	}

}