public function mergerleadcompanyAction() { $dbSql = $this->getServiceLocator()->get('dbSql'); $dbAdapter = $this->getServiceLocator()->get('dbAdapter'); $delete = $dbSql->delete(\Crm\Model\Lead\CompanyMapper::TABLE_NAME); $delete->where(['leadId IS NULL', 'accountId IS NULL', 'opportunityId IS NULL']); $query = $dbSql->buildSqlString($delete); $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo $query; echo '<br/>'; //$rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); $select = $dbSql->select(['lc' => \Crm\Model\Lead\CompanyMapper::TABLE_NAME]); $select->columns(['companyId', 'leadId', 'totalDuplicate' => new Expression('COUNT(id)')]); $select->group(['companyId', 'leadId']); $select->having(['totalDuplicate > 1']); $query = $dbSql->buildSqlString($select); $rows = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); $leadCompanyMapper = $this->getServiceLocator()->get('\\Crm\\Model\\Lead\\CompanyMapper'); if ($rows) { foreach ($rows->toArray() as $row) { $select = $dbSql->select(['lc' => \Crm\Model\Lead\CompanyMapper::TABLE_NAME]); $select->where(['companyId' => $row['companyId']]); if ($row['leadId']) { $select->where(['leadId' => $row['leadId']]); } else { $select->where(['leadId IS NULL']); } $query = $dbSql->buildSqlString($select); echo $query; echo '<br/>'; $rows2 = $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); if ($rows2) { $leadCompany = new \Crm\Model\Lead\Company(); foreach ($rows2->toArray() as $row2) { $leadCompany->setId($row2['id']); $leadCompany->setLeadId($row2['leadId']); $leadCompany->setCompanyId($row2['companyId']); if ($row2['opportunityId']) { $leadCompany->setOpportunityId($row2['opportunityId']); } if ($row2['accountId']) { $leadCompany->setAccountId($row2['accountId']); } if ($row2['lastActivityDateTime']) { $leadCompany->setLastActivityDateTime($row2['lastActivityDateTime']); } if ($row2['releaseDateTime']) { $leadCompany->setReleaseDateTime($row2['releaseDateTime']); } if ($row2['source']) { $leadCompany->setSource($row2['source']); } if ($row2['sourceReference']) { $leadCompany->setSourceReference($row2['sourceReference']); } if ($row2['sourceCampaignId']) { $leadCompany->setSourceCampaignId($row2['sourceCampaignId']); } if ($row2['sourceAccountId']) { $leadCompany->setSourceAccountId($row2['sourceAccountId']); } if ($row2['sourceContactId']) { $leadCompany->setSourceContactId($row2['sourceContactId']); } if ($row2['sourceEmployeeId']) { $leadCompany->setSourceEmployeeId($row2['sourceEmployeeId']); } if ($row2['status']) { $leadCompany->setStatus($row2['status']); } } $leadCompanyMapper->save($leadCompany); vdump(array('id' => $leadCompany->getId(), 'companyId' => $leadCompany->getCompanyId() ?: null, 'leadId' => $leadCompany->getLeadId() ?: null, 'accountId' => $leadCompany->getAccountId() ?: null, 'opportunityId' => $leadCompany->getOpportunityId() ?: null, 'releaseDateTime' => $leadCompany->getReleaseDateTime() ?: null, 'lastActivityDateTime' => $leadCompany->getLastActivityDateTime() ?: null, 'source' => $leadCompany->getSource() ?: null, 'sourceReference' => $leadCompany->getSourceReference() ?: null, 'sourceCampaignId' => $leadCompany->getSourceCampaignId() ?: null, 'sourceAccountId' => $leadCompany->getSourceAccountId() ?: null, 'sourceContactId' => $leadCompany->getSourceContactId() ?: null, 'sourceEmployeeId' => $leadCompany->getSourceEmployeeId() ?: null, 'status' => $leadCompany->getStatus() ?: null)); $delete = $dbSql->delete(\Crm\Model\Lead\CompanyMapper::TABLE_NAME); $delete->where(['companyId' => $leadCompany->getCompanyId()]); $delete->where(['leadId' => $leadCompany->getLeadId()]); $delete->where(['id != ?' => $leadCompany->getId()]); $query = $dbSql->buildSqlString($delete); echo $query; $dbAdapter->query($query, $dbAdapter::QUERY_MODE_EXECUTE); echo '<br/>'; echo '================'; echo '<br/>'; unset($leadCompany); } } } die; }