/**
* Method to fetch all data in pagination object
**/
public function fetchAll($optionArray = array(), $paginated = false)
{
if ($paginated) {
// create a new Select object for the table cmspage
$select = new Select('cms_pages');
if (!empty($optionArray['fieldArray'])) {
$select->columns($optionArray['fieldArray']);
}
if (!empty($optionArray['sortByColumn']['sort_column']) && !empty($optionArray['sortByColumn']['sort_order'])) {
$orderBy = $optionArray['sortByColumn']['sort_column'] . ' ' . $optionArray['sortByColumn']['sort_order'];
$select->order($orderBy);
} else {
if (!empty($optionArray['default_sort_column']) && !empty($optionArray['default_sort_order'])) {
$orderBy = $optionArray['default_sort_column'] . ' ' . $optionArray['default_sort_order'];
$select->order($orderBy);
}
}
if (!empty($optionArray['searchColumns']['searchKey']) && !empty($optionArray['searchColumns']['searchCol'])) {
$searchKey = "%" . $optionArray['searchColumns']['searchKey'] . "%";
$searchCol = $optionArray['searchColumns']['searchCol'] ? $optionArray['searchColumns']['searchCol'] : $optionArray['fieldArray'][1];
$select->where->like($searchCol, $searchKey);
}
// create a new result set based on the cmspage entity
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Cmspage());
// create a new pagination adapter object
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
$resultSet = $this->tableGateway->select(function (Select $select) use($optionArray) {
if (!empty($optionArray['fieldArray'])) {
$select->columns($optionArray['fieldArray']);
}
if (!empty($optionArray['sortByColumn']['sort_column']) && !empty($optionArray['sortByColumn']['sort_order'])) {
$orderBy = $optionArray['sortByColumn']['sort_column'] . ' ' . $optionArray['sortByColumn']['sort_order'];
$select->order($orderBy);
} else {
if (!empty($optionArray['default_sort_column']) && !empty($optionArray['default_sort_order'])) {
$orderBy = $optionArray['default_sort_column'] . ' ' . $optionArray['default_sort_order'];
$select->order($orderBy);
}
}
if (!empty($optionArray['searchColumns']['searchKey']) && !empty($optionArray['searchColumns']['searchCol'])) {
$searchKey = "%" . $optionArray['searchColumns']['searchKey'] . "%";
$searchCol = $optionArray['searchColumns']['searchCol'] ? $optionArray['searchColumns']['searchCol'] : $optionArray['fieldArray'][1];
$select->where->like($searchCol, $searchKey);
}
});
return $resultSet;
}
public function setParameters(Parameters $params)
{
$groupsPostsTable = Api::_()->getDbTable('Group\\DbTable\\GroupsPosts');
$groupsPostsTableName = $groupsPostsTable->initTableName()->getTable();
$groupsCategoriesTable = Api::_()->getDbTable('Group\\DbTable\\CategoriesGroups');
$groupsCategoriesTableName = $groupsCategoriesTable->initTableName()->getTable();
if ($params->group_id || $params->groupCategory) {
$params->inGroup = true;
}
if ($params->inGroup) {
$groupId = $params->group_id;
$categoryId = $params->groupCategory;
$this->where(function ($where) use($groupsPostsTableName, $groupsCategoriesTableName, $groupId, $categoryId) {
$select = new Select($groupsPostsTableName);
$select->columns(array('post_id'));
if ($groupId) {
$select->where(array('group_id' => $groupId));
}
if ($categoryId) {
$cateSelect = new Select($groupsCategoriesTableName);
$cateSelect->columns(array('group_id'));
$cateSelect->where(array('category_id' => $categoryId));
$select->where(function ($where) use($cateSelect) {
$where->in('group_id', $cateSelect);
return $where;
});
}
$where->in('id', $select);
return $where;
});
}
return parent::setParameters($params);
}
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;
}
/**
* Returns all the comments pending approval for a thread.
*
* @param string $thread
* @return ResultSet
*/
public function fetchAllPendingForThread($thread)
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array('id', 'author', 'content', 'contact', 'published_on_raw' => 'published_on', 'published_on' => new Expression("DATE_FORMAT(published_on, '%M %d, %Y %H:%i')")))->where(array('thread' => $thread, 'visible' => 0))->order('published_on_raw ASC');
$resultSet = $this->tableGateway->selectWith($select);
return $resultSet;
}
public function setUp()
{
$this->column = $this->getMockBuilder('ZfcDatagrid\\Column\\Select')->disableOriginalConstructor()->getMock();
$this->column->method('getSelectPart1')->willReturn('myCol');
$this->column->method('getType')->willReturn(new \ZfcDatagrid\Column\Type\PhpString());
$this->column->setUniqueId('myCol');
$this->column->setSelect('myCol');
$this->column2 = $this->getMockBuilder('ZfcDatagrid\\Column\\Select')->disableOriginalConstructor()->getMock();
$this->column2->method('getSelectPart1')->willReturn('myCol2');
$this->column2->method('getType')->willReturn(new \ZfcDatagrid\Column\Type\PhpString());
$this->column2->setUniqueId('myCol2');
$this->column2->setSelect('myCol2');
$this->mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface');
$this->mockConnection = $this->getMock('Zend\\Db\\Adapter\\Driver\\ConnectionInterface');
$this->mockDriver->expects($this->any())->method('checkEnvironment')->will($this->returnValue(true));
$this->mockDriver->expects($this->any())->method('getConnection')->will($this->returnValue($this->mockConnection));
$this->mockPlatform = $this->getMock('Zend\\Db\\Adapter\\Platform\\PlatformInterface');
$this->mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface');
$this->mockDriver->expects($this->any())->method('createStatement')->will($this->returnValue($this->mockStatement));
$this->adapter = new Adapter($this->mockDriver, $this->mockPlatform);
$sql = new Sql($this->adapter, 'foo');
$select = new Select('myTable');
$select->columns(['myCol', 'myCol2']);
$this->filterSelect = new FilterSelect($sql, $select);
}
public function getDetails_OderDetails($id_user, $id_oeder)
{
$select = new Select('odersdetails');
$select->columns(array('id_detail' => 'id', 'description' => 'description', 'status_details' => 'status', 'quantity' => 'quantity', 'cpmRate' => 'cpmRate', 'amount' => 'amount', 'type_details' => 'type', 'AdobeFlashEnabled' => 'AdobeFlashEnabled', 'MaxDailyBudget' => 'MaxDailyBudget', 'MobileTargeting' => 'MobileTargeting', 'trafficsources' => 'trafficsources', 'PreviousWebsite' => 'PreviousWebsite', 'payment_select' => 'payment_select'))->join('oders', 'oders.id = odersdetails.oder_id', array('id' => 'id', 'creatnamecampaign' => 'creatnamecampaign', 'date_creat' => 'date_creat', 'date_mod' => 'date_mod', 'id_user' => 'id_user', 'status' => 'status', 'status_oder' => 'status_oder', 'type' => 'type', 'DolarTotal' => 'DolarTotal', 'TotaVistor' => 'TotaVistor', 'Cpmrate' => 'Cpmrate'), 'left')->join('ads', 'ads.oder_id = oders.id', array('id_code' => 'id', 'delay' => 'delay', 'time_display' => 'time_display', 'namecampaigns' => 'name', 'js_id' => 'js_id', 'status_code' => 'status'), 'left')->join('process', 'process.oder_id = oders.id', array('date_reg' => 'date_reg', 'date_active' => 'date_active', 'checked' => 'checked', 'id_proc' => 'id', 'ads_id' => 'ads_id', 'advertiser_id' => 'advertiser_id', 'publisher_id' => 'publisher_id', 'type_id' => 'type_id', 'status_process' => 'status', 'website' => 'website'), 'left')->where(array('odersdetails.oder_id' => $id_oeder));
//->order ( 'oders.id DESC' )->limit ( $limit )->offset ( $offset );
$rowset = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
$results = $rowset->execute();
/*
<div id="shoppingnew">
<input type="hidden" class="form-control" id="spVietnamid" name="palpost[Vietnam][id]" value="218">
<input type="hidden" class="form-control" id="spVietnamoder_id" name="palpost[Vietnam][oder]" value="105">
<input type="hidden" class="form-control" id="spVietnamtype" name="palpost[Vietnam][type]" value="0">
<input type="hidden" class="form-control" id="spVietnamnamepackge" name="palpost[Vietnam][namepackge]" value="Vietnam">
<input type="hidden" class="form-control" id="spVietnamprice" name="palpost[Vietnam][price]" value="0.4">
<input type="hidden" class="form-control" id="spVietnamvistor" name="palpost[Vietnam][vistor]" value="5">
<input type="hidden" class="form-control" id="spVietnamamount" name="palpost[Vietnam][amount]" value="2">
</div>
*/
$i = 0;
$array = array();
foreach ($results as $key => $value) {
$array[$i]['namepackge'] = $value['description'];
$array[$i]['id'] = $value['id_detail'];
// id details
$array[$i]['oder'] = $value['id'];
// oder id
$array[$i]['type'] = $value['type_details'];
$array[$i]['price'] = $value['cpmRate'];
$array[$i]['vistor'] = $value['quantity'];
$array[$i]['amount'] = $value['cpmRate'] * $value['quantity'];
$i++;
}
return $array;
}
/**
*
* @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 find($params)
{
$sql = new Sql($this->getAdapter());
$select = new Select();
$select->from($params['from']);
if (!empty($params['columns'])) {
$select->columns($params['columns']);
}
foreach ($params['where'] as $where) {
$select->where($where);
}
foreach ($params['joins'] as $join) {
if (empty($join['columns'])) {
$join['columns'] = Select::SQL_STAR;
}
if (empty($join['type'])) {
$join['type'] = Select::JOIN_INNER;
}
$select->join($join['name'], $join['on'], $join['columns'], $join['type']);
}
$query = $sql->getSqlStringForSqlObject($select);
$results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
$data = $results->toArray();
if (empty($data)) {
return false;
} else {
if (count($data) == 1) {
return $data[0];
} else {
return $data;
}
}
}
public function fetchAll($paginated = false, $sort = 'name', $order = 'ASC')
{
switch ($sort) {
case "id":
$sort = "C_Id";
break;
case "name":
$sort = "C_Name";
break;
case "lastinvoice":
$sort = "C_LastInvoice";
break;
default:
$sort = "C_Name";
}
$select = new Select();
$select->from($this->table);
$subquery = "(SELECT I_BaseDate FROM Invoice WHERE Invoice.I_ClientId = Client.C_Id ORDER BY I_Id DESC LIMIT 1)";
$select->columns(array('C_Id' => 'C_Id', 'C_Name' => 'C_Name', 'C_PaymentTerms' => 'C_PaymentTerms', 'C_Reference' => 'C_Reference', 'C_CRN' => 'C_CRN', 'C_Class' => 'C_Class', 'C_Updated' => 'C_Updated', 'C_LastInvoice' => new Expression($subquery)))->order($sort . ' ' . $order);
if ($paginated) {
return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype));
}
$resultSet = $this->select($select);
return $resultSet;
}
/**
* @testdox unit test: Test columns() returns Select object (is chainable)
* @covers Zend\Db\Sql\Select::columns
*/
public function testColumns()
{
$select = new Select();
$return = $select->columns(array('foo', 'bar'));
$this->assertSame($select, $return);
return $select;
}
public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
{
if ($paginate) {
$select = new Select('booking');
$select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,payment_history.currency as currency,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus")));
$select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner');
$select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left');
$select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left');
$select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left');
$select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left');
$select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision'), 'inner');
$select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner');
$select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner');
$select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left');
$select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)');
count($filter) > 0 ? $select->where($filter) : "";
/* Data sorting code starts here */
if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
switch ($orderBy['sort_field']) {
case 'user':
$select->order('users.first_name ' . $orderBy['sort_order']);
break;
case 'service_provider':
$select->order('service_provider.first_name ' . $orderBy['sort_order']);
break;
case 'service':
$select->order('service_category.category_name ' . $orderBy['sort_order']);
break;
case 'booked_date':
$select->order('booking.booked_date ' . $orderBy['sort_order']);
break;
}
} else {
$select->order('booking.id desc');
}
/* Data sorting code ends here */
//echo str_replace('"', '', $select->getSqlString()); exit;
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Bookings());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
} else {
$select = $this->tableGateway->getSql()->select();
$select->columns(array('*', new Expression("service_provider.first_name as sp_first_name, service_provider.last_name as sp_last_name,\n\t\t\tinvoice.status_id AS invoice_status,CASE invoice.status_id WHEN 0 THEN 'Unpaid' WHEN 1 THEN 'Paid' WHEN 2 THEN 'Partially Paid' END AS PaymentStatus")));
$select->join('booking_suggestion_history', 'booking_suggestion_history.booking_id = booking.id', array('booking_time', 'booking_status'), 'inner');
$select->join('users', 'users.id = booking.user_id', array('first_name', 'last_name'), 'left');
$select->join(array('service_provider' => 'users'), 'service_provider.id = booking.service_provider_id', array(), 'left');
$select->join('service_provider_service', 'service_provider_service.id = booking.service_provider_service_id', array('duration', 'price'), 'left');
$select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'left');
$select->join('invoice', 'invoice.id = booking.invoice_id', array('invoice_total', 'site_commision', 'created_date'), 'inner');
$select->join('invoice_details', 'invoice_details.invoice_id = invoice.id', array('sale_item_details'), 'inner');
$select->join('payment_history', 'payment_history.invoice_id = invoice.id', array(), 'inner');
$select->join('lookup_status', 'lookup_status.status_id = booking_suggestion_history.booking_status', array('status'), 'left');
$select->where('booking_suggestion_history.id = (SELECT id FROM booking_suggestion_history WHERE booking_id = booking.id ORDER BY id DESC LIMIT 1)');
count($filter) > 0 ? $select->where($filter) : "";
return $this->tableGateway->selectwith($select);
}
}
public function fetchDrafts()
{
$select = new Select();
$select->from($this->table);
$select->columns(array('I_Id' => 'I_Id', 'I_Number' => 'I_Number', 'I_BaseDate' => 'I_BaseDate', 'I_Vat' => 'I_Vat', 'I_DueDays' => 'I_DueDays', 'I_SentDate' => 'I_SentDate', 'I_PayedDate' => 'I_PayedDate', 'I_ClientId' => 'I_ClientId', 'I_ClientReference' => 'I_ClientReference', 'I_OwnReference' => 'I_OwnReference', 'I_AddressId' => 'I_AddressId', 'I_Text' => 'I_Text', 'I_Updated' => 'I_Updated'))->where(array(new \Zend\Db\Sql\Predicate\IsNull('I_Number')))->join('Client', 'Invoice.I_ClientId = Client.C_Id');
$resultSet = $this->selectWith($select);
return $resultSet;
}
public function getNewsList()
{
$select = new Select();
$select->columns(['content' => 'en', 'title' => 'en_title', 'url' => 'en_title', 'date']);
$select->from($this->table);
$select->order('date DESC');
return new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype));
}
public function getLoginUser()
{
$select = new Select(self::TABLE_NAME);
$columns = array('emailId' => 'p_user_email_id', 'password' => 'p_user_password');
$select->columns($columns);
$sql = new Sql($this->tableGateway->getAdapter());
$db = $this->tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
return $this->getSqlContent($db, $sql, $select);
}
public function get($id, $status = null)
{
$select = new Select();
$select->from($this->tableGateway->getTable());
$select->columns(array('*'));
$select->where($status === null ? array('users.id' => $id) : array('users.id' => $id, 'users.status' => $status));
$resultSet = $this->tableGateway->selectWith($select);
$row = $resultSet->current();
return !$row ? false : $row;
}
public function getActiveProducts()
{
$akilia1db = $this->configuration['synchronizer']['db_akilia1'];
$select = new Select();
$select->from(["a" => new \Zend\Db\Sql\TableIdentifier('article', $akilia1db)], [])->where('a.flag_archive <> 1');
$select->columns(['id_article' => new Expression('TRIM(a.id_article)'), 'reference' => new Expression('a.reference'), 'id_marque' => new Expression('a.id_marque')], true);
$store = $this->getStore($select);
$data = $store->getData();
return $data;
}
public function getSkillCount()
{
$select = new Select();
$select->from($this->table);
$select->columns(array('skills_count' => new \Zend\Db\Sql\Expression('COUNT(skill_id)')));
//$select->where(array("skill_status"=>1));
$resultSet = $this->selectWith($select);
$rowset = $resultSet->current();
return $rowset["skills_count"];
}
public function getDeptCount()
{
$select = new Select();
$select->from($this->table);
$select->columns(array('departments_count' => new \Zend\Db\Sql\Expression('COUNT(dept_id)')));
//$select->where(array("{$this->table}.parent_id is NULL"));
$resultSet = $this->selectWith($select);
$rowset = $resultSet->current();
return $rowset["departments_count"];
}
/**
* Sets the filter
* @param \Zend\Db\Sql\Select $select
* @param array $criteria
* @return \Zend\Db\Sql\Select
*/
private function setFilter($select, $criteria)
{
$select->columns(array('*', 'role' => new \Zend\Db\Sql\Predicate\Expression('(SELECT `description` FROM `profile_role` WHERE `profile_role`.`id`=`profile`.`role_id` )')));
if (array_key_exists('role_id', $criteria) && $criteria['role_id'] != '') {
$select->where('`profile`.`role_id` = ' . intval($criteria['role_id']));
}
if (array_key_exists('sSearch', $criteria) && $criteria['sSearch'] != '') {
$select->where(' ( `profile`.`first_name` LIKE "' . addslashes($criteria['sSearch']) . '%" OR `profile`.`last_name` LIKE "' . addslashes($criteria['sSearch']) . '%" ) ');
}
return $select;
}
public function getPostCountForThread($threadId)
{
$predicate = new Predicate(null, Predicate::OP_AND);
$predicate->equalTo('thread_id', $threadId);
$select = new Select();
$select->from($this->tableName);
$select->columns(['count' => new Expression('COUNT(*)')]);
$select->where($predicate);
$result = $this->select($select);
return $result->toArray();
}
public function fetchBrands()
{
$select = new Select();
$select->from('brands');
$select->columns(array('b_name'));
$resultSet = $this->tableGateway->selectWith($select);
foreach ($resultSet as $res) {
$selectData[$res['id']] = $res['title'];
}
return $selectData;
}
public function getForums()
{
$selectCount = $this->getThreadCounts();
$selectLatest = $this->getForumLatest();
$select = new Select();
$select->from($this->tableName);
$select->join(['count' => $selectCount], new Expression('forum.id = count.forum_id'), [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER)->join(['latest' => $selectLatest], new Expression('forum.id = latest.forum_id'), [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER)->join('forum_category', new Expression('forum.forum_category_id = forum_category.id'), [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
$select->columns(['id' => new Expression('forum.id'), 'title' => new Expression('forum.title'), 'description' => new Expression('forum.description'), 'minimum_access' => new Expression('forum.minimum_access'), 'category_id' => new Expression('forum_category.id'), 'category' => new Expression('forum_category.category'), 'thread_count' => new Expression('count_for_forum'), 'last_poster' => new Expression('last_post'), 'last_post_date' => new Expression('latest.date_updated'), 'last_thread' => new Expression('last_thread'), 'last_thread_id' => new Expression('last_thread_id')], false);
$result = $this->select($select);
return $result->toArray();
}
public function fetchAll($cityId, $serviceId, $categoryId, $deliveryType = 'Regular')
{
$select = new Select('item_price');
$select->columns(array("item_price_id", "item_id", "service_id", "item_city_id", "delivery_type_name", "price"));
$select->join("items", "items.item_id = item_price.item_id", array("item_name"));
$select->where(" item_city_id = '{$cityId}' and item_price.service_id = '{$serviceId}' and items.category_id = '{$categoryId}' and delivery_type_name = '{$deliveryType}' ");
// echo '<pre>'; print_r($select->getSqlString()); exit('Macro die');
$paginatorAdapter = new DbSelect($select, $this->adapter);
$collection = new RateCollection($paginatorAdapter);
return $collection;
}
public function getCategorySpecificationUncheckValue()
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array('specification'));
$select->join('specifications', "specifications.id = " . $this->tableGateway->getTable() . ".specification", array('specification' => 'id', 'specification_name' => 'name'), 'right');
$select->where(array($this->tableGateway->getTable() . '.specification' => null));
$resulset = $this->tableGateway->selectWith($select);
//echo str_replace('"', '', $select->getSqlString());
//exit();
return $resulset;
}
public function getMostRecentListing($id = null)
{
$subSelect = new Select();
$subSelect->columns(array('mostRecent' => new Expression('MAX(`listings_id`)')));
$subSelect->from(ListingsTable::$tableName);
$where = new Where();
$where->in('listings_id', $subSelect);
$select = new Select();
$select->where($where)->from(ListingsTable::$tableName);
return $this->selectWith($select)->current();
}
public function fetchAll($paginate = true, $filter = array(), $orderBy = array())
{
if ($paginate) {
$select = new Select('feedback');
$select->columns(array('*', new Expression("service_provider_contact.first_name AS sp_first_name, service_provider_contact.last_name AS sp_last_name")));
$select->join(array('service_provider_contact' => 'users'), 'service_provider_contact.id = feedback.users_id', array(), 'inner');
$select->join('users', 'users.id = feedback.created_by', array('first_name', 'last_name'), 'inner');
$select->join('service_provider_service', 'service_provider_service.id = feedback.service_id', array('duration'), 'inner');
$select->join('service_category', 'service_category.id = service_provider_service.service_id', array('category_name'), 'inner');
$select->join('lookup_status', 'lookup_status.status_id = feedback.status_id', array('status'), 'left');
/* Data filter code start here*/
if (count($filter) > 0) {
isset($filter['name']) && $filter['name'] != "" ? $select->where("CONCAT(service_provider_contact.first_name,' ',service_provider_contact.last_name) LIKE '%" . $filter['name'] . "%'") : "";
if (isset($filter['from_date']) && $filter['from_date'] != "" && isset($filter['to_date']) && $filter['to_date'] != "") {
$select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') BETWEEN '" . $filter['from_date'] . "' AND '" . $filter['to_date'] . "'");
} else {
if (isset($filter['from_date']) && !isset($filter['to_date']) && $filter['from_date'] != "") {
$select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') = '" . $filter['from_date'] . "'");
} else {
if (!isset($filter['from_date']) && isset($filter['to_date']) && $filter['to_date'] != "") {
$select->where("DATE_FORMAT(feedback.created_date , '%Y-%m-%d') = '" . $filter['to_date'] . "'");
}
}
}
if (isset($filter['service_id']) && $filter['service_id'] != "") {
$select->where("feedback.service_id = " . $filter['service_id']);
}
isset($filter['status_id']) && $filter['status_id'] != "" ? $select->where("users.status_id = " . $filter['status_id']) : "";
}
/* Data filter code end here*/
/* Data sorting code starts here */
if (count($orderBy) > 0 && $orderBy['sort_field'] != '' && $orderBy['sort_order'] != '') {
switch ($orderBy['sort_field']) {
case 'name':
$select->order('service_provider_contact.first_name ' . $orderBy['sort_order']);
break;
case 'service':
$select->order('service_category.category_name ' . $orderBy['sort_order']);
break;
case 'date':
$select->order('feedback.created_date ' . $orderBy['sort_order']);
break;
}
}
/* Data sorting code ends here */
//echo str_replace('"','',$select->getSqlString()); exit;
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Feedbacks());
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
return $this->tableGateway->select();
}
function getCountry($id)
{
$id = (int) $id;
$select = new Select($this->tableGateway->getTable());
$select->columns(['id', 'code', 'alpha2', 'alpha3', 'nom_en_gb', 'nom_fr_fr', 'devise', 'taux_tva'])->where(['id' => $id]);
$rowSet = $this->tableGateway->selectWith($select);
$row = $rowSet->current();
if (!$row) {
throw new \Exception("could not find row {$id}");
}
return $row;
}
public function getSerialName($category)
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array('id', 'name'));
$select->join('categories_serials_name', '' . $this->tableGateway->getTable() . '.id = categories_serials_name.serial_name', array(), 'inner');
$rows = $this->tableGateway->selectWith($select);
$result = array();
foreach ($rows as $row) {
$result[$row->getId()] = $row->getName();
}
return $result;
}
public function get($id, $user)
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array("id", "register_date", "guide", "observations"));
$select->join('customers', "customers.id = " . $this->tableGateway->getTable() . ".client", array('client_first_name' => 'first_name', 'client_last_name' => 'first_name'), 'inner');
$select->join(array('sellers' => 'customers'), "sellers.id = " . $this->tableGateway->getTable() . ".seller", array('seller_first_name' => 'first_name', 'seller_last_name' => 'last_name', 'seller_company' => 'company'), 'inner');
$select->join('payments_methods', "payments_methods.id = " . $this->tableGateway->getTable() . ".payment_method", array('payment_method_name' => 'name'), 'inner');
$select->where(array($this->tableGateway->getTable() . ".id" => $id, $this->tableGateway->getTable() . ".user" => $user));
$rowset = $this->tableGateway->selectWith($select);
$row = $rowset->current();
return $row;
}
public function searchSerial($serial)
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array());
$select->join("products", "products.id = " . $this->tableGateway->getTable() . ".product", array('id' => 'id'));
$select->join("products_receive_inventory", "products_receive_inventory.details_receive_inventory = " . $this->tableGateway->getTable() . ".id", array('serial' => 'serial'));
$select->where(array("products_receive_inventory.status" => 0));
$select->where->like("products_receive_inventory.serial", "%" . $serial . "%");
error_log($select->getSqlString());
$result = $this->tableGateway->selectWith($select);
return $result;
}