Advertisement
sbarbosa115

Untitled

Jul 3rd, 2015
254
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 6.68 KB | None | 0 0
  1. <?php
  2.  
  3. use Symfony\Component\HttpFoundation\Response;
  4. use Symfony\Component\HttpFoundation\Request;
  5. use Client\Utils;
  6.  
  7. require_once __DIR__ . '/../vendor/autoload.php';
  8.  
  9. $app = new Silex\Application();
  10. $app->register(new Silex\Provider\DoctrineServiceProvider(), array(
  11.     'dbs.options' => array(
  12.         'mysql_read' => array(
  13.             'driver' => 'pdo_mysql',
  14.             'host' => 'funway-prod.c8kofcdrbu67.us-east-1.rds.amazonaws.com',
  15.             'dbname' => 'funway_prod',
  16.             'user' => 'root',
  17.             'password' => 'Colombia2015',
  18.             'charset' => 'utf8',
  19.         ),
  20.     ),
  21. ));
  22.  
  23. $app->register(new Silex\Provider\MonologServiceProvider(), array(
  24.     'monolog.logfile' => __DIR__ . '/development.log',
  25. ));
  26.  
  27. $reports = $app['controllers_factory'];
  28.  
  29. /**
  30.  * Return number of inactive users.
  31.  * @author Sergio Barbosa <sbarbosa115@gmail.com>
  32.  */
  33. $reports->get('/behavior-users/{range}/{from_date}/{to_date}/{client_id}', function (Request $request) use($app) {
  34.  
  35.     $fromDate = $request->get('from_date') ? $request->get('from_date') : date('Y-m-d');
  36.     $toDate = $request->get('to_date') ? $request->get('to_date') : date('Y-m-d');
  37.  
  38.     $sql = "SELECT route_summary.route, SUM(route_summary.views) AS views
  39.     FROM route_summary
  40.     WHERE route_summary.client_id = {$request->get('client_id')} AND route_summary.created_at BETWEEN '{$fromDate}' AND '{$toDate}'
  41.                GROUP BY route_summary.route ";
  42.  
  43.     if ($request->get('range') == 'all') {
  44.         $sql .= 'HAVING LENGTH(route_summary.route) BETWEEN 1 AND 100 AND views  > 4 ';
  45.     } else {
  46.         $sql .= 'HAVING LENGTH(route_summary.route) BETWEEN 1 AND 100 AND  views BETWEEN ' . explode(',', $request->get('range'))[0] . ' AND ' . explode(',', $request->get('range'))[1] . ' ';
  47.     }
  48.  
  49.     $sql .= "ORDER BY LENGTH(route_summary.route) desc";
  50.  
  51.     $app['monolog']->addDebug($sql);
  52.     $push = $app['dbs']['mysql_read']->fetchAll($sql);
  53.  
  54.     $response = '';
  55.     foreach ($push as $entity) {
  56.         if ($entity['route'] && $entity['views']) {
  57.             $response .= trim($entity['route']) . "," . trim($entity['views']) . "\n";
  58.         }
  59.     }
  60.  
  61.     return new Response($response, 200, array(
  62.         'Content-Encoding' => 'UTF-8',
  63.         'Content-Type' => 'text/csv; charset=UTF-8',
  64.         'Content-Transfer-Encoding' => 'binary'
  65.     ));
  66. })->bind('behavior_users');
  67.  
  68.  
  69.  
  70. /**
  71.  * Return number of lose users.
  72.  * @author Sergio Barbosa <sbarbosa115@gmail.com>
  73.  */
  74. $reports->get('/cohorts/{dateRange}/{interval}/{client_id}', function (Request $request) use($app) {
  75.  
  76.     if (!file_exists($request->server->get('DOCUMENT_ROOT') . $request->getBasePath() . '/history/')) {
  77.         mkdir($request->server->get('DOCUMENT_ROOT') . $request->getBasePath() . '/history/', 7777);
  78.     }
  79.  
  80.     $fileRoute = $request->server->get('DOCUMENT_ROOT') . $request->getBasePath() . '/history/' . $request->get('dateRange') . '_' . $request->get('interval') . '.json';
  81.     if (file_exists($fileRoute)) {
  82.         $stream = function () use ($fileRoute) {
  83.             readfile($fileRoute);
  84.         };
  85.  
  86.         return $app->stream($stream, 200, array(
  87.                     'Content-Type' => 'application/json',
  88.                     'Content-length' => filesize($fileRoute),
  89.         ));
  90.     }
  91.  
  92.     $firstDate = new \DateTime($request->get('dateRange'));
  93.     $firstDate->sub(new DateInterval('P7D'));
  94.  
  95.     $cohorts = array();
  96.  
  97.     for ($j = 0; $j <= 7; $j++) {
  98.         $userCurrentDate = new \DateTime($request->get('dateRange'));
  99.         $userCurrentDate->sub(new DateInterval('P7D'));
  100.         for ($i = 0; $i <= 7; $i++) {
  101.             $sql = "SELECT
  102.                    COUNT(*) AS total
  103.                    FROM user_visit_summary
  104.                    WHERE visits_dates LIKE '%{$userCurrentDate->format('Y-m-d')}%' AND first_date = '{$firstDate->format('Y-m-d')}' AND client_id = {$request->get('client_id')} ";
  105.  
  106.             $users = $app['dbs']['mysql_read']->fetchAssoc($sql);
  107.             $cohorts[$firstDate->format('Y-m-d')][] = $users['total'];
  108.             $userCurrentDate->add(new \DateInterval('P1D'));
  109.         }
  110.         $firstDate->add(new \DateInterval('P1D'));
  111.     }
  112.  
  113.     $result = array('result' => $cohorts, 'name' => 'Infome de Retencion');
  114.     file_put_contents($fileRoute, json_encode($result));
  115.  
  116.     return $app->json($result);
  117. })->bind('cohort');
  118.  
  119.  
  120. /**
  121.  * Return total rfs statistics
  122.  * @author Sergio Barbosa <sbarbosa115@gmail.com>
  123.  */
  124. $reports->get('/rfs/{period}/{client_id}', function (Request $request) use($app) {
  125.     $clientId = $request->get('client_id');
  126.     $period = $request->get('period');
  127.  
  128.     $sql = "SELECT segmentation, newusers_period_{$period} AS newusers_period, avgr_period_{$period} AS avgr_period, avgf_period_{$period} AS avgf_period, avgs_period_{$period} AS avgs_period, period_{$period} AS period
  129.     FROM rfs_total
  130.     WHERE rfs_total.client_id = '{$clientId}'";
  131.  
  132.     $result = $app['dbs']['mysql_read']->fetchAll($sql);
  133.     return $app->json($result);
  134. })->bind('rfs');
  135.  
  136. $app->mount('/api-rest/v1/', $reports);
  137.  
  138.  
  139. /**
  140.  * Return total rfs statistics
  141.  * @author Sergio Barbosa <sbarbosa115@gmail.com>
  142.  */
  143. $reports->get('/rfs-session/{segment}/{client_id}/{period}', function (Request $request) use($app) {
  144.     $clientId = $request->get('client_id');
  145.     $period = $request->get('period');
  146.     $segment = $request->get('segment');
  147.  
  148.     $sql = "SELECT COUNT(user_route_summary_p{$period}.id) AS views, route
  149.                FROM user_route_summary_p{$period}
  150.                WHERE user_route_summary_p{$period}.segment = {$segment} AND user_route_summary_p{$period}.route NOT LIKE '%articulo%' AND user_route_summary_p{$period}.route NOT LIKE '%galeria%'
  151.                      AND user_route_summary_p{$period}.client_id = {$clientId}
  152.                GROUP BY user_route_summary_p{$period}.route
  153.                HAVING COUNT(user_route_summary_p{$period}.id) > 5
  154.                ORDER BY LENGTH(user_route_summary_p{$period}.route) desc";
  155.  
  156.     $app['monolog']->addDebug($sql);
  157.  
  158.     $entities = $app['dbs']['mysql_read']->fetchAll($sql);
  159.  
  160.     $response = '';
  161.     foreach ($entities as $entity) {
  162.         if ($entity['route'] && $entity['views']) {
  163.             $response .= trim($entity['route']) . "," . trim($entity['views']) . "\n";
  164.         }
  165.     }
  166.  
  167.     return new Response($response, 200, array(
  168.         'Content-Encoding' => 'UTF-8',
  169.         'Content-Type' => 'text/csv; charset=UTF-8',
  170.         'Content-Transfer-Encoding' => 'binary'
  171.     ));
  172. })->bind('rfs_detail');
  173.  
  174. $app->mount('/api-rest/v1/', $reports);
  175.  
  176. $app->after(function (Request $request, Response $response) {
  177.     $response->headers->set('Access-Control-Allow-Origin', '*');
  178. });
  179.  
  180. $app->run();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement