Created
March 11, 2015 09:05
-
-
Save dewid1985/9b06657feb557ba680d7 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /***************************************************************************** | |
| * Copyright (C) 2006-2009, onPHP's MetaConfiguration Builder. * | |
| * Generated by onPHP-1.1.master at 2014-02-19 13:03:54 * | |
| * This file will never be generated again - feel free to edit. * | |
| *****************************************************************************/ | |
| class CRMDimensionsProjectEntityUserDAO extends AutoCRMDimensionsProjectEntityUserDAO | |
| { | |
| protected static $pageLimit = 50; | |
| public function getSequence() | |
| { | |
| return parent::getSequence() . '_seq'; | |
| } | |
| public function getUserCount($id) | |
| { | |
| if (!is_array($id)) { | |
| return Criteria::create($this)-> | |
| addProjection(Projection::count('id'))-> | |
| setSilent(TRUE)-> | |
| add(Expression::eq('projectEntity', $id))-> | |
| getCustom('count'); | |
| } else if(!empty($id)) | |
| { | |
| $tempid = array(); | |
| foreach($id as $k => $v) | |
| { | |
| if(is_array($v) && isset($v['id'])) | |
| { | |
| $tempid[] = $v['id']; | |
| } | |
| } | |
| return Criteria::create($this)-> | |
| addProjection(Projection::count('id'))-> | |
| setSilent(TRUE)-> | |
| add(Expression::in('projectEntity',$tempid))-> | |
| getCustom('count'); | |
| } else | |
| { | |
| return null; | |
| } | |
| } | |
| public function getPrizesReport($promoId) | |
| { | |
| list($startDate, $endDate) = HttpRequestUtil::getFilterDateVars(); | |
| $channels = HttpRequestUtil::getHttpGetVar(HttpRequestHelper::me()->getRequest(), 'channels', NULL); | |
| $dateFormat = 'fuca.date::DATE'; | |
| $dateFormat = CriteriaUtils::addIntervalsIfExistToSQL($dateFormat, 'dates'); | |
| $orderedId = Criteria::create(CRMDimensionsUserPrizeAssignType::dao()) | |
| ->setSilent(TRUE) | |
| ->add(Expression::eq('name', CRMEnumPackCode::ASSIGN)) | |
| ->setLimit(1) | |
| ->getCustom('id'); | |
| if (is_null($orderedId)) { | |
| $orderedId = 3; | |
| } | |
| $query = "select [BEFORE_SELECT_QUERY] {$dateFormat}, count(fuca.id), pr.name | |
| from facts.user_prize AS fuca | |
| join dimensions.project_entity_user AS dpeu ON dpeu.project_entity_id = {$promoId} | |
| join dimensions.user_prize_assign_type AS ducat ON ducat.id = fuca.prize_assign_type_id | |
| join dimensions.prize AS pr ON pr.id = fuca.prize_id | |
| where | |
| (fuca.date BETWEEN '{$startDate}' AND '{$endDate}') | |
| AND | |
| (dpeu.channel_id > 1) | |
| AND | |
| (fuca.project_entity_user_id = dpeu.id) | |
| AND (fuca.prize_assign_type_id = {$orderedId}) | |
| [RESERVED] | |
| GROUP BY fuca.date::DATE, pr.name, ducat.name, dpeu.channel_id | |
| ORDER BY fuca.date::DATE ASC"; | |
| $query = str_replace('[RESERVED]', '', $query); | |
| $query = str_replace('[BEFORE_SELECT_QUERY]', '', $query); | |
| $query = str_replace('[GROUP_BY]', '', $query); | |
| $pgLink = DBPool::me()->getLink(); | |
| $resource = $pgLink->queryRaw($query); | |
| $data = pg_fetch_all($resource); | |
| return $data; | |
| } | |
| public static function getDrillDownSuccessActivationCodes($promoId, $filters = NULL, $params = array()) | |
| { | |
| $channel = HttpRequestUtil::getHttpGetVar( | |
| HttpRequestHelper::me()->getRequest(), | |
| 'channels', | |
| (count($params) > 0) ? HttpRequestUtil::getReportChannel() : NULL | |
| ); | |
| $packcodeId = Criteria::create(CRMDimensionsUserCodeActionType::dao()) | |
| ->setSilent(TRUE) | |
| ->add(Expression::eq('name', CRMEnumPackCode::CODE_SUCCESS)) | |
| ->setLimit(1) | |
| ->getCustom('id'); | |
| $query = "SELECT | |
| row_number() OVER () as row_number, | |
| t1.user_id, | |
| ch.name AS channel_name, | |
| concat(prj.first_name, ' ', prj.middle_name, ' ', prj.last_name) as fio, | |
| prj.email, | |
| prj.mobile_phone, | |
| t1.date::DATE, | |
| COUNT(t1.rnk) as cnt | |
| FROM ( | |
| SELECT | |
| count(prj.id) OVER() AS sum_count, | |
| prj.id AS user_id, | |
| code.channel_id, | |
| code.date::DATE AS date, | |
| RANK() OVER(PARTITION BY prj.id ORDER BY code.id) AS rnk | |
| FROM | |
| facts.user_code_action as code, | |
| dimensions.project_entity_user AS prj | |
| WHERE | |
| code.project_entity_user_id = prj.id | |
| AND prj.project_entity_id = {$promoId} | |
| AND code.action_type_id = {$packcodeId} | |
| [RESERVED] | |
| ) AS t1 | |
| LEFT JOIN dimensions.project_entity_user AS prj ON prj.id = t1.user_id | |
| LEFT JOIN dimensions.channel AS ch ON ch.id = t1.channel_id | |
| GROUP BY t1.user_id, t1.date, prj.first_name, prj.middle_name, prj.last_name, prj.email, prj.mobile_phone, t1.channel_id, ch.name | |
| ORDER BY t1.date ASC"; | |
| if (isset($params['to_tsquery']) && isset($params['to_tsvector']) && !empty($params['searchInput'])) { | |
| $query = str_replace('[RESERVED]', ' AND ((to_tsvector(' . $params['to_tsvector'] . ') @@ to_tsquery(' . $params['to_tsquery'] . ')) [RESERVED]', $query); | |
| $words = explode(':* ', str_replace("'", '', $params['to_tsquery'])); | |
| $will = array(); | |
| foreach ($words as $word) { | |
| $word = trim($word); | |
| if (empty($word)) { | |
| continue; | |
| } | |
| $will[] = "prj.first_name ilike '%{$word}%'"; | |
| $will[] = "prj.last_name ilike '%{$word}%'"; | |
| $will[] = "prj.middle_name ilike '%{$word}%'"; | |
| $will[] = "prj.email ilike '%{$word}%'"; | |
| $will[] = "prj.mobile_phone ilike '%{$word}%'"; | |
| } | |
| $query = str_replace('[RESERVED]', " OR (" . join(' OR ', $will) . ")) [RESERVED]", $query); | |
| } | |
| if ($channel) { | |
| $query = str_replace('[RESERVED]', "AND code.channel_id = {$channel} [RESERVED] \n", $query); | |
| } | |
| if (empty($params['intervals'])) { | |
| $params['intervals'] = 'day'; | |
| } | |
| switch ($params['intervals']) { | |
| case "week": | |
| if (strlen($params['period']) > 2) { | |
| $week = intVal(substr($params['period'], 0, 2)); | |
| list($week, $weekName, $year) = explode(' ', $params['period']); | |
| $week = $week + 0; | |
| $year = $year + 0; | |
| } | |
| $query = str_replace('[RESERVED]', " AND (date_part('week', code.date::DATE) = {$week} AND date_part('year', code.date::DATE) = {$year}) [RESERVED] \n", $query); | |
| break; | |
| case "day": | |
| if (ValidatorsUtils::isValidDate($params['period'])) { | |
| $date = $params['period']; | |
| $date = date('Y-m-d', strtotime($date)); | |
| $query = str_replace('[RESERVED]', " AND (code.date::DATE = '{$date}') [RESERVED] \n", $query); | |
| } | |
| break; | |
| case "month": | |
| if ($params['period']) { | |
| list($date, $year) = explode(' ', $params['period']); | |
| $month = preg_replace('/[0-9\ \.]/', '', $date); | |
| $monthNumber = ValidatorsUtils::getByMonthName(trim($month)); | |
| $year = $year + 0; | |
| $query = str_replace('[RESERVED]', " AND (date_part('month', code.date::DATE) = {$monthNumber} AND date_part('year', code.date::DATE) = {$year}) [RESERVED] \n", $query); | |
| } | |
| break; | |
| default: | |
| break; | |
| }; | |
| $query = str_replace('[RESERVED]', '', $query); | |
| if (isset($params['pagination']) && !$params['pagination']) { | |
| $link = DBPool::me()->getLink(); | |
| $res = $link->queryRaw($query); | |
| return $res; | |
| return array('dataList' => pg_fetch_all($res)); | |
| } | |
| $countQuery = "SELECT CEIL(COUNT(*)::float / " . (self::$pageLimit) . "::float) as count, COUNT(*) as total_count, SUM(t2.cnt) AS sum_count FROM ( "; | |
| $countQuery .= $query . ' ) AS t2'; | |
| //$query = "SET work_mem TO '500MB';" . $query; | |
| $pgLink = DBPool::me()->getLink(); | |
| $pgLink->queryRaw('SET enable_seqscan TO on'); | |
| if (count($params) > 0) { | |
| return self::cursorQuery( | |
| $query, $countQuery, | |
| HttpRequestUtil::getHttpGetVar(HttpRequestHelper::me()->getRequest(), 'page', 1) | |
| ); | |
| } | |
| $resource = $pgLink->queryRaw($query); | |
| $data = pg_fetch_all($resource); | |
| return $data; | |
| } | |
| public static function getSuccessActivationCodes($promoId, $filters = NULL, $params = array()) | |
| { | |
| list($startDate, $endDate) = HttpRequestUtil::getFilterDateVars(); | |
| $startDate = date("Y-m-d", strtotime($startDate)); | |
| $endDate = date("Y-m-d", strtotime($endDate)); | |
| $channels = HttpRequestUtil::getHttpGetVar( | |
| HttpRequestHelper::me()->getRequest(), | |
| 'channels', | |
| (count($params) > 0) ? HttpRequestUtil::getReportChannel() : NULL | |
| ); | |
| $pgLink = DBPool::me()->getLink(); | |
| //$pgLink->queryRaw('SET enable_seqscan TO on'); | |
| $dateFormat = 'fuca.date::DATE'; | |
| $dateFormat = CriteriaUtils::addIntervalsIfExistToSQL($dateFormat, 'date'); | |
| $packcodeId = Criteria::create(CRMDimensionsUserCodeActionType::dao()) | |
| ->setSilent(TRUE) | |
| ->add(Expression::eq('name', CRMEnumPackCode::CODE_SUCCESS)) | |
| ->setLimit(1) | |
| ->getCustom('id'); | |
| $query = "select [BEFORE_SELECT_QUERY] fuca.channel_id, ducat.name as error_name, {$dateFormat}, count(fuca.id) | |
| from facts.user_code_action AS fuca | |
| join dimensions.project_entity_user AS dpeu ON dpeu.project_entity_id = {$promoId} AND (fuca.project_entity_user_id = dpeu.id) AND (fuca.channel_id > 1) | |
| join dimensions.user_code_action_type AS ducat ON ducat.id = fuca.action_type_id | |
| where | |
| (fuca.date::DATE BETWEEN '{$startDate}' AND '{$endDate}') | |
| AND | |
| (fuca.action_type_id = {$packcodeId}) | |
| [RESERVED] | |
| GROUP BY fuca.date::DATE, ducat.name, fuca.channel_id [GROUP_BY] | |
| ORDER BY fuca.date::DATE ASC"; | |
| if (count($params) > 0) { | |
| $query = "select [BEFORE_SELECT_QUERY] | |
| row_number() OVER () as row_number, fuca.project_entity_user_id as id, concat(dpeu.last_name, ' ', dpeu.first_name, ' ', dpeu.middle_name) as fio, dpeu.email, dpeu.mobile_phone, dpeu.registration_date | |
| from facts.user_code_action AS fuca | |
| join dimensions.project_entity_user AS dpeu ON dpeu.project_entity_id = {$promoId} AND (fuca.project_entity_user_id = dpeu.id) AND (fuca.channel_id > 1) | |
| where (fuca.project_entity_user_id = dpeu.id) AND (fuca.action_type_id = {$packcodeId}) | |
| [RESERVED] | |
| GROUP BY fuca.project_entity_user_id, fuca.channel_id, dpeu.id | |
| ORDER BY row_number ASC, dpeu.last_name ASC"; | |
| $countQuery = "SELECT CEIL(COUNT(temp.count)::float / " . (self::$pageLimit) . "::float) as count, SUM(temp.tc) as sum_count, COUNT(temp.count) as total_count FROM (SELECT fuca.project_entity_user_id as count, count(fuca.project_entity_user_id) as tc | |
| from facts.user_code_action AS fuca | |
| join dimensions.project_entity_user AS dpeu ON dpeu.project_entity_id = {$promoId} | |
| where (fuca.project_entity_user_id = dpeu.id) AND (fuca.action_type_id = {$packcodeId}) AND (fuca.project_entity_user_id = dpeu.id) AND (fuca.channel_id > 1) | |
| [RESERVED] | |
| GROUP BY fuca.project_entity_user_id, fuca.channel_id, dpeu.id) as temp"; | |
| switch ($params['intervals']) { | |
| case "week": | |
| if (strlen($params['period']) > 2) { | |
| $week = intVal(substr($params['period'], 0, 2)); | |
| } | |
| else { | |
| $week = intVal(date("W")); | |
| } | |
| $query = str_replace('[RESERVED]', " AND (date_part('week', fuca.date::DATE) = {$week}) [RESERVED] \n", $query); | |
| $countQuery = str_replace('[RESERVED]', " AND (date_part('week', fuca.date::DATE) = {$week}) [RESERVED] \n", $countQuery); | |
| break; | |
| case "day": | |
| if (ValidatorsUtils::isValidDate($params['period'])) { | |
| $date = $params['period']; | |
| $query = str_replace('[RESERVED]', " AND (fuca.date::DATE = '{$date}') [RESERVED] \n", $query); | |
| $countQuery = str_replace('[RESERVED]', " AND (fuca.date::DATE = '{$date}') [RESERVED] \n", $countQuery); | |
| } | |
| break; | |
| case "month": | |
| if ($params['period']) { | |
| $date = $params['period']; | |
| $query = str_replace('[RESERVED]', " AND (fuca.date::DATE BETWEEN '{$startDate}' AND '{$endDate}') [RESERVED] \n", $query); | |
| $countQuery = str_replace('[RESERVED]', " AND (fuca.date::DATE BETWEEN '{$startDate}' AND '{$endDate}') [RESERVED] \n", $countQuery); | |
| $month = preg_replace('/[0-9\ \.]/', '', $date); | |
| $monthNumber = ValidatorsUtils::getByMonthName(trim($month)); | |
| $query = str_replace('[RESERVED]', " AND (date_part('month', fuca.date::DATE) = $monthNumber) [RESERVED] \n", $query); | |
| $countQuery = str_replace('[RESERVED]', " AND (date_part('month', fuca.date::DATE) = $monthNumber) [RESERVED] \n", $countQuery); | |
| } | |
| break; | |
| default: | |
| break; | |
| } | |
| } | |
| if ($channels) { | |
| $query = str_replace('[RESERVED]', " AND (fuca.channel_id = '{$channels}') [RESERVED] \n", $query); | |
| if (isset($countQuery)) { | |
| $countQuery = str_replace('[RESERVED]', " AND (fuca.channel_id = '{$channels}') [RESERVED] \n", $countQuery); | |
| } | |
| } | |
| $query = str_replace('[RESERVED]', '', $query); | |
| $query = str_replace('[BEFORE_SELECT_QUERY]', '', $query); | |
| $query = str_replace('[GROUP_BY]', '', $query); | |
| if (isset($countQuery)) { | |
| $countQuery = str_replace('[RESERVED]', '', $countQuery); | |
| $countQuery = str_replace('[BEFORE_SELECT_QUERY]', '', $countQuery); | |
| $countQuery = str_replace('[GROUP_BY]', '', $countQuery); | |
| } | |
| if (isset($params['pagination']) && !$params['pagination']) { | |
| $link = DBPool::me()->getLink(); | |
| $res = $link->queryRaw($query); | |
| return array('dataList' => pg_fetch_all($res)); | |
| } | |
| if (count($params) > 0) { | |
| return self::cursorQuery( | |
| $query, $countQuery, | |
| HttpRequestUtil::getHttpGetVar(HttpRequestHelper::me()->getRequest(), 'page', 0) | |
| ); | |
| } | |
| $resource = $pgLink->queryRaw($query); | |
| $data = pg_fetch_all($resource); | |
| $pgLink->queryRaw('SET enable_seqscan TO on'); | |
| return $data; | |
| } | |
| public function getPackCodeTryAndExistsReport($promoId, $filters = NULL) | |
| { | |
| list($startDate, $endDate) = HttpRequestUtil::getFilterDateVars(); | |
| $channels = NULL; | |
| $packcodeId = Criteria::create(CRMDimensionsUserCodeActionType::dao()) | |
| ->setSilent(TRUE) | |
| ->add(Expression::eq('name', CRMEnumPackCode::CODE_ERROR)) | |
| ->setLimit(1) | |
| ->getCustom('id'); | |
| $dateFormat = 'fuca.date::DATE'; | |
| $dateFormat = CriteriaUtils::addIntervalsIfExistToSQL($dateFormat, 'date'); | |
| $startDate = date("Y-m-d", strtotime($startDate)); | |
| $endDate = date("Y-m-d", strtotime($endDate)); | |
| $query = "select dpeu.channel_id, ducat.name as error_name, ducast.name as suberror_name, {$dateFormat}, count(fuca.id) | |
| from facts.user_code_action AS fuca | |
| join dimensions.project_entity_user AS dpeu ON dpeu.project_entity_id = {$promoId} | |
| join dimensions.user_code_action_type AS ducat ON ducat.id = fuca.action_type_id | |
| left join dimensions.user_code_action_subtype AS ducast ON ducast.id = fuca.action_subtype_id | |
| where | |
| (fuca.date::DATE BETWEEN '{$startDate}' AND '{$endDate}') | |
| AND | |
| (dpeu.channel_id > 1) | |
| AND | |
| (fuca.project_entity_user_id = dpeu.id) | |
| AND | |
| (fuca.action_type_id = {$packcodeId}) | |
| [RESERVED] | |
| GROUP BY fuca.date::DATE, ducat.name, ducast.name, dpeu.channel_id | |
| ORDER BY fuca.date::DATE ASC"; | |
| if ($channels) { | |
| $query = str_replace('[RESERVED]', " AND (fuca.channel_id = '{$channels}') [RESERVED] \n", $query); | |
| } | |
| $query = str_replace('[RESERVED]', '', $query); | |
| $pgLink = DBPool::me()->getLink(); | |
| $resource = $pgLink->queryRaw($query); | |
| $data = pg_fetch_all($resource); | |
| return $data; | |
| } | |
| /** | |
| * Получение данных для отчета "Пол участников" | |
| * | |
| * @param $promoId | |
| * @param null $filters | |
| * @return array | |
| */ | |
| public function getRegistredUsersGenderReport($promoId, $filters = NULL) | |
| { | |
| list($startDate, $endDate) = HttpRequestUtil::getFilterDateVars(); | |
| $criteria = Criteria::create($this); | |
| $datePart = DBField::create('registration_date')->castTo(DataType::create(DataType::DATE)->getName()); | |
| $dateBetween = $datePart; | |
| $datePart = CriteriaUtils::addIntervalsIfExist($datePart, 'registration_date'); | |
| $criteria = CriteriaUtils::addChannelIfExists($criteria); | |
| $criteria | |
| ->setSilent(TRUE) | |
| ->addProjection( | |
| Projection::chain() | |
| ->add(Projection::property('gender')) | |
| ->add(Projection::property($datePart, 'registration_date')) | |
| ->add(Projection::group($datePart)) | |
| ->add(Projection::count('gender')) | |
| ->add(Projection::group('gender')) | |
| ->add(Projection::group($dateBetween)) | |
| ) | |
| ->add( | |
| Expression::between( | |
| $dateBetween, | |
| date("Y-m-d", strtotime($startDate)), | |
| date("Y-m-d", strtotime($endDate)) | |
| ) | |
| ) | |
| ->add(Expression::eq('projectEntity', $promoId)) | |
| ->add(Expression::notNull('registrationDate')) | |
| ->addOrder(OrderBy::create($dateBetween)); | |
| return $criteria->getCustomList(); | |
| } | |
| public function getRegionUsers($promoId, $filters = NULL) | |
| { | |
| list($startDate, $endDate) = HttpRequestUtil::getFilterDateVars(); | |
| $criteria = Criteria::create($this); | |
| $datePart = DBField::create('registration_date')->castTo(DataType::create(DataType::DATE)->getName()); | |
| $dateBetween = $datePart; | |
| $datePart = CriteriaUtils::addIntervalsIfExist($datePart, 'registration_date'); | |
| $criteria = CriteriaUtils::addChannelIfExists($criteria); | |
| $criteria | |
| ->setSilent(TRUE) | |
| ->addProjection( | |
| Projection::chain() | |
| ->add(Projection::property('channel')) | |
| ->add(Projection::property('location')) | |
| ->add(Projection::property($datePart, 'registration_date')) | |
| ->add(Projection::group($datePart)) | |
| ->add(Projection::count('channel')) | |
| ->add(Projection::group('channel')) | |
| ->add(Projection::group('location')) | |
| ->add(Projection::group($dateBetween)) | |
| ) | |
| ->add( | |
| Expression::between( | |
| $dateBetween, | |
| date("Y-m-d", strtotime($startDate)), | |
| date("Y-m-d", strtotime($endDate)) | |
| ) | |
| ) | |
| ->add(Expression::eq('projectEntity', $promoId)) | |
| //->add(Expression::notNull('registrationDate')) | |
| //->add(Expression::notNull('location')) | |
| ->addOrder(OrderBy::create($dateBetween)); | |
| return $criteria->getCustomList(); | |
| } | |
| public function getRigistredUsers($promoId, $filters = NULL) | |
| { | |
| list($startDate, $endDate) = HttpRequestUtil::getFilterDateVars(); | |
| $criteria = Criteria::create($this); | |
| $datePart = DBField::create('registration_date')->castTo(DataType::create(DataType::DATE)->getName()); | |
| $dateBetween = $datePart; | |
| $datePart = CriteriaUtils::addIntervalsIfExist($datePart, 'registration_date'); | |
| $criteria = CriteriaUtils::addChannelIfExists($criteria); | |
| $projection = Projection::chain() | |
| ->add(Projection::property('channel')) | |
| ->add(Projection::property($datePart, 'registration_date')) | |
| ->add(Projection::group($datePart)) | |
| ->add(Projection::group($dateBetween)) | |
| ->add(Projection::count('channel')) | |
| ->add(Projection::group('channel')); | |
| $criteria->setSilent(TRUE) | |
| ->addProjection($projection) | |
| ->add( | |
| Expression::between( | |
| $dateBetween, | |
| date("Y-m-d", strtotime($startDate)), | |
| date("Y-m-d", strtotime($endDate)) | |
| ) | |
| ) | |
| ->add(Expression::eq('projectEntity', $promoId)) | |
| ->add(Expression::notNull('registrationDate')) | |
| ->addOrder(OrderBy::create($dateBetween)->asc()); | |
| return $criteria->getCustomList(); | |
| } | |
| public static function cursorQuery($queryS = NULL, $countQuery = NULL, $page) | |
| { | |
| if (is_null($page)) { | |
| $page = 1; | |
| } | |
| $page--; | |
| $skipCount = self::$pageLimit * $page; | |
| $query = " | |
| BEGIN; | |
| DECLARE NAV CURSOR FOR {$queryS}; | |
| MOVE {$skipCount} FROM NAV; | |
| FETCH " . (self::$pageLimit) . " FROM NAV; | |
| "; | |
| $pgLink = DBPool::me()->getLink(); | |
| $result = $pgLink->queryRaw($countQuery); | |
| $numRows = pg_fetch_all($result); | |
| $result = $pgLink->queryRaw($query); | |
| $returnData = pg_fetch_all($result); | |
| $query = "CLOSE NAV;COMMIT;"; | |
| $pgLink->queryRaw($query); | |
| return array('dataList' => $returnData, 'numRows' => $numRows); | |
| } | |
| public function getUsers($projectEntity, $sortby, $page, $scope = array(), $params = array()) | |
| { | |
| if (is_null($page)) { | |
| $page = 0; | |
| } | |
| $order = 'row_number ASC'; | |
| if ($sortby) { | |
| $sortby = explode(',', $sortby); | |
| $order = array(); | |
| foreach ($sortby as $sort) { | |
| $order[] = $sort . ' ASC'; | |
| } | |
| if (count($order) > 0) { | |
| $order = join(', ', $order); | |
| } | |
| } | |
| $forQuery = "SELECT row_number() OVER () as row_number, prj.* FROM dimensions.project_entity_user AS prj "; | |
| $forQueryWhere = " WHERE project_entity_id = '{$projectEntity}'"; | |
| if (isset($params['to_tsquery']) && isset($params['to_tsvector']) && !empty($params['searchInput'])) { | |
| $forQueryWhere .= ' AND (('; | |
| $forQueryWhere .= 'to_tsvector(' . $params['to_tsvector'] . ') @@ to_tsquery(' . $params['to_tsquery'] . '))'; | |
| $words = explode(':* ', str_replace("'", '', $params['to_tsquery'])); | |
| $will = array(); | |
| foreach ($words as $word) { | |
| $word = trim($word); | |
| if (empty($word)) { | |
| continue; | |
| } | |
| $will[] = "first_name ilike '%{$word}%'"; | |
| $will[] = "last_name ilike '%{$word}%'"; | |
| $will[] = "middle_name ilike '%{$word}%'"; | |
| $will[] = "email ilike '%{$word}%'"; | |
| $will[] = "mobile_phone ilike '%{$word}%'"; | |
| } | |
| $forQueryWhere .= " OR (" . join(' OR ', $will) . "))"; | |
| } | |
| $forCountOrder = " ORDER BY {$order};"; | |
| $forQuery .= $forQueryWhere; | |
| $forQuery .= $forCountOrder; | |
| $forCountQuery = "SELECT CEIL(COUNT(*)::float /" . self::$pageLimit . "::float) as count FROM dimensions.project_entity_user "; | |
| $forCountQuery .= $forQueryWhere; | |
| if (empty($scope) && count($scope) > 0) { | |
| $forQuery = str_replace("*", join(",", $scope), $forQuery); | |
| } | |
| if (count($params) > 0) { | |
| } | |
| $skipCount = self::$pageLimit * $page; | |
| $query = " | |
| BEGIN; | |
| DECLARE NAV CURSOR FOR {$forQuery} | |
| MOVE {$skipCount} FROM NAV; | |
| FETCH " . self::$pageLimit . " FROM NAV; | |
| "; | |
| if (isset($params['pagination']) && !$params['pagination']) { | |
| $link = DBPool::me()->getLink(); | |
| $res = $link->queryRaw($forQuery); | |
| //set_time_limit(360); | |
| return $res; | |
| } | |
| $pgLink = DBPool::me()->getLink(); | |
| $result = $pgLink->queryRaw($forCountQuery); | |
| $numRows = pg_fetch_all($result); | |
| $result = $pgLink->queryRaw($query); | |
| $returnData = pg_fetch_all($result); | |
| $query = "CLOSE NAV;COMMIT;"; | |
| $pgLink->queryRaw($query); | |
| return array('dataList' => $returnData, 'numRows' => $numRows); | |
| } | |
| public function getUser($userId) | |
| { | |
| return Criteria::create($this)-> | |
| setSilent(TRUE)-> | |
| add(Expression::eq('user', $userId))-> | |
| get(); | |
| } | |
| /** | |
| * Получение данных для отчета "Возраст участников" | |
| * | |
| * @param $promoId | |
| * @param $ageStart | |
| * @param $ageEnd | |
| * @return array | |
| */ | |
| public function getAgeListUsers($promoId, $ageStart, $ageEnd) | |
| { | |
| list($startDate, $endDate) = HttpRequestUtil::getFilterDateVars(); | |
| $datePart = DBField::create('registration_date')->castTo(DataType::create(DataType::DATE)->getName()); | |
| $dateBetween = $datePart; | |
| $datePart = CriteriaUtils::addIntervalsIfExist($datePart, 'registration_date'); | |
| $criteria = Criteria::create($this) | |
| ->setSilent(TRUE)-> | |
| addProjection( | |
| Projection::chain() | |
| ->add(Projection::property($datePart, 'registration_date')) | |
| ->add( | |
| Projection::property( | |
| SQLFunction::create( | |
| 'date_part', 'year', | |
| SQLFunction::create( | |
| 'age', | |
| DBField::create('birth_date') | |
| ->castTo('DATE') | |
| ) | |
| ) | |
| ) | |
| ) | |
| ->add(Projection::count('id')) | |
| ->add(Projection::group($dateBetween)) | |
| ->add(Projection::group('birthDate')) | |
| ) | |
| ->add( | |
| Expression::between( | |
| $dateBetween, | |
| date("Y-m-d", strtotime($startDate)), | |
| date("Y-m-d", strtotime($endDate)) | |
| ) | |
| ) | |
| ->add(Expression::eq('projectEntity', $promoId)) | |
| ->add( | |
| Expression::chain() | |
| ->expAnd( | |
| Expression::between( | |
| SQLFunction::create( | |
| 'date_part', 'year', | |
| SQLFunction::create( | |
| 'age', | |
| DBField::create('birth_date') | |
| ->castTo('DATE') | |
| ) | |
| ), | |
| $ageStart, | |
| $ageEnd | |
| ) | |
| ) | |
| ->expOr(Expression::isNull('birthDate')) | |
| ) | |
| ->addOrder( | |
| OrderBy::create( | |
| DBField::create('registration_date') | |
| ) | |
| ->asc() | |
| ) | |
| ->toString(); | |
| //->getCustomList(); | |
| $link = DBPool::me()->getLink(); | |
| $resource = $link->queryRaw($criteria); | |
| $resultArray = array(); | |
| while ($row = pg_fetch_assoc($resource)) { | |
| $resultArray[] = $row; | |
| } | |
| return $resultArray; | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment