public function testCanPassPredicatesAndDefaultCombinationViaConstructor()
{
$set = new PredicateSet(array(new IsNull('foo'), new IsNull('bar')), 'OR');
$parts = $set->getWhereParts();
$this->assertEquals(3, count($parts));
$this->assertContains('OR', $parts[1]);
$this->assertNotContains('AND', $parts[1]);
}
/**
* @covers Zend\Db\Sql\Predicate\PredicateSet::addPredicates
*/
public function testAddPredicates()
{
$predicateSet = new PredicateSet();
$predicateSet->addPredicates('x = y');
$predicateSet->addPredicates(array('foo > ?' => 5));
$predicateSet->addPredicates(array('id' => 2));
$predicateSet->addPredicates(array('a = b'), PredicateSet::OP_OR);
$predicateSet->addPredicates(array('c1' => null));
$predicateSet->addPredicates(array('c2' => array(1, 2, 3)));
$predicateSet->addPredicates(array(new \Zend\Db\Sql\Predicate\IsNotNull('c3')));
$predicates = $this->readAttribute($predicateSet, 'predicates');
$this->assertEquals('AND', $predicates[0][0]);
$this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\Literal', $predicates[0][1]);
$this->assertEquals('AND', $predicates[1][0]);
$this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\Expression', $predicates[1][1]);
$this->assertEquals('AND', $predicates[2][0]);
$this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\Operator', $predicates[2][1]);
$this->assertEquals('OR', $predicates[3][0]);
$this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\Literal', $predicates[3][1]);
$this->assertEquals('AND', $predicates[4][0]);
$this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\IsNull', $predicates[4][1]);
$this->assertEquals('AND', $predicates[5][0]);
$this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\In', $predicates[5][1]);
$this->assertEquals('AND', $predicates[6][0]);
$this->assertInstanceOf('Zend\\Db\\Sql\\Predicate\\IsNotNull', $predicates[6][1]);
$test = $this;
$predicateSet->addPredicates(function ($what) use($test, $predicateSet) {
$test->assertSame($predicateSet, $what);
});
$this->setExpectedException('Zend\\Db\\Sql\\Exception\\InvalidArgumentException', 'Predicate cannot be null');
$predicateSet->addPredicates(null);
}
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 testCanUseOrPredicateAndAndPredicateMethods()
{
$predicateSet = new PredicateSet();
$predicateSet->orPredicate(new IsNull('foo'))->andPredicate(new IsNull('bar'))->orPredicate(new IsNull('baz'))->andPredicate(new IsNull('bat'));
$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;
}
