<?php



class ApiController extends AppController {

    public $components = array('RequestHandler', 'Paginator');
    public $DATA = array();
    public $apiKey =  "098f6bcd4621d373cade4e832627b4f6";
    public $requestedData;
    public $limit = 50;

    public function beforeFilter() {

        $this->Auth->allow();

        $this->requestedData = $this->request->query;

        $this->requestedData = $this->request->data;

        // if(isset($this->requestedData['api_key'])) {

        //     if($this->requestedData['api_key'] != $this->apiKey) {

        //       $this->DATA['error'] = "Authentication failed! Please contact to administrator.";

        //       echo json_encode($this->DATA);

        //       exit;

        //     }

        // } else {

        //      $this->DATA['error'] = "Authentication failed! Please contact to administrator.";

        //      echo json_encode($this->DATA);

        //      exit;

        // }
    }

    function check_login() {
        if(!isset($this->requestedData['username'])){
            $this->DATA["error"] = "username is not given";
        } else if(!isset($this->requestedData['password'])){
            $this->DATA["error"] = "password is not given";
        } else {
            $username = $this->requestedData['username'];
            $password = $this->requestedData['password'];

            $this->Mssql->connect();
            if($this->Mssql->conn){
                $sql = "SELECT * FROM Runner WHERE Runner = '$username' AND pass = '$password'";
                $stmt = sqlsrv_query( $this->Mssql->conn, $sql);
                if($stmt){
                    $params = array();
                    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
                    $stmt = sqlsrv_query( $this->Mssql->conn, $sql , $params, $options );
                    $row_count = sqlsrv_num_rows($stmt);

                    if($row_count !=1) {
                      $this->DATA["error"] = "Wrong username or password!";
                    } else {
                        $result = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC);
                        $this->DATA["success"] = "Logged in successfully!";
                        $this->DATA['data']['runner_id'] = $result['Runner'];

                        sqlsrv_begin_transaction ($this->Mssql->conn);
                        $sql = "UPDATE Runner SET online = '1' WHERE Runner = '$result[Runner]'";
                        $stmt = sqlsrv_prepare( $this->Mssql->conn, $sql);
                        if($stmt) {
                            if(sqlsrv_execute( $stmt )) {

                                sqlsrv_commit ($this->Mssql->conn );
                                $this->DATA['success'] = "Runner status has been saved successfully.";
                            } else {
                                $this->DATA["error"] = "error on query!";
                            }
                        } else {
                            $this->DATA["error"] = "error on query!";
                        }

                        $this->loadModel('Status');
                        $things = $this->Status->find('all');
                        $this->DATA['Status'] = Set::extract('/Status/.', $things);

                        $this->loadModel('IssueType');
                        $things = $this->IssueType->find('all');
                        $this->DATA['IssueType'] = Set::extract('/IssueType/.', $things);
                   }
                } else {
                    $this->DATA["error"] = "error on query!";
                }
            } else {
                $this->DATA["error"] = "Connection error!";
            }
        }

