/**
* Join customers
*
* @return $this
*/
protected function _joinCustomers()
{
/** @var $adapter \Magento\Framework\DB\Adapter\AdapterInterface */
$adapter = $this->getConnection();
/** @var $firstnameAttr \Magento\Eav\Model\Entity\Attribute */
$firstnameAttr = $this->_customerResource->getAttribute('firstname');
/** @var $lastnameAttr \Magento\Eav\Model\Entity\Attribute */
$lastnameAttr = $this->_customerResource->getAttribute('lastname');
$firstnameCondition = array('table_customer_firstname.entity_id = detail.customer_id');
if ($firstnameAttr->getBackend()->isStatic()) {
$firstnameField = 'firstname';
} else {
$firstnameField = 'value';
$firstnameCondition[] = $adapter->quoteInto('table_customer_firstname.attribute_id = ?', (int) $firstnameAttr->getAttributeId());
}
$this->getSelect()->joinInner(array('table_customer_firstname' => $firstnameAttr->getBackend()->getTable()), implode(' AND ', $firstnameCondition), array());
$lastnameCondition = array('table_customer_lastname.entity_id = detail.customer_id');
if ($lastnameAttr->getBackend()->isStatic()) {
$lastnameField = 'lastname';
} else {
$lastnameField = 'value';
$lastnameCondition[] = $adapter->quoteInto('table_customer_lastname.attribute_id = ?', (int) $lastnameAttr->getAttributeId());
}
//Prepare fullname field result
$customerFullname = $adapter->getConcatSql(array("table_customer_firstname.{$firstnameField}", "table_customer_lastname.{$lastnameField}"), ' ');
$this->getSelect()->reset(\Zend_Db_Select::COLUMNS)->joinInner(array('table_customer_lastname' => $lastnameAttr->getBackend()->getTable()), implode(' AND ', $lastnameCondition), array())->columns(array('customer_id' => 'detail.customer_id', 'customer_name' => $customerFullname, 'review_cnt' => 'COUNT(main_table.review_id)'))->group('detail.customer_id');
return $this;
}
/**
* Add customer data
*
* @param unknown_type $filter
* @return $this
*/
public function addCustomerData($filter = null)
{
$attrFirstname = $this->_customerResource->getAttribute('firstname');
$attrFirstnameId = (int) $attrFirstname->getAttributeId();
$attrFirstnameTableName = $attrFirstname->getBackend()->getTable();
$attrLastname = $this->_customerResource->getAttribute('lastname');
$attrLastnameId = (int) $attrLastname->getAttributeId();
$attrLastnameTableName = $attrLastname->getBackend()->getTable();
$attrEmail = $this->_customerResource->getAttribute('email');
$attrEmailTableName = $attrEmail->getBackend()->getTable();
$adapter = $this->getSelect()->getAdapter();
$customerName = $adapter->getConcatSql(array('cust_fname.value', 'cust_lname.value'), ' ');
$this->getSelect()->joinInner(array('cust_email' => $attrEmailTableName), 'cust_email.entity_id = main_table.customer_id', array('email' => 'cust_email.email'))->joinInner(array('cust_fname' => $attrFirstnameTableName), implode(' AND ', array('cust_fname.entity_id = main_table.customer_id', $adapter->quoteInto('cust_fname.attribute_id = ?', (int) $attrFirstnameId))), array('firstname' => 'cust_fname.value'))->joinInner(array('cust_lname' => $attrLastnameTableName), implode(' AND ', array('cust_lname.entity_id = main_table.customer_id', $adapter->quoteInto('cust_lname.attribute_id = ?', (int) $attrLastnameId))), array('lastname' => 'cust_lname.value', 'customer_name' => $customerName));
$this->_joinedFields['customer_name'] = $customerName;
$this->_joinedFields['email'] = 'cust_email.email';
if ($filter) {
if (isset($filter['customer_name'])) {
$likeExpr = '%' . $filter['customer_name'] . '%';
$this->getSelect()->where($this->_joinedFields['customer_name'] . ' LIKE ?', $likeExpr);
}
if (isset($filter['email'])) {
$likeExpr = '%' . $filter['email'] . '%';
$this->getSelect()->where($this->_joinedFields['email'] . ' LIKE ?', $likeExpr);
}
}
return $this;
}
/**
* @param \Magento\Framework\DB\Select $select
* @return \Magento\Framework\DB\Select
*/
protected function getCustomerNames($select)
{
$attrFirstname = $this->_customerResource->getAttribute('firstname');
$attrFirstnameId = (int) $attrFirstname->getAttributeId();
$attrFirstnameTableName = $attrFirstname->getBackend()->getTable();
$attrLastname = $this->_customerResource->getAttribute('lastname');
$attrLastnameId = (int) $attrLastname->getAttributeId();
$attrLastnameTableName = $attrLastname->getBackend()->getTable();
$select->joinInner(['cust_fname' => $attrFirstnameTableName], 'customer.entity_id = cust_fname.entity_id', ['firstname' => 'cust_fname.value'])->joinInner(['cust_lname' => $attrLastnameTableName], 'customer.entity_id = cust_lname.entity_id', ['lastname' => 'cust_lname.value'])->where('cust_fname.attribute_id = ?', (int) $attrFirstnameId)->where('cust_lname.attribute_id = ?', (int) $attrLastnameId);
return $select;
}
