예제 #1
0
 public function getBuilder()
 {
     if (empty($this->parsed['SELECT'])) {
         throw new \InvalidArgumentException('Queries other than SELECT are not supported yet');
     }
     $sql = new Select();
     $sql->columns($this->getBaseExprs($this->parsed['SELECT']));
     foreach ($this->parsed['FROM'] as $table) {
         if (!$table['ref_type']) {
             $sql->from(array($table['alias']['name'] => $table['table']));
             continue;
         }
         if ('JOIN' == $table['join_type']) {
             $table['join_type'] = 'INNER';
         }
         $sql->join(array($table['alias']['name'] => $table['table']), join(' ', $this->getBaseExprs($table['ref_clause'])), array(), $table['join_type']);
     }
     if (isset($this->parsed['GROUP'])) {
         $sql->group($this->getBaseExprs($this->parsed['GROUP']));
     }
     if (isset($this->parsed['WHERE'])) {
         $sql->where(join(' ', $this->getBaseExprs($this->parsed['WHERE'])));
     }
     unset($this->parsed['WHERE'], $this->parsed['GROUP'], $this->parsed['FROM'], $this->parsed['WHERE']);
     var_dump($this->parsed);
     #die('Stopped at ' . __FILE__ . ' on line ' . __LINE__);
     return $sql;
 }
 /**
  *
  * @param int $days_threshold days threshold
  * @param int $ca_threshold turnover threshold
  * @param int $limit
  * @return type
  */
 public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000)
 {
     $akilia2db = $this->configuration['synchronizer']['db_akilia2'];
     $select = new Select();
     $bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db);
     $bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db);
     $bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db);
     $bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db);
     $so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db);
     $sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db);
     $select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1');
     $columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')];
     $select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true);
     $select->group($columns);
     $select->having("sum(sol.price_total_net) > {$ca_threshold}");
     $select->where(function (Where $where) use($min_accuracy) {
         //$where->greaterThan('so.date_order', '2012-12-31');
         $where->notLike('bc.name', '%FINISHED%');
         $where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest;
     });
     $select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}"));
     if ($limit > 0) {
         $select->limit($limit);
     }
     $store = $this->getStore($select);
     $data = $store->getData()->toArray();
     return $data;
 }
 public function countMessages($uid)
 {
     $fetchFn = function () use($uid) {
         $select = new Select($this->table);
         $select->columns(['id', 'read', 'count' => new Expression('COUNT(`id`)'), 'max_id' => new Expression('MAX(`message_id`)')])->where->equalTo('recipient', $uid);
         $select->group(['id', 'read']);
         $result = $this->selectWith($select)->toArray();
         return $result;
     };
     $cacheKey = MemcacheProvider::getKeyDirectusCountMessages($uid);
     $result = $this->memcache->getOrCache($cacheKey, $fetchFn, 1800);
     $count = ['read' => 0, 'unread' => 0, 'total' => 0, 'max_id' => 0];
     foreach ($result as $item) {
         if ((int) $item['max_id'] > $count['max_id']) {
             $count['max_id'] = (int) $item['max_id'];
         }
         switch ($item['read']) {
             case '1':
                 $count['read'] = (int) $item['count'];
                 break;
             case '0':
                 $count['unread'] = (int) $item['count'];
                 break;
         }
     }
     $count['total'] = $count['read'] + $count['unread'];
     return $count;
 }
예제 #4
0
파일: Model.php 프로젝트: gridguyz/banner
 /**
  * Get paginator for listing
  *
  * @return \Zend\Paginator\Paginator
  */
 public function getPaginator()
 {
     $xTag = new Select('banner_x_set_by_tag');
     $xLocale = new Select('banner_x_set_by_locale');
     $xGlobal = new Select('banner_x_set_by_global');
     $xTag->join('banner_set_x_tag', 'banner_set_x_tag.id = banner_x_set_by_tag.setXTagId', array('setId'), Select::JOIN_INNER)->join('tag', 'tag.id = banner_set_x_tag.tagId', array(), Select::JOIN_INNER)->group('banner_set_x_tag.setId')->columns(array('tags' => new Expression('STRING_AGG( DISTINCT ?.?, ? )', array('tag', 'name', "\n"), array(Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE))));
     $xLocale->group('banner_x_set_by_locale.setId')->columns(array('setId', 'locales' => new Expression('STRING_AGG( DISTINCT ?, ? )', array('locale', "\n"), array(Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE))));
     $xGlobal->group('banner_x_set_by_global.setId')->columns(array('setId', 'globals' => new Expression('COUNT(*) > 0')));
     return $this->getMapper()->getPaginator(null, null, null, array('x_tag' => array('table' => array('x_tag' => $xTag), 'where' => 'x_tag.setId = banner_set.id', 'columns' => array('tags'), 'type' => Select::JOIN_LEFT), 'x_locale' => array('table' => array('x_locale' => $xLocale), 'where' => 'x_locale.setId = banner_set.id', 'columns' => array('locales'), 'type' => Select::JOIN_LEFT), 'x_global' => array('table' => array('x_global' => $xGlobal), 'where' => 'x_global.setId = banner_set.id', 'columns' => array('globals'), 'type' => Select::JOIN_LEFT)));
 }
 public function fetchByUniqueRoles(array $roleNames)
 {
     $select = new Select(self::$_tableName);
     $select->group('role');
     $select->where->in('role', $roleNames);
     $rows = $this->selectWith($select);
     $roles = [];
     foreach ($rows as $row) {
         $row = $row->toArray();
         // The adapter's `params` column is JSON serialized.
         $row['params'] = $this->jsonDecodeIfPossible($row['params']);
         $roles[$row['role']] = $row;
     }
     return $roles;
 }
 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;
 }
예제 #7
0
 public function selectCart($cart_id = null)
 {
     //NOTICE:   get all items with cart_id == '$cart_id'
     //NOTICE:   if $cart_id don't passed, return num of all items in cart (admin needed)
     if ($cart_id) {
         $select = new Select();
         $select->from('cart');
         $select->join('images', "images.img_item_id = cart.item_id", array('img_link'), 'left');
         $select->where->equalTo('cart.cart_id', $cart_id);
         $select->group('cart.item_id');
         $resultSet = $this->cartTableGateway->selectWith($select);
     } else {
         $resultSet = $this->cartTableGateway->select()->count();
     }
     return $resultSet;
 }