        $this->set(array(
           'result' => $this->DATA,
           '_serialize' => 'result'
        ));
    }


    function saveGcmId(){
        $this->loadModel('GcmDevice');
        $gcmId = $this->requestedData['gcm_id'];
        $runnerId = $this->requestedData['runner_id'];

        $data['GcmDevice']['gcm_id'] = $gcmId;
        $data['GcmDevice']['runner_id'] = $runnerId;

        $deviceData = $this->GcmDevice->findByRunnerId($runnerId);

        if($deviceData){
            $this->GcmDevice->id = $deviceData['GcmDevice']['id'];
        }

        if($this->GcmDevice->save($data)){
            $this->DATA['success'] = "GCM ID has been saved successfully.";
        } else {
            $this->DATA['error'] = "We're facing problem, while saving GCM ID.";
        }

        $this->set(
            array(
                'result' => $this->DATA,
                '_serialize' => 'result'
            )
        );
    }

    function saveRunnerLocation(){
        $this->loadModel('RunnerLocation');

        $data['RunnerLocation']['runner_id'] = $this->requestedData['runner_id'];
        $data['RunnerLocation']['latitude'] = $this->requestedData['latitude'];
        $data['RunnerLocation']['longitude'] = $this->requestedData['longitude'];

        if($this->RunnerLocation->save($data)){
            $this->DATA['success'] = "Runner location has been saved successfully.";
        } else {
            $this->DATA['error'] = "We're facing problem, while saving runner location.";
        }

        $this->set(
            array(
                'result' => $this->DATA,
                '_serialize' => 'result'
            )
        );
    }

    function saveRunnerOffline(){
        $runnerId = $this->requestedData['runner_id'];
        $this->Mssql->connect();
        if($this->Mssql->conn){
            sqlsrv_begin_transaction ($this->Mssql->conn);
            $sql = "UPDATE Runner SET online = '0' WHERE Runner = '$runnerId'";
            $stmt = sqlsrv_prepare( $this->Mssql->conn, $sql);
            if($stmt) {
                if(sqlsrv_execute( $stmt )) {
                    echo "Updated";
                    sqlsrv_commit ($this->Mssql->conn );
                    $this->DATA['success'] = "Runner status has been saved successfully.";
                } else {
                    $this->DATA["error"] = "error on query!";
                }
            } else {
                $this->DATA["error"] = "error on query!";
            }

        } else {
            $this->DATA["error"] = "Connection error!";
        }


        $this->set(
            array(
                'result' => $this->DATA,
                '_serialize' => 'result'
            )
        );
    }

    function getRunnerMessages(){
        $this->loadModel('RunnerMessage');
        $conditions['RunnerMessage.runner_id'] = $this->requestedData['runner_id'];
        $conditions['DATE(RunnerMessage.created)'] = date('Y-m-d');

        $this->Paginator->settings = array(
          'limit'=>100,
          'fields'=>array('*'),
          'order'=>'RunnerMessage.id DESC',
          'conditions'=>$conditions
        );

        $things = $this->Paginator->paginate('RunnerMessage');
        $this->DATA['data']['messages'] = Set::extract('/RunnerMessage/.', $things);
        $this->set(array(
           'result' => $this->DATA,
           '_serialize' => 'result'
        ));
    }

    function getDistributions(){
        $this->Mssql->connect();
        $runner_id = $this->requestedData['runner_id'];

        $sql = "SELECT TOP 10 * FROM Tracking as Tracking, Main as Main, Phones as Phones, Issue as Issue, Status as Status WHERE Status.Status_code = Tracking.ST_code AND Issue.ISSUE = Tracking.ISSUE AND Tracking.ZIP5 = Main.ZIP5 AND Tracking.ZIP5 = Phones.Zip5 AND Tracking.RUNNER = '$runner_id' ";

        if(isset($this->requestedData['search_status'])){
            $search_status = $this->requestedData['search_status'];
            $sql.= " AND Tracking.ST_code = '$search_status' ";
        }

        if(isset($this->requestedData['search_customer_code'])){
            $search_customer_code = '%'.$this->requestedData['search_customer_code'].'%';
            $sql.= " AND Tracking.ZIP5 LIKE '$search_customer_code' ";
        }

        if(isset($this->requestedData['search_customer_name'])){
            $search_customer_name = '%'.$this->requestedData['search_customer_name'].'%';
            $sql.= " AND Main.NAME LIKE '$search_customer_name' ";
        }

        if(isset($this->requestedData['search_job_date'])){
            $search_job_date = date('Y/m/d', strtotime($this->requestedData['search_job_date']));
        } else {
            $search_job_date = date('Y/m/d');
        }

        //$sql.= " AND Tracking.ST_DATE = '$search_job_date' ";

        $results = array();
        if($this->Mssql->conn){
            $stmt = sqlsrv_query( $this->Mssql->conn, $sql);

            if($stmt){

                while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
                  $result['RunnerJob']['id'] = $row['Sfworx'];
                  $result['RunnerJob']['customer_code'] = $row['ZIP5'];
                  $result['RunnerJob']['customer_name'] = $row['NAME'];
                  $result['RunnerJob']['issue_type'] = $row['ISSUE'];
                  $result['RunnerJob']['address'] = $row['ADDRESS'];
                  $result['RunnerJob']['phone'] = $row['Tel'];
                  $result['RunnerJob']['barcode'] = "";
                  $result['RunnerJob']['job_date'] = $row['ST_DATE'];
                  $result['RunnerJob']['runner_id'] = $row['RUNNER'];
                  $result['RunnerJob']['latitude'] = $row['latitude'];
                  $result['RunnerJob']['longitude'] = $row['longitude'];
                  $result['RunnerJob']['status_code'] = $row['ST_code'];
                  $result['RunnerJob']['signature'] = $row['COMMENT'];
                  $result['RunnerJob']['custom_image'] = $row['Custom_image'];

                  $result['IssueType']['id'] = $row['ISSUE'];
                  $result['IssueType']['issue_name'] = $row['ISSUE_TXT'];

                  $result['Status']['id'] = $row['ISSUE_TXT'];
                  $result['Status']['status'] = $row['Result'];
                  $result['Status']['status_code'] = $row['ST_code'];
                  $result['Status']['result'] = $row['Status'];
                  $result['Status']['result_code'] = $row['Result_code'];

                  $results[] = $result;
                }
            } else {
                $this->DATA["error"] = "error on query!";
            }
        } else {
            $this->DATA["error"] = "Connection error!";
        }

        $this->DATA['data']['jobs'] = $results;
        $this->set(array(
           'result' => $this->DATA,
           '_serialize' => 'result'
        ));
    }

    function getReports(){
        $this->Mssql->connect();
        $this->loadModel('RunnerJob');
        $this->loadModel('Status');
        $runner_id = $this->requestedData['runner_id'];

        $date = date('Y/m/d');

        if(isset($this->requestedData['issue_type']) && $this->requestedData['issue_type']!=''){
            $conditions['RunnerJob.issue_type'] = $this->requestedData['issue_type'];
            $issue = " AND ISSUE =  '".$this->requestedData['issue_type']."' ";
        } else {
            $issue = " AND ISSUE != '' ";
        }

        if($this->Mssql->conn){
            $sql = "SELECT COUNT(*) as count FROM Tracking_Android WHERE Runner = '$runner_id' AND ST_DATE = '$date' $issue AND Result LIKE '%UnDelivered%'";
            $stmt = sqlsrv_query( $this->Mssql->conn, $sql);
            if($stmt){
                $result = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC);
                $this->DATA['data']['undelivered'] = $result['count'];
            } else {
                $this->DATA["error"] = "error on query!";
            }

            $sql = "SELECT COUNT(*) as count FROM Tracking_Android WHERE Runner = '$runner_id' AND ST_DATE = '$date' $issue AND Result LIKE '%Delivered%'";
            $stmt = sqlsrv_query( $this->Mssql->conn, $sql);
            if($stmt){
                $result = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC);
                $this->DATA['data']['delivered'] = $result['count'];

            } else {
                $this->DATA["error"] = "error on query!";
            }

            $sql = "SELECT COUNT(*) as count FROM Tracking_Android WHERE Runner = '$runner_id' AND ST_DATE = '$date' $issue AND Result LIKE '%In Progress%'";
            $stmt = sqlsrv_query( $this->Mssql->conn, $sql);
            if($stmt){
                $result = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC);
                $this->DATA['data']['in_progress'] = $result['count'];

            } else {
                $this->DATA["error"] = "error on query!";
            }
        } else {
            $this->DATA["error"] = "Connection error!";
        }

        $this->set(array(
           'result' => $this->DATA,
           '_serialize' => 'result'
        ));
    }

    function saveStatusUsingCron(){
        $this->Mssql->connect();
        if($this->Mssql->conn){
            $sql = "SELECT * FROM Status";
            $stmt = sqlsrv_query( $this->Mssql->conn, $sql);
            if($stmt){
                $this->loadModel('Status');
                $this->Status->query('TRUNCATE TABLE uc_statuses;');
                $result = array();
                while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
                    $result['Status']['status'] = $row['Status'];
                    $result['Status']['status_code'] = $row['Status_code'];
                    $result['Status']['result'] = $row['Result'];
                    $result['Status']['result_code'] = $row['Result_code'];

                    $result[] = $result;
                }

                if($this->Status->saveMany($result)){
                    $this->DATA['success'] = "Staus table updated using CRON JOB.";
                } else {
                    $this->DATA['error'] = "Staus table failed updation using CRON JOB.";
                }
            } else {
                $this->DATA["error"] = "error on query!";
            }
        } else {
            $this->DATA["error"] = "Connection error!";
        }

        $this->set(
            array(
                'result' => $this->DATA,
                '_serialize' => 'result'
            )
        );
    }

    function saveIssueUsingCron(){
        $this->Mssql->connect();
        if($this->Mssql->conn){
            $sql = "SELECT * FROM Issue";
            $stmt = sqlsrv_query( $this->Mssql->conn, $sql);
            if($stmt){
                $this->loadModel('IssueType');
                $this->IssueType->query('TRUNCATE TABLE uc_issue_types;');
                $result = array();
                while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
                    $result['IssueType']['issue_name'] = trim($row['ISSUE_TXT']);
                    $result[] = $result;
                }

                if($this->IssueType->saveMany($result)){
                    $this->DATA['success'] = "Issue Type table updated using CRON JOB.";
                } else {
                    $this->DATA['error'] = "Issue Type table failed updation using CRON JOB.";
                }
            } else {
                $this->DATA["error"] = "error on query!";
            }
        } else {
            $this->DATA["error"] = "Connection error!";
        }

        $this->set(
            array(
                'result' => $this->DATA,
                '_serialize' => 'result'
            )
        );
    }

    function saveDistributionReport(){
        $this->loadModel('RunnerJob');
        $this->Mssql->connect();

        if(!isset($this->requestedData['job_id']) || $this->requestedData['job_id']==''){
            $this->DATA["error"] = "Job ID is not provided or empty.";
        } else if(!isset($this->requestedData['latitude']) || $this->requestedData['latitude']==''){
            $this->DATA["error"] = "Latitude is not provided or empty.";
        } else if(!isset($this->requestedData['longitude']) || $this->requestedData['longitude']==''){
            $this->DATA["error"] = "Longitude is not provided or empty.";
        } else if(!isset($this->requestedData['status_code']) || $this->requestedData['status_code']==''){
            $this->DATA["error"] = "Status code is not provided or empty.";
        } else {

            $job_id          = $this->requestedData['job_id'];
            $latitude        = $this->requestedData['latitude'];
            $longitude       = $this->requestedData['longitude'];
            $status_code     = $this->requestedData['status_code'];
            $custom_image_name = '';
            $signature_name = '';

            if(isset($_FILES['s_image'])){
                $data = $_FILES['s_image'];
                $allowed = array('image/jpeg', 'image/JPEG', 'image/JPG', 'image/jpg','image/png', 'image/*');

                if(in_array($data['type'], $allowed)){
                    $path = "files/signatures/job-".$job_id;
                    $name = $path."/".$data['name'];

                    if(!is_dir($path)){
                        mkdir($path);
                    }

                    if(file_exists($name)){
                        unlink($name);
                    }

                    App::uses('Folder', 'Utility');
                    App::uses('File', 'Utility');

                    if(move_uploaded_file($data['tmp_name'], $name)){
                        $signature_name = $name;
                        $this->DATA['success'] = "Image has been saved successfully, Please proceed.";
                    } else {
                        $this->DATA['error'] = "We're facing problem, while saving data.";
                    }
                } else {
                    $this->DATA['error'] = "Not allowed filetype, Please retry.";
                }
            }

            if(isset($_FILES['custom_image'])){
                $custom_image_data = $_FILES['custom_image'];
                $allowed = array('image/jpeg', 'image/JPEG', 'image/JPG', 'image/jpg','image/png', 'image/*');

                if(in_array($custom_image_data['type'], $allowed)){
                    $custom_image_path = "files/custom_images/job-".$job_id;
                    $custom_image_name = $custom_image_path."/".$custom_image_data['name'];

                    if(!is_dir($custom_image_path)){
                        mkdir($custom_image_path);
                    }

                    if(file_exists($custom_image_name)){
                        unlink($custom_image_name);
                    }

                    App::uses('Folder', 'Utility');
                    App::uses('File', 'Utility');

                    if(move_uploaded_file($custom_image_data['tmp_name'], $custom_image_name)){

                        $this->DATA['success'] = "Custom image has been saved successfully, Please proceed.";
                    } else {
                        $this->DATA['error'] = "We're facing problem, while saving data.";
                    }
                } else {
                    $this->DATA['error'] = "Not allowed filetype, Please retry.";
                }
            }

            if($this->Mssql->conn){
                sqlsrv_begin_transaction ($this->Mssql->conn);
                $sql = "UPDATE Tracking SET latitude = '$latitude', longitude = '$longitude', ST_code = '$status_code', COMMENT = '$signature_name', Custom_image = '$custom_image_name' WHERE Sfworx = '$job_id' ";
                $stmt = sqlsrv_prepare( $this->Mssql->conn, $sql);
                if($stmt){
                    if(sqlsrv_execute( $stmt )) {
                        sqlsrv_commit ($this->Mssql->conn );
                        $this->DATA['success'] = "Job data has been saved successfully.";
                    } else {
                       $this->DATA["error"] = "error on query!";
                    }
                } else {
                    $this->DATA["error"] = "error on query!";
                }
            } else {
                $this->DATA["error"] = "Connection error!";
            }
        }

        $this->set(
            array(
                'result' => $this->DATA,
                '_serialize' => 'result'
            )
        );
    }
}