diff --git a/application/modules/statistics/Bootstrap.php b/application/modules/statistics/Bootstrap.php new file mode 100644 index 000000000..8dcba440a --- /dev/null +++ b/application/modules/statistics/Bootstrap.php @@ -0,0 +1,99 @@ +. + **/ +class Statistics_Bootstrap extends Zend_Application_Module_Bootstrap +{ + + protected function _initAutoloader() + { + $autoloader = new Zend_Application_Module_Autoloader(array( + 'namespace' => 'Statistics', + 'basePath' => realpath(dirname(__FILE__)), + )); + $autoloader->addResourceType('Ranking', 'library/statistics/ranking', 'Ranking'); + return $autoloader; + } + + protected function _initRouter() + { + $frontController = Zend_Controller_Front::getInstance(); + /** @var $router Zend_Controller_Router_Rewrite */ + $router = $frontController->getRouter(); + +// $dir = $router->getFrontController()->getControllerDirectory(); + + $router->addRoute( + 'statistics_daily_ajax', + new Zend_Controller_Router_Route( + '/statistics/daily/ajax/:project_id/:year/:month/:day/', + array( + 'module' => 'statistics', + 'controller' => 'daily', + 'action' => 'ajax', + 'project_id' => null, + 'year' => null, + 'month' => null, + 'day' => null + ) + ) + ); + + $router->addRoute( + 'statistics_monthly_ajax', + new Zend_Controller_Router_Route( + '/statistics/monthly/ajax/:project_id/:year/:month/', + array( + 'module' => 'statistics', + 'controller' => 'monthly', + 'action' => 'ajax', + 'project_id' => null, + 'year' => null, + 'month' => null + ) + ) + ); + + $router->addRoute( + 'statistics_weekly_ajax', + new Zend_Controller_Router_Route( + '/statistics/weekly/ajax/:project_id/:yearweek/', + array( + 'module' => 'statistics', + 'controller' => 'weekly', + 'action' => 'ajax', + 'project_id' => null, + 'yearweek' => null + ) + ) + ); + + return $router; + } + + protected function _initIncludePath () { + set_include_path(implode(PATH_SEPARATOR, array( + dirname(__FILE__) . '/library', + get_include_path(), + ))); + } + +} + diff --git a/application/modules/statistics/controllers/CronController.php b/application/modules/statistics/controllers/CronController.php new file mode 100644 index 000000000..bda8c2922 --- /dev/null +++ b/application/modules/statistics/controllers/CronController.php @@ -0,0 +1,64 @@ +. + **/ +class Statistics_CronController extends Local_Controller_Action_CliAbstract +{ + + const DATE_FORMAT = "Y-m-d"; + + public function runAction() + { + $today = new DateTime(); + // $yesterday = $today->sub(new DateInterval("P1D")); + + $validator = new Zend_Validate_Date(array('format' => self::DATE_FORMAT)); + + $date = $this->getRequest()->getParam('date', $today->format(self::DATE_FORMAT)); + if ($validator->isValid($date)) { + $statistics = new Statistics_Model_GoalStatistics(); + $result = $statistics->generateDailyStatistics($date); + + foreach (get_object_vars($result) as $name => $value) { + echo "$name: $value\n"; + } + } + } + + + public function dailypageviewsAction() + { + $statistics = new Statistics_Model_GoalStatistics(); + $statistics->dailyPageviews(); + } + + + public function migrateAction() + { + $statistics = new Statistics_Model_GoalStatistics(); + $result = $statistics->migrateStatistics(); + + foreach (get_object_vars($result) as $name => $value) { + echo "$name: $value\n"; + } + } + +} diff --git a/application/modules/statistics/controllers/CronDailyController.php b/application/modules/statistics/controllers/CronDailyController.php new file mode 100644 index 000000000..b4b5346ea --- /dev/null +++ b/application/modules/statistics/controllers/CronDailyController.php @@ -0,0 +1,33 @@ +. + **/ +class Statistics_CronDailyController extends Local_Controller_Action_CliAbstract +{ + + public function runAction() + { + //20170505 Ronald: remove this for now, we do not use this table + //$stat = new Statistics_Model_Stat(); + //$stat->dailyPageviews(); + } + +} diff --git a/application/modules/statistics/controllers/DailyController.php b/application/modules/statistics/controllers/DailyController.php new file mode 100644 index 000000000..8fd2d3c82 --- /dev/null +++ b/application/modules/statistics/controllers/DailyController.php @@ -0,0 +1,93 @@ +. + **/ +class Statistics_DailyController extends Zend_Controller_Action +{ + + const PROJECT_ID = 'project_id'; + const YEAR = 'year'; + const MONTH = 'month'; + const DAY = 'day'; + + /** @var Statistics_Model_GoalStatistics */ + protected $tableStatistics; + /** @var Zend_Auth */ + protected $authorization; + /** @var Zend_Controller_Request_Abstract */ + protected $request; + /** @var mixed */ + protected $loginMemberId; + + public function init() + { + parent::init(); + + $this->tableStatistics = new Statistics_Model_GoalStatistics(); + $this->authorization = Zend_Auth::getInstance(); + $this->loginMemberId = + $this->authorization->hasIdentity() ? $this->authorization->getStorage()->read()->member_id : 0; + $this->request = $this->getRequest(); + } + + public function showAction() + { + $this->_helper->layout->disableLayout(); + } + + public function ajaxAction() + { + $this->_helper->layout->disableLayout(); + $this->_helper->viewRenderer->setNoRender(true); + + $identifier = $this->request->getParam(self::PROJECT_ID); + $year = $this->request->getParam(self::YEAR); + $month = $this->request->getParam(self::MONTH); + $day = $this->request->getParam(self::DAY); + + $resultSet = $this->tableStatistics->getDailyStatistics($identifier, $year, $month, $day); + + if (empty($resultSet)) { + $this->_helper->json(null); + } else { + $this->_helper->json($this->generateGoogleChartDataSet($resultSet)); + } + } + + /** + * @param array $dataSet + * + * @return array + */ + protected function generateGoogleChartDataSet($dataSet) + { + $rows = array(); + $rows[] = array('Key', 'Quantity'); + foreach ($dataSet as $value) { + foreach ($value as $key => $rowElement) { + $rows[] = array($key, (int)$rowElement); + } + } + + return $rows; + } + +} diff --git a/application/modules/statistics/controllers/DataController.php b/application/modules/statistics/controllers/DataController.php new file mode 100644 index 000000000..7d5100357 --- /dev/null +++ b/application/modules/statistics/controllers/DataController.php @@ -0,0 +1,214 @@ +. + * + * Created: 31.07.2017 + */ + +class Statistics_DataController extends Zend_Controller_Action +{ + + /** @var Zend_Config */ + protected $db_config; + + public function init() + { + $contextSwitch = $this->_helper->getHelper('contextSwitch'); + $contextSwitch->setDefaultContext('json'); + $this->db_config = Zend_Registry::get('config')->settings->dwh; + } + + public function projectAction() + { + $modelData = new Statistics_Model_Data($this->db_config->toArray()); + $id = (int) $this->getParam('p'); + try { + $result = $modelData->getProject($id); + } catch (Exception $e) { + Zend_Registry::get('logger')->error($e->getMessage()); + return $this->_helper->json->sendJson(array( + 'status' => 'error', + 'msg' => 'error while processing request', + 'data' => '' + )); + } + if ($result) { + $msg = array( + 'status' => 'ok', + 'msg' => '', + 'data' => $result + ); + return $this->_helper->json->sendJson($msg); + } + return $this->_helper->json->sendJson(array( + 'status' => 'not found', + 'msg' => 'data with given id could not be found.', + 'data' => '' + )); + } + + public function projectsAction() + { + /* + $modelData = new Statistics_Model_Data($this->db_config->toArray()); + $limit = (int) $this->getParam('l'); + try { + $result = $modelData->getProjects($limit); + } catch (Exception $e) { + Zend_Registry::get('logger')->error($e->getMessage()); + return $this->_helper->json->sendJson(array( + 'status' => 'error', + 'msg' => 'error while processing request', + 'data' => '' + )); + } + if ($result) { + $msg = array( + 'status' => 'ok', + 'msg' => '', + 'data' => $result + ); + return $this->_helper->json->sendJson($msg); + } + return $this->_helper->json->sendJson(array( + 'status' => 'not found', + 'msg' => 'data with given id could not be found.', + 'data' => '' + )); + */ + $result = array(); + $result[] = array('memberdate'=>'07-26', 'daycount'=>'88'); + $result[] = array('memberdate'=>'07-27', 'daycount'=>'84'); + $result[] = array('memberdate'=>'07-28', 'daycount'=>'101'); + $result[] = array('memberdate'=>'07-29', 'daycount'=>'96'); + $result[] = array('memberdate'=>'07-30', 'daycount'=>'66'); + $result[] = array('memberdate'=>'07-31', 'daycount'=>'110'); + $result[] = array('memberdate'=>'08-01', 'daycount'=>'90'); + $result[] = array('memberdate'=>'08-02', 'daycount'=>'81'); + $result[] = array('memberdate'=>'08-03', 'daycount'=>'81'); + $result[] = array('memberdate'=>'08-04', 'daycount'=>'85'); + $result[] = array('memberdate'=>'08-05', 'daycount'=>'72'); + $result[] = array('memberdate'=>'08-06', 'daycount'=>'61'); + $result[] = array('memberdate'=>'08-07', 'daycount'=>'64'); + $result[] = array('memberdate'=>'08-08', 'daycount'=>'84'); + $result[] = array('memberdate'=>'08-09', 'daycount'=>'81'); + $result[] = array('memberdate'=>'08-10', 'daycount'=>'86'); + $result[] = array('memberdate'=>'08-11', 'daycount'=>'86'); + $result[] = array('memberdate'=>'08-12', 'daycount'=>'82'); + $result[] = array('memberdate'=>'08-13', 'daycount'=>'64'); + + return $this->_helper->json->sendJson($result); + } + + public function memberAction() + { + $result = array(); + $result[] = array("projectdate"=>"July 26th","daycount"=>"29"); + $result[] = array("projectdate"=>"July 27th","daycount"=>"34"); + $result[] = array("projectdate"=>"July 28th","daycount"=>"32"); + $result[] = array("projectdate"=>"July 29th","daycount"=>"26"); + $result[] = array("projectdate"=>"July 30th","daycount"=>"13"); + $result[] = array("projectdate"=>"July 31st","daycount"=>"33"); + $result[] = array("projectdate"=>"August 1st","daycount"=>"25"); + $result[] = array("projectdate"=>"August 2nd","daycount"=>"30"); + $result[] = array("projectdate"=>"August 3rd","daycount"=>"31"); + $result[] = array("projectdate"=>"August 4th","daycount"=>"31"); + $result[] = array("projectdate"=>"August 5th","daycount"=>"22"); + $result[] = array("projectdate"=>"August 6th","daycount"=>"12"); + $result[] = array("projectdate"=>"August 7th","daycount"=>"13"); + $result[] = array("projectdate"=>"August 8th","daycount"=>"35"); + $result[] = array("projectdate"=>"August 9th","daycount"=>"31"); + $result[] = array("projectdate"=>"August 10th","daycount"=>"25"); + $result[] = array("projectdate"=>"August 11th","daycount"=>"43"); + $result[] = array("projectdate"=>"August 12th","daycount"=>"26"); + $result[] = array("projectdate"=>"August 13th","daycount"=>"15"); + + return $this->_helper->json->sendJson($result); + + /* + $modelData = new Statistics_Model_Data($this->db_config->toArray()); + $id = (int) $this->getParam('m'); + try { + $result = $modelData->getMember($id); + } catch (Exception $e) { + Zend_Registry::get('logger')->error($e->getMessage()); + return $this->_helper->json->sendJson(array( + 'status' => 'error', + 'msg' => 'error while processing request', + 'data' => '' + )); + } + if ($result) { + $msg = array( + 'status' => 'ok', + 'msg' => '', + 'data' => $result + ); + return $this->_helper->json->sendJson($msg); + } + return $this->_helper->json->sendJson(array( + 'status' => 'not found', + 'msg' => 'data with given id could not be found.', + 'data' => '' + )); + */ + } + + public function membersAction() + { + $modelData = new Statistics_Model_Data($this->db_config->toArray()); + $limit = (int) $this->getParam('l'); + try { + $result = $modelData->getMembers($limit); + } catch (Exception $e) { + Zend_Registry::get('logger')->error($e->getMessage()); + return $this->_helper->json->sendJson(array( + 'status' => 'error', + 'msg' => 'error while processing request', + 'data' => '' + )); + } + if ($result) { + $msg = array( + 'status' => 'ok', + 'msg' => '', + 'data' => $result + ); + return $this->_helper->json->sendJson($msg); + } + return $this->_helper->json->sendJson(array( + 'status' => 'not found', + 'msg' => 'data with given id could not be found.', + 'data' => '' + )); + } + + public function newprojectsstatAction() + { + // last two year for example + $result=array(100,200,50,60,80,70,100,200,50,60,80,70); + $msg = array( + 'status' => 'ok', + 'msg' => '', + 'data' => $result + ); + return $this->_helper->json->sendJson($msg); + } +} \ No newline at end of file diff --git a/application/modules/statistics/controllers/MonthlyController.php b/application/modules/statistics/controllers/MonthlyController.php new file mode 100644 index 000000000..0828da3ed --- /dev/null +++ b/application/modules/statistics/controllers/MonthlyController.php @@ -0,0 +1,94 @@ +. + **/ +class Statistics_MonthlyController extends Zend_Controller_Action +{ + + const PROJECT_ID = 'project_id'; + const YEAR = 'year'; + const MONTH = 'month'; + const DAY = 'day'; + + /** @var Statistics_Model_GoalStatistics */ + protected $tableStatistics; + /** @var Zend_Auth */ + protected $authorization; + /** @var Zend_Controller_Request_Abstract */ + protected $request; + /** @var mixed */ + protected $loginMemberId; + + public function init() + { + parent::init(); + + $this->tableStatistics = new Statistics_Model_GoalStatistics(); + $this->authorization = Zend_Auth::getInstance(); + $this->loginMemberId = + $this->authorization->hasIdentity() ? $this->authorization->getStorage()->read()->member_id : 0; + $this->request = $this->getRequest(); + } + + public function showAction() + { + $this->_helper->layout->disableLayout(); + } + + public function ajaxAction() + { + $this->_helper->layout->disableLayout(); + $this->_helper->viewRenderer->setNoRender(true); + + $identifier = $this->request->getParam(self::PROJECT_ID); + $year = $this->request->getParam(self::YEAR); + $month = $this->request->getParam(self::MONTH); + + $resultSet = $this->tableStatistics->getMonthlyStatistics($identifier, $year, $month); + + if (empty($resultSet)) { + $this->_helper->json(null); + } else { + $this->_helper->json($this->generateGoogleChartDataSet($resultSet)); + } + } + + /** + * @param array $dataSet + * + * @return array + */ + protected function generateGoogleChartDataSet($dataSet) + { + $rows = array(); + $rows[] = array_keys($dataSet[0]); + foreach ($dataSet as $value) { + $row = array(); + foreach ($value as $key => $rowElement) { + $row[] = (int)$rowElement; + } + $rows[] = $row; + } + + return $rows; + } + +} diff --git a/application/modules/statistics/controllers/WeeklyController.php b/application/modules/statistics/controllers/WeeklyController.php new file mode 100644 index 000000000..7eafd9637 --- /dev/null +++ b/application/modules/statistics/controllers/WeeklyController.php @@ -0,0 +1,94 @@ +. + **/ +class Statistics_WeeklyController extends Zend_Controller_Action +{ + + const PROJECT_ID = 'project_id'; + const YEAR = 'year'; + const MONTH = 'month'; + const DAY = 'day'; + const YEARWEEK = 'yearweek'; + + /** @var Statistics_Model_GoalStatistics */ + protected $tableStatistics; + /** @var Zend_Auth */ + protected $authorization; + /** @var Zend_Controller_Request_Abstract */ + protected $request; + /** @var mixed */ + protected $loginMemberId; + + public function init() + { + parent::init(); + + $this->tableStatistics = new Statistics_Model_GoalStatistics(); + $this->authorization = Zend_Auth::getInstance(); + $this->loginMemberId = + $this->authorization->hasIdentity() ? $this->authorization->getStorage()->read()->member_id : 0; + $this->request = $this->getRequest(); + } + + public function showAction() + { + $this->_helper->layout->disableLayout(); + } + + public function ajaxAction() + { + $this->_helper->layout->disableLayout(); + $this->_helper->viewRenderer->setNoRender(true); + + $identifier = $this->request->getParam(self::PROJECT_ID); + $yearWeek = $this->request->getParam(self::YEARWEEK); + + $resultSet = $this->tableStatistics->getWeeklyStatistics($identifier, $yearWeek); + + if (empty($resultSet)) { + $this->_helper->json(null); + } else { + $this->_helper->json($this->generateGoogleChartDataSet($resultSet)); + } + } + + /** + * @param array $dataSet + * + * @return array + */ + protected function generateGoogleChartDataSet($dataSet) + { + $rows = array(); + $rows[] = array_keys($dataSet[0]); + foreach ($dataSet as $value) { + $row = array(); + foreach ($value as $key => $rowElement) { + $row[] = (int)$rowElement; + } + $rows[] = $row; + } + + return $rows; + } + +} diff --git a/application/modules/statistics/library/statistics/ranking/RankingInterface.php b/application/modules/statistics/library/statistics/ranking/RankingInterface.php new file mode 100644 index 000000000..1d6a3634a --- /dev/null +++ b/application/modules/statistics/library/statistics/ranking/RankingInterface.php @@ -0,0 +1,31 @@ +. + **/ +interface Statistics_Ranking_RankingInterface +{ + + /** + * @param $data + * @return mixed + */ + public function calculateRankingValue($data); + +} diff --git a/application/modules/statistics/library/statistics/ranking/WeightedAverageRanking.php b/application/modules/statistics/library/statistics/ranking/WeightedAverageRanking.php new file mode 100644 index 000000000..d717ed775 --- /dev/null +++ b/application/modules/statistics/library/statistics/ranking/WeightedAverageRanking.php @@ -0,0 +1,57 @@ +. + **/ +class Statistics_Ranking_WeightedAverageRanking implements Statistics_Ranking_RankingInterface +{ + + protected $weightForKeys; + + + function __construct() + { + $this->weightForKeys = array( + 'count_views' => 0.1, + 'count_plings' => 5, + 'count_updates' => 1, + 'count_comments' => 1, + 'count_followers' => 0, + 'count_supporters' => 0, + 'count_money' => 0 + ); + } + + /** + * @param $data + * @return float + */ + public function calculateRankingValue($data) + { + $weightedSum = 0.0; + $dividerSum = 0.0; + + foreach ($this->weightForKeys as $key => $weight) { + $weightedSum += $weight * (float)$data[$key]; + $dividerSum += $weight; + } + return $weightedSum / $dividerSum; + } + +} diff --git a/application/modules/statistics/models/Data.php b/application/modules/statistics/models/Data.php new file mode 100644 index 000000000..1a8419ecb --- /dev/null +++ b/application/modules/statistics/models/Data.php @@ -0,0 +1,620 @@ +. + * + * Created: 31.07.2017 + */ + +class Statistics_Model_Data +{ + + const DEFAULT_STORE_ID = 22; //opendesktop + + /** @var Zend_Db_Adapter_Pdo_Abstract */ + protected $_db; + + public function __construct($options) { + if (isset($options['db'])) { + $this->initDbAdapter($options['db']); + } else { + throw new Exception('configuration parameter for database connection needed'); + } + } + + private function initDbAdapter($db) + { + $adapter = $db['adapter']; + $params = $db['params']; + //$default = (int)(isset($params['isDefaultTableAdapter']) && $params['isDefaultTableAdapter'] + // || isset($params['default']) && $params['default']); + unset($params['adapter'], $params['default'], $params['isDefaultTableAdapter']); + $adapter = Zend_Db::factory($adapter, $params); + $this->_db = $adapter; + } + + public function getNewmemberstats(){ + $sql = "SELECT DATE(`created_at`) as memberdate , count(*) as daycount FROM dwh.ods_member_v group by memberdate order by memberdate desc limit 30"; + $result = $this->_db->fetchAll($sql); + return $result; + } + + public function getNewprojectstats(){ + $sql = "SELECT DATE(`created_at`) as projectdate , count(*) as daycount FROM dwh.ods_project_v where status>=40 group by projectdate order by projectdate desc limit 30"; + $result = $this->_db->fetchAll($sql); + return $result; + } + + public function getNewprojectWeeklystats(){ + $sql = "SELECT YEARWEEK(`created_at`) as yyyykw , count(*) as amount + FROM project p + join stat_cat_tree t on p.project_category_id = t.project_category_id + where status=100 and type_id = 1 + group by yyyykw + order by yyyykw + desc limit 60"; + $result = $this->_db->fetchAll($sql); + return $result; + } + public function getNewprojectWeeklystatsWithoutWallpapers(){ + $sql = "SELECT YEARWEEK(`created_at`) as yyyykw , count(*) as amount + FROM project p + join stat_cat_tree t on p.project_category_id = t.project_category_id + where status=100 and type_id = 1 + and (t.lft<899 or t.rgt>988) + group by yyyykw + order by yyyykw + desc limit 60"; + $result = $this->_db->fetchAll($sql); + return $result; + } + + public function getNewprojectWeeklystatsWallpapers(){ + $sql = "SELECT YEARWEEK(`created_at`) as yyyykw , count(*) as amount + FROM project p + join stat_cat_tree t on p.project_category_id = t.project_category_id + where status=100 and type_id = 1 + and (t.lft>899 and t.rgt<988) + group by yyyykw + order by yyyykw + desc limit 60"; + $result = $this->_db->fetchAll($sql); + return $result; + } + + public function getPayout($yyyymm){ + + $sql = "SELECT * , + (select username from member m where m.member_id = p.member_id) username + FROM dwh.member_payout p where yearmonth = :yyyymm order by amount desc"; + $result = $this->_db->fetchAll($sql, array("yyyymm"=>$yyyymm)); + return $result; + } + + public function getPayoutMemberPerCategory($yyyymm,$catid){ + + $modelProjectCategories = new Default_Model_DbTable_ProjectCategory(); + $ids = $modelProjectCategories->fetchChildIds($catid); + array_push($ids, $catid); + $idstring = implode(',', $ids); + + $sql = " + select * from + ( + select + member_id + ,(select username from member m where m.member_id = v.member_id) username + ,round(sum(probably_payout_amount)) as amount + from member_dl_plings_v as v + where project_category_id IN (".$idstring.") and v.yearmonth= :yyyymm + group by v.member_id + order by amount desc + ) tmp where amount>0 + "; + $result = $this->_db->fetchAll($sql, array("yyyymm"=>$yyyymm)); + return $result; + } + + public function getNewcomer($yyyymm){ + $yyyymm_vor = $this->getLastYearMonth($yyyymm); + $sql = "SELECT member_id + , (select username from member m where m.member_id = member_payout.member_id) as username + , paypal_mail,round(amount,2) as amount FROM member_payout WHERE yearmonth =:yyyymm + and member_id not in (select member_id from member_payout where yearmonth =:yyyymm_vor) + order by amount desc + "; + $result = $this->_db->fetchAll($sql, array("yyyymm"=>$yyyymm, "yyyymm_vor"=>$yyyymm_vor)); + return $result; + } + + public function getNewloser($yyyymm){ + $yyyymm_vor = $this->getLastYearMonth($yyyymm); + $sql = "SELECT member_id + , (select username from member m where m.member_id = member_payout.member_id) as username + , paypal_mail,round(amount,2) as amount FROM member_payout WHERE yearmonth =:yyyymm_vor + and member_id not in (select member_id from member_payout where yearmonth =:yyyymm) + order by amount desc + "; + $result = $this->_db->fetchAll($sql, array("yyyymm"=>$yyyymm, "yyyymm_vor"=>$yyyymm_vor)); + return $result; + } + + public function getMonthDiff($yyyymm){ + $yyyymm_vor = $this->getLastYearMonth($yyyymm); + $sql = " + select akt.member_id + , (select username from member m where m.member_id = akt.member_id) as username + , akt.amount as am_akt + , let.amount as am_let + , round(akt.amount-let.amount) as am_diff + , akt.yearmonth ym_akt + , let.yearmonth ym_let + from + (select member_id, amount,yearmonth from member_payout where yearmonth = :yyyymm) akt, + (select member_id, amount,yearmonth from member_payout where yearmonth = :yyyymm_vor) let + where akt.member_id = let.member_id + order by am_diff desc + "; + $result = $this->_db->fetchAll($sql, array("yyyymm"=>$yyyymm, "yyyymm_vor"=>$yyyymm_vor)); + + return $result; + } + +/* + public function getDownloadsDaily($numofmonthback){ + $sql = " + select + SUBSTR(d.date_yyyymmdd,1,6) as symbol + ,SUBSTR(d.date_yyyymmdd,7,8)*1 as date + ,d.count as price + from dwh.files_downloads_daily as d + where STR_TO_DATE(date_yyyymmdd,'%Y%m%d' ) >= (DATE_FORMAT(CURDATE(), '%Y-%m-01')- INTERVAL :numofmonthback MONTH) + and STR_TO_DATE(date_yyyymmdd,'%Y%m%d' )< CURDATE() + order by date_yyyymmdd asc + "; + $result = $this->_db->fetchAll($sql,array("numofmonthback"=>$numofmonthback)); + return $result; + } +*/ + + public function getDownloadsDaily($numofmonthback){ + $sql = " + select + SUBSTR(d.date_yyyymmdd,1,6) as symbol + ,SUBSTR(d.date_yyyymmdd,7,8)*1 as date + ,d.count as price + from dwh.files_downloads_daily as d + where STR_TO_DATE(date_yyyymmdd,'%Y%m%d' ) >= (DATE_FORMAT(CURDATE(), '%Y-%m-01')- INTERVAL :numofmonthback MONTH) + and STR_TO_DATE(date_yyyymmdd,'%Y%m%d' )< CURDATE() + union + + select + concat(SUBSTR(d.date_yyyymmdd,1,6),' payout') as symbol + ,SUBSTR(d.date_yyyymmdd,7,8)*1 as date + ,d.count as price + from dwh.payout_daily as d + where STR_TO_DATE(date_yyyymmdd,'%Y%m%d' ) >= (DATE_FORMAT(CURDATE(), '%Y-%m-01')- INTERVAL :numofmonthback MONTH) + and STR_TO_DATE(date_yyyymmdd,'%Y%m%d' )< CURDATE() + + "; + $result = $this->_db->fetchAll($sql,array("numofmonthback"=>$numofmonthback)); + return $result; + } + + public function getDownloadsUndPayoutsDaily($yyyymm){ + $sql = " + select + concat(SUBSTR(d.date_yyyymmdd,1,6),' downloads') as symbol + ,SUBSTR(d.date_yyyymmdd,7,8)*1 as date + ,d.count as price + from dwh.files_downloads_daily as d + where SUBSTR(d.date_yyyymmdd,1,6)=:yyyymm + union + select + concat(SUBSTR(d.date_yyyymmdd,1,6),' payouts') as symbol + ,SUBSTR(d.date_yyyymmdd,7,8)*1 as date + ,d.count as price + from dwh.payout_daily as d + where SUBSTR(d.date_yyyymmdd,1,6)=:yyyymm + + "; + $result = $this->_db->fetchAll($sql,array("yyyymm"=>$yyyymm)); + return $result; + } + + +/** + ,(select count(1) from dwh.files_downloads dd where dd.project_id = d.project_id + and dd.downloaded_timestamp between :date_start and :date_end + and dd.referer like 'https://www.google%') as cntGoogle +*/ + + public function getTopDownloadsPerDate($date){ + $date_start =$date.' 00:00:00'; + $date_end =$date.' 23:59:59'; + $sql = " + select d.project_id + , count(1) as cnt + ,(select p.title from project p where p.project_id = d.project_id) as ptitle + ,(select p.created_at from project p where p.project_id = d.project_id) as pcreated_at + ,(select c.title from category c, project p where p.project_id = d.project_id and p.project_category_id=c.project_category_id) as ctitle + ,(select username from member m , project p where m.member_id = p.member_id and p.project_id = d.project_id) as username + from dwh.files_downloads d + where d.downloaded_timestamp between :date_start and :date_end + group by d.project_id + order by cnt desc + limit 50 + "; + + $result = $this->_db->fetchAll($sql,array("date_start"=>$date_start,"date_end"=>$date_end)); + return $result; + } + + public function getTopDownloadsPerMonth($month,$catid){ + + $sd = $month.'-01'; + $date_start =date('Y-m-01', strtotime($sd)).' 00:00:00'; + $date_end =date('Y-m-t', strtotime($sd)).' 23:59:59'; + + if($catid==0) + { + + // $sql = " + // select d.project_id + // , count(1) as cnt + // ,(select p.title from project p where p.project_id = d.project_id) as ptitle + // ,(select p.created_at from project p where p.project_id = d.project_id) as pcreated_at + // ,(select c.title from category c where d.project_category_id=c.project_category_id) as ctitle + // ,(select username from member m where m.member_id = d.member_id) as username + // from dwh.files_downloads d + // where d.yyyymm = :month + // group by d.project_id,project_category_id,member_id + // order by cnt desc + // limit 50 + // "; + $sql = "select d.project_id + , sum(d.count) as cnt + ,p.title as ptitle + ,p.created_at as pcreated_at + ,(select c.title from category c where d.project_category_id=c.project_category_id) as ctitle + ,(select username from member m where m.member_id = p.member_id) as username + from dwh.files_downloads_project_daily d + join project p on d.project_id = p.project_id + where d.yyyymm = :month + group by d.project_id,d.project_category_id,p.member_id + order by cnt desc + limit 50"; + + }else + { + $modelProjectCategories = new Default_Model_DbTable_ProjectCategory(); + $ids = $modelProjectCategories->fetchChildIds($catid); + array_push($ids, $catid); + $idstring = implode(',', $ids); + // $sql = ' + // select d.project_id + // , count(1) as cnt + // ,(select p.title from project p where p.project_id = d.project_id) as ptitle + // ,(select p.created_at from project p where p.project_id = d.project_id) as pcreated_at + // ,(select c.title from category c where d.project_category_id=c.project_category_id) as ctitle + // ,(select username from member m where m.member_id = d.member_id) as username + // from dwh.files_downloads d + // where d.yyyymm = :month + // and d.project_category_id in ('.$idstring.') + // group by d.project_id,project_category_id,member_id + // order by cnt desc + // limit 50 + // '; + $sql = 'select d.project_id + , sum(d.count) as cnt + ,p.title as ptitle + ,p.created_at as pcreated_at + ,(select c.title from category c where d.project_category_id=c.project_category_id) as ctitle + ,(select username from member m where m.member_id = p.member_id) as username + from dwh.files_downloads_project_daily d + join project p on d.project_id = p.project_id + where d.yyyymm = :month + and d.project_category_id in ('.$idstring.') + group by d.project_id,d.project_category_id,p.member_id + order by cnt desc + limit 50'; + } + + $result = $this->_db->fetchAll($sql,array("month"=>$month)); + return $result; + } + + public function getProductMonthly($project_id) + { + $sql = " + select + yyyymm as yearmonth + ,sum(count) as amount + from dwh.files_downloads_project_daily + where project_id = :project_id + group by yyyymm + limit 100 + "; + $result = $this->_db->fetchAll($sql,array("project_id"=>$project_id)); + return $result; + } + + public function getProductDayly($project_id) + { + $sql = " + select yyyymmdd as yearmonth,count as amount + from dwh.files_downloads_project_daily + where project_id = :project_id + order by yyyymmdd desc + limit 1000 + "; + $result = $this->_db->fetchAll($sql,array("project_id"=>$project_id)); + return array_reverse($result); + } + + + public function getDownloadsDomainStati($begin, $end){ + $date_start =$begin.' 00:00:00'; + $date_end =$end.' 23:59:59'; + $sql = " + select count(1) as cnt + ,d.referer_domain + ,is_from_own_domain + from dwh.files_downloads d + where d.downloaded_timestamp between :date_start and :date_end + group by d.referer_domain,is_from_own_domain + order by is_from_own_domain desc, cnt desc + "; + $result = $this->_db->fetchAll($sql,array("date_start"=>$date_start,"date_end"=>$date_end)); + return $result; + } + + + public function getPayoutCategoryMonthly($yyyymm){ + $sql = " + select * from + ( + select project_category_id + ,(select title from category as c where c.project_category_id = v.project_category_id) as title + ,round(sum(probably_payout_amount)) as amount + ,sum(v.num_downloads) as num_downloads + from member_dl_plings_v as v + where yearmonth =:yyyymm + group by v.project_category_id + order by amount desc + ) tmp where amount>0 + "; + $result = $this->_db->fetchAll($sql, array("yyyymm"=>$yyyymm)); + return $result; + } + + + + private function getPayoutCategorySingle($catid) + { + + $modelProjectCategories = new Default_Model_DbTable_ProjectCategory(); + $ids = $modelProjectCategories->fetchChildIds($catid); + array_push($ids, $catid); + $idstring = implode(',', $ids); + // Zend_Registry::get('logger')->info(__METHOD__ . ' - ===================================' ); + // Zend_Registry::get('logger')->info(__METHOD__ . ' - ' . $idstring); + $sql = " + select * from + ( + select + yearmonth + ,(select title from category as c where c.project_category_id = ".$catid.") as symbol + ,round(sum(probably_payout_amount)) as amount + from member_dl_plings_v as v + where project_category_id IN (".$idstring.") + group by v.yearmonth + order by yearmonth asc + ) tmp where amount>0 + "; + $result = $this->_db->fetchAll($sql); + return $result; + } + + public function getPayoutCategory_($catid){ + + if($catid==0) + { + $pids = array(152, 233,158, 148,491,445,295); + $sql = " + select * from + ( + select + 'All' as symbol + ,yearmonth + ,round(sum(probably_payout_amount)) as amount + from member_dl_plings_v as v + group by v.yearmonth + order by yearmonth asc + ) tmp where amount>0 + "; + $result = $this->_db->fetchAll($sql); + foreach ($pids as $catid) { + $t = self::getPayoutCategorySingle($catid); + $result = array_merge($result, $t); + } + } + else + { + $result = self::getPayoutCategorySingle($catid); + } + + return $result; + + } + + public function getPayoutCategory($catid){ + + if($catid==0) + { + // $pids = array(152, 233,158,404, 148,491,445,295); + $modelCategoryStore = new Default_Model_DbTable_ConfigStoreCategory(); + $pids = $modelCategoryStore->fetchCatIdsForStore(self::DEFAULT_STORE_ID); + $sql = " + select * from + ( + select + 'All' as symbol + ,yearmonth + ,round(sum(probably_payout_amount)) as amount + from member_dl_plings_v as v + group by v.yearmonth + order by yearmonth asc + ) tmp where amount>0 + "; + $result = $this->_db->fetchAll($sql); + foreach ($pids as $c) { + $tmp = self::getPayoutCategorySingle($c); + foreach ($result as &$row) { + $row['amount'.$c] = 0; + foreach ($tmp as $t) { + if($t['yearmonth']==$row['yearmonth']) + { + $row['amount'.$c] = $t['amount']; + break; + } + } + } + } + } + else + { + $result = self::getPayoutCategorySingle($catid); + $modelCategoriesTable = new Default_Model_DbTable_ProjectCategory(); + $pids = $modelCategoriesTable->fetchImmediateChildrenIds($catid); + foreach ($pids as $c) { + $tmp = self::getPayoutCategorySingle($c); + foreach ($result as &$row) { + $row['amount'.$c] = 0; + foreach ($tmp as $t) { + if($t['yearmonth']==$row['yearmonth']) + { + $row['amount'.$c] = $t['amount']; + break; + } + } + } + } + } + + return $result; + + } + + public function _getPayoutCategory($catid){ + + if($catid==0) + { + $pids = array(152, 233,158,404, 148,491,445,295); + $sql = " + select * from + ( + select + 'All' as symbol + ,yearmonth + ,round(sum(probably_payout_amount)) as amount + from member_dl_plings_v as v + group by v.yearmonth + order by yearmonth asc + ) tmp where amount>0 + "; + $result = $this->_db->fetchAll($sql); + foreach ($pids as $c) { + $tmp = self::getPayoutCategorySingle($c); + foreach ($result as &$row) { + $row['amount'.$c] = 0; + foreach ($tmp as $t) { + if($t['yearmonth']==$row['yearmonth']) + { + $row['amount'.$c] = $t['amount']; + break; + } + } + } + } + } + else + { + $result = self::getPayoutCategorySingle($catid); + } + + return $result; + + } + + + public function getLastYearMonth($yyyymm){ + $aktdate = strval($yyyymm).'01'; + $fmt = 'Ymd'; + $d = DateTime::createFromFormat($fmt, $aktdate); + $d->modify( 'last day of previous month' ); + return $d->format( 'Ym' ); + } + + public function getPayoutyear(){ + $sql = "select round(sum(amount)) amount,yearmonth from dwh.member_payout group by yearmonth order by yearmonth"; + $result = $this->_db->fetchAll($sql); + return $result; + } + + + public function getPayoutOfMember($member_id){ + //$sql = "select yearmonth, amount from dwh.member_payout where member_id = :member_id order by yearmonth asc"; + $sql = "select yearmonth, amount from dwh.member_payout where member_id = :member_id order by yearmonth asc"; + $result = $this->_db->fetchAll($sql, array("member_id"=>$member_id)); + return $result; + } + + public function getProject($project_id) + { + $sql = "SELECT * FROM ods_project_v WHERE project_id = :projectId"; + $result = $this->_db->fetchAll($sql, array('projectId' => $project_id)); + return $result; + } + + public function getProjects($limit = 50) + { + $limit = (int)$limit; + $sql = "SELECT * FROM ods_project_v LIMIT {$limit}"; + $result = $this->_db->fetchAll($sql); + return $result; + } + + public function getMember($member_id) + { + $sql = "SELECT * FROM ods_member_v WHERE member_id = :memberId"; + $result = $this->_db->fetchAll($sql, array('memberId' => (int)$member_id)); + return $result; + } + + + public function getMembers($limit = 50) + { + $sql = "SELECT * FROM ods_member_v"; + $sql = $this->_db->limit($sql, (int)$limit); + $result = $this->_db->fetchAll($sql); + return $result; + } + +} \ No newline at end of file diff --git a/application/modules/statistics/models/DbTable/StatDaily.php b/application/modules/statistics/models/DbTable/StatDaily.php new file mode 100644 index 000000000..f0b07680b --- /dev/null +++ b/application/modules/statistics/models/DbTable/StatDaily.php @@ -0,0 +1,77 @@ +. + **/ +class Statistics_Model_DbTable_StatDaily extends Zend_Db_Table_Abstract +{ + + /** + * The primary key column or columns. + * A compound key should be declared as an array. + * You may declare a single-column primary key + * as a string. + * + * @var mixed + */ + protected $_primary = 'daily_id'; + + /** + * The table name. + * + * @var string + */ + protected $_name = 'stat_daily'; + + protected $_keyColumnsForRow = array('project_id', 'project_category_id', 'project_type_id', 'year', 'month', 'day'); + + + /** + * @param $data + */ + public function save($data) + { + $rowSet = $this->findForColumns($data, $this->_keyColumnsForRow); + + if (null === $rowSet) { + $rowSet = $this->createRow($data); + } else { + $rowSet->setFromArray($data); + } + + $rowSet->save(); + } + + /** + * @param $data + * @param $columns + * @return null|Zend_Db_Table_Row_Abstract + */ + public function findForColumns($data, $columns) + { + + $statement = $this->select()->setIntegrityCheck(false)->from($this->_name); + foreach ($columns as $identifier) { + $statement->where($this->_db->quoteIdentifier($identifier) . ' = ?', $data[$identifier]); + } + + return $this->fetchRow($statement); + } + +} diff --git a/application/modules/statistics/models/GoalStatistics.php b/application/modules/statistics/models/GoalStatistics.php new file mode 100644 index 000000000..b8c55b334 --- /dev/null +++ b/application/modules/statistics/models/GoalStatistics.php @@ -0,0 +1,323 @@ +. + **/ +class Statistics_Model_GoalStatistics +{ + + const MYSQL_DATE_FORMAT = "Y-m-d H:i:s"; + const DEFAULT_RANKING_PLUGIN = 'Statistics_Ranking_WeightedAverageRanking'; + + /** @var Statistics_Ranking_RankingInterface */ + protected $_rankingPlugin; + + + /** + * @param Statistics_Ranking_RankingInterface $rankingPlugin + */ + function __construct(Statistics_Ranking_RankingInterface $rankingPlugin = null) + { + if (is_null($rankingPlugin)) { + $default = self::DEFAULT_RANKING_PLUGIN; + $this->_rankingPlugin = new $default; + return $this; + } + $this->_rankingPlugin = $rankingPlugin; + } + + public function setupDatabase() + { + exit(0); + + $sql = " + CREATE TABLE `stat_daily` ( + `daily_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', + `project_id` INT(11) NOT NULL COMMENT 'ID of the project', + `project_category_id` INT(11) DEFAULT '0' COMMENT 'Category', + `project_type` INT(11) NOT NULL COMMENT 'type of the project', + `count_views` INT(11) DEFAULT '0', + `count_plings` INT(11) DEFAULT '0', + `count_updates` INT(11) DEFAULT NULL, + `count_comments` INT(11) DEFAULT NULL, + `count_followers` INT(11) DEFAULT NULL, + `count_supporters` INT(11) DEFAULT NULL, + `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, + `year` INT(11) DEFAULT NULL COMMENT 'z.B.: 1988', + `month` INT(11) DEFAULT NULL COMMENT 'z.b: 1-12', + `day` INT(11) DEFAULT NULL COMMENT 'z.B. 1-31', + `year_week` INT(11) DEFAULT NULL COMMENT 'z.b.: 201232', + PRIMARY KEY (`daily_id`) + ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='Store daily statistic'; + + CREATE TABLE `app_config` ( + `config_id` INT(11) NOT NULL AUTO_INCREMENT, + `group` VARCHAR(20) NOT NULL, + `name` VARCHAR(20) NOT NULL, + `value` VARCHAR(20) NOT NULL, + PRIMARY KEY (`config_id`), + KEY `index_group` (`group`) + ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Store config for statistic'; + "; + + $sql_alter = " + ALTER TABLE pling.stat_daily + ADD ranking_value INT AFTER year_week; + ALTER TABLE pling.stat_daily + ADD count_money FLOAT AFTER count_supporters; + ALTER TABLE pling.stat_daily + CHANGE ranking_value ranking_value FLOAT; + ALTER TABLE pling.stat_daily + CHANGE project_type project_type_id INT(11) NOT NULL COMMENT 'type of the project'; + "; + + $database = Zend_Db_Table::getDefaultAdapter(); + $database->query($sql)->execute(); + $database->query($sql_alter)->execute(); + } + + public function dailyPageviews() + { + $sql = ' + insert into stat_daily_pageviews + select project_id + ,count(project_id) cnt + ,(select p.project_category_id from project p where v.project_id = p.project_id) project_category_id + ,CURDATE() created_at + from stat_page_views v where v.created_at > date_sub(CURDATE(), interval 6 month) + group by project_id + '; + $database = Zend_Db_Table::getDefaultAdapter(); + $database->query($sql)->execute(); + } + + public function migrateStatistics() + { + $database = Zend_Db_Table::getDefaultAdapter(); + + $sqlTruncate = "TRUNCATE stat_daily"; + $result = $database->query($sqlTruncate); + + + $sql = " + SELECT DATE_FORMAT(stat_page_views.created_at,\"%d.%m.%Y\") AS stat_date + FROM pling.stat_page_views stat_page_views + GROUP BY stat_date + ORDER BY created_at ASC; + "; + + $queryObject = $database->query($sql); + $resultSet = $queryObject->fetchAll(); + + $addedRows = 0; + $resultMessage = new stdClass(); + + if (count($resultSet) > 0) { + foreach ($resultSet as $element) { + $resultMessage = $this->generateDailyStatistics($element['stat_date']); + if ($resultMessage->result === false) { + break; + } + $addedRows += $resultMessage->AffectedRows; + } + + } + + $resultMessage->AffectedRows = $addedRows; + $resultMessage->Environment = APPLICATION_ENV; + + return $resultMessage; + } + + /** + * @param string|null $forDate + * @return stdClass + */ + public function generateDailyStatistics($forDate = null) + { + $database = Zend_Db_Table::getDefaultAdapter(); + + $dateCreatedAt = new DateTime(); + $mysqlCreatedAt = $dateCreatedAt->format(self::MYSQL_DATE_FORMAT); + + if (is_null($forDate)) { + $generateForDateObject = new DateTime(); + } else { + $generateForDateObject = new DateTime($forDate); + } + + $generateForDateObject->setTime(0, 0, 0); + $mysqlStartDate = $generateForDateObject->format(self::MYSQL_DATE_FORMAT); + + $generateForDateObject->setTime(23, 59, 59); + $mysqlEndDate = $generateForDateObject->format(self::MYSQL_DATE_FORMAT); + + $sql = " + select + prj.project_id, + prj.type_id as project_type_id, + prj.project_category_id, + (select count(1) from stat_page_views pv where pv.project_id = prj.project_id and pv.created_at between '{$mysqlStartDate}' and '{$mysqlEndDate}' group by pv.project_id) AS count_views, + (select count(1) from plings p where p.project_id = prj.project_id and p.pling_time between '{$mysqlStartDate}' and '{$mysqlEndDate}' AND p.status_id in (2,3,4) group by p.project_id) AS count_plings, + (select count(1) from project pu where pu.pid = prj.project_id and (pu.created_at between '{$mysqlStartDate}' and '{$mysqlEndDate}') and pu.type_id = 2 group by pu.pid) AS count_updates, + (select count(1) from project_follower pf where pf.project_id = prj.project_id) AS count_followers, + (SELECT count(1) FROM plings WHERE status_id >= 2 AND create_time BETWEEN '{$mysqlStartDate}' AND '{$mysqlEndDate}' AND project_id = prj.project_id AND comment is not null GROUP BY project_id) AS count_comments, + (SELECT count(member_id) FROM (SELECT member_id, project_id FROM plings WHERE status_id >= 2 AND create_time BETWEEN '{$mysqlStartDate}' AND '{$mysqlEndDate}' GROUP BY member_id, project_id) AS tempCountProjectSupporter WHERE project_id = prj.project_id) AS count_supporters, + (SELECT sum(amount) FROM plings WHERE status_id >= 2 AND create_time BETWEEN '{$mysqlStartDate}' AND '{$mysqlEndDate}' AND project_id = prj.project_id GROUP BY project_id) AS count_money, + '" . $mysqlCreatedAt . "' AS created_at, + DATE_FORMAT('{$mysqlStartDate}', '%Y') AS year, + DATE_FORMAT('{$mysqlStartDate}', '%m') AS month, + DATE_FORMAT('{$mysqlStartDate}', '%d') AS day, + YEARWEEK('{$mysqlStartDate}',1) AS year_week + from + project as prj + where + prj.status = " . Default_Model_DbTable_Project::PROJECT_ACTIVE . " + and prj.type_id = " . Default_Model_DbTable_Project::PROJECT_TYPE_STANDARD . " + group by prj.project_id; + "; + + $statement = $database->query($sql); + + $statTable = new Statistics_Model_DbTable_StatDaily(); + + while ($row = $statement->fetch(Zend_Db::FETCH_ASSOC, Zend_Db::FETCH_ORI_NEXT)) { + $row['ranking_value'] = $this->_rankingPlugin->calculateRankingValue($row); + $statTable->save($row); + } + + $resultMessage = new stdClass(); + $resultMessage->result = $statement->errorCode() == '00000' ? true : false; + $resultMessage->errorMessage = implode(' ', $statement->errorInfo()); + $resultMessage->errorCode = $statement->errorCode(); + $resultMessage->AffectedRows = $statement->rowCount(); + + return $resultMessage; + + } + + /** + * @param string $identifier + * @param int $year + * @param int $month + * @param int $day + * @return array + */ + public function getDailyStatistics($identifier, $year, $month, $day) + { + $sql = " + SELECT sd.count_views AS views, sd.count_plings AS plings, sd.count_updates AS updates, sd.count_comments AS comments, sd.count_followers AS followers, sd.count_supporters AS supporters + FROM stat_daily AS sd + WHERE + sd.project_id = ? + AND sd.year = ? + AND sd.month = ? + AND sd.day = ?; + "; + $database = Zend_Db_Table::getDefaultAdapter(); + $sql = $database->quoteInto($sql, $identifier, 'INTEGER', 1); + $sql = $database->quoteInto($sql, $year, 'INTEGER', 1); + $sql = $database->quoteInto($sql, $month, 'INTEGER', 1); + $sql = $database->quoteInto($sql, $day, 'INTEGER', 1); + + $resultSet = $database->query($sql)->fetchAll(); + + return $resultSet; + } + + /** + * @param string $identifier + * @param int $year + * @param int $month + * @return array + */ + public function getMonthlyStatistics($identifier, $year, $month) + { + $sql = " + SELECT sd.year_week, sum(sd.count_views) AS views, sum(sd.count_plings) AS plings, sum(sd.count_updates) AS updates, sum(sd.count_comments) AS comments, avg(sd.count_followers) AS followers, avg(sd.count_supporters) AS supporters + FROM stat_daily AS sd + WHERE + sd.project_id = ? + AND sd.year = ? + AND sd.month = ? + GROUP BY sd.year_week + ORDER BY sd.day ASC; + "; + $database = Zend_Db_Table::getDefaultAdapter(); + $sql = $database->quoteInto($sql, $identifier, 'INTEGER', 1); + $sql = $database->quoteInto($sql, $year, 'INTEGER', 1); + $sql = $database->quoteInto($sql, $month, 'INTEGER', 1); + + $resultSet = $database->query($sql)->fetchAll(); + + return $resultSet; + + } + + /** + * @param string $identifier + * @param int $yearWeek + * @return array + */ + public function getWeeklyStatistics($identifier, $yearWeek) + { + $sql = " + SELECT sd.day, sd.count_views AS views, sd.count_plings AS plings, sd.count_updates AS updates, sd.count_comments AS comments, sd.count_followers AS followers, sd.count_supporters AS supporters + FROM stat_daily AS sd + WHERE + sd.project_id = ? + AND sd.year_week = ? + ORDER BY sd.day ASC; + "; + $database = Zend_Db_Table::getDefaultAdapter(); + $sql = $database->quoteInto($sql, $identifier, 'INTEGER', 1); + $sql = $database->quoteInto($sql, $yearWeek, 'INTEGER', 1); + + $resultSet = $database->query($sql)->fetchAll(); + + return $resultSet; + + } + + /** + * @param DateTime $forDate + * @throws Exception + */ + protected function generateRanking(DateTime $forDate) + { + throw new Exception('this code is outdated'); + + $statisticsTable = new Statistics_Model_DbTable_StatDaily(); + + $statement = $statisticsTable->select(); + $statement->setIntegrityCheck(false)->where('year = ?', $forDate->format('Y')) + ->where('month = ?', $forDate->format('m')) + ->where('day = ?', $forDate->format('d')) + ->forUpdate(true); + + $rowSet = $statisticsTable->fetchAll($statement); + + foreach ($rowSet as $row) { + $row->ranking_value = $this->_rankingPlugin->calculateRankingValue($row->toArray()); + $row->save(); + } + + } + +} diff --git a/application/modules/statistics/models/Stat.php b/application/modules/statistics/models/Stat.php new file mode 100644 index 000000000..165ae9ede --- /dev/null +++ b/application/modules/statistics/models/Stat.php @@ -0,0 +1,39 @@ +. + **/ +class Statistics_Model_Stat +{ + + public function dailyPageviews() + { + $sql = ' + INSERT INTO stat_daily_pageviews + SELECT project.project_id, count(stat_page_views.project_id) AS cnt, project.project_category_id, CURDATE() AS created_at + FROM project + JOIN stat_page_views on project.project_id = stat_page_views.project_id AND stat_page_views.created_at > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) + WHERE project.type_id = '.Default_Model_Project::PROJECT_TYPE_STANDARD.' + GROUP BY project.project_id; + '; + $database = Zend_Db_Table::getDefaultAdapter(); + $database->query($sql)->execute(); + } + +}