예제 #8
0
파일: User.php 프로젝트: galaco/chatter
 public function getUserByUsername($username)
 {
     $selectPost = new Select();
     $selectPost->from('post');
     $selectPost->group('user_id');
     $selectPost->columns(['user_id', 'postcount' => new Expression('COUNT(user_id)')]);
     $predicate = new Predicate(null, Predicate::OP_AND);
     $predicate->equalTo('username', $username);
     $select = new Select();
     $select->from($this->tableName);
     $select->join(['po' => $selectPost], 'user.user_id = po.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
     $select->where($predicate);
     $select->columns(['user_id' => new Expression('user.user_id'), 'username', 'display_name', 'date_joined', 'role', 'title', 'avatar', 'post_signature', 'post_count' => new Expression('po.postcount')], false);
     $result = $this->select($select);
     return $result->toArray();
 }
예제 #9
0
 public function getWsr()
 {
     $dateStart = date('Y-m-01');
     $dateEnd = date('Y-m-t');
     $select = new Select(self::TABLE_NAME);
     $columns = array('projectName' => 'r_project_name', 'quality' => 'r_project_quality', 'schedule' => 'r_project_schedule', 'process' => 'r_project_process', 'effortVariance' => 'r_project_effort_variance', 'customerManagment' => 'r_project_customer_managment', 'currentMonthBillingTarget' => 'r_project_current_month_billing_target', 'comment' => 'r_project_description');
     $select->columns($columns);
     $subQry = new Select(TimeEntryTable::TABLE_NAME);
     $subQry->columns(array("projectId" => 'r_project_id', 'consumeHrs' => new Expression('SUM(r_issue_timelog_logged_hours)')));
     $subQry->group('r_project_id');
     $subQry2 = new Select(TimeEntryTable::TABLE_NAME);
     $subQry2->columns(array("projectID" => 'r_project_id', 'currentMonthConsumeHrs' => new Expression('SUM(r_issue_timelog_logged_hours)')));
     $subQry2->where->between('r_issue_timelog_date', $dateStart, $dateEnd);
     $subQry2->group('r_project_id');
     $subQry3 = new Select(IssueTable::TABLE_NAME);
     $subQry3->columns(array("sub3ID" => 'r_project_id', 'effort' => new Expression('SUM(r_issue_estimated_hours)')));
     $subQry3->where->between('r_issue_start_date', $dateStart, $dateEnd);
     $subQry3->where->between('r_issue_due_date', $dateStart, $dateEnd);
     $subQry3->where(array('r_issue_tracker_code' => 26));
     $subQry3->group('r_project_id');
     $subQry4 = new Select(IssueTable::TABLE_NAME);
     $subQry4->columns(array("sub4ID" => 'r_project_id', 'estimate' => new Expression('SUM(r_issue_estimated_hours)')));
     $subQry4->join(array('t' => TimeEntryTable::TABLE_NAME), 't.r_issue_id=r_issue.r_issue_id', array('issueID' => 'r_issue_id', 'spent' => new Expression('SUM(r_issue_timelog_logged_hours)')), Select::JOIN_LEFT);
     $subQry4->where(array('r_issue_tracker_code' => 26));
     $subQry4->where->between('r_issue_due_date', $dateStart, $dateEnd);
     //$subQry4->where->greaterThanOrEqualTo('r_issue_start_date',$dateStart);
     $subQry4->group('r_issue.r_project_id');
     $subQry5 = new Select(IssueTable::TABLE_NAME);
     $subQry5->columns(array("sub5ID" => 'r_project_id', 'totalEstimate' => new Expression('SUM(r_issue_estimated_hours)'), 'start_day_of_project' => new Expression('DAY(r_issue_start_date)'), 'end_day_of_month' => new Expression('DAY("2015-09-30")'), 'diffrence_dates' => new Expression('DATEDIFF(r_issue_due_date,r_issue_start_date)')));
     $subQry5->where->between('r_issue_start_date', $dateStart, $dateEnd);
     //$subQry5->where->greaterThan('r_issue_due_date',$dateEnd);
     $subQry5->where(array('r_issue_tracker_code' => 26));
     $subQry5->group('r_project_id');
     $select->join(array('issue' => IssueTable::TABLE_NAME), 'issue.r_project_id=r_project.r_project_id', array('owner' => new Expression('MAX(r_user_name)'), 'estimatedHours' => new Expression('SUM(r_issue_estimated_hours)'), 'percentComplete' => 'r_issue_completion_ratio', 'completionDate' => 'r_issue_target_completion_date', 'projectType' => 'r_project_type'), Select::JOIN_LEFT);
     $select->join(array('sub' => $subQry), 'sub.projectId = r_project.r_project_id', array('totalConsumeHrs' => 'consumeHrs'), Select::JOIN_LEFT);
     $select->join(array('sub2' => $subQry2), 'sub2.projectID = r_project.r_project_id', array('totalCurrentMnthConsumeHrs' => 'currentMonthConsumeHrs'), Select::JOIN_LEFT);
     $select->join(array('sub3' => $subQry3), 'sub3.sub3ID = r_project.r_project_id', array('effort' => 'effort'), Select::JOIN_LEFT);
     $select->join(array('sub4' => $subQry4), 'sub4.sub4ID = r_project.r_project_id', array('estimate' => 'estimate', 'spent' => 'spent'), Select::JOIN_LEFT);
     $select->join(array('sub5' => $subQry5), 'sub5.sub5ID = r_project.r_project_id', array('totalEstimate' => 'totalEstimate', 'startDay' => 'start_day_of_project', 'endDay' => 'end_day_of_month', 'diffDate' => 'diffrence_dates'), Select::JOIN_LEFT);
     $select->where(array('r_issue_tracker_code' => 26));
     $select->group('r_project_name', 'issue.r_user_name');
     $sql = new Sql($this->tableGateway->getAdapter());
     $db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
     return $this->getSqlContent($db, $sql, $select);
 }
예제 #10
0
 public function fetchById($item_id, $param = 'single')
 {
     //die($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->equalTo('items.item_id', $item_id);
     switch ($param) {
         case 'single':
             $select->group('items.item_id');
             break;
         case 'group':
             break;
     }
     $resultSet = $this->tableGateway->selectWith($select);
     return $resultSet;
 }
예제 #11
0
파일: Post.php 프로젝트: galaco/chatter
 public function getPostsByDate($threadId, $pageNo = 1)
 {
     $selectPost = new Select();
     $selectPost->from('post');
     $selectPost->group('user_id');
     $selectPost->columns(['user_id', 'postcount' => new Expression('COUNT(user_id)')]);
     $predicate = new Predicate(null, Predicate::OP_AND);
     $predicate->equalTo('thread_id', $threadId);
     $select = new Select();
     $select->from($this->tableName);
     $select->order('date_added ASC');
     $select->join(['po' => $selectPost], 'post.user_id = po.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
     $select->join('user', 'user.user_id = post.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
     $select->limit(20);
     $select->offset(($pageNo - 1) * 20);
     $select->columns(['id', 'date_added', 'content', 'last_updated', 'username' => new Expression('user.username'), 'user_title' => new Expression('user.title'), 'user_avatar' => new Expression('user.avatar'), 'user_signature' => new Expression('user.post_signature'), 'user_joined' => new Expression('user.date_joined'), 'user_postcount' => new Expression('po.postcount')], false);
     $select->where($predicate);
     $result = $this->select($select);
     return $result->toArray();
 }
 public function getTimeline()
 {
     $select = new Select();
     $select->from(array('fl' => 'fg_flicks'));
     $select->columns(array('*'));
     $select->join(array('fr' => 'fg_friends'), 'fl.owner = fr.user_one OR fl.owner = fr.user_two', array());
     $select->join(array('u' => 'fg_users'), 'fl.owner = u.id', array("nickname"));
     $where = new Where();
     $or = $where->nest();
     $or->equalTo('fr.user_one', $this->user_id);
     $or->OR->equalTo('fr.user_two', $this->user_id);
     $or->unnest();
     $where->AND->equalTo('state', 1);
     $where->AND->notEqualTo('fl.owner', $this->user_id);
     $select->where($where);
     $select->order(array('id DESC'));
     $select->group("id");
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
     $resultSet = $statement->execute();
     // var_dump($resultSet->current());
     // exit;
     return $resultSet;
 }
예제 #13
0
 public function getProjectList()
 {
     $dateStart = '2015-08-01';
     $dateEnd = '2015-08-31';
     $select = new Select(self::TABLE_NAME);
     $columns = array('projectName' => 'r_project_name', 'quality' => 'r_project_quality', 'schedule' => 'r_project_schedule', 'process' => 'r_project_process', 'effortVariance' => 'r_project_effort_variance', 'comment' => 'r_project_description');
     $select->columns($columns);
     $subQry = new Select(TimeEntryTable::TABLE_NAME);
     $subQry->columns(array("projectId" => 'r_project_id', 'consumeHrs' => new Expression('SUM(r_issue_timelog_logged_hours)')));
     $subQry->group('r_project_id');
     $subQry2 = new Select(TimeEntryTable::TABLE_NAME);
     $subQry2->columns(array("projectID" => 'r_project_id', 'currentMonthConsumeHrs' => new Expression('SUM(r_issue_timelog_logged_hours)')));
     $subQry2->where->between('r_issue_timelog_date', $dateStart, $dateEnd);
     $subQry2->group('r_project_id');
     $select->join(array('issue' => ProductionIssueTable::TABLE_NAME), 'issue.r_project_id=r_project.r_project_id', array('owner' => new Expression('MAX(r_user_name)'), 'estimatedHours' => new Expression('SUM(r_issue_estimated_hours)'), 'percentRemain' => 'r_issue_completion_ratio', 'completionDate' => 'r_issue_due_date', 'hoursNeedToFinish' => new Expression('SUM(r_hours_needed_to_finish)')), Select::JOIN_INNER);
     $select->join(array('sub' => $subQry), 'sub.projectId = r_project.r_project_id', array('totalConsumeHrs' => 'consumeHrs'), Select::JOIN_INNER);
     $select->join(array('sub2' => $subQry2), 'sub2.projectID = r_project.r_project_id', array('totalCurrentMnthConsumeHrs' => 'currentMonthConsumeHrs'), Select::JOIN_INNER);
     $select->where(array('r_issue_tracker_code' => 26));
     $select->group('r_project_name', 'issue.r_user_name');
     $sql = new Sql($this->tableGateway->getAdapter());
     $db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
     return $this->getSqlContent($db, $sql, $select);
 }
예제 #14
0
 public function getSources($active = true, $format = 'array')
 {
     $select = new Select();
     $select->from('form');
     $select->columns(array('id' => 'form.id', 'source' => 'form.source'), false);
     if ($active) {
         $select->join('lead', 'form.id = lead.formid', array('formid'), $select::JOIN_INNER);
     }
     $select->order(array("form.source asc"));
     $select->group('form.id');
     // echo "<pre>" . print_r($select->getSqlString(), true) . "</pre>";
     $resultSet = $this->tableGateways['form']->selectWith($select);
     return $format == 'array' ? $resultSet->toArray() : $resultSet;
 }
 public function fetchMessagesInbox($uid, $messageId = null)
 {
     $select = new Select($this->table);
     $select->columns(['message_id' => 'response_to', 'thread_length' => new Expression('COUNT(`directus_messages`.`id`)')])->join('directus_messages_recipients', 'directus_messages_recipients.message_id = directus_messages.id', ['id', 'message_id', 'recipient', 'read', 'group']);
     $select->where->equalTo('recipient', $uid);
     if (!empty($messageId)) {
         if (gettype($messageId) == 'array') {
             $select->where->in('response_to', $messageId)->or->in('directus_messages.id', $messageId);
         } else {
             $select->where->nest->equalTo('response_to', $messageId)->or->equalTo('directus_messages.id', $messageId)->unnest;
         }
     }
     $select->group(['directus_messages_recipients.id', 'directus_messages_recipients.message_id', 'directus_messages_recipients.recipient', 'directus_messages_recipients.read', 'directus_messages_recipients.group', 'response_to', 'directus_messages.id'])->order('directus_messages.id DESC');
     $result = $this->selectWith($select)->toArray();
     $messageIds = [];
     foreach ($result as $message) {
         $messageIds[] = $message['message_id'];
     }
     if (sizeof($messageIds) == 0) {
         return [];
     }
     $result = $this->fetchMessageThreads($messageIds, $uid);
     if (sizeof($result) == 0) {
         return [];
     }
     $resultLookup = [];
     $ids = [];
     // Grab ids;
     foreach ($result as $item) {
         $ids[] = $item['id'];
     }
     $directusMessagesTableGateway = new DirectusMessagesRecipientsTableGateway($this->acl, $this->adapter);
     $recipients = $directusMessagesTableGateway->fetchMessageRecipients($ids);
     foreach ($result as $item) {
         $item['responses'] = ['rows' => []];
         $item['recipients'] = implode(',', $recipients[$item['id']]);
         $resultLookup[$item['id']] = $item;
     }
     foreach ($result as $item) {
         if ($item['response_to'] != NULL) {
             // Move it to resultLookup
             unset($resultLookup[$item['id']]);
             $item = $this->parseRecord($item);
             $resultLookup[$item['response_to']]['responses']['rows'][] = $item;
         }
     }
     $result = array_values($resultLookup);
     foreach ($result as &$row) {
         $row = $this->parseRecord($row);
     }
     // Add date_updated
     // Update read
     foreach ($result as &$message) {
         $responses = $message['responses']['rows'];
         /*foreach ($responses as $response) {
               if($response['read'] == "0") {
                   $message['read'] = "0";
                   break;
               }
           }*/
         $lastResponse = end($responses);
         if ($lastResponse) {
             $message['date_updated'] = $lastResponse['datetime'];
         } else {
             $message['date_updated'] = $message['datetime'];
         }
     }
     return $result;
 }
예제 #16
0
 public function buildSelect(array $conditions)
 {
     $select = new Select();
     if (isset($conditions['from']) && $conditions['from']) {
         $select->from($conditions['from']);
     }
     if (isset($conditions['columns']) && $conditions['columns']) {
         $select->columns($conditions['columns']);
     }
     if (isset($conditions['joins']) && is_array($conditions['joins'])) {
         foreach ($conditions['joins'] as $join) {
             list($join_name, $join_on, $join_columns) = $join;
             if (!isset($join[3])) {
                 $join_type = $select::JOIN_INNER;
             } else {
                 switch ($join[3]) {
                     case 'INNER':
                         $join_type = $select::JOIN_INNER;
                         break;
                     case 'OUTER':
                         $join_type = $select::JOIN_OUTER;
                         break;
                     case 'LEFT':
                         $join_type = $select::JOIN_LEFT;
                         break;
                     case 'RIGHT':
                         $join_type = $select::JOIN_RIGHT;
                         break;
                     default:
                         $join_type = $select::JOIN_INNER;
                         break;
                 }
             }
             if (is_array($join_name)) {
                 $join_table = key($join_name);
             } else {
                 $join_table = $join_name;
             }
             $join_ons = new Predicate\PredicateSet();
             if (is_array($join_on)) {
                 foreach ($join_on as $p) {
                     if ($p instanceof Predicate\PredicateInterface) {
                         $join_ons->addPredicate($p);
                     } else {
                         if (is_array($p) && count($p) == 3) {
                             $join_ons->addPredicate(new Predicate\Operator($p[0], $p[1], $p[2]));
                         } else {
                             if (is_string($p)) {
                                 $join_ons->addPredicate(new Predicate\Expression($p));
                                 if (strpos($p, $join_table . ".bool_deleted") !== false) {
                                     unset($join_table);
                                 }
                             }
                         }
                     }
                 }
             } else {
                 $join_ons->addPredicate(new Predicate\Expression($join_on));
                 if (strpos($join_on, $join_table . ".bool_deleted") !== false) {
                     unset($join_table);
                 }
             }
             if (isset($join_table)) {
                 $join_ons->addPredicate(new Predicate\Expression($join_table . ".bool_deleted = '0'"));
             }
             $select->join($join_name, $join_ons, $join_columns, $join_type);
         }
     }
     if (isset($conditions['like']) && is_array($conditions['like'])) {
         foreach ($conditions['like'] as $column => $val) {
             $conditions['where'][] = new Predicate\Like($column, '%' . $val . '%');
         }
     }
     if (isset($conditions['like_begin']) && is_array($conditions['like_begin'])) {
         foreach ($conditions['like_begin'] as $column => $val) {
             $conditions['where'][] = new Predicate\Like($column, $val . '%');
         }
     }
     if (isset($conditions['like_end']) && is_array($conditions['like_end'])) {
         foreach ($conditions['like_end'] as $column => $val) {
             $conditions['where'][] = new Predicate\Like($column, '%' . $val);
         }
     }
     $select->where($conditions['where']);
     if (isset($conditions['group']) && $conditions['group']) {
         $select->group($conditions['group']);
     }
     if (isset($conditions['having']) && $conditions['having']) {
         if (is_array($conditions['having'])) {
             foreach ($conditions['having'] as $combination => $having) {
                 if ($combination !== Predicate\PredicateSet::OP_OR) {
                     $combination = Predicate\PredicateSet::OP_AND;
                 }
                 $select->having($having, $combination);
             }
         } else {
             $select->having($conditions['having']);
         }
     }
     if (isset($conditions['order'])) {
         if (is_array($conditions['order']) && count($conditions['order']) > 0) {
             $orderBy = array();
             foreach ($conditions['order'] as $field => $sort) {
                 if (is_int($field)) {
                     $orderBy[] = $sort;
                 } else {
                     $orderBy[] = $field . ' ' . $sort;
                 }
             }
             $select->order($orderBy);
         } else {
             if ($conditions['order']) {
                 $select->order($conditions['order']);
             }
         }
     }
     if (isset($conditions['limit']) && $conditions['limit']) {
         $select->limit($conditions['limit']);
     }
     if (isset($conditions['offset']) && $conditions['offset']) {
         $select->offset($conditions['offset']);
     }
     //print_ar($conditions);
     //echo($select->getSqlString());exit;
     return $select;
 }
예제 #17
0
파일: SelectTest.php 프로젝트: nuklehed/zf2
 /**
  * @testdox unit test: Test group() returns same Select object (is chainable)
  * @covers Zend\Db\Sql\Select::group
  */
 public function testGroup()
 {
     $select = new Select();
     $return = $select->group(array('col1', 'col2'));
     $this->assertSame($select, $return);
     return $return;
 }
 public function applyParamsToTableEntriesSelect(array $params, Select $select, array $schema, $hasActiveColumn = false)
 {
     $tableName = $this->getTable();
     if (isset($params['group_by'])) {
         $select->group($tableName . '.' . $params['group_by']);
     } else {
         $select->group($tableName . '.' . $this->primaryKeyFieldName);
     }
     //If this is a relational order, than it is an array.
     if (is_array($params['orderBy'])) {
         $select->join(array('jsort' => $params['orderBy']['junction_table']), 'jsort.' . $params['orderBy']['jkeyRight'] . ' = ' . $tableName . '.' . $this->primaryKeyFieldName, array(), $select::JOIN_LEFT);
         $select->join(array('rsort' => $params['orderBy']['related_table']), 'rsort.id = jsort.' . $params['orderBy']['jkeyLeft'], array(), $select::JOIN_LEFT);
         $select->order('rsort.title', $params['orderDirection']);
     } else {
         $select->order(implode(' ', array($params['orderBy'], $params['orderDirection'])));
     }
     if (isset($params['perPage']) && isset($params['currentPage'])) {
         $select->limit($params['perPage'])->offset($params['currentPage'] * $params['perPage']);
     }
     // Are we sorting on a relationship?
     foreach ($schema as $column) {
         if ($column['column_name'] != $params['orderBy']) {
             continue;
         }
         // Must have defined table_related
         if (!isset($column['relationship']) || !is_array($column['relationship']) || !isset($column['relationship']['table_related'])) {
             break;
         }
         // Must have defined visible_column
         if (!isset($column['options']) || !is_array($column['options']) || !isset($column['options']['visible_column'])) {
             break;
         }
         $relatedTable = $column['relationship']['table_related'];
         $visibleColumn = $column['options']['visible_column'];
         $keyLeft = $params['table_name'] . "." . $params['orderBy'];
         // @todo it's wrong to assume PKs are "id" but this is currently endemic to directus6
         $keyRight = $relatedTable . ".id";
         $joinedSortColumn = $relatedTable . "." . $visibleColumn;
         $select->reset(Select::ORDER)->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT)->order("{$joinedSortColumn} " . $params['orderDirection']);
         break;
     }
     // Note: be sure to explicitly check for null, because the value may be
     // '0' or 0, which is meaningful.
     if (null !== $params[STATUS_COLUMN_NAME] && $hasActiveColumn) {
         $haystack = is_array($params[STATUS_COLUMN_NAME]) ? $params[STATUS_COLUMN_NAME] : explode(",", $params[STATUS_COLUMN_NAME]);
         if (!isset($params['table_name']) || empty($params['table_name'])) {
             $tableName = $this->getTable();
         } else {
             $tableName = $params['table_name'];
         }
         $select->where->in($tableName . '.' . STATUS_COLUMN_NAME, $haystack);
     }
     // Select only ids from the ids if provided
     if (array_key_exists('ids', $params)) {
         $entriesIds = array_filter(explode(',', $params['ids']), 'is_numeric');
         if (count($entriesIds) > 0) {
             $select->where->in($this->getTable() . '.' . $this->primaryKeyFieldName, $entriesIds);
         }
     }
     // Where
     $select->where->nest->expression('-1 = ?', $params[$this->primaryKeyFieldName])->or->equalTo($tableName . '.' . $this->primaryKeyFieldName, $params[$this->primaryKeyFieldName])->unnest;
     // very very rudimentary ability to supply where conditions to fetch...
     // at the moment, only 'equalTo' and 'between' are supported... also, the 'total' key returned
     // in the json does not reflect these filters...
     // -MG
     if (array_key_exists('where', $params)) {
         $outer = $select->where->nest;
         foreach ($params['where'] as $whereCond) {
             $type = $whereCond['type'];
             $column = $whereCond['column'];
             if ($type == 'equalTo') {
                 $val = $whereCond['val'];
                 if (is_array($val)) {
                     $where = $select->where->nest;
                     foreach ($val as $currentval) {
                         $where->equalTo($column, $currentval);
                         if ($currentval != end($val)) {
                             $where->or;
                         }
                     }
                     $where->unnest;
                 } else {
                     $outer->equalTo($column, $val);
                 }
             } else {
                 if ($type == 'between') {
                     $val1 = $whereCond['val1'];
                     $val2 = $whereCond['val2'];
                     $outer->between($column, $val1, $val2);
                 }
             }
         }
         $outer->unnest;
     }
     //@TODO: Make this better
     if (isset($params['adv_where'])) {
         $select->where($params['adv_where']);
     }
     if (isset($params['adv_search']) && !empty($params['adv_search'])) {
         $i = 0;
         foreach ($params['adv_search'] as $search_col) {
             $target = array();
             foreach ($schema as $col) {
                 if ($col['id'] == $search_col['id']) {
                     $target = $col;
                     break;
                 }
             }
             if (empty($target)) {
                 continue;
             }
             // TODO: fix this, it must be refactored
             if (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOMANY") {
                 $relatedTable = $target['relationship']['table_related'];
                 $relatedAliasName = $relatedTable . "_" . $i;
                 if ($target['relationship']['type'] == "MANYTOMANY") {
                     $junctionTable = $target['relationship']['junction_table'];
                     $jkl = $target['relationship']['junction_key_left'];
                     $jkr = $target['relationship']['junction_key_right'];
                     $keyleft = $params['table_name'] . ".id";
                     $keyRight = $junctionTable . '.' . $jkl;
                     $jkeyleft = $junctionTable . '.' . $jkr;
                     $jkeyright = $relatedAliasName . ".id";
                     $select->join($junctionTable, "{$keyleft} = {$keyRight}", array(), Select::JOIN_INNER);
                 } else {
                     $select->join(array($relatedAliasName => $relatedTable), $tableName . '.' . $target['column_name'] . " = " . $relatedAliasName . ".id", array(), Select::JOIN_INNER);
                 }
                 $relatedTableMetadata = TableSchema::getSchemaArray($relatedTable);
                 if ($search_col['type'] == "like") {
                     $select->join(array($relatedAliasName => $relatedTable), "{$jkeyleft} = {$jkeyright}", array(), Select::JOIN_INNER);
                     $search_col['value'] = "%" . $search_col['value'] . "%";
                     if (isset($target['options']['filter_column'])) {
                         $targetCol = $target['options']['filter_column'];
                     } else {
                         $targetCol = $target['options']['visible_column'];
                     }
                     foreach ($relatedTableMetadata as $col) {
                         if ($col['id'] == $targetCol) {
                             if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
                                 $where = $select->where->nest;
                                 $columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
                                 $columnName = $relatedAliasName . "." . $columnName;
                                 $like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", $search_col['value']);
                                 $where->addPredicate($like, Predicate\Predicate::OP_OR);
                                 $where->unnest;
                             }
                         }
                     }
                 } else {
                     $select->where($jkeyleft . ' = ' . $this->adapter->platform->quoteValue($search_col['value']));
                 }
             } elseif (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOONE") {
                 $relatedTable = $target['relationship']['table_related'];
                 $keyLeft = $this->getTable() . "." . $target['relationship']['junction_key_left'];
                 $keyRight = $relatedTable . ".id";
                 $filterColumn = $target['options']['filter_column'];
                 $joinedFilterColumn = $relatedTable . "." . $filterColumn;
                 // do not let join this table twice
                 // TODO: do a extra checking in case it's being used twice
                 // and none for sorting
                 if ($target['column_name'] != $params['orderBy']) {
                     $select->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT);
                 }
                 if ($search_col['type'] == 'like') {
                     $searchLike = '%' . $search_col['value'] . '%';
                     $spec = function (Where $where) use($joinedFilterColumn, $searchLike) {
                         $where->like($joinedFilterColumn, $searchLike);
                     };
                     $select->where($spec);
                 } else {
                     $select->where($search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value']));
                 }
             } else {
                 if ($target['type'] == "DATETIME" && strpos($search_col['value'], " ") == false) {
                     $select->where('date(' . $tableName . '.' . $search_col['id'] . ") = " . $this->adapter->platform->quoteValue($search_col['value']));
                 } else {
                     if ($search_col['type'] == "like") {
                         $select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue("%" . $search_col['value'] . "%"));
                     } else {
                         $select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value']));
                     }
                 }
             }
             $i++;
         }
     } else {
         if (isset($params['search']) && !empty($params['search'])) {
             $params['search'] = "%" . $params['search'] . "%";
             $where = $select->where->nest;
             foreach ($schema as $col) {
                 if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
                     $columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
                     $like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", strtolower($params['search']));
                     $where->addPredicate($like, Predicate\Predicate::OP_OR);
                 }
             }
             $where->unnest;
         }
     }
     return $select;
 }
 /**
  *
  * @return Select
  */
 public function getProductDescSelect(array $params)
 {
     $lang = $params['lang'];
     $select = new Select();
     $select->from(['p' => 'product'], [])->join(['p18' => 'product_translation'], new Expression("p18.product_id = p.product_id and p18.lang='{$lang}'"), [], $select::JOIN_LEFT)->join(['pb' => 'product_brand'], new Expression('pb.brand_id = p.brand_id'), [])->join(['pstub' => 'product_stub'], new Expression('pstub.product_stub_id = p.product_stub_id'), [], $select::JOIN_LEFT)->join(['pstub18' => 'product_stub_translation'], new Expression("pstub.product_stub_id = pstub18.product_stub_id and pstub18.lang='{$lang}'"), [], $select::JOIN_LEFT)->join(['pc' => 'product_category'], new Expression('p.category_id = pc.category_id'), [], $select::JOIN_LEFT)->join(['pc18' => 'product_category_translation'], new Expression("pc.category_id = pc18.category_id and pc18.lang='{$lang}'"), [], $select::JOIN_LEFT)->join(['pg' => 'product_group'], new Expression('pg.group_id = p.group_id'), [], $select::JOIN_LEFT)->join(['pg18' => 'product_group_translation'], new Expression("pg18.group_id = pg.group_id and pg18.lang='{$lang}'"), [], $select::JOIN_LEFT)->join(['ppl' => 'product_pricelist'], new Expression("ppl.product_id = p.product_id"), [], $select::JOIN_INNER)->join(['pl' => 'pricelist'], new Expression("ppl.pricelist_id = pl.pricelist_id"), [], $select::JOIN_INNER)->join(['pt' => 'product_type'], new Expression('p.type_id = pt.type_id'), [], $select::JOIN_LEFT)->join(['pst' => 'product_status'], new Expression('pst.status_id = ppl.status_id'), [], $select::JOIN_LEFT)->join(['serie' => 'product_serie'], new Expression('serie.serie_id = p.serie_id'), [], $select::JOIN_LEFT);
     $columns = ['product_id' => new Expression('p.product_id'), 'reference' => new Expression('p.reference'), 'brand_id' => new Expression('p.brand_id'), 'brand_reference' => new Expression('pb.reference'), 'category_id' => new Expression('p.category_id'), 'category_reference' => new Expression('pc.reference'), 'category_title' => new Expression('pc18.title'), 'category_breadcrumb' => new Expression('pc18.breadcrumb'), 'serie_id' => new Expression('serie.serie_id'), 'serie_reference' => new Expression('serie.reference'), 'product_type_id' => new Expression('pt.type_id'), 'product_type_reference' => new Expression('pt.reference'), 'product_status_id' => new Expression('pst.status_id'), 'product_status_reference' => new Expression('pst.reference'), 'product_stub_id' => new Expression('pstub.product_stub_id'), 'product_stub_reference' => new Expression('pstub.reference'), 'invoice_title' => new Expression('COALESCE(p18.invoice_title, p.invoice_title)'), 'title' => new Expression('COALESCE(p18.title, p.title)'), 'description' => new Expression('
                 CONCAT_WS("\\n",
                     pstub18.description_header,
                     if ((pstub.product_stub_id is not null and p.parent_id is null), 
                             null, 
                             if (p.product_stub_id is null,
                                 COALESCE(p18.description, p.description),
                                 p18.description
                             )
                         ),
                     pstub18.description_footer
                 )    
                 '), 'characteristic' => new Expression('COALESCE(p18.characteristic, p.characteristic)'), 'created_at' => new Expression('p.created_at'), 'weight' => new Expression('p.weight'), 'weight_gross' => new Expression('p.weight_gross'), 'volume' => new Expression('p.volume'), 'length' => new Expression('p.length'), 'width' => new Expression('p.width'), 'height' => new Expression('p.height'), 'diameter' => new Expression('p.diameter')];
     $select->columns(array_merge($columns, ['product_status_references' => new Expression('GROUP_CONCAT(distinct pst.reference order by pst.reference)'), 'flag_end_of_lifecycle' => new Expression('MAX(pst.flag_end_of_lifecycle)'), 'end_of_lifecycle_pricelists' => new Expression('GROUP_CONCAT(distinct if(pst.flag_end_of_lifecycle = 1, pl.reference, null) ORDER BY pl.reference)'), 'active_pricelists' => new Expression("GROUP_CONCAT(distinct pl.reference order by pl.reference)")]), true);
     $select->group(array_keys($columns));
     $select->order('pc.category_id, pc.sort_index, p.reference');
     // Standard where clause, excluding products that are not in any pricelists:
     $select->where(['p.flag_active' => 1, 'ppl.flag_active' => 1, "pb.reference <> '****'"]);
     return $select;
 }
예제 #20
0
파일: Select.php 프로젝트: t4web/base
 public function group($group)
 {
     return $this->zendSelect->group($group);
 }
예제 #21
0
 /**
  * Builds a new aggregated select from normal select
  * @param \Zend\Db\Sql\Select $select
  * @param \Application\Utils\QueryAggregateInterface[] $aggregates
  * @return Select
  */
 protected function aggregateSelect(Select $select, $aggregates, $prefix = '')
 {
     $columns = array();
     foreach ($aggregates as $aggregate) {
         $aggName = $aggregate->getAggregateName();
         if ($aggregate->getGroup()) {
             if ($aggName) {
                 $select->group($aggName);
             } else {
                 $select->group($aggregate->getAggregateExpression($prefix));
             }
         }
         if ($aggName) {
             $columns[$aggName] = $aggregate->getAggregateExpression($prefix);
         } else {
             $columns[] = $aggregate->getAggregateExpression($prefix);
         }
     }
     $select->columns($columns, false);
     return $select;
 }
예제 #22
0
 public function dailyGrowth()
 {
     $subSelect = new \Zend\Db\Sql\Select();
     $subSelect->from('resource')->columns(array('id', 'created_date'));
     $subSelect->where->notEqualTo('node_status', \Application\Model\Resource::NODE_STATUS_DISABLED);
     $select = new \Zend\Db\Sql\Select();
     $select->from('calendar_table')->columns(array('*', new Expression('COUNT(resource.id) AS total'), new Predicate\Expression('YEAR(calendar_table.calendar_date) AS year'), new Predicate\Expression('MONTH(calendar_table.calendar_date) AS month'), new Predicate\Expression('MONTHNAME(calendar_table.calendar_date) AS monthname'), new Predicate\Expression('DAY(calendar_table.calendar_date) AS day')));
     $select->join(array('resource' => $subSelect), 'resource.created_date = calendar_table.calendar_date', array(), Select::JOIN_LEFT);
     $select->group('calendar_date');
     $statement = $this->getAdapter()->createStatement();
     $select->prepareStatement($this->getAdapter(), $statement);
     $resultset = new ResultSet();
     return iterator_to_array($resultset->initialize($statement->execute()));
 }
예제 #23
0
 public function getPracStatsByCities($user_type_id, $date, $status)
 {
     $total = 0;
     $data = array();
     $selectTotal = $this->tableGateway->getSql()->select();
     $selectTotal->columns(array(new Expression('count(id) as total')));
     $selectTotal->where('user_type_id = "' . $user_type_id . '" AND date_format(created_date,"%Y-%m-%d") >= "' . $date . '"');
     $status == 3 || $status == 10 ? $selectTotal->where('status_id = ' . $status . '') : $selectTotal->where('status_id IN (' . $status . ', 5)');
     //echo str_replace('"','',$selectTotal->getSqlString()); exit;
     $resultTotal = $this->tableGateway->selectwith($selectTotal)->current();
     $total = $resultTotal->total;
     $subquery = new Select('service_provider_address');
     $subquery->columns(array('*', new Expression('min(service_provider_address.address_id) AS adrs_id')));
     $subquery->group('user_id');
     $select = $this->tableGateway->getSql()->select();
     $select->columns(array(new Expression('users.user_name, date_format(date(users.created_date),"%Y-%m-%d") as created_date, COUNT( users.id ) AS users_count, state.state_name, address.state_id')));
     $user_type_id == 3 ? $select->join(array('address_link' => $subquery), 'address_link.user_id = users.id', array(), 'left') : $select->join(array('address_link' => 'user_address'), 'users.id = address_link.user_id', array(), 'left');
     $select->join('address', 'address_link.address_id = address.id', array(), 'left');
     $select->join('state', 'address.state_id = state.id', array(), 'inner');
     $select->where('user_type_id = "' . $user_type_id . '" AND date_format(users.created_date,"%Y-%m-%d") >= "' . $date . '"');
     $status == 3 || $status == 10 ? $select->where('users.status_id = ' . $status . '') : $select->where('users.status_id IN (' . $status . ', 5)');
     $select->group('address.state_id');
     $select->order('users_count DESC');
     $select->limit(10);
     //echo str_replace('"','',$select->getSqlString()); exit;
     $results = $this->tableGateway->selectwith($select);
     foreach ($results as $result) {
         //print_r($result);
         $row = array('state_name' => $result->state_name, 'users_count' => $result->users_count, 'total' => 0, 'growth' => 0);
         $row['total'] = $total > 0 ? round($result->users_count / $total * 100) : 0;
         $growthSubquery = new Select('service_provider_address');
         $growthSubquery->columns(array('*', new Expression('min(service_provider_address.address_id) AS adrs_id')));
         $growthSubquery->group('user_id');
         $growthSelect = $this->tableGateway->getSql()->select();
         $growthSelect->columns(array(new Expression('users.user_name, date_format(date(users.created_date),"%Y-%m-%d") as created_date, COUNT( users.id ) AS users_count, state.state_name, address.state_id')));
         $user_type_id == 3 ? $growthSelect->join(array('address_link' => $growthSubquery), 'address_link.user_id = users.id', array(), 'left') : $growthSelect->join(array('address_link' => 'user_address'), 'users.id = address_link.user_id', array(), 'left');
         $growthSelect->join('address', 'address_link.address_id = address.id', array(), 'left');
         $growthSelect->join('state', 'address.state_id = state.id', array(), 'inner');
         $status == 3 || $status == 10 ? $growthSelect->where('users.status_id = ' . $status . '') : $growthSelect->where('users.status_id IN (' . $status . ', 5)');
         $growthSelect->where('user_type_id = "' . $user_type_id . '" AND state.id = ' . $result->state_id);
         switch ($date) {
             case date("Y-m-d", strtotime("now")):
                 $growthSelect->where("date_format(users.created_date,'%Y-%m-%d') = '" . date("Y-m-d", strtotime("-1 days")) . "'");
                 break;
             case date("Y-m-d", strtotime("-1 week")):
                 $growthSelect->where("date_format(users.created_date,'%Y-%m-%d') BETWEEN '" . date("Y-m-d", strtotime("-14 days")) . "' AND '" . date("Y-m-d", strtotime("-7 days")) . "'");
                 break;
             case date("Y-m-d", strtotime("-1 month")):
                 $growthSelect->where("date_format(users.created_date,'%Y-%m-%d') BETWEEN '" . date("Y-m-d", strtotime("-2 month -1 days")) . "' AND '" . date("Y-m-d", strtotime("-1 month -1 days")) . "'");
                 break;
             case date("Y-m-d", strtotime("-1 year")):
                 $growthSelect->where("date_format(users.created_date,'%Y-%m-%d') BETWEEN '" . date("Y-m-d", strtotime("-2 year")) . "' AND '" . date("Y-m-d", strtotime("-1 year")) . "'");
                 break;
         }
         //echo str_replace('"','',$select->getSqlString()); exit;
         $growth = $this->tableGateway->selectwith($growthSelect)->current();
         //echo '<pre>'; print_r($growth);
         $row['growth'] = isset($growth->users_count) && $growth->users_count > 0 ? round($result->users_count - $growth->users_count) / $growth->users_count * 100 : ($result->users_count - $growth->users_count) * 100;
         $data[] = $row;
     }
     return $data;
 }
 public function fetchPerTable($groupId)
 {
     // Don't include tables that can't have privileges changed
     /*$blacklist = array(
           'directus_columns',
           'directus_ip_whitelist',
           'directus_messages_recipients',
           'directus_preferences',
           'directus_privileges',
           'directus_settings',
           'directus_social_feeds',
           'directus_social_posts',
           'directus_storage_adapters',
           'directus_tab_privileges',
           'directus_tables',
           'directus_ui',
           'directus_users_copy'
       );*/
     $blacklist = array();
     $select = new Select($this->table);
     $select->where->equalTo('group_id', $groupId);
     $select->group(array('group_id', 'table_name', 'status_id'));
     $rowset = $this->selectWith($select);
     $rowset = $rowset->toArray();
     $tableSchema = new TableSchema();
     $tables = $tableSchema->getTablenames();
     $privileges = array();
     $privilegesHash = array();
     foreach ($rowset as $item) {
         if (in_array($item['table_name'], $blacklist)) {
             continue;
         }
         $privilegesHash[$item['table_name']] = $item;
         $privileges[] = $item;
     }
     foreach ($tables as $table) {
         if (in_array($table, $blacklist)) {
             continue;
         }
         if (array_key_exists($table, $privilegesHash)) {
             continue;
         }
         $item = array('table_name' => $table, 'group_id' => $groupId, 'status_id' => null);
         $privileges[] = $item;
     }
     // sort ascending
     usort($privileges, function ($a, $b) {
         return strcmp($a['table_name'], $b['table_name']);
     });
     return $privileges;
 }
예제 #25
0
 function countActiveOld($no_active = false)
 {
     $select = new Select($this->table);
     return array('active' => 0, 'inactive' => 0, 'trash' => 0);
     $result = array('active' => 0);
     if ($no_active) {
         $select->columns(array('count' => new \Zend\Db\Sql\Expression('COUNT(*)'), STATUS_COLUMN_NAME => STATUS_COLUMN_NAME));
     } else {
         $select->columns(array(new \Zend\Db\Sql\Expression('CASE ' . STATUS_COLUMN_NAME . 'WHEN 0 THEN \'trash\'
           WHEN 1 THEN \'active\'
           WHEN 2 THEN \'active\'
         END AS ' . STATUS_COLUMN_NAME), 'count' => new \Zend\Db\Sql\Expression('COUNT(*)')));
         $select->group(STATUS_COLUMN_NAME);
     }
     $rows = $this->selectWith($select)->toArray();
     print_r($rows);
     die;
     while ($row = $sth->fetch(\PDO::FETCH_ASSOC)) {
         $result[$row[STATUS_COLUMN_NAME]] = (int) $row['count'];
     }
     $total = 0;
     return $result;
 }
예제 #26
0
 public function getSubscriptionByStates($per = 'day')
 {
     $subquery = new Select('service_provider_address');
     $subquery->columns(array('*', new Expression('min(service_provider_address.address_id) AS adrs_id')));
     $subquery->group('user_id');
     switch ($per) {
         case 'day':
             $select = $this->tableGateway->getSql()->select();
             $select->columns(array(new Expression('COUNT(address.state_id) AS total, state_name, address.state_id')));
             $select->join('invoice', 'invoice.id = user_subscriptions.invoice_id', array(), 'inner');
             $select->join(array('address_link' => $subquery), 'address_link.user_id = user_subscriptions.user_id', array(), 'inner');
             $select->join('address', 'address.id = address_link.address_id', array(), 'inner');
             $select->join('state', 'state.id = address.state_id', array(), 'inner');
             $select->where("DATE_FORMAT(invoice.created_date, '%Y-%m-%d') = '" . date('Y-m-d') . "'");
             $select->group('address.state_id');
             $select->limit(10);
             break;
         case 'week':
             $select = $this->tableGateway->getSql()->select();
             $select->columns(array(new Expression('COUNT(address.state_id) AS total, state_name, address.state_id')));
             $select->join('invoice', 'invoice.id = user_subscriptions.invoice_id', array(), 'inner');
             $select->join(array('address_link' => $subquery), 'address_link.user_id = user_subscriptions.user_id', array(), 'inner');
             $select->join('address', 'address.id = address_link.address_id', array(), 'inner');
             $select->join('state', 'state.id = address.state_id', array(), 'inner');
             $select->where("DATE_FORMAT(invoice.created_date, '%Y-%m-%d') BETWEEN '" . date('Y-m-d', strtotime('-6 days')) . "' AND '" . date('Y-m-d') . "'");
             $select->group('address.state_id');
             $select->limit(10);
             break;
         case 'month':
             $select = $this->tableGateway->getSql()->select();
             $select->columns(array(new Expression('COUNT(address.state_id) AS total, state_name, address.state_id')));
             $select->join('invoice', 'invoice.id = user_subscriptions.invoice_id', array(), 'inner');
             $select->join(array('address_link' => $subquery), 'address_link.user_id = user_subscriptions.user_id', array(), 'inner');
             $select->join('address', 'address.id = address_link.address_id', array(), 'inner');
             $select->join('state', 'state.id = address.state_id', array(), 'inner');
             $select->where("DATE_FORMAT(invoice.created_date, '%Y-%m-%d') BETWEEN '" . date('Y-m-d', strtotime('-1 month')) . "' AND '" . date('Y-m-d') . "'");
             $select->group('address.state_id');
             $select->limit(10);
             break;
         case 'year':
             $select = $this->tableGateway->getSql()->select();
             $select->columns(array(new Expression('COUNT(address.state_id) AS total, state_name, address.state_id')));
             $select->join('invoice', 'invoice.id = user_subscriptions.invoice_id', array(), 'inner');
             $select->join(array('address_link' => $subquery), 'address_link.user_id = user_subscriptions.user_id', array(), 'inner');
             $select->join('address', 'address.id = address_link.address_id', array(), 'inner');
             $select->join('state', 'state.id = address.state_id', array(), 'inner');
             $select->where("DATE_FORMAT(invoice.created_date, '%Y-%m-%d') BETWEEN '" . date('Y-m-d', strtotime('-1 year')) . "' AND '" . date('Y-m-d') . "'");
             $select->group('address.state_id');
             $select->limit(10);
             break;
     }
     //echo str_replace('"', '', $select->getSqlString()); exit;
     $results = $this->tableGateway->selectwith($select);
     $subscriptions = array();
     $total = 0;
     foreach ($results as $result) {
         if ($result->state_name != "") {
             $subquery = new Select('service_provider_address');
             $subquery->columns(array('*', new Expression('min(service_provider_address.address_id) AS adrs_id')));
             $subquery->group('user_id');
             $growth = $this->tableGateway->getSql()->select();
             $growth->columns(array(new Expression('COUNT(address.state_id) AS total, state_name, address.state_id')));
             $growth->join('invoice', 'invoice.id = user_subscriptions.invoice_id', array(), 'inner');
             $growth->join(array('address_link' => $subquery), 'address_link.user_id = user_subscriptions.user_id', array(), 'inner');
             $growth->join('address', 'address.id = address_link.address_id', array(), 'inner');
             $growth->join('state', 'state.id = address.state_id', array(), 'inner');
             switch ($per) {
                 case 'day':
                     $growth->where("address.state_id = " . $result->state_id . " AND DATE_FORMAT(invoice.created_date, '%Y-%m-%d') = '" . date('Y-m-d', strtotime('-1 days')) . "'");
                     break;
                 case 'week':
                     $growth->where("address.state_id = " . $result->state_id . " AND DATE_FORMAT(invoice.created_date, '%Y-%m-%d') BETWEEN '" . date('Y-m-d', strtotime('-14 days')) . "' AND '" . date('Y-m-d', strtotime('-7 days')) . "'");
                     break;
                 case 'month':
                     $growth->where("address.state_id = " . $result->state_id . " AND DATE_FORMAT(invoice.created_date, '%Y-%m-%d') BETWEEN '" . date('Y-m-d', strtotime('-2 month -1 days')) . "' AND '" . date('Y-m-d', strtotime('-1 month -1 days')) . "'");
                     break;
                 case 'year':
                     $growth->where("address.state_id = " . $result->state_id . " AND DATE_FORMAT(invoice.created_date, '%Y-%m-%d') BETWEEN '" . date('Y-m-d', strtotime('-2 year')) . "' AND '" . date('Y-m-d', strtotime('-1 year')) . "'");
                     break;
             }
             $growth_result = $this->tableGateway->selectwith($growth);
             $growth = $growth_result->current();
             $totalGrowth = isset($growth->total) && $growth->total > 0 ? round(($result->total - $growth->total) / $growth->total * 100) : round(($result->total - $growth->total) * 100);
             $subscriptions[] = array('total' => $result->total, 'state_name' => $result->state_name, 'growth' => $totalGrowth);
             $total = $total + $result->total;
         }
     }
     // Calculating total %
     foreach ($subscriptions as $key => $value) {
         $subscriptions[$key]['total_percentage'] = round($subscriptions[$key]['total'] / $total * 100);
     }
     return $subscriptions;
 }
예제 #27
0
 /**
  * @testdox unit test: Test reset() resets internal stat of Select object, based on input
  * @covers Zend\Db\Sql\Select::reset
  */
 public function testReset()
 {
     $select = new Select();
     // table
     $select->from('foo');
     $this->assertEquals('foo', $select->getRawState(Select::TABLE));
     $select->reset(Select::TABLE);
     $this->assertNull($select->getRawState(Select::TABLE));
     // columns
     $select->columns(array('foo'));
     $this->assertEquals(array('foo'), $select->getRawState(Select::COLUMNS));
     $select->reset(Select::COLUMNS);
     $this->assertEmpty($select->getRawState(Select::COLUMNS));
     // joins
     $select->join('foo', 'id = boo');
     $this->assertEquals(array(array('name' => 'foo', 'on' => 'id = boo', 'columns' => array('*'), 'type' => 'inner')), $select->getRawState(Select::JOINS));
     $select->reset(Select::JOINS);
     $this->assertEmpty($select->getRawState(Select::JOINS));
     // where
     $select->where('foo = bar');
     $where1 = $select->getRawState(Select::WHERE);
     $this->assertEquals(1, $where1->count());
     $select->reset(Select::WHERE);
     $where2 = $select->getRawState(Select::WHERE);
     $this->assertEquals(0, $where2->count());
     $this->assertNotSame($where1, $where2);
     // group
     $select->group(array('foo'));
     $this->assertEquals(array('foo'), $select->getRawState(Select::GROUP));
     $select->reset(Select::GROUP);
     $this->assertEmpty($select->getRawState(Select::GROUP));
     // having
     $select->having('foo = bar');
     $having1 = $select->getRawState(Select::HAVING);
     $this->assertEquals(1, $having1->count());
     $select->reset(Select::HAVING);
     $having2 = $select->getRawState(Select::HAVING);
     $this->assertEquals(0, $having2->count());
     $this->assertNotSame($having1, $having2);
     // limit
     $select->limit(5);
     $this->assertEquals(5, $select->getRawState(Select::LIMIT));
     $select->reset(Select::LIMIT);
     $this->assertNull($select->getRawState(Select::LIMIT));
     // offset
     $select->offset(10);
     $this->assertEquals(10, $select->getRawState(Select::OFFSET));
     $select->reset(Select::OFFSET);
     $this->assertNull($select->getRawState(Select::OFFSET));
     // order
     $select->order('foo asc');
     $this->assertEquals(array('foo asc'), $select->getRawState(Select::ORDER));
     $select->reset(Select::ORDER);
     $this->assertNull($select->getRawState(Select::ORDER));
 }
예제 #28
0
 public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
 {
     if ($paginate) {
         $subquery = new Select('service_provider_address');
         $subquery->columns(array('*', new Expression('min(service_provider_address.address_id) AS adrs_id')));
         $subquery->group('user_id');
         $select = new Select('users');
         $select->join(array('spa' => $subquery), 'spa.user_id = users.id', array(), 'left');
         $select->join('address', 'address.id = spa.adrs_id', array('city', 'state_id', 'country_id'), 'left');
         $select->join('state', 'address.state_id = state.id', array('state_name'), 'left');
         $select->join('country', 'address.country_id = country.id', array('country_name'), 'left');
         $select->join('lookup_status', 'lookup_status.status_id = users.status_id', array('status'), 'left');
         $select->where("users.user_type_id = 3");
         /* 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'] . "%'") : "";
             if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") {
                 $select->where("DATE_FORMAT(users.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(users.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(users.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'");
                     }
                 }
             }
             if (isset($filter['service_id']) && $filter['service_id'] != "") {
                 $select->join('service_provider_service', 'users.id = service_provider_service.user_id', array(), 'left');
                 $select->where("service_provider_service.id = " . $filter['service_id']);
             }
             isset($filter['country_id']) && $filter['country_id'] != "" ? $select->where("address.country_id = " . $filter['country_id']) : "";
             isset($filter['state_id']) && $filter['state_id'] != "" ? $select->where("address.state_id = " . $filter['state_id']) : "";
             isset($filter['city']) && $filter['city'] != "" ? $select->where("address.city LIKE '%" . $filter['city'] . "%'") : "";
             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('users.first_name ' . $orderBy['sort_order']);
                     break;
                 case 'date':
                     $select->order('users.created_date ' . $orderBy['sort_order']);
                     break;
                 case 'country':
                     $select->order('country.country_name ' . $orderBy['sort_order']);
                     break;
                 case 'state':
                     $select->order('state.state_name ' . $orderBy['sort_order']);
                     break;
                 case 'city':
                     $select->order('address.city ' . $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 ServiceProvider());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         $paginator = new Paginator($paginatorAdapter);
         return $paginator;
     } else {
         $select = $this->tableGateway->getSql()->select();
         $select->where("users.user_type_id = 3");
         //echo str_replace('"','',$select->getSqlString()); exit;
         return $this->tableGateway->selectwith($select);
     }
 }