/**
*
* @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;
}
/**
* @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));
}
/**
* @testdox unit test: Test having() returns same Select object (is chainable)
* @covers Zend\Db\Sql\Select::having
*/
public function testHaving()
{
$select = new Select();
$return = $select->having(array('x = ?' => 5));
$this->assertSame($select, $return);
return $return;
}
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;
}