FloatBackOfffice/CRONS/update_surge_price_data.php

<?php

  echo "[".date("Y-m-d H:i:s")."] update_surge_price_data job is starting.\n";

  require('./lock.php');
  $lock_file = lock_pid_file();

  require('../backend.php');

  $db_host = $savvyext->cfgReadChar('database.host');
  $db_name = $savvyext->cfgReadChar('database.name');
  $db_user = $savvyext->cfgReadChar('database.user');
  $db_pass = $savvyext->cfgReadChar('database.pass');
  $db_port = $savvyext->cfgReadLong('database.port');
  $connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
  $con = pg_connect($connstr);

  $db_host = $savvyext->cfgReadChar('database_replica.host');
  $db_name = $savvyext->cfgReadChar('database_replica.name');
  $db_user = $savvyext->cfgReadChar('database_replica.user');
  $db_pass = $savvyext->cfgReadChar('database_replica.pass');
  $db_port = $savvyext->cfgReadLong('database_replica.port');
  $readOnlyReplicaConnstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
  $readOnlyReplicaConn = pg_connect($readOnlyReplicaConnstr);

  if ($con===FALSE || $readOnlyReplicaConn===FALSE) {
    unlock_pid_file($lock_file);
    die ("Could not connect to server\n");
  }
  $radius = 100;
  $rate = 1.1;
  $numberOfDays = 365;

  // update surge price data for trip (parsedemail_item)
  $query = "
    SELECT
      cpvt.data_source_id,
      cpvt.data_source,
      cpvt.cost,
      a1.latitude AS location_start_lat,
      a1.longitude AS location_start_lng,
      a2.latitude AS location_end_lat,
      a2.longitude AS location_end_lng
    FROM
      (
        SELECT
          *
        FROM
          union_trip_and_quote_view_table
        WHERE
        (
          data_source = 1
          AND data_source_id >
          (
            SELECT
              CASE WHEN max(tpc.data_source_id) IS NULL THEN
                0
              ELSE
                max(tpc.data_source_id)
              END
            FROM
              trip_surge_price AS tpc
            WHERE
              tpc.data_source = 1
          )
        )
        OR
        (
          data_source = 2
          AND data_source_id >
          (
            SELECT
              CASE WHEN max(tpc.data_source_id) IS NULL THEN
                0
              ELSE
                max(tpc.data_source_id)
              END FROM trip_surge_price AS tpc
            WHERE
              tpc.data_source = 2
          )
        )
      ) AS cpvt
    LEFT JOIN address a1 ON cpvt.location_start_id = a1.id
    LEFT JOIN address a2 ON cpvt.location_end_id = a2.id
  ";

  $rs = pg_query($readOnlyReplicaConn, $query);
  if ($rs===FALSE) {
    unlock_pid_file($lock_file);
    die("Cannot execute query: $query\n");
  }
  while ($row = pg_fetch_assoc($rs)) {
    $root_id = $row['data_source_id'];
    $root_type = $row['data_source'];

    $location_start_lng = $row['location_start_lng'];
    $location_start_lat = $row['location_start_lat'];
    $location_end_lng = $row['location_end_lng'];
    $location_end_lat = $row['location_end_lat'];

    $cost = $row['cost'];

    $check_surge_query = "
      SELECT
        avg(result.cost) AS avg_cost
      FROM
      (
        SELECT
          cost
        FROM
          union_trip_and_quote_view_table AS utaqvt
          LEFT JOIN address a1 ON utaqvt.location_start_id = a1.id
          LEFT JOIN address a2 ON utaqvt.location_end_id = a2.id
        WHERE
          utaqvt.travel_date BETWEEN NOW() - INTERVAL '" . $numberOfDays . " days' AND NOW()
          AND ST_DWithin (a1.geometry, ST_MakePoint ('".pg_escape_string($location_start_lng)."', '".pg_escape_string($location_start_lat)."')::geography, '".$radius."')
          AND ST_DWithin (a2.geometry, ST_MakePoint ('".pg_escape_string($location_end_lng)."', '".pg_escape_string($location_end_lat)."')::geography, '".$radius."')
      ) AS result
    ";

    $rs_exists = pg_query($readOnlyReplicaConn, $check_surge_query);
    while ($data = pg_fetch_object($rs_exists)) {
      if((float)$cost > ((float)$data->avg_cost * $rate)) {
        $executeQuery = "
          INSERT INTO trip_surge_price (data_source_id, data_source)
          VALUES('".pg_escape_string($root_id)."', '".pg_escape_string($root_type)."')
        ";
        $result = pg_query($con, $executeQuery);
        echo "[".date("Y-m-d H:i:s")."] Update surge option data for trip record: " . $root_id;
        echo "\n";
      }
    }
  }

  pg_close($con);
  pg_close($readOnlyReplicaConn);

  echo "[".date("Y-m-d H:i:s")."] update_surge_price_data job complete.\n";

  unlock_pid_file($lock_file);

?>