/**
* Get properties
*
* @param boolean $forceReload to initiliaze properties
*
* @return array
*/
public function getProperties($forceReload = false)
{
if ($this->getData('properties') == null or $forceReload) {
$select = new Select();
$select->from('tab')->columns(array())->join('property', 'tab.id = property.tab_id', '*', Select::JOIN_INNER);
if ($this->getDocumentId() !== null) {
$select->join('document', 'document.document_type_id = tab.document_type_id', array(), Select::JOIN_INNER);
$select->join('property_value', 'document.id = property_value.document_id AND property.id = property_value.property_id', array('value'), Select::JOIN_LEFT);
$select->where(array('document.id' => $this->getDocumentId()));
}
if ($this->getTabId() != null) {
$select->where(array('tab.id' => $this->getTabId()));
}
if ($this->getDocumentTypeId() != null) {
$select->where(array('tab.document_type_id' => $this->getDocumentTypeId()));
}
$select->order('property.sort_order ASC');
$rows = $this->fetchAll($select);
$properties = array();
foreach ($rows as $row) {
$propertyModel = Model::fromArray((array) $row);
if ($this->getDocumentId() !== null) {
$propertyModel->setDocumentId($this->getDocumentId());
}
$properties[] = $propertyModel;
}
$this->setData('properties', $properties);
}
return $this->getData('properties');
}
public function fetchAll($id, $paginate = true, $filter = array(), $orderBy = array())
{
if ($paginate) {
$select = new Select('media');
//$select->columns(array('*', new Expression("user_feature_setting.email as email_status")));
$select->join('users', 'users.id = media.user_id', array('first_name', 'last_name'), 'inner');
$select->join('lookup_status', 'lookup_status.status_id = media.status_id', array('status'), 'left');
$select->where(array('user_id' => $id, 'media_type' => 1));
/* Data filter code start here*/
if (count($filter) > 0) {
isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(users.first_name,' ',users.last_name) LIKE '%" . $filter['name'] . "%'") : "";
isset($filter['title']) && $filter['title'] != "" ? $select->where("media.media_title LIKE '%" . $filter['title'] . "%'") : "";
isset($filter['media_type']) && $filter['media_type'] != "" ? $select->where("media.media_type = " . $filter['media_type']) : "";
if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") {
$select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'");
} else {
if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") {
$select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'");
} else {
if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") {
$select->where("DATE_FORMAT(media.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'");
}
}
}
isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("media.status_id = " . $filter['status_id']) : "";
}
/* Data filter code end here*/
/* Data sorting code starts here */
if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
switch ($orderBy['sort_field']) {
case 'name':
$select->order('users.first_name ' . $orderBy['sort_order']);
break;
case 'title':
$select->order('media.media_title ' . $orderBy['sort_order']);
break;
case 'media_type':
$select->order('media.media_type ' . $orderBy['sort_order']);
break;
case 'date':
$select->order('media.created_date ' . $orderBy['sort_order']);
break;
case 'status':
$select->order('lookup_status.status ' . $orderBy['sort_order']);
break;
}
}
/* Data sorting code ends here */
//echo str_replace('"', '', $select->getSqlString()); exit;
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new UsersMedia());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
} else {
$select = $this->tableGateway->getSql()->select();
return $this->tableGateway->selectwith($select);
}
}
/**
* @param array $fields
* @param string|array $order Multi-column sort should be given as an array
* @param bool $paginated Whether to return a paginator or a raw resultSet
* @param int $limit
*/
public function find($fields = null, $order = 'tickets.enteredDate desc', $paginated = false, $limit = null)
{
$select = new Select('tickets');
$select->join(['c' => 'categories'], 'tickets.category_id=c.id', [], $select::JOIN_LEFT);
if (count($fields)) {
foreach ($fields as $key => $value) {
if ($value) {
switch ($key) {
case 'reportedByPerson_id':
$select->join(['i' => 'issues'], 'tickets.id=i.ticket_id', [], $select::JOIN_LEFT);
$select->where(["i.{$key}" => $value]);
break;
case 'start_date':
$d = date(ActiveRecord::MYSQL_DATE_FORMAT, strtotime($value));
$select->where("tickets.enteredDate>='{$d}'");
break;
case 'end_date':
$d = date(ActiveRecord::MYSQL_DATE_FORMAT, strtotime($value));
$select->where("tickets.enteredDate<='{$d}'");
break;
case 'lastModified_before':
$d = date(ActiveRecord::MYSQL_DATE_FORMAT, strtotime($value));
$select->where("tickets.lastModified<='{$d}'");
break;
case 'lastModified_after':
$d = date(ActiveRecord::MYSQL_DATE_FORMAT, strtotime($value));
$select->where("tickets.lastModified>='{$d}'");
break;
case 'bbox':
$bbox = explode(',', $value);
if (count($bbox) == 4) {
$minLat = (double) $bbox[0];
$minLong = (double) $bbox[1];
$maxLat = (double) $bbox[2];
$maxLong = (double) $bbox[3];
$select->where('tickets.latitude is not null and tickets.longitude is not null');
$select->where("tickets.latitude > {$minLat}");
$select->where("tickets.longitude > {$minLong}");
$select->where("tickets.latitude < {$maxLat}");
$select->where("tickets.longitude < {$maxLong}");
}
break;
default:
$select->where(["tickets.{$key}" => $value]);
}
}
}
}
// Only get tickets for categories this user is allowed to see
if (!isset($_SESSION['USER'])) {
$select->where("c.displayPermissionLevel='anonymous'");
} elseif ($_SESSION['USER']->getRole() != 'Staff' && $_SESSION['USER']->getRole() != 'Administrator') {
$select->where("c.displayPermissionLevel in ('public','anonymous')");
}
return parent::performSelect($select, $order, $paginated, $limit);
}
public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
{
if ($paginate) {
$select = new Select('booking');
$select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,payment_history.currency as currency,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus")));
$select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner');
$select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left');
$select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left');
$select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left');
$select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left');
$select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision'), 'inner');
$select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner');
$select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner');
$select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left');
$select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)');
count($filter) > 0 ? $select->where($filter) : "";
/* Data sorting code starts here */
if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
switch ($orderBy['sort_field']) {
case 'user':
$select->order('users.first_name ' . $orderBy['sort_order']);
break;
case 'service_provider':
$select->order('service_provider.first_name ' . $orderBy['sort_order']);
break;
case 'service':
$select->order('service_category.category_name ' . $orderBy['sort_order']);
break;
case 'booked_date':
$select->order('booking.booked_date ' . $orderBy['sort_order']);
break;
}
} else {
$select->order('booking.id desc');
}
/* Data sorting code ends here */
//echo str_replace('"', '', $select->getSqlString()); exit;
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Bookings());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
} else {
$select = $this->tableGateway->getSql()->select();
$select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus")));
$select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner');
$select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left');
$select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left');
$select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left');
$select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left');
$select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision', 'created_date'), 'inner');
$select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner');
$select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner');
$select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left');
$select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)');
count($filter) > 0 ? $select->where($filter) : "";
return $this->tableGateway->selectwith($select);
}
}
public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
{
if ($paginate) {
$select = new Select('feedback');
$select->columns(array('*', new Expression("service_provider_contact.first_name AS sp_first_name, service_provider_contact.last_name AS sp_last_name")));
$select->join(array('service_provider_contact' => 'users'), 'service_provider_contact.id = feedback.users_id', array(), 'inner');
$select->join('users', 'users.id = feedback.created_by', array('first_name', 'last_name'), 'inner');
$select->join('service_provider_service', 'service_provider_service.id = feedback.service_id', array('duration'), 'inner');
$select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'inner');
$select->join('lookup_status', 'lookup_status.status_id = feedback.status_id', array('status'), 'left');
/* Data filter code start here*/
if (count($filter) > 0) {
isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(service_provider_contact.first_name,' ',service_provider_contact.last_name) LIKE '%" . $filter['name'] . "%'") : "";
if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") {
$select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'");
} else {
if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") {
$select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'");
} else {
if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") {
$select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'");
}
}
}
if (isset($filter['service_id']) && $filter['service_id'] != "") {
$select->where("feedback.service_id = " . $filter['service_id']);
}
isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("users.status_id = " . $filter['status_id']) : "";
}
/* Data filter code end here*/
/* Data sorting code starts here */
if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
switch ($orderBy['sort_field']) {
case 'name':
$select->order('service_provider_contact.first_name ' . $orderBy['sort_order']);
break;
case 'service':
$select->order('service_category.category_name ' . $orderBy['sort_order']);
break;
case 'date':
$select->order('feedback.created_date ' . $orderBy['sort_order']);
break;
}
}
/* Data sorting code ends here */
//echo str_replace('"','',$select->getSqlString()); exit;
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Feedbacks());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
return $this->tableGateway->select();
}
public function get($id, $user)
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array("id", "register_date", "guide", "observations"));
$select->join('customers', "customers.id = " . $this->tableGateway->getTable() . ".client", array('client_first_name' => 'first_name', 'client_last_name' => 'first_name'), 'inner');
$select->join(array('sellers' => 'customers'), "sellers.id = " . $this->tableGateway->getTable() . ".seller", array('seller_first_name' => 'first_name', 'seller_last_name' => 'last_name', 'seller_company' => 'company'), 'inner');
$select->join('payments_methods', "payments_methods.id = " . $this->tableGateway->getTable() . ".payment_method", array('payment_method_name' => 'name'), 'inner');
$select->where(array($this->tableGateway->getTable() . ".id" => $id, $this->tableGateway->getTable() . ".user" => $user));
$rowset = $this->tableGateway->selectWith($select);
$row = $rowset->current();
return $row;
}
public function searchSerial($serial)
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array());
$select->join("products", "products.id = " . $this->tableGateway->getTable() . ".product", array('id' => 'id'));
$select->join("products_receive_inventory", "products_receive_inventory.details_receive_inventory = " . $this->tableGateway->getTable() . ".id", array('serial' => 'serial'));
$select->where(array("products_receive_inventory.status" => 0));
$select->where->like("products_receive_inventory.serial", "%" . $serial . "%");
error_log($select->getSqlString());
$result = $this->tableGateway->selectWith($select);
return $result;
}
public function fetchCartOnItemId($item_id)
{
$select = new Select();
$select->from('items');
$select->join('brands', "brands.b_id = items.item_brand", array('b_name'), 'left');
$select->join('categories', "categories.cat_id = items.item_category", array('cat_name'), 'left');
$select->join('sub_categories', "sub_categories.subcat_id = items.item_sub_category", array('subcat_name'), 'left');
$select->join('images', "images.img_item_id = items.item_id", array('img_link'), 'left');
$select->where("items.item_id = {$item_id}");
$resultSet = $this->cartTableGateway->selectWith($select);
return $resultSet;
}
public function getByProduct($product)
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array("id" => "id", "measure_value" => "measure_value", "image" => "image"));
$select->join('products_measures', 'products_measures.measure = ' . $this->tableGateway->getTable() . '.id', array(), 'inner');
$select->join('measures_types', 'measures_types.id = ' . $this->tableGateway->getTable() . '.measure_type', array('mt_name' => 'name'), $select::JOIN_LEFT);
$select->join('specifications', $this->tableGateway->getTable() . ".specification = specifications.id", array('s_name' => 'name', 'image' => 'image', 'specification' => 'id'), 'inner');
$select->join('categories_specifications', "categories_specifications.specification = specifications.id", array(), 'inner');
$select->order('categories_specifications.order');
$select->where(array("products_measures.product" => $product));
$rows = $this->tableGateway->selectWith($select);
return $rows ? $rows : false;
}
public function getOrdersList()
{
$sql = new Sql($this->tableGateway->getAdapter());
$db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
$select = new Select(self::TABLE_NAME);
$columns = array('orderId' => 'aufri_orders_id', 'productId' => 'aufri_orders_product_id_fk', 'shopkeeperId' => 'aufri_orders_shopkeeper_id_fk', 'userId' => 'aufri_orders_user_id_fk', 'orderDate' => 'aufri_orders_date', 'couponId' => 'aufri_orders_coupon_id_fk', 'orderStatus' => 'aufri_orders_status');
$select->columns($columns);
$select->join(array('productTable' => ProductsTable::TABLE_NAME), 'productTable.aufri_products_id=aufri_orders.aufri_orders_product_id_fk', array('productName' => 'aufri_products_name', 'productRent' => 'aufri_products_rent', 'productDeposit' => 'aufri_products_security'), Select::JOIN_LEFT);
$select->join(array('shopkeeperTable' => ShopkeeperTable::TABLE_NAME), 'shopkeeperTable.aufri_shopkeeper_id=aufri_orders.aufri_orders_shopkeeper_id_fk', array('shopkeeperName' => 'aufri_shopkeeper_name'), Select::JOIN_LEFT);
$select->join(array('couponTable' => CouponTable::TABLE_NAME), 'couponTable.aufri_coupons_id=aufri_orders.aufri_orders_coupon_id_fk', array('couponName' => 'aufri_coupons_name', 'couponCode' => 'aufri_coupons_code', 'couponDiscount' => 'aufri_coupons_discount'), Select::JOIN_LEFT);
$select->join(array('userTable' => UserTable::TABLE_NAME), 'userTable.aufri_users_id=aufri_orders.aufri_orders_user_id_fk', array('userName' => 'aufri_users_name', 'userPhone' => 'aufri_users_phone_no', 'userAddressId' => 'aufri_users_address_id_fk'), Select::JOIN_LEFT);
$select->join(array('addressTable' => AddressTable::TABLE_NAME), 'addressTable.aufri_address_id=userTable.aufri_users_address_id_fk', array('addressId' => 'aufri_address_id', 'userAddress' => 'aufri_address_address'), Select::JOIN_LEFT);
return $this->getSqlContent($db, $sql, $select);
}
public function fetchAll($userType = false)
{
$select = new Select($this->tableGateway->getTable());
$select->join('cities', "cities.id = " . $this->tableGateway->getTable() . ".city", array('city_name' => 'name'), 'inner');
$select->join('customers_classifications', "customers_classifications.customer = " . $this->tableGateway->getTable() . ".id", array(), 'inner');
$select->join('classifications', "classifications.id = customers_classifications.classification", array(), 'inner');
$select->join('user_types', "user_types.id = classifications.user_type", array(), 'inner');
if ($userType) {
$select->where(array("user_types.id" => $userType));
}
$resultSet = $this->tableGateway->selectWith($select);
$resultSet->buffer();
return $resultSet;
}
public function fetchAllReplies($id)
{
$select = new Select('messages');
$select->columns(array('*', new Expression("from_user.user_name as from_user, to_user.user_name as to_user")));
$select->join(array('from_user' => 'users'), 'from_user.id = messages.from_user_id', array(), 'left');
$select->join(array('to_user' => 'users'), 'to_user.id = messages.to_user_id', array(), 'left');
$select->where(array('topLevel_id' => $id));
$select->order('created_date desc');
$rowset = $this->tableGateway->selectwith($select);
if (!$rowset) {
return false;
}
return $rowset;
}
/**
* Returns all accepted and declined applications
*/
public function getProcessedApplications($paginated = false)
{
if ($paginated) {
$select = new Select('application');
$select->join(['a' => 'account'], 'application.processed_by = a.id', ['account_name' => 'name'])->where('processed > 0')->order('date_applied DESC');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Application());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
return new Paginator($paginatorAdapter);
}
return $this->tableGateway->select(function (Select $select) {
$select->join(['a' => 'account'], 'application.processed_by = a.id', ['account_name' => 'name'])->where('processed > 0')->order('date_applied DESC');
});
}
/**
* Returns all Media in db
*
* @return \Zend\Db\ResultSet\ResultSet
*/
public function fetchAll($paginated = false)
{
if ($paginated) {
$select = new Select('media');
$select->join(['a' => 'account'], 'media.account_id = a.id', ['name'])->order('date_posted DESC');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Media());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
return new Paginator($paginatorAdapter);
}
return $this->tableGateway->select(function (Select $select) {
$select->join(['a' => 'account'], 'media.account_id = a.id', ['name'])->order('date_posted DESC');
});
}
/**
* Returns all News in db
*
* @return \Zend\Db\ResultSet\ResultSet
*/
public function fetchAll($paginated = false)
{
if ($paginated) {
$select = new Select('news');
$select->join(['a' => 'account'], 'news.account_id = a.id', ['name'])->join(['c' => 'news_category'], 'news.category_id = c.id', ['cname' => 'name'])->join(['m' => 'comment'], 'news.id = m.news_id', ['cid' => 'id'], $select::JOIN_LEFT)->order('news.date_posted DESC')->columns(['id', 'title', 'content', 'date_posted', 'comment_count' => new Expression('COUNT(m.id)')])->group('news.id');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new News());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
return new Paginator($paginatorAdapter);
}
return $this->tableGateway->select(function (Select $select) {
$select->join(['a' => 'account'], 'news.account_id = a.id', ['name'])->join(['c' => 'news_category'], 'news.category_id = c.id', ['category'])->order('date_posted DESC');
});
}
public function fetchAll($paginate = true)
{
if ($paginate) {
$select = new Select('lookup_rating');
$select->join('users', 'users.id = lookup_rating.created_by', array('first_name', 'last_name'), 'left');
$select->join('lookup_status', 'lookup_status.status_id = lookup_rating.status_id', array('status'), 'left');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new RatingType());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
return $this->tableGateway->select();
}
public function fetchAll($paginate = true)
{
if ($paginate) {
$select = new Select('notification_settings');
$select->join('users', 'users.id = notification_settings.user_id', array('user_name'), 'left');
$select->join('site_modules', 'site_modules.id = notification_settings.module_id', array('module_name'), 'left');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new NotificationSettings());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
return $this->tableGateway->select();
}
public function fetchAll()
{
$select = new Select();
$select->from('items');
//$select->columns(array('item_name','item_brand','item_price'));
$select->join('brands', "brands.b_id = items.item_brand", array('b_name'), 'left');
$select->join('categories', "categories.cat_id = items.item_category", array('cat_name'), 'left');
$select->join('sub_categories', "sub_categories.subcat_id = items.item_sub_category", array('subcat_name'), 'left');
$select->join('images', "images.img_item_id = items.item_id", array('img_link'), 'left');
$select->group('items.item_id');
//echo $select->getSqlString();
$resultSet = $this->tableGateway->selectWith($select);
//$resultSet = $this->tableGateway->select();
return $resultSet;
}
public function fetchAll($paginate = true)
{
if ($paginate) {
$select = new Select('newsletter');
$select->join('lookup_status', 'lookup_status.status_id = newsletter.status_id', array('status'), 'left');
$select->join('lookup_user_type', 'lookup_user_type.id = newsletter.user_type_id', array('user_type'), 'left');
$select->where('newsletter.created_by = 1');
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Newsletters());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
return $this->tableGateway->select();
}
/**
* Populates the collection, using strict matching of the requested fields
*
* @param array $fields
* @param string|array $order Multi-column sort should be given as an array
* @param bool $paginated Whether to return a paginator or a raw resultSet
* @param int $limit
*/
public function find($fields = null, $order = ['g.ordering', 'g.name', 'categories.name'], $paginated = false, $limit = null)
{
$select = new Select('categories');
$select->join(['g' => 'categoryGroups'], 'categories.categoryGroup_id=g.id', [], $select::JOIN_LEFT);
if (count($fields)) {
foreach ($fields as $key => $value) {
switch ($key) {
case 'postableBy':
// If they're authenticated, but they are not staff
if ($value instanceof Person) {
if ($value->getRole() != 'Staff' && $value->getRole() != 'Administrator') {
// Limit them to public and anonymous categories
$select->where(['categories.postingPermissionLevel' => ['public', 'anonymous']]);
}
} else {
$select->where(['categories.postingPermissionLevel' => 'anonymous']);
}
break;
case 'displayableTo':
// If they're authenticated, but they are not staff
if ($value instanceof Person) {
if ($value->getRole() != 'Staff' && $value->getRole() != 'Administrator') {
// Limit them to public and anonymous categories
$select->where(['categories.displayPermissionLevel' => ['public', 'anonymous']]);
}
} else {
$select->where(['categories.displayPermissionLevel' => 'anonymous']);
}
break;
case 'department_id':
$select->join(['d' => 'department_categories'], 'categories.id=d.category_id', [], $select::JOIN_LEFT);
$select->where(['d.department_id' => $value]);
break;
default:
if ($value) {
$select->where(["categories.{$key}" => $value]);
}
}
}
}
// Only get categories this user is allowed to see or post to
if (!isset($_SESSION['USER'])) {
$select->where("(categories.postingPermissionLevel='anonymous' or categories.displayPermissionLevel='anonymous')");
} elseif ($_SESSION['USER']->getRole() != 'Staff' && $_SESSION['USER']->getRole() != 'Administrator') {
$select->where("(categories.postingPermissionLevel in ('public','anonymous') or categories.displayPermissionLevel in ('public','anonymous'))");
}
return parent::performSelect($select, $order, $paginated, $limit);
}
public function getList($where = array(), $order = null, $offset = null, $limit = null)
{
if (empty($where['ProductFilterOption.productCategoryFilterOptionID'])) {
$select = new Select();
$select->from(array('b' => 'Product'));
$select->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'));
$select->where($where);
$select->offset($offset);
$select->limit($limit);
$select->order($order);
} else {
$select = $this->getSelect();
$select->columns(array())->join(array('b' => 'Product'), 'ProductFilterOption.productID = b.productID')->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'))->where($where)->offset($offset)->limit($limit)->group(array('ProductFilterOption.productID'))->having('count(ProductFilterOption.productID) > ' . (count($where['ProductFilterOption.productCategoryFilterOptionID']) - 1));
$select->order($order);
}
$paginator = $this->paginate($select);
$paginator->setCurrentPageNumber(ceil($offset / $limit) + 1);
//$paginator->setItemCountPerPage(1);
$products = $paginator->getCurrentItems()->getArrayCopy();
$pages = $paginator->getPages();
$productsCount = $paginator->getTotalItemCount();
foreach ($products as $k => $v) {
$products[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
}
return array('products' => $products, 'productsCount' => $productsCount, 'pages' => $pages);
}
public function fetchPostsByFeeds($feeds, $offset = null, $limit = null)
{
$select = new Select(self::$_tableName);
if (!is_null($offset)) {
$select->offset($offset);
}
if (!is_null($limit)) {
$select->limit($limit);
}
$select->join('directus_social_feeds', 'directus_social_feeds.id = directus_social_posts.feed', ['feed_type' => 'type'])->order('directus_social_posts.datetime DESC');
$select->where->equalTo('directus_social_posts.status', 1)->equalTo('directus_social_feeds.status', 1);
$FeedWhere = new Where();
$SocialCache = new SocialCache();
foreach ($feeds as $feed) {
// Run scrape if due
$SocialCache->scrapeFeedIfDue($feed['name'], $feed['type']);
$FeedWhere->or->nest->equalTo('directus_social_feeds.name', $feed['name'])->equalTo('directus_social_feeds.type', $feed['type'])->unnest;
$select->where($FeedWhere);
}
$socialPosts = $this->selectWith($select);
$socialPosts = $socialPosts->toArray();
// Unserialize cached feed entry API-responses
foreach ($socialPosts as &$post) {
$post['data'] = json_decode($post['data'], true);
}
return $socialPosts;
}
public function get($outputInventoryId, $id = false)
{
$select = new Select($this->tableGateway->getTable());
$select->join('output_inventory', "output_inventory.id = " . $this->tableGateway->getTable() . ".output_inventory", array(), 'inner');
$select->where(array($this->tableGateway->getTable() . ".output_inventory" => $outputInventoryId));
if ($id) {
$select->where(array($this->tableGateway->getTable() . ".id" => $id));
$resultSet = $this->tableGateway->selectWith($select);
$result = $resultSet->current();
$product = $this->productTable->getName(false, $result->getProduct());
$result->setProduct(implode($product));
} else {
$resultSet = $this->tableGateway->selectWith($select);
$result = array();
foreach ($resultSet as $rows) {
$product = $this->productTable->getName(false, $rows->getProduct());
$rows->setProduct(implode($product));
switch ($rows->getIva()) {
case 1:
$rows->setIvaAccumulated($rows->getCost() - $rows->getCost() / 1.16);
$rows->setCost($rows->getCost() / 1.16);
break;
case 2:
$rows->setIvaAccumulated($rows->getCost() * 0.16);
break;
case 3:
$rows->setIvaAccumulated(0);
break;
}
$result[] = $rows;
}
}
return $result;
}
public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
{
if ($paginate) {
$select = new Select('service_language');
$select->join('lookup_status', 'lookup_status.status_id = service_language.status_id', array('status'), 'left');
/* Data filter code start here*/
if (count($filter) > 0) {
$filter['language_name'] != "" ? $select->where("service_language.language_name LIKE '%" . $filter['language_name'] . "%'") : "";
$filter['status_id'] != "" ? $select->where("service_language.status_id = " . $filter['status_id']) : "";
}
/* Data filter code end here*/
/* Data sorting code starts here */
if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
switch ($orderBy['sort_field']) {
case 'language':
$select->order('service_language.language_name ' . $orderBy['sort_order']);
break;
case 'status':
$select->order('lookup_status.status ' . $orderBy['sort_order']);
break;
}
}
/* Data sorting code ends here */
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new ServiceLanguages());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
return $this->tableGateway->select();
}
public function fetchAll($paginate = true)
{
if ($paginate) {
$select = new Select('faqs');
$select->join('lookup_user_type', 'lookup_user_type.id = faqs.user_type_id', array('user_type'), 'left');
$select->join('faq_index', 'faq_index.id = faqs.index_id', array('index_name'), 'left');
$select->join('lookup_status', 'lookup_status.status_id = faqs.status_id', array('status'), 'left');
//echo str_replace('"','',$select->getSqlString()); exit;
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Faqs());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
return $this->tableGateway->select();
}
public function find($params)
{
$sql = new Sql($this->getAdapter());
$select = new Select();
$select->from($params['from']);
if (!empty($params['columns'])) {
$select->columns($params['columns']);
}
foreach ($params['where'] as $where) {
$select->where($where);
}
foreach ($params['joins'] as $join) {
if (empty($join['columns'])) {
$join['columns'] = Select::SQL_STAR;
}
if (empty($join['type'])) {
$join['type'] = Select::JOIN_INNER;
}
$select->join($join['name'], $join['on'], $join['columns'], $join['type']);
}
$query = $sql->getSqlStringForSqlObject($select);
$results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
$data = $results->toArray();
if (empty($data)) {
return false;
} else {
if (count($data) == 1) {
return $data[0];
} else {
return $data;
}
}
}
public function fetchAll($paginate = true, $usertype = 8)
{
if ($paginate) {
switch ($usertype) {
case 3:
$select = new Select('users');
$select->join('lookup_status', 'lookup_status.status_id = users.status_id', array('status'), 'left');
$select->join('user_feature_setting', 'user_feature_setting.user_id = users.id', array(), 'left');
$select->where('users.user_type_id = 3 and user_feature_setting.newsletter in (1,4)');
break;
case 4:
$select = new Select('users');
$select->join('lookup_status', 'lookup_status.status_id = users.status_id', array('status'), 'left');
$select->join('user_feature_setting', 'user_feature_setting.user_id = users.id', array(), 'left');
$select->where('users.user_type_id = 4 and user_feature_setting.newsletter in (1,4)');
break;
case 8:
$select = new Select('newsletter_subscription');
$select->join('lookup_status', 'lookup_status.status_id = newsletter_subscription.status_id', array('status'), 'left');
break;
default:
$select = new Select('newsletter_subscription');
$select->join('lookup_status', 'lookup_status.status_id = newsletter_subscription.status_id', array('status'), 'left');
break;
}
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new NewsletterSubscribers());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
return $this->tableGateway->select();
}
public function getSampleWithRelationshipsById($id)
{
$select = new Select($this->getTable());
//Join Junction table
$select->join('sample_users', 'sample_users.sample_id = sample.id', array(), $select::JOIN_LEFT);
//Join related table to fetch related users
$select->join(array('du' => 'directus_users'), 'du.id = sample_users.user_id', array('first_name', 'last_name'), $select::JOIN_LEFT);
//Select based off sample id
$select->where->equalTo('sample.id', $id);
//Only return 1 result or empty array if not found.
$rows = $this->selectWith($select)->toArray();
if (count($rows) > 0) {
return $rows[0];
}
return array();
}
/**
* @testdox unit test: Test join() returns same Select object (is chainable)
* @covers Zend\Db\Sql\Select::join
*/
public function testJoin()
{
$select = new Select();
$return = $select->join('foo', 'x = y', Select::SQL_STAR, Select::JOIN_INNER);
$this->assertSame($select, $return);
return $return;
}
public function join(Select $select, $on, $prefix = null)
{
if (!$prefix) {
$prefix = $on;
}
$select->join(array('table' . $prefix => $this->tableGateway->getTable()), 'table' . $prefix . '.id = ' . $on, array($prefix . '_id' => 'id', $prefix . '_name' => 'name', $prefix . '_path' => 'path', $prefix . '_mime' => 'mime', $prefix . '_size' => 'size', $prefix . '_created' => 'created', $prefix . '_status' => 'status'), 'left');
}
