gistfile1.php

<?

class StatController extends Controller
{
    public $layout = '//layouts/clear';

    public function actionIndex()
    {

        $ids = array();

        $sel2 = Yii::app()->db->createCommand()
            ->select("Id, androidAdId")
            ->from('profiles')
            ->group("Id")
            ->queryAll();

        foreach ($sel2 as $val)
            if (($val["androidAdId"] != "") and (isset($val["Id"])))
                $ids[$val["Id"]] = $val["androidAdId"];

        $sel2 = Yii::app()->db->createCommand()
            ->select("id, ad_id")
            ->from('profile')
            ->group("id")
            ->queryAll();

        $idd = array();
        foreach ($sel2 as $val)
            if ($val["ad_id"] != "")
                $idd[$val["ad_id"]] = $val["id"];

        foreach ($ids as $id => $ad) {
            if (isset($idd[$ad]))
                $ids[$id] = $idd[$ad];
            else
                unset($ids[$id]);
        }

        $chunkSize = 30000;
        for ($i = 9150000; $i < Sessions::model()->count(); $i += $chunkSize) {

            $sel = Yii::app()->db->createCommand()
                ->from('sessions')
                ->order("Id ASC")
                ->offset($i)
                ->limit($chunkSize)
                ->queryAll();

            $json = array();
            foreach ($sel as $k => $line) {
                $json[$k] = $line;

                if (array_key_exists($json[$k]["profileId"], $ids)) {
                    unset($json[$k]["Id"]);
                    $json[$k]["timeStamp"] = strtotime($json[$k]["timeStamp"]) . "0000";
                    $json[$k]["profileId"] = $ids[$json[$k]["profileId"]];
                    $json[$k]["packageName"] = $json[$k]["package"];
                } else {
                    unset($json[$k]);
                    continue;
                }
            }

            echo count($json);
            $this->actionSession($json);
            echo "\nChunk [{$chunkSize}]";
        }

        echo "\n\n--" . $i . "\n\n";

        return;

        $test = Session::model()->findAll(array("limit" => 1, "order" => "id DESC"));
        $test2 = App::model()->findAll(array("condition" => "id = '" . $test[0]->app . "'", "limit" => 1, "order" => "id DESC"));
        $test = Sessions::model()->findAll(array("condition" => "installed = '" . $test[0]->installed . "' AND duration = '" . $test[0]->duration . "' AND package = '" . $test2[0]->name . "'", "order" => "id ASC", "limit" => 1));
        $test = Sessions::model()->findAll(array("condition" => "Id > '" . ($test[0]->Id - 1) . "'", "order" => "id ASC", "limit" => 70000));

        echo "[START] \n";
        $json = array();

        foreach ($test as $k => $line) {
            //$line->delete(); continue;
            $json[$k] = $line->attributes;
            unset($json[$k]["Id"]);
            $json[$k]["timeStamp"] = strtotime($json[$k]["timeStamp"]) . "0000";
        }

        $this->actionSession($json);

        return;
        $chunkSize = 10000;
        for ($i = 0; $i < ChartAu::model()->count(); $i += $chunkSize) {
            $sel = Yii::app()->db->createCommand()
                //  ->select("profile, cnt, date")
                ->from('profiles')
                ->limit($chunkSize)
                ->order("Id ASC")
                ->offset($i)
                ->queryAll();

            foreach ($sel as $json) {
                unset($json["ip"]);
                unset($json["Id"]);
                unset($json["deviceName"]);
                $this->actionProfile($json, 0);
                //    echo " ";
            }
            echo "\n\nchunk\n\n";
        }
    }

