$offlineParam = $offlineParam == 1 ? 0 : 1; } if ($start > $end) { $errors[] = Resources::Get('search.wrong.dates'); } else { $nTotal = Thread::getInstance()->GetListThreadsAdvCount($operator['operatorid'], $q, $start, $end, $op_param, $show_empty, $departmentidParam, $localeParam, $rateParam, $offlineParam); if ($nTotal) { $pagination = setup_pagination_cnt($nTotal, $items_per_page); $nLimit = $pagination['items']; $nOffset = $pagination['start']; $threads = Thread::getInstance()->GetListThreadsAdv($operator['operatorid'], $q, $start, $end, $op_param, $show_empty, $departmentidParam, $localeParam, $rateParam, $offlineParam, $nLimit, $nOffset); $tmlPage['pagination'] = $pagination; $tmlPage['pagination_items'] = $threads; if (!empty($tmlPage['pagination_items'])) { for ($i = 0; $i < count($tmlPage['pagination_items']); ++$i) { $tmlPage['pagination_items'][$i]['diff'] = webim_date_diff($tmlPage['pagination_items'][$i]['modified'] - $tmlPage['pagination_items'][$i]['created']); } $TML->assign('pagination', generate_pagination($tmlPage['pagination'])); } } $tmlPage['formq'] = $q; $tmlPage['formoperator'] = $op_param; $tmlPage['formstartday'] = date('d', $start); $tmlPage['formstartmonth'] = date('m.y', $start); $tmlPage['formendday'] = date('d', $end - 24 * 60 * 60); $tmlPage['formendmonth'] = date('m.y', $end - 24 * 60 * 60); // $TML->assign('pagination', generate_pagination($tmlPage['pagination'])); } // no errors and need to find } else { // no query
public function getListThreads($currentoperatorid, $q, $show_empty = true, $checkDepartmentsAccess = true, $start_date = null, $end_date = null, $operatorid = null, $offset = null, $limit = null, $departmentid = null, $locale = null, $rate = null) { $departmentsExist = count(MapperFactory::getMapper("Department")->enumDepartments(Resources::getCurrentLocale())); // TODO probably not the best place $query_params = array(); $sql = ' SELECT WM_UNIX_TIMESTAMP(t."created") "created", WM_UNIX_TIMESTAMP(t."modified") "modified", t."threadid", t."operatorfullname", t."visitormessagecount" as "size", v."ip" as "remote", v."remotehost", v."visitorname" FROM "{thread}" t LEFT JOIN "{visitsession}" v ON v."visitsessionid" = t."visitsessionid" WHERE 1=1'; if (!empty($q)) { $query_params['query'] = "%%{$q}%%"; $sql .= ' AND (t."threadid" IN ( SELECT "threadid" FROM "{message}" m WHERE m."sendername" LIKE :query OR m."message" LIKE :query ) OR v."visitorid" LIKE :query OR v."ip" LIKE :query OR v."remotehost" LIKE :query OR t."operatorfullname" LIKE :query )'; } if (!empty($rate)) { $sign = $rate == 'positive' ? '>' : '<'; $sql .= ' AND EXISTS(SELECT * FROM "{rate}" r WHERE r."threadid"=t."threadid" AND r."rate" ' . $sign . ' 0 AND r."deldate" IS NULL)'; } if ($checkDepartmentsAccess) { $sql .= ' AND (t."departmentid" IS NULL OR EXISTS(SELECT * FROM "{operatordepartment}" od WHERE od."operatorid"=:currentoperatorid AND od."departmentid"=t."departmentid"))'; $query_params['currentoperatorid'] = $currentoperatorid; } if (!$show_empty) { $sql .= ' AND t."visitormessagecount" > 0 '; } if ($start_date !== null) { $query_params['start_date'] = $start_date; $sql .= ' AND WM_UNIX_TIMESTAMP(t."created") >= :start_date'; } if ($end_date !== null) { $query_params['end_date'] = $end_date; $sql .= ' AND WM_UNIX_TIMESTAMP(t."created") < :end_date'; } if ($operatorid !== null) { $query_params['operatorid'] = $operatorid; $sql .= ' AND (:operatorid IS NULL OR t."operatorid"=:operatorid)'; } if (!empty($departmentid)) { $query_params['departmentid'] = $departmentid; $sql .= ' AND t."departmentid" = :departmentid '; } if (!empty($locale)) { $query_params['locale'] = $locale; $sql .= ' AND t."locale" = :locale '; } if ($limit !== null && $offset !== null) { $query_params['limit'] = $limit; $query_params['offset'] = $offset; $sql .= " AND rownum BETWEEN :offset AND :limit"; } $sql .= ' ORDER BY t."created" DESC'; try { $this->db->query($sql, $query_params); $result = $this->db->getArrayOfRows(); } catch (Exception $e) { return array(); } foreach ($result as $k => $v) { $geodata = GeoIPLookup::getGeoDataByIP($v['remote']); //for testing purpose //$geodata = GeoIPLookup::getGeoDataByIP('89.113.218.99'); if ($geodata == NULL) { $geodata = array('city' => null, 'country' => null, 'lat' => null, 'lng' => null); } $result[$k] = array_merge($v, $geodata); $result[$k]['created'] = date(getDateTimeFormat(), $v['created']); $result[$k]['modified'] = date(getDateTimeFormat(), $v['modified']); $result[$k]['diff'] = webim_date_diff($v['modified'] - $v['created']); } return $result; }
$visitSession = null; if (isset($_GET['visitsessionid'])) { $visitSession = VisitSession::GetInstance()->GetVisitSessionById($_GET['visitsessionid']); } elseif (isset($_GET['pageid'])) { $visitdpageid = verify_param("pageid", "/^[a-z0-9]{32}\$/"); $vistedpage = VisitedPage::GetInstance()->GetVisitedPageById($_GET['pageid']); $visitSession = VisitSession::GetInstance()->GetVisitSessionById($vistedpage['visitsessionid']); } if (empty($visitSession)) { die("Invalid or no visitsessionid or pageid"); } $visitedPages = VisitedPage::GetInstance()->enumVisitedPagesByVisitSessionId($visitSession['visitsessionid']); $landingPage = end($visitedPages); $exitPage = reset($visitedPages); $timeend = 0; $timestart = 0; foreach ($visitedPages as $k => $vp) { $timeend = $timeend == 0 ? $vp['updated'] : max($timeend, $vp['updated']); $timestart = $timestart == 0 ? $vp['opened'] : min($timestart, $vp['opened']); $visitedPages[$k]['sessionduration'] = $vp['updated'] - $vp['opened']; } $geodata = GeoIPLookup::getGeoDataByIP($visitSession['ip']); //for testing purpose //$geodata = GeoIPLookup::getGeoDataByIP('89.113.218.99'); if ($geodata == NULL) { $geodata = array('city' => null, 'country' => null, 'lat' => null, 'lng' => null); } $tmlPage = array('visitsessionid' => $visitSession['visitsessionid'], 'visitedpages' => $visitedPages, 'landingpage' => $landingPage['uri'], 'exitpage' => $exitPage['uri'], 'timestart' => $timestart, 'timeend' => $timeend, 'timediff' => webim_date_diff($timeend - $timestart), 'active' => time() - $timeend < VISITED_PAGE_TIMEOUT, 'ip' => $visitSession['ip'], 'city' => $geodata['city'], 'country' => $geodata['country'], 'lat' => $geodata['lat'], 'lng' => $geodata['lng'], 'browser' => get_user_agent($visitSession['useragent']), 'enterref' => $landingPage['referrer'], 'leaveref' => $exitPage['referrer'], 'historyParams' => array("q" => "" . $visitSession['visitorid'])); $TML = new SmartyClass($TITLE_KEY); $TML->assign('page_settings', $tmlPage); $TML->display('visit_info.tpl');
public function getListThreads($currentoperatorid, $q, $show_empty = true, $checkDepartmentsAccess = true, $nLimit = 15, $nOffset = 0, $start_date = null, $end_date = null, $operatorid = null, $departmentid = null, $locale = null, $rate = null, $offline = null) { $departmentsExist = count(MapperFactory::getMapper('Department')->enumDepartments(Resources::getCurrentLocale())); // TODO probably not the best place $query_params = array(); $sWhere = ''; $sql = 'SELECT unix_timestamp(t.created) as created, unix_timestamp(t.modified) as modified, t.threadid, t.operatorfullname, t.visitormessagecount as size, v.ip as remote, v.remotehost, v.visitorname FROM {thread} as t LEFT JOIN {visitsession} as v ON v.visitsessionid = t.visitsessionid WHERE 1'; if (!empty($q)) { $query_params['query'] = "%%{$q}%%"; $sWhere .= ' AND (t.threadid IN ( SELECT threadid FROM {message} as m WHERE m.sendername LIKE :query OR m.message LIKE :query ) OR v.visitorid LIKE :query OR v.ip LIKE :query OR v.remotehost LIKE :query OR t.operatorfullname LIKE :query )'; } if (!empty($rate)) { $sign = $rate == 'positive' ? '>' : '<'; $sWhere .= " AND EXISTS (SELECT * FROM {rate} r WHERE r.threadid=t.threadid AND r.rate {$sign} 0 AND r.deldate IS NULL) "; } if ($checkDepartmentsAccess) { $sWhere .= ' AND (t.departmentid IS NULL OR EXISTS(SELECT * FROM {operatordepartment} od WHERE od.operatorid=:currentoperatorid AND od.departmentid=t.departmentid)) '; $query_params['currentoperatorid'] = $currentoperatorid; } if (!$show_empty) { $sWhere .= ' AND t.visitormessagecount > 0 '; } if ($start_date !== null) { $query_params['start_date'] = $start_date; $sWhere .= ' AND unix_timestamp(t.created) >= :start_date'; } if ($end_date !== null) { $query_params['end_date'] = $end_date; $sWhere .= ' AND unix_timestamp(t.created) < :end_date'; } if ($operatorid !== null) { $query_params['operatorid'] = $operatorid; $sWhere .= ' AND (:operatorid IS NULL OR t.operatorid=:operatorid)'; } if (!empty($departmentid)) { $query_params['departmentid'] = $departmentid; $sWhere .= ' AND t.departmentid = :departmentid '; } if (!empty($locale)) { $query_params['locale'] = $locale; $sWhere .= ' AND t.locale = :locale '; } if ($offline !== null) { $query_params['offline'] = $offline; $sWhere .= ' AND t.offline=:offline'; } $sql .= $sWhere . " ORDER BY t.created DESC LIMIT {$nOffset}, {$nLimit}"; try { $this->db->query($sql, $query_params); $result = $this->db->getArrayOfRows(); } catch (Exception $e) { return array(); } foreach ($result as $k => $v) { $geodata = GeoIPLookup::getGeoDataByIP($v['remote']); //for testing purpose //$geodata = GeoIPLookup::getGeoDataByIP('89.113.218.99'); if ($geodata == null) { $geodata = array('city' => null, 'country' => null, 'lat' => null, 'lng' => null); } $result[$k] = array_merge($v, $geodata); $result[$k]['created'] = date(getDateTimeFormat(), $v['created']); $result[$k]['modified'] = date(getDateTimeFormat(), $v['modified']); $result[$k]['diff'] = webim_date_diff($v['modified'] - $v['created']); } return $result; }