public function getList()
{
$entityManager = $this->getEntityManager();
$user = $this->getCurrentUser();
// Get staff group
$staffGroup = $entityManager->find('User\\Entity\\UserGroup', UserGroup::ADMIN_GROUP_ID);
$staffList = $entityManager->getRepository('User\\Entity\\User');
//->findBy(array('group' => $freelancerGroup));
$queryBuilder = $staffList->createQueryBuilder('user');
// check search condition
$request = $this->getRequest();
if ($request->getQuery('search') && $request->getQuery('type')) {
$role = $entityManager->getRepository('User\\Entity\\Roles')->findBy(array('id' => $request->getQuery('type')));
$queryBuilder->leftJoin('user.staff', 'staff')->where('staff.client=?1 and staff.type=?2')->setParameter(1, $user)->setParameter(2, $role);
} else {
//$queryBuilder->leftJoin('user.staff', 'staff')->where('staff.client=?1')->setParameter(1, $user);
}
$queryBuilder->andWhere("user.group = ?3")->setParameter(3, $staffGroup);
if ($request->getQuery('search')) {
// search by country aa
if ($request->getQuery('country')) {
$country = $entityManager->getRepository('User\\Entity\\Country')->findBy(array('id' => (int) $request->getQuery('country')));
$queryBuilder->andWhere("user.country=?3")->setParameter(3, $country);
}
// search by name
if ($request->getQuery('name')) {
$arrayName = explode(' ', $request->getQuery('name'));
if (count($arrayName) != 2) {
$queryBuilder->andWhere("user.firstName like '%" . $request->getQuery('name') . "%' OR user.lastName like '%" . $request->getQuery('name') . "%'");
} else {
$queryBuilder->andWhere("user.firstName like '%" . $arrayName[0] . "%' OR user.lastName like '%" . $arrayName[1] . "%'");
}
}
if ($request->getQuery('email')) {
$queryBuilder->andWhere("user.email like '%" . $request->getQuery('email') . "%'");
}
if ($request->getQuery('alias')) {
$queryBuilder->andWhere("user.alias like '%" . $request->getQuery('alias') . "%'");
}
// search by id
if ($request->getQuery('idStaff')) {
$queryBuilder->andWhere("user.id={$request->getQuery}('idStaff')");
}
// search include inactive
if (!$request->getQuery('includeInactive') || $request->getQuery('includeInactive') && $request->getQuery('includeInactive') == 'false') {
$queryBuilder->andWhere("user.isActive='1'");
}
}
$queryBuilder->orderBy('user.createdTime', 'ASC');
//var_dump($queryBuilder->getQuery());
//exit;
$adapter = new DoctrineAdapter(new ORMPaginator($queryBuilder));
$paginator = new Paginator($adapter);
$paginator->setDefaultItemCountPerPage(10);
$page = (int) $this->getRequest()->getQuery('page');
if ($page) {
$paginator->setCurrentPageNumber($page);
}
$data = array();
$helper = new Helper();
if (count($paginator) > 0) {
//var_dump($paginator);
foreach ($paginator as $user) {
$userData = $user->getData();
$userData['staff'] = $user->getStaff() ? $user->getStaff()->getData() : null;
$userData['createdTime'] = $helper->formatDate($userData['createdTime']);
$data[] = $userData;
}
return new JsonModel(array('staffList' => $data, 'pages' => $paginator->getPages()));
}
return new JsonModel(['staffList' => []]);
}
public function getList()
{
$entityManager = $this->getEntityManager();
// Get freelancer group
$freelancerGroup = $entityManager->find('User\\Entity\\UserGroup', UserGroup::FREELANCER_GROUP_ID);
$freelancerList = $entityManager->getRepository('User\\Entity\\User');
//->findBy(array('group' => $freelancerGroup));
$queryBuilder = $freelancerList->createQueryBuilder('user')->innerJoin("user.freelancer", "f")->where("user.group = :group1")->setParameter('group1', $freelancerGroup);
// check search condition
$request = $this->getRequest();
if ($request->getQuery('search')) {
// search by name
if ($request->getQuery('name')) {
$arrayName = explode(' ', $request->getQuery('name'));
if (count($arrayName) != 2) {
$queryBuilder->andWhere("user.firstName like :name1 OR user.lastName like :name1")->setParameter('name1', '%' . $request->getQuery('name') . '%');
} else {
$queryBuilder->andWhere("(user.firstName like :name1 AND user.lastName like :name2)\n OR (user.lastName like :name1 AND user.firstName like :name2)")->setParameter('name1', '%' . $arrayName[0] . '%')->setParameter('name2', '%' . $arrayName[1] . '%');
}
}
// search by id
if ($request->getQuery('idFreelancer')) {
$queryBuilder->andWhere("user.id = ?1")->setParameter(1, (int) $request->getQuery('idFreelancer'));
}
// search by mail
if ($request->getQuery('email')) {
$queryBuilder->andWhere("user.email like :email1")->setParameter('email1', '%' . $request->getQuery('email') . '%');
}
// search by country aa
if ($request->getQuery('country')) {
$queryBuilder->andWhere("user.country = :counttry1")->setParameter('counttry1', $request->getQuery('country'));
}
// search include inactive
if (!$request->getQuery('includeInactive')) {
$queryBuilder->andWhere("user.isActive = ?1")->setParameter(1, 1);
}
// search Senior account
if ($request->getQuery('senior')) {
$queryBuilder->andWhere("f.isSenior = ?1")->setParameter(1, 1);
}
// search source
if ($source = $this->params()->fromQuery('source')) {
$queryBuilder->innerJoin("f.Resources r")->andWhere("r.id = ?1 ")->setParameter(1, $source);
}
// search rating
if ($rate = $this->params()->fromQuery('rate')) {
$queryBuilder->innerJoin("f.Rating ra")->andWhere("ra.id = ?1 ")->setParameter(1, $rate);
}
//search specialism
/*if($specialism = $this->params()->fromQuery('specialism')){
$queryBuilder->innerJoin("f.InterpretingSpecialisms i")
->andWhere("i.id = ?1")
->innerJoin("f.TranslationSpecialisms t")
->orWhere("t.id = ?1")
->setParameter(1, $specialism);
}*/
if ($specialism = $this->params()->fromQuery('specialism')) {
$queryBuilder->leftJoin("f.InterpretingSpecialisms i")->leftJoin("f.TranslationSpecialisms t")->andWhere($queryBuilder->expr()->orX($queryBuilder->expr()->eq('i.id', '?1'), $queryBuilder->expr()->eq('t.id ', '?1')))->setParameter(1, $specialism);
}
}
$queryBuilder->orderBy('user.createdTime', 'ASC');
//$vvd = $queryBuilder->getSql();
//var_dump($vvd);exit;
$adapter = new DoctrineAdapter(new ORMPaginator($queryBuilder));
$paginator = new Paginator($adapter);
$paginator->setDefaultItemCountPerPage(10);
$page = (int) $this->getRequest()->getQuery('page');
if ($page) {
$paginator->setCurrentPageNumber($page);
}
$data = array();
$helper = new Helper();
if (count($paginator) > 0) {
foreach ($paginator as $user) {
$userData = $user->getData();
$userData['createdTime'] = $helper->formatDate($userData['createdTime']);
$userData['rating'] = $user->getFreelancer()->getRating();
// Get Tasks done
$entityManager = $this->getEntityManager();
$taskList = $entityManager->createQueryBuilder()->select("COUNT(task.id)")->from('User\\Entity\\Task', 'task')->where("task.assignee=?1")->setParameter(1, $user->getFreelancer()->getId())->andWhere('task.is_deleted = 0')->andWhere('task.status = 1');
$taskNum = $taskList->getQuery()->getSingleScalarResult();
$userData['tasksDone'] = $taskNum;
$data[] = $userData;
}
return new JsonModel(array('freelancerList' => $data, 'pages' => $paginator->getPages()));
}
return new JsonModel(['freelancerList' => []]);
}
public function getTouchTime()
{
$helper = new Helper();
return $this->updateTime ? $helper->formatDate($this->updateTime) : $helper->formatDate($this->createdTime);
}
public function getList()
{
$entityManager = $this->getEntityManager();
$employerGroup = $entityManager->find('User\\Entity\\UserGroup', UserGroup::EMPLOYER_GROUP_ID);
$employerList = $entityManager->getRepository('User\\Entity\\User');
$queryBuilder = $employerList->createQueryBuilder('user');
$request = $this->getRequest();
if ($request->getQuery('search') && $request->getQuery('company')) {
$company = $entityManager->getRepository('User\\Entity\\Company')->findBy(array('id' => $request->getQuery('company')));
$queryBuilder->leftJoin('user.employer', 'employer')->where('employer.company=?1')->setParameter(1, $company);
$queryBuilder->andWhere("user.group=?2")->setParameter(2, $employerGroup);
} else {
$queryBuilder->where("user.group=?1")->setParameter(1, $employerGroup);
}
if ($request->getQuery('search')) {
if ($arrayNames = $this->params()->fromQuery('name')) {
$arrayName = explode(' ', $arrayNames);
if (count($arrayName) != 2) {
$queryBuilder->andWhere($queryBuilder->expr()->like('user.firstName', "'%" . $arrayName[0] . "%'"));
} else {
$queryBuilder->andWhere($queryBuilder->expr()->like('user.firstName', "'%" . $arrayName[0] . "%'"));
$queryBuilder->andWhere($queryBuilder->expr()->like('user.lastName', "'%" . $arrayName[1] . "%'"));
}
}
if ($request->getQuery('idEmployer')) {
$queryBuilder->andWhere($queryBuilder->expr()->eq("user.id", (int) $request->getQuery('idEmployer')));
}
if ($request->getQuery('email')) {
$queryBuilder->andWhere($queryBuilder->expr()->like('user.email', "'%" . $request->getQuery('email') . "%'"));
}
if ($request->getQuery('currency')) {
$queryBuilder->andWhere($queryBuilder->expr()->eq("user.currency", $queryBuilder->expr()->literal($request->getQuery('currency'))));
}
if ($request->getQuery('country')) {
$queryBuilder->andWhere($queryBuilder->expr()->eq("user.country", (int) $request->getQuery('country')));
}
if (!$request->getQuery('includeInactive') || $request->getQuery('includeInactive') && $request->getQuery('includeInactive') == 'false') {
$queryBuilder->andWhere($queryBuilder->expr()->eq("user.isActive", 1));
}
}
$queryBuilder->orderBy('user.createdTime', 'ASC');
$adapter = new DoctrineAdapter(new ORMPaginator($queryBuilder));
$paginator = new ZendPaginator($adapter);
$paginator->setDefaultItemCountPerPage(10);
$page = (int) $this->getRequest()->getQuery('page');
if ($page) {
$paginator->setCurrentPageNumber($page);
}
$data = array();
$helper = new Helper();
foreach ($paginator as $user) {
$userData = $user->getData();
$userData['employer'] = $user->getEmployer()->getData();
$userData['createdTime'] = $helper->formatDate($userData['createdTime']);
// Count TaskDone
$taskCount = $entityManager->getRepository('User\\Entity\\Task');
$taskQueryBuilder = $taskCount->createQueryBuilder('task');
$taskQueryBuilder->select('COUNT(task.id)');
$taskQueryBuilder->innerJoin('User\\Entity\\Project', 'p', 'WITH', 'task.project = p.id');
$taskQueryBuilder->andWhere('p.client = ?1')->setParameter(1, $userData['id']);
// user.id
$taskQueryBuilder->andWhere('task.status = ?2')->setParameter(2, 1);
$taskquery = $taskQueryBuilder->getQuery();
$task_result = $taskquery->getArrayResult();
$userData['taskDone'] = $task_result[0][1];
// End Count TaskDone
$data[] = $userData;
}
return new JsonModel(array('employers' => $data, 'pages' => $paginator->getPages()));
}
