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