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;
}
/**
* 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;
}
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;
}
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();
}
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);
}
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;
}
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;
}
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);
}
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;
}
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;
}
/**
* @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;
}
public function group($group)
{
return $this->zendSelect->group($group);
}
/**
* 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;
}
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()));
}
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;
}
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;
}
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;
}
/**
* @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));
}
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);
}
}
