/**
  * @author KienNN
  * @param \Accounting\Model\Transaction $item
  * @param unknown $options
  */
 public function searchCrmContract($item, $options)
 {
     $select = $this->getDbSql()->select(['t' => self::TABLE_NAME]);
     $select->join(['c' => \Crm\Model\ContractMapper::TABLE_NAME], 't.itemId=c.id', []);
     $select->where(['t.itemType' => \Accounting\Model\Transaction::ITEM_TYPE_CRM_CONTRACT]);
     if ($item->getCompanyId()) {
         $select->where(['t.companyId' => $item->getCompanyId()]);
     }
     if ($item->getOption('companyIds')) {
         $select->where(['t.companyId' => $item->getOption('companyIds')]);
     }
     if ($item->getOption('userIds')) {
         $select->join(['lu' => \Crm\Model\Lead\UserMapper::TABLE_NAME], new Expression('lu.accountId=c.accountId OR lu.leadId=c.leadId'), []);
         $select->where(['lu.userId' => $item->getOption('userIds')]);
     }
     if ($item->getId()) {
         $select->where(['t.id' => $item->getId()]);
     }
     if ($item->getStatus()) {
         $select->where(['t.status' => $item->getStatus()]);
     }
     if ($item->getType()) {
         $select->where(['t.type' => $item->getType()]);
     }
     if ($item->getCreatedById()) {
         $select->where(['t.createdById' => $item->getCreatedById()]);
     }
     if ($item->getOption('departmentId')) {
         $select->where(['c.departmentId' => $item->getOption('departmentId')]);
     }
     if ($item->getOption('contractId')) {
         $select->where(['t.itemId' => $item->getOption('contractId')]);
     }
     if ($item->getOption('productId')) {
         $select->join(['cp' => \Crm\Model\Contract\ProductMapper::TABLE_NAME], 'cp.contractId=t.itemId', []);
         $select->where(['cp.productId' => $item->getOption('productId')]);
     }
     if ($item->getOption('accountingType')) {
         $select->join(['i' => \Accounting\Model\Transaction\ItemMapper::TABLE_NAME], 'i.transactionId=t.id', []);
         $condition = new Expression('i.creditAccountId=ac.id OR i.debitAccountId=ac.id');
         $select->join(['ac' => \Accounting\Model\AccountMapper::TABLE_NAME], $condition, []);
         $select->where(['ac.type' => $item->getOption('accountingType')]);
     }
     if ($item->getOption('accountId')) {
         $select->where(['c.accountId' => $item->getOption('accountId')]);
     }
     if ($item->getOption('leadId')) {
         $select->where(['c.leadId' => $item->getOption('leadId')]);
     }
     if ($item->getOption('fromApplyDate')) {
         $select->where(['t.applyDate >= ?' => $item->getOption('fromApplyDate')]);
         $select->where(['t.accountingById IS NOT NULL']);
     }
     if ($item->getOption('toApplyDate')) {
         $select->where(['t.applyDate <= ?' => $item->getOption('toApplyDate')]);
         $select->where(['t.accountingById IS NOT NULL']);
     }
     if ($item->getOption('fromCreatedDate')) {
         $select->where(['t.createdDate >= ?' => $item->getOption('fromCreatedDate')]);
     }
     if ($item->getOption('toCreatedDate')) {
         $select->where(['t.createdDate <= ?' => $item->getOption('toCreatedDate')]);
     }
     if ($item->getCreatedById()) {
         $select->where(['t.createdById' => $item->getCreatedById()]);
     }
     if ($item->getAccountingById()) {
         $select->where(['t.accountingById' => $item->getAccountingById()]);
     }
     if ($item->getOption('commisstionToEmployeeId')) {
         $select->join(['com' => \Crm\Model\Contract\CommissionMapper::TABLE_NAME], 't.itemId=com.contractId', []);
         $select->where(['com.employeeId' => $item->getOption('commisstionToEmployeeId')]);
     }
     $select->group(['t.id']);
     $select->order([new Expression('IFNULL(t.applyDate, t.createdDate) DESC'), 't.id DESC']);
     $paginator = $this->preparePaginator($select, $options, new \Accounting\Model\Transaction());
     $contractIds = [];
     $userIds = [];
     $transactionIds = [];
     foreach ($paginator as $transaction) {
         if ($transaction->getItemId()) {
             $contractIds[$transaction->getItemId()] = $transaction->getItemId();
         }
         if ($transaction->getCreatedById()) {
             $userIds[$transaction->getCreatedById()] = $transaction->getCreatedById();
         }
         if ($transaction->getApprovedById()) {
             $userIds[$transaction->getApprovedById()] = $transaction->getApprovedById();
         }
         if ($transaction->getAccountingById()) {
             $userIds[$transaction->getAccountingById()] = $transaction->getAccountingById();
         }
         if ($transaction->getPaymentById()) {
             $userIds[$transaction->getPaymentById()] = $transaction->getPaymentById();
         }
         $transactionIds[$transaction->getId()] = $transaction->getId();
     }
     $crmAccounts = [];
     $crmLeads = [];
     $contractValues = [];
     $contractPaids = [];
     if (count($contractIds)) {
         if ($item->getOption('loadCustomer')) {
             $select = $this->getDbSql()->select(['a' => \Crm\Model\AccountMapper::TABLE_NAME]);
             $select->join(['c' => \Crm\Model\ContractMapper::TABLE_NAME], 'c.accountId=a.id', ['contractId' => 'id']);
             $select->where(['c.id' => $contractIds]);
             $rows = $this->getDbAdapter()->query($this->getDbSql()->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE);
             if ($rows->count()) {
                 foreach ($rows->toArray() as $row) {
                     $account = new \Crm\Model\Account();
                     $account->exchangeArray($row);
                     $crmAccounts[$row['contractId']] = $account;
                 }
             }
             $select = $this->getDbSql()->select(['l' => \Crm\Model\LeadMapper::TABLE_NAME]);
             $select->join(['c' => \Crm\Model\ContractMapper::TABLE_NAME], 'c.leadId=l.id', ['contractId' => 'id']);
             $select->where(['c.id' => $contractIds]);
             $rows = $this->getDbAdapter()->query($this->getDbSql()->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE);
             if ($rows->count()) {
                 foreach ($rows->toArray() as $row) {
                     $lead = new \Crm\Model\Lead();
                     $lead->exchangeArray($row);
                     $crmLeads[$row['contractId']] = $lead;
                 }
             }
         }
         if ($item->getOption('loadContractValue')) {
             $select = $this->getDbSql()->select(['p' => \Crm\Model\Contract\ProductMapper::TABLE_NAME]);
             $select->where(['contractId' => $contractIds]);
             $rows = $this->getDbAdapter()->query($this->getDbSql()->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE);
             if ($rows->count()) {
                 foreach ($rows->toArray() as $row) {
                     $product = new \Crm\Model\Contract\Product($row);
                     $value = $product->calculateValue();
                     if (isset($contractValues[$product->getContractId()])) {
                         $contractValues[$product->getContractId()] += $value;
                     } else {
                         $contractValues[$product->getContractId()] = $value;
                     }
                 }
             }
         }
         if ($item->getOption('loadContractPaid')) {
             $select = $this->getDbSql()->select(['t' => self::TABLE_NAME]);
             $select->where(['itemType' => \Accounting\Model\Transaction::ITEM_TYPE_CRM_CONTRACT]);
             $select->where(['itemId' => $contractIds]);
             $select->where(['status' => [\Accounting\Model\Transaction::STATUS_ACCOUNTING, \Accounting\Model\Transaction::STATUS_PAYMENT]]);
             $rows = $this->getDbAdapter()->query($this->getDbSql()->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE);
             if ($rows->count()) {
                 foreach ($rows->toArray() as $row) {
                     if (isset($contractPaids[$row['itemId']])) {
                         $contractPaids[$row['itemId']] += $row['amount'];
                     } else {
                         $contractPaids[$row['itemId']] = $row['amount'];
                     }
                 }
             }
         }
     }
     $accountingAccounts = [];
     if ($item->getOption('loadAccountingAccount') && count($transactionIds)) {
         $select = $this->getDbSql()->select(['a' => \Accounting\Model\AccountMapper::TABLE_NAME]);
         $select->join(['i' => \Accounting\Model\Transaction\ItemMapper::TABLE_NAME], new Expression('i.creditAccountId=a.id OR i.debitAccountId=a.id'), ['transactionId' => 'transactionId']);
         $select->where(['i.transactionId' => $transactionIds]);
         $select->group(['i.transactionId']);
         $rows = $this->getDbAdapter()->query($this->getDbSql()->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE);
         if ($rows->count()) {
             foreach ($rows->toArray() as $row) {
                 $accountingAccount = new \Accounting\Model\Account();
                 $accountingAccount->exchangeArray($row);
                 $accountingAccounts[$row['transactionId']] = $accountingAccount;
             }
         }
     }
     $users = [];
     if ($item->getOption('loadUsers') && count($userIds)) {
         $select = $this->getDbSql()->select(['u' => \User\Model\UserMapper::TABLE_NAME]);
         $select->where(['id' => $userIds]);
         $rows = $this->getDbAdapter()->query($this->getDbSql()->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE);
         if ($rows->count()) {
             foreach ($rows->toArray() as $row) {
                 $user = new \User\Model\User();
                 $user->exchangeArray($row);
                 $users[$user->getId()] = $user;
             }
         }
     }
     if ($paginator->getCurrentModels()) {
         foreach ($paginator->getCurrentModels() as $transaction) {
             if ($transaction->getItemId() && isset($crmAccounts[$transaction->getItemId()])) {
                 $transaction->addOption('crmAccount', $crmAccounts[$transaction->getItemId()]);
             }
             if ($transaction->getItemId() && isset($crmLeads[$transaction->getItemId()])) {
                 $transaction->addOption('crmLead', $crmLeads[$transaction->getItemId()]);
             }
             if (isset($accountingAccounts[$transaction->getId()])) {
                 $transaction->addOption('accountingAccount', $accountingAccounts[$transaction->getId()]);
             }
             if ($transaction->getCreatedById() && isset($users[$transaction->getCreatedById()])) {
                 $transaction->addOption('createdBy', $users[$transaction->getCreatedById()]);
             }
             if ($transaction->getAccountingById() && isset($users[$transaction->getAccountingById()])) {
                 $transaction->addOption('accountingBy', $users[$transaction->getAccountingById()]);
             }
             if ($transaction->getPaymentById() && isset($users[$transaction->getPaymentById()])) {
                 $transaction->addOption('paymentBy', $users[$transaction->getCreatedById()]);
             }
             if (isset($contractValues[$transaction->getItemId()])) {
                 $transaction->addOption('contractValue', $contractValues[$transaction->getItemId()]);
             }
             if (isset($contractPaids[$transaction->getItemId()])) {
                 $transaction->addOption('contractPaid', $contractPaids[$transaction->getItemId()]);
             }
         }
     }
     return $paginator;
 }
 /**
  * Rà soát bảng crm_contracts của office, với status = 1, lấy ra customer_id
  * Nếu account có officeId tương ứng thì bỏ qua
  * Đối soát lại bảng lead dựa theo customer_id
  * Nếu lead đã có accountId, bỏ qua
  * Nếu không, lấy thông tin của lead rà soát xem đã tồn tại account như vậy chưa
  *      Nếu đã tồn tại account, update 1 số thông tin cho account
  *      Nếu không tạo mới account
  * Update accountId cho leadUser và lead
  */
 public function officeconvertaccountAction()
 {
     list($officeAdapter, $officeSql) = $this->createOfficeAdapter();
     $dbAdapter = $this->getServiceLocator()->get('dbAdapter');
     /*@var $dbAdapter \Zend\Db\Adapter\Adapter */
     $dbSql = $this->getServiceLocator()->get('dbSql');
     $company = new \Company\Model\Company();
     $companyMapper = $this->getServiceLocator()->get('\\Company\\Model\\CompanyMapper');
     $groupCompanyIds = $companyMapper->getAllCompanyIdsInGroup(10);
     $select = $officeSql->select(['cc' => 'crm_contracts']);
     $select->where(['customer_id IS NOT NULL']);
     $select->where(['status' => '1']);
     $select->group(['customer_id']);
     $select->order(['customer_id']);
     $paginatorAdapter = new \Zend\Paginator\Adapter\DbSelect($select, $officeAdapter);
     $paginator = new \Zend\Paginator\Paginator($paginatorAdapter);
     $paginator->setItemCountPerPage(100);
     $page = $this->getRequest()->getQuery('page', 1);
     $totalCreate = $this->getRequest()->getQuery('totalCreate', 0);
     $totalUpdate = $this->getRequest()->getQuery('totalUpdate', 0);
     $paginator->setCurrentPageNumber($page);
     $leadMapper = $this->getServiceLocator()->get('\\Crm\\Model\\LeadMapper');
     $informationMapper = $this->getServiceLocator()->get('\\Contact\\Model\\Contact\\InformationMapper');
     $accountMapper = $this->getServiceLocator()->get('\\Crm\\Model\\AccountMapper');
     foreach ($paginator as $contractRow) {
         $contractRow = (array) $contractRow;
         $oneofficeId = $contractRow['customer_id'];
         //Đối soát lại bảng lead dựa theo customer_id
         $lead = new \Crm\Model\Lead();
         $lead->setOneofficeId($oneofficeId);
         if (!$leadMapper->isExistedOneofficeId($lead)) {
             continue;
         }
         //Nếu lead đã có accountId, bỏ qua
         if ($lead->getAccountId()) {
             continue;
         }
         //Nếu không, lấy thông tin của lead rà soát xem đã tồn tại account như vậy chưa
         $information = new \Contact\Model\Contact\Information();
         $information->setItemId($lead->getId());
         $information->setType(\Contact\Model\Contact\Information::TYPE_CRM_LEAD);
         $informations = $informationMapper->fetchAll($information);
         $accountId = null;
         if (count($informations)) {
             foreach ($informations as $information) {
                 $checkInfo = new \Contact\Model\Contact\Information();
                 $checkInfo->setContent($information->getContent());
                 $checkInfo->addOption('companyIds', $groupCompanyIds);
                 $checkInfo->setType(\Contact\Model\Contact\Information::TYPE_CRM_ACCOUNT);
                 $checkInfo->setItemType($information->getItemType());
                 if ($informationMapper->isExisted($checkInfo)) {
                     $accountId = $checkInfo->getItemId();
                     break;
                 }
             }
         }
         //Nếu như chưa tồn tại account thì tạo mới dựa trên lead
         if (!$accountId) {
             $account = new \Crm\Model\Account();
             $account->setCompanyId($lead->getCompanyId());
             $account->setCityId($lead->getCityId());
             $account->exchangeArray(array('companyId' => $lead->getCompanyId(), 'type' => \Crm\Model\Account::TYPE_PERSONAL, 'name' => $lead->getName() ?: $lead->getCompanyName(), 'cityId' => $lead->getCityId() ?: 2, 'districtId' => $lead->getDistrictId() ?: 1, 'address' => $lead->getAddress() ?: '', 'nhanhStoreId' => $lead->getNhanhStoreId(), 'nhanhStoreName' => $lead->getNhanhStoreName()));
             $account->setCreatedById($this->user()->getIdentity());
             $account->setCreatedDate(DateBase::getCurrentDate());
             $account->setCreatedDateTime(DateBase::getCurrentDateTime());
             $accountMapper->save($account);
         } else {
             $account = new \Crm\Model\Account();
             $account->setId($accountId);
             if (!$accountMapper->get($account)) {
                 continue;
             }
         }
         // update information cho account
         $infor1 = new \Contact\Model\Contact\Information();
         $infor1->setType(\Contact\Model\Contact\Information::TYPE_CRM_LEAD);
         $infor1->setItemId($lead->getId());
         $infor2 = new \Contact\Model\Contact\Information();
         $infor2->setType(\Contact\Model\Contact\Information::TYPE_CRM_ACCOUNT);
         $infor2->setItemId($account->getId());
         $infoDiffs = $informationMapper->compare($infor1, $infor2);
         if (count($infoDiffs)) {
             foreach ($infoDiffs as $infoData) {
                 $infor = new \Contact\Model\Contact\Information();
                 $infor->exchangeArray($infoData);
                 $infor->setType(\Contact\Model\Contact\Information::TYPE_CRM_ACCOUNT);
                 $infor->setItemId($account->getId());
                 $infor->setCompanyId($account->getCompanyId());
                 if (!$informationMapper->isExisted($infor)) {
                     $informationMapper->save($infor);
                 }
             }
         }
         // update accountId cho leadUser và lead
         $lead->setAccountId($account->getId());
         $leadMapper->save($lead);
         $leadUser = new \Crm\Model\Lead\User();
         $leadUserMapper = $this->getServiceLocator()->get('\\Crm\\Model\\Lead\\UserMapper');
         $leadUserMapper->updateColumns(['accountId' => $account->getId()], ['leadId' => $lead->getId(), 'accountId IS NULL']);
     }
     $this->getViewModel()->setTerminal(true);
     $this->getViewModel()->setVariable('paginator', $paginator);
     $this->getViewModel()->setVariable('page', $page);
     $this->getViewModel()->setVariable('totalPages', $paginator->count() + 1);
     $this->getViewModel()->setVariable('totalCreate', $totalCreate);
     $this->getViewModel()->setVariable('totalUpdate', $totalUpdate);
     if ($paginator->count()) {
         $this->getViewModel()->setVariable('redirectUri', Uri::build('/system/tool/officeconvertaccount', ['page' => $page + 1, 'totalCreate' => $totalCreate, 'totalUpdate' => $totalUpdate]));
     }
     return $this->getViewModel();
 }