/** * 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; }