    public function actionSession($json)
    {
        $resp = 1;
        if (!isset($json))
            $json = json_decode(file_get_contents('php://input'), true);

        if (!count($json)) {
            echo "JSON ERROR";
            return;
        }

        foreach ($json as $sess) {
            $session = new Session();

            if (!isset($sess["packageName"])) {
                echo "JSON ERROR";
                continue;
            }

            # App
            $session->app = 0;
            try {
                $test = App::model()->findAll(array("condition" => "name = '" . htmlspecialchars((string)str_replace("'", "", $sess["packageName"])) . "'", "limit" => 1));
                if (count($test)) {
                    $session->app = $test[0]->id;
                } else {
                    $test = new App();
                    $test->name = htmlspecialchars((string)str_replace("'", "", $sess["packageName"]));
                    $test->dev = 1;
                    $test->save();
                    $session->app = $test->id;
                }
            } catch (Exception $ex) {
                //ignore
            }

            if (!intval($session->app))
                continue;

            # Time
            $session->time = intval(substr($sess['timeStamp'], 0, 10));
            $tDelta = time() - $session->time;
            if ($tDelta > 0) {
                if ($tDelta > 1728000)
                    $session->time = time();
            } else {
                if (-$tDelta > 43200)
                    $session->time = time();
            }

            # Other
            $session->profile = intval($sess["profileId"]);
            $session->installed = intval($sess["installed"]);
            $session->duration = intval($sess["duration"]);

            if (!$session->save()) {
                $resp = 0;
                echo "\nInvalid query: " . print_r($session->getErrors(), true);
            }
        }
        echo $resp;
    }

    public function actionProfile()
    {
        $json = json_decode(file_get_contents('php://input'), true);

        if ((!isset($json["androidAdId"])) or ($json["androidAdId"] == "")) {
            echo "JSON ERROR";
            return;
        }

        $test = Profile::model()->findAll(array("condition" => "ad_id = '" . str_replace("'", "", $json["androidAdId"]) . "'", "limit" => 1));
        if (isset($test[0])) {
            echo $test[0]->id;
            return;
        }

        $profile = new Profile();

        # Manufacturer
        $test = Manufacturer::model()->findAll(array("condition" => "name = '" . htmlspecialchars((string)$json["manufacturer"]) . "'", "limit" => 1));
        if (count($test)) {
            $man = $test[0]->id;
        } else {
            $test = new Manufacturer();
            $test->name = htmlspecialchars((string)$json["manufacturer"]);
            $test->save();
            $man = $test->id;
        }

        # Device (model)
        $test = Device::model()->findAll(array("condition" => "name = '" . htmlspecialchars((string)$json["model"]) . "' AND manufacturer = '" . $man . "'", "limit" => 1));
        if (count($test)) {
            $profile->device = $test[0]->id;
        } else {
            $test = new Device();
            $test->name = htmlspecialchars((string)$json["model"]);
            $test->manufacturer = $man;
            $test->save();
            $profile->device = $test->id;
        }
        unset($man);

        # Dev
        $test = Dev::model()->findAll(array("condition" => "name = '" . htmlspecialchars((string)$json["developerId"]) . "'", "limit" => 1));
        if (count($test)) {
            $dev = $test[0]->id;
        } else {
            $test = new Dev();
            $test->name = htmlspecialchars((string)$json["developerId"]);
            $test->save();
            $dev = $test->id;
        }

        # App
        $test = App::model()->findAll(array("condition" => "name = '" . htmlspecialchars((string)$json["appInit"]) . "' AND dev = '" . $dev . "'", "limit" => 1));
        if (count($test)) {
            $profile->app = $test[0]->id;
        } else {
            $test = new App();
            $test->name = htmlspecialchars((string)$json["appInit"]);
            $test->dev = $dev;
            $test->save();
            $profile->app = $test->id;
        }
        unset($dev);

        # IP
        if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
            $profile->ip = $_SERVER['HTTP_CLIENT_IP'];
        } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
            $profile->ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
        } else {
            if (!empty($_SERVER['REMOTE_ADDR']))
                $profile->ip = $_SERVER['REMOTE_ADDR'];
        }
        $profile->ip = sprintf('%u', ip2long($profile->ip));

        # Other
        $profile->android_id = $json["androidId"];
        $profile->ad_id = str_replace("'", "", $json["androidAdId"]);
        $profile->gplus = $json["gplus"];
        $profile->fb = $json["facebook"];
        $profile->api = intval($json["api"]);

        if ($profile->save())
            echo $profile->id;
        else
            echo 'Invalid query: ' . print_r($profile->getErrors(), true);
    }
}