protected function _filterToPredicate($field, $predicate) { if (strtolower($field) == 'categories' && !$predicate instanceof Predicate) { // Handle split amount $predicateSet = new PredicateSet(); $predicateSet->addPredicate(parent::_filterToPredicate('category', $predicate)); $secondField = new Expression('(SELECT category FROM ' . Operation::SPLIT_AMOUNT_TABLE . ' WHERE operation_id = main_table.id)'); $predicateSet->addPredicate(parent::_filterToPredicate($secondField, $predicate), Predicate::OP_OR); return $predicateSet; } return parent::_filterToPredicate($field, $predicate); }
public function testCanPassBothPredicateAndCombinationToAddPredicate() { $predicateSet = new PredicateSet(); $predicateSet->addPredicate(new IsNull('foo'), 'OR')->addPredicate(new IsNull('bar'), 'AND')->addPredicate(new IsNull('baz'), 'OR')->addPredicate(new IsNull('bat'), 'AND'); $parts = $predicateSet->getExpressionData(); $this->assertEquals(7, count($parts)); $this->assertNotContains('OR', $parts[1], var_export($parts, 1)); $this->assertContains('AND', $parts[1]); $this->assertContains('OR', $parts[3]); $this->assertNotContains('AND', $parts[3]); $this->assertNotContains('OR', $parts[5]); $this->assertContains('AND', $parts[5]); }
/** * Thả nổi các lead sau 1 số ngày nhất định mà ko có ghi nhận activity nào * @return \Zend\View\Model\ViewModel */ public function freeleadAction() { // tạm ko sử dụng nữa echo 'Tạm không hỗ trợ nữa'; die; set_time_limit(300); $dbAdapter = $this->getServiceLocator()->get('dbAdapter'); /* @var $dbAdapter \Zend\Db\Adapter\Adapter */ $dbSql = $this->getServiceLocator()->get('dbSql'); /* @var $dbSql \Zend\Db\Sql\Sql */ $select = $dbSql->select(array('lu' => \Crm\Model\Lead\UserMapper::TABLE_NAME)); $select->join(['lc' => \Crm\Model\Lead\CompanyMapper::TABLE_NAME], new Expression('lu.leadId = lc.leadId AND lu.companyId=lc.companyId'), ['lastActivityDateTime', 'releaseDateTime', 'lc.id' => 'id'], $select::JOIN_LEFT); $select->where(['lu.type' => \Crm\Model\Lead\User::TYPE_SALE]); $select->where(['lc.lastActivityDateTime IS NOT NULL']); $select->where(['lc.accountId IS NULL']); $date = new \DateTime(); $date->setTime('0', 0, 0); $today = $date->format(DateBase::COMMON_DATETIME_FORMAT); $date->sub(new \DateInterval('P' . \Crm\Model\Lead::MAXIMUM_KEEP_DAY . 'D')); $lastActDate = $date->format(DateBase::COMMON_DATETIME_FORMAT); $select->where(['lc.lastActivityDateTime <= ?' => $lastActDate]); $predicateSet = new PredicateSet(); $predicateSet->addPredicate(new IsNull('lc.releaseDateTime'), $predicateSet::OP_OR); $predicateSet->addPredicate(new Operator('lc.releaseDateTime', '<=', $today), $predicateSet::OP_OR); $select->where($predicateSet); $select->limit(300); $query = $dbSql->buildSqlString($select); echo $query; echo '<br/>'; $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); $leadUserMapper = $this->getServiceLocator()->get('\\Crm\\Model\\Lead\\UserMapper'); $activityMapper = $this->getServiceLocator()->get('\\Crm\\Model\\ActivityMapper'); $leadCompanyMapper = $this->getServiceLocator()->get('\\Crm\\Model\\Lead\\CompanyMapper'); $lcIds = []; if ($rows->count()) { foreach ($rows as $row) { $row = (array) $row; $leadUser = new \Crm\Model\Lead\User($row); $leadUserMapper->delete($leadUser); $activity = new \Crm\Model\Activity(); $activity->setType(\Crm\Model\Activity::TYPE_AUTO_FREE); $activity->setCompanyId($leadUser->getCompanyId()); $activity->setLeadId($leadUser->getLeadId()); $activity->setAccountId($leadUser->getAccountId()); $activity->setTitle('Tự giải phóng sau ' . \Crm\Model\Lead::MAXIMUM_KEEP_DAY . ' ngày'); $activity->setRelatedUserId($leadUser->getUserId()); $activity->setCreatedById(1); $activity->setCreatedDate(DateBase::getCurrentDate()); $activity->setCreatedDateTime(DateBase::getCurrentDateTime()); $activity->setStatus(\Crm\Model\Activity::STATUS_SUCCESS); $activityMapper->save($activity); $lcIds[$row['lc.id']] = $row['lc.id']; } if (count($lcIds)) { $select = $dbSql->select(array('lc' => \Crm\Model\Lead\CompanyMapper::TABLE_NAME)); $select->join(['lu' => \Crm\Model\Lead\UserMapper::TABLE_NAME], new Expression('lu.leadId = lc.leadId AND lu.companyId=lc.companyId AND lu.type=' . \Crm\Model\Lead\User::TYPE_SALE), [], $select::JOIN_LEFT); $select->where(['lc.id' => $lcIds]); $select->where(['lu.id IS NULL']); $relatedLcIds = []; $query = $dbSql->buildSqlString($select); echo $query; $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rows->count()) { $currentDatetime = DateBase::getCurrentDateTime(); foreach ($rows as $row) { $row = (array) $row; $leadCompany = new \Crm\Model\Lead\Company($row); $leadCompanyMapper->updateColumns(['lastActivityDateTime' => $currentDatetime], $leadCompany); } } } $updateQuery = 'UPDATE crm_leads_companies lc LEFT JOIN crm_leads_users lu ON lu.companyId=lc.companyId AND lu.leadId=lc.leadId AND lu.type=' . \Crm\Model\Lead\User::TYPE_SALE . ' SET lc.status=' . \Crm\Model\Lead\Company::STATUS_FREE . ' WHERE lc.accountId IS NULL AND lu.id IS NULL'; $updateQuery2 = 'UPDATE crm_leads_companies lc INNER JOIN crm_leads_users lu ON lc.leadId=lu.leadId AND lc.companyId=lu.companyId AND lu.type= ' . \Crm\Model\Lead\User::TYPE_SALE . ' SET lc.status = ' . \Crm\Model\Lead\Company::STATUS_BELONG . ' WHERE lc.leadId IS NOT NULL AND lc.companyId IS NOT NULL'; echo '<br/>'; echo '<br/>'; echo $updateQuery; $results = $dbAdapter->query($updateQuery, $dbAdapter::QUERY_MODE_EXECUTE); echo '<br/>'; echo '<br/>'; echo $updateQuery2; $results = $dbAdapter->query($updateQuery2, $dbAdapter::QUERY_MODE_EXECUTE); echo '<br/>'; echo '<br/>'; echo 'DONE!'; \Home\Service\Uri::autoLink('/system/auto/freelead'); } die; }
/** * Trình tự: tìm các account ko có leadCompany -> xem nếu có lead gắn với account đó thì tạo * lead company ko thôi, nếu ko có thì tạo lead rồi gắn với lead company */ public function createleadcompanyAction() { set_time_limit(300); $page = $this->getRequest()->getQuery('page', 1); $dbSql = $this->getServiceLocator()->get('dbSql'); $dbAdapter = $this->getServiceLocator()->get('dbAdapter'); $select = $dbSql->select(array('a' => \Crm\Model\AccountMapper::TABLE_NAME)); $select->join(['lc' => \Crm\Model\Lead\CompanyMapper::TABLE_NAME], 'lc.accountId=a.id', [], $select::JOIN_LEFT); $select->where(['lc.accountId IS NULL']); $query = $dbSql->buildSqlString($select); echo $query; echo '<br/>'; $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo 'Tìm thấy: ' . $rows->count() . ' result <br/>'; $leadCompanyMapper = $this->getServiceLocator()->get('\\Crm\\Model\\Lead\\CompanyMapper'); $leadMapper = $this->getServiceLocator()->get('\\Crm\\Model\\LeadMapper'); if ($rows->count()) { foreach ($rows as $row) { $row = (array) $row; $account = new \Crm\Model\Account(); $account->exchangeArray($row); if ($account->getLeadId()) { $leadCompany = new \Crm\Model\Lead\Company(); $leadCompany->setLeadId($account->getLeadId()); $leadCompany->setCompanyId($account->getCompanyId()); if ($leadCompanyMapper->isExisted($leadCompany)) { echo 'update lead_company.id ' . $leadCompany->getId() . '<br/>'; $leadCompany->setAccountId($account->getId()); } else { echo 'insert lead_company.id ' . $leadCompany->getId() . '<br/>'; $leadCompany->setAccountId($account->getId()); $leadCompany->setLastActivityDateTime($account->getCreatedDateTime()); } $leadCompanyMapper->save($leadCompany); } else { $select = $dbSql->select(array('l' => \Crm\Model\LeadMapper::TABLE_NAME)); $select->where(['accountId' => $account->getId()]); $select->limit(1); $query = $dbSql->buildSqlString($select); $rows2 = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rows2->count()) { $row2 = (array) $rows2->current(); $leadCompany = new \Crm\Model\Lead\Company(); $leadCompany->setLeadId($row2['id']); $leadCompany->setCompanyId($account->getCompanyId()); if ($leadCompanyMapper->isExisted($leadCompany)) { echo 'update lead_company.id ' . $leadCompany->getId() . '<br/>'; $leadCompany->setAccountId($account->getId()); } else { $leadCompany->setAccountId($account->getId()); $leadCompany->setLastActivityDateTime($account->getCreatedDateTime()); echo 'insert lead_company.id ' . $leadCompany->getId() . '<br/>'; } $leadCompanyMapper->save($leadCompany); } else { $leadId = null; if ($account->getMobile()) { $select = $dbSql->select(array('l' => \Crm\Model\LeadMapper::TABLE_NAME)); $predicate = new \Zend\Db\Sql\Predicate\PredicateSet(); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('mobile', '=', $account->getMobile(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('mobile2', '=', $account->getMobile(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('phone', '=', $account->getMobile(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('phone2', '=', $account->getMobile(), $predicate::OP_OR)); $select->where($predicate); $select->where(['companyId' => $this->company()->getCompanyIdsInGroup()]); $select->limit(1); $query = $dbSql->buildSqlString($select); $rowLead = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rowLead->count()) { $rowLead = (array) $rowLead->current(); $leadId = $rowLead['id']; } } if (!$leadId && $account->getMobile2()) { $select = $dbSql->select(array('l' => \Crm\Model\LeadMapper::TABLE_NAME)); $predicate = new \Zend\Db\Sql\Predicate\PredicateSet(); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('mobile', '=', $account->getMobile2(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('mobile2', '=', $account->getMobile2(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('phone', '=', $account->getMobile2(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('phone2', '=', $account->getMobile2(), $predicate::OP_OR)); $select->where($predicate); $select->where(['companyId' => $this->company()->getCompanyIdsInGroup()]); $select->limit(1); $query = $dbSql->buildSqlString($select); $rowLead = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rowLead->count()) { $rowLead = (array) $rowLead->current(); $leadId = $rowLead['id']; } } if (!$leadId && $account->getPhone()) { $select = $dbSql->select(array('l' => \Crm\Model\LeadMapper::TABLE_NAME)); $predicate = new \Zend\Db\Sql\Predicate\PredicateSet(); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('mobile', '=', $account->getPhone(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('mobile2', '=', $account->getPhone(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('phone', '=', $account->getPhone(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('phone2', '=', $account->getPhone(), $predicate::OP_OR)); $select->where($predicate); $select->where(['companyId' => $this->company()->getCompanyIdsInGroup()]); $select->limit(1); $query = $dbSql->buildSqlString($select); $rowLead = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rowLead->count()) { $rowLead = (array) $rowLead->current(); $leadId = $rowLead['id']; } } if (!$leadId && $account->getPhone2()) { $select = $dbSql->select(array('l' => \Crm\Model\LeadMapper::TABLE_NAME)); $predicate = new \Zend\Db\Sql\Predicate\PredicateSet(); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('mobile', '=', $account->getPhone(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('mobile2', '=', $account->getPhone(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('phone', '=', $account->getPhone(), $predicate::OP_OR)); $predicate->addPredicate(new \Zend\Db\Sql\Predicate\Operator('phone2', '=', $account->getPhone(), $predicate::OP_OR)); $select->where($predicate); $select->where(['companyId' => $this->company()->getCompanyIdsInGroup()]); $select->limit(1); $query = $dbSql->buildSqlString($select); $rowLead = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rowLead->count()) { $rowLead = (array) $rowLead->current(); $leadId = $rowLead['id']; } } if (!$leadId && $account->getEmail()) { $select = $dbSql->select(array('l' => \Crm\Model\LeadMapper::TABLE_NAME)); $select->where(['email' => $account->getEmail()]); $select->where(['companyId' => $this->company()->getCompanyIdsInGroup()]); $select->limit(1); $query = $dbSql->buildSqlString($select); $rowLead = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rowLead->count()) { $rowLead = (array) $rowLead->current(); $leadId = $rowLead['id']; } } if (!$leadId && $account->getWebsite()) { $select = $dbSql->select(array('l' => \Crm\Model\LeadMapper::TABLE_NAME)); $select->where(['website' => $account->getWebsite()]); $select->where(['companyId' => $this->company()->getCompanyIdsInGroup()]); $select->limit(1); $query = $dbSql->buildSqlString($select); $rowLead = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rowLead->count()) { $rowLead = (array) $rowLead->current(); $leadId = $rowLead['id']; } } if (!$leadId) { $lead = new \Crm\Model\Lead(); $lead->setMobile($account->getMobile()); $lead->setPhone($account->getPhone()); $lead->setMobile2($account->getMobile2()); $lead->setPhone2($account->getPhone2()); $lead->setEmail($account->getEmail()); $lead->setWebsite($account->getWebsite()); $lead->setAddress($account->getAddress()); $lead->setCityId($account->getCityId()); $lead->setDistrictId($account->getDistrictId()); $lead->setCompanyId($account->getCompanyId()); $lead->setCreatedById($this->user()->getIdentity()); $lead->setCreatedDate(DateBase::getCurrentDate()); $lead->setCreatedDateTime(DateBase::getCurrentDateTime()); $leadMapper->save($lead); echo 'Tạo mới lead ' . $lead->getId() . '<br/>'; $leadCompany = new \Crm\Model\Lead\Company(); $leadCompany->setLeadId($lead->getId()); $leadCompany->setCompanyId($account->getCompanyId()); $leadCompany->setAccountId($account->getId()); $leadCompany->setLastActivityDateTime($account->getCreatedDateTime()); $leadCompanyMapper->save($leadCompany); echo 'insert lead_company.id ' . $leadCompany->getId() . '<br/>'; } else { $leadCompany = new \Crm\Model\Lead\Company(); $leadCompany->setLeadId($leadId); $leadCompany->setCompanyId($account->getCompanyId()); $leadCompanyMapper->isExisted($leadCompany); $leadCompany->setAccountId($account->getId()); $leadCompany->setLastActivityDateTime($leadCompany->getLastActivityDateTime() ?: $account->getCreatedDateTime()); $leadCompanyMapper->save($leadCompany); echo 'update lead_company.id ' . $leadCompany->getId() . '<br/>'; } } } } } die; }
public function deleteaccountAction() { $dbSql = $this->getServiceLocator()->get('dbSql'); $dbAdapter = $this->getServiceLocator()->get('dbAdapter'); //Lấy các account chưa có hợp đồng hoặc hợp đồng chưa có phiếu thu được duyệt $select = $dbSql->select(['a' => \Crm\Model\AccountMapper::TABLE_NAME]); $select->join(['c' => \Crm\Model\ContractMapper::TABLE_NAME], 'c.accountId=a.id', [], $select::JOIN_LEFT); $select->join(['cp' => \Crm\Model\Contract\PaymentMapper::TABLE_NAME], 'cp.contractId=c.id', [], $select::JOIN_LEFT); $predicateSet = new PredicateSet(); $predicateSet->addPredicate(new IsNull('cp.id'), $predicateSet::OP_OR); $predicateSet->addPredicate(new In('cp.status', [\Crm\Model\Contract\Payment::STATUS_DELETED, \Crm\Model\Contract\Payment::STATUS_UNCHECKED]), $predicateSet::OP_OR); $select->where($predicateSet); $select->group(['a.id']); $query = $dbSql->buildSqlString($select); echo $query; $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); $accountIds = []; if ($rows) { foreach ($rows->toArray() as $row) { $accountIds[$row['id']] = $row['id']; } } // Loại bỏ các account trong danh sách đã có hợp đồng có phiếu thu dc duyệt $select = $dbSql->select(['c' => \Crm\Model\ContractMapper::TABLE_NAME]); $select->join(['cp' => \Crm\Model\Contract\PaymentMapper::TABLE_NAME], 'cp.contractId=c.id', []); $select->columns(['accountId']); $select->where(['cp.status' => \Crm\Model\Contract\Payment::STATUS_CHECKED]); $select->where(['c.accountId' => $accountIds]); $select->group(['c.accountId']); $query = $dbSql->buildSqlString($select); $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rows) { foreach ($rows->toArray() as $row) { unset($accountIds[$row['accountId']]); } } // Loại bỏ các account có liên kết đặc biệt trong 1 số bảng $fks = array(array('table' => \Contact\Model\ContactMapper::TABLE_NAME, 'column' => 'crmAccountId'), array('table' => \Crm\Model\Account\ProductMapper::TABLE_NAME, 'column' => 'accountId'), array('table' => \Crm\Model\ContactMapper::TABLE_NAME, 'column' => 'accountId'), array('table' => \Crm\Model\ContractMapper::TABLE_NAME, 'column' => 'refererAccountId'), array('table' => \Crm\Model\Lead\CompanyMapper::TABLE_NAME, 'column' => 'sourceAccountId'), array('table' => \Idea\Model\IdeaMapper::TABLE_NAME, 'column' => 'crmAccountId'), array('table' => \Work\Model\MeetingMapper::TABLE_NAME, 'column' => 'crmAccountId'), array('table' => \Work\Model\PlanMapper::TABLE_NAME, 'column' => 'crmAccountId'), array('table' => \Work\Model\ProjectUserMapper::TABLE_NAME, 'column' => 'crmAccountId'), array('table' => \Work\Model\Task\RequirementMapper::TABLE_NAME, 'column' => 'accountId'), array('table' => \Work\Model\TaskMapper::TABLE_NAME, 'column' => 'crmAccountId')); foreach ($fks as $fk) { if (count($accountIds)) { $select = $dbSql->select($fk['table']); $select->where([$fk['column'] => $accountIds]); $select->columns([$fk['column']]); $select->group([$fk['column']]); $query = $dbSql->buildSqlString($select); $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rows) { foreach ($rows->toArray() as $row) { unset($accountIds[$row[$fk['column']]]); } } } } if (count($accountIds)) { $select = $dbSql->select(['a' => \Crm\Model\AccountMapper::TABLE_NAME]); $select->join(['lc' => \Crm\Model\Lead\CompanyMapper::TABLE_NAME], 'lc.accountId=a.id', ['accountId', 'companyId', 'leadId'], $select::JOIN_LEFT); $select->columns([]); $select->where(['a.id' => $accountIds]); $select->where(['lc.leadId IS NOT NULL']); $select->group(['a.id']); $query = $dbSql->buildSqlString($select); echo $query; echo '<br/>=================</br/>'; $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rows) { $leadCompanyMapper = $this->getServiceLocator()->get('\\Crm\\Model\\Lead\\CompanyMapper'); $contractMapper = $this->getServiceLocator()->get('\\Crm\\Model\\ContractMapper'); foreach ($rows->toArray() as $row) { // update lead user $update = $dbSql->update(\Crm\Model\Lead\UserMapper::TABLE_NAME); $update->set(['leadId' => $row['leadId'], 'accountId' => null]); $update->where(['accountId' => $row['accountId']]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update activity $update = $dbSql->update(\Crm\Model\ActivityMapper::TABLE_NAME); $update->set(['leadId' => $row['leadId'], 'accountId' => null]); $update->where(['accountId' => $row['accountId']]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update contract $update = $dbSql->update(\Crm\Model\ContractMapper::TABLE_NAME); $update->set(['leadId' => $row['leadId'], 'accountId' => null]); $update->where(['accountId' => $row['accountId']]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update lead company $update = $dbSql->update(\Crm\Model\Lead\CompanyMapper::TABLE_NAME); $update->set(['leadId' => $row['leadId'], 'accountId' => null]); $update->where(['accountId' => $row['accountId']]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update lead $update = $dbSql->update(\Crm\Model\LeadMapper::TABLE_NAME); $update->set(['accountId' => null]); $update->where(['accountId' => $row['accountId']]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // xoa account $delete = $dbSql->delete(\Crm\Model\AccountMapper::TABLE_NAME); $delete->where(['id' => $row['accountId']]); $query = $dbSql->buildSqlString($delete); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; echo '<br/>========================<br/>'; unset($accountIds[$row['accountId']]); } } } if (count($accountIds)) { $select = $dbSql->select(['a' => \Crm\Model\AccountMapper::TABLE_NAME]); $select->where(['id' => $accountIds]); $query = $dbSql->buildSqlString($select); $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rows) { foreach ($rows->toArray() as $row) { $account = new \Crm\Model\Account(); $account->exchangeArray($row); if ($account->getLeadId()) { // update lead user $update = $dbSql->update(\Crm\Model\Lead\UserMapper::TABLE_NAME); $update->set(['leadId' => $account->getLeadId(), 'accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update activity $update = $dbSql->update(\Crm\Model\ActivityMapper::TABLE_NAME); $update->set(['leadId' => $account->getLeadId(), 'accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update contract $update = $dbSql->update(\Crm\Model\ContractMapper::TABLE_NAME); $update->set(['leadId' => $account->getLeadId(), 'accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update lead company $update = $dbSql->update(\Crm\Model\Lead\CompanyMapper::TABLE_NAME); $update->set(['leadId' => $account->getLeadId(), 'accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update lead $update = $dbSql->update(\Crm\Model\LeadMapper::TABLE_NAME); $update->set(['accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // xoa account $delete = $dbSql->delete(\Crm\Model\AccountMapper::TABLE_NAME); $delete->where(['id' => $account->getId()]); $query = $dbSql->buildSqlString($delete); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; echo '<br/>========================<br/>'; unset($accountIds[$account->getId()]); } else { // update lead user $update = $dbSql->update(\Crm\Model\Lead\UserMapper::TABLE_NAME); $update->set(['accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update activity $update = $dbSql->update(\Crm\Model\ActivityMapper::TABLE_NAME); $update->set(['accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update contract $update = $dbSql->update(\Crm\Model\ContractMapper::TABLE_NAME); $update->set(['accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update lead company $update = $dbSql->update(\Crm\Model\Lead\CompanyMapper::TABLE_NAME); $update->set(['accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // update lead $update = $dbSql->update(\Crm\Model\LeadMapper::TABLE_NAME); $update->set(['accountId' => null]); $update->where(['accountId' => $account->getId()]); $query = $dbSql->buildSqlString($update); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; // xoa account $delete = $dbSql->delete(\Crm\Model\AccountMapper::TABLE_NAME); $delete->where(['id' => $account->getId()]); $query = $dbSql->buildSqlString($delete); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; echo '<br/>========================<br/>'; unset($accountIds[$account->getId()]); } } } } die; }
/** * @param $mess \Admin\Model\MessagesMG */ public function reportdetail($mess) { $dm = $this->getServiceLocator()->get('doctrine.documentmanager.odm_default'); $select = $this->getDbSql()->select(['ca' => self::TABLE_NAME]); $predicateSet = new PredicateSet(); $predicateSet->addPredicate(new Operator('ca.callcenter', Operator::OPERATOR_EQUAL_TO, $mess->getSender()), $predicateSet::OP_OR); $predicateSet->addPredicate(new Operator('ca.mentor', Operator::OPERATOR_EQUAL_TO, $mess->getSender()), $predicateSet::OP_OR); $select->where($predicateSet); $select->where(['ca.endedDate' => DateBase::toCommonDate($mess->getCreated())]); $query = $this->getDbSql()->buildSqlString($select); $rows = $this->getDbAdapter()->query($query, Adapter::QUERY_MODE_EXECUTE); $result = []; $time = ''; foreach ($rows as $r) { $result[DateBase::toDisplayDateTime($r['createdDateTime']) . ' - ' . DateBase::toDisplayDateTime($r['endedDateTime'])] = ['rating' => $r['rating'], 'comment' => $r['note']]; $createdDatetime = new \DateTime($r['createdDateTime']); $endDatetime = new \DateTime($r['endedDateTime']); $timediff = date_diff($createdDatetime, $endDatetime); $result[DateBase::toDisplayDateTime($r['createdDateTime']) . ' - ' . DateBase::toDisplayDateTime($r['endedDateTime'])]['time'] += $timediff->format('%i'); $qb = $dm->createQueryBuilder('Admin\\Model\\MessagesMG'); $qb->field('receiver')->equals($r['room']); $qb->field('created')->range($createdDatetime, $endDatetime); $querymg = $qb->getQuery(); $resultmg = $querymg->execute(); $i = 0; /** @var \Admin\Model\MessagesMG $m */ foreach ($resultmg as $m) { $i++; $result[DateBase::toDisplayDateTime($r['createdDateTime']) . ' - ' . DateBase::toDisplayDateTime($r['endedDateTime'])]['messages'][] = ['sender' => $m->getSender(), 'room' => $m->getReceiver(), 'msg' => $m->getMsg(), 'imgPath' => $m->getImgPath(), 'created' => $m->getCreated()->format(DateBase::DISPLAY_DATETIME_FORMAT)]; // vdump($i.'. Sender: '.$m->getSender().'|| Receiver: '.$m->getReceiver().' || Msg: '.$m->getMsg().' || Created: '.$m->getCreated()->format(DateBase::DISPLAY_DATETIME_FORMAT)); } } return $result; // if($mess->getSender()){ // $qb->field('sender')->equals($mess->getSender()); // } }
public function parenthese($data, $predicate = 'AND', $convert = FALSE) { $parenthese = new PredicateSet(); $data = $this->extractWhere($data, $predicate, $convert); foreach ($data as $item) { $item = $this->convertValues(self::WHERE, $item); $item = $item[0]; $parenthese->addPredicate($item[0], $item[1]); } return $parenthese; }
public function buildSelect($conditions = array()) { $select = $this->getSql()->select(); 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['where'][$this->table . '.bool_deleted'])) { $conditions['where'][$this->table . '.bool_deleted'] = '0'; } if ($this->hasTenant() && !isset($conditions['where'][$this->table . '.nid_tenant']) && defined('NID_TENANT')) { $conditions['where'][$this->table . '.nid_tenant'] = NID_TENANT; } 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']); } //if (defined('DEBUG') && DEBUG==4) print_ar($conditions); //if (defined('DEBUG') && DEBUG==4) {echo str_replace('"', '', $select->getSqlString());exit;} return $select; }