<?php
/**
 * Created by PhpStorm.
 * User: Jure
 * Date: 8.4.2014
 * Time: 17:36
 */
//include_once("_dirinfo.php");
//include_once("../../init.php");
//include_once("../../config.php");
//include("../../_support/class.db.php");
//include_once ("../../init.php");
require_once '../AutoLoader.php';
include_once ("../../_ajaxManipulators/_class/class.treeData.php");
include_once ("../../_ajaxManipulators/_class/class.gridData.php");
include_once("../../config.php");


class NfieldSurveyManager  extends TreeDataManipulator {

    public $rmplusProjectId;
    public $nfieldSurveyId;
    /*public function  __construct($db){
        $this->db = $db;
        $this->$rmplusProjectId = 'projectid';
        $this->$nfieldSurveyId = 'nfieldId';

    }*/
    function createSamplePointsByProjectAction(){

        $projectId = $_POST['projectId'];//'5736';
        $nfieldSurveyId =  $_POST['nfieldSurveyId'];//'8c58c100-47fd-4c35-8134-eedd5d5d2b04';
        $nfieldSurveyName = $_POST['nfieldSurveyName'];
        $capiTableName = 'nfield_'.$_POST['projectName'];//'capiTableJuretest';

        $sql = [];
        $surveyService = new NfieldSurveysService();
        try{
            //TO DO First we must check id tables if exists
            //Get NField sample point by project

            //Get SamplePoints by Project $_POST['nfieldSurveyId'];'5c221ae3-52d3-4d04-b2c6-3bbfc5311b51'
            $start_time = time();
            while(true) {
                try{
                    if ((time() - $start_time) > 30) {
                        break; // timeout, function took longer than 30 seconds
                    }
                        $samplePointsByProjectData = $surveyService->samplingPointsQuery($nfieldSurveyId);

                    $samplePointsDecoded = json_decode($samplePointsByProjectData, true);
                    if (isset($samplePointsDecoded[0]['SamplingPointId'])) {
                        break;
                    }

                    sleep(5);
                }
                catch(Exception $e){

                }
            }
            if (!isset($samplePointsDecoded[0]['SamplingPointId'])) {
                return "{success:false,error:'SamplingPointsId is undefined'}";
            }

            //$samplePointsByProjectData = $surveyService->samplingPointsQuery($nfieldSurveyId);

            $capi_table_insert_sql = $this->createCapiTablesInsertSql('_CapiTables',$capiTableName);
            //Tial gre vhod ime capiTableName
            $sample_points_create_sql = $this->createSamplePointsTable($capiTableName);

            $prepareSamplePointsData = $this->getPrepareSamplePointsDataToInsert($samplePointsByProjectData);
            $sample_points_insert_sql = $this->createSamplePointsInsertSql($prepareSamplePointsData,'capi___'.$capiTableName);

            $sql[] =$capi_table_insert_sql;
            $sql[] = $sample_points_create_sql;
            $sql[] = $sample_points_insert_sql;
            // Create and insert sample points

            foreach ($sql as $sqlStatement) {

                if(is_array($sqlStatement)){
                    foreach($sqlStatement as $statement){
                        If (!$this->db->SQLexecute($statement)) {

                            return "{success:false,error:'Adding SQL error'}";

                        }
                    }
                }
                else{
                    If (!$this->db->SQLexecute($sqlStatement)) {


                        return "{success:false,error:'Adding SQL error'}";

                    }
                }
            }


            //Update project id_capiTable attribute
            $sql = "SELECT c.id as id FROM _CapiTables as c WHERE c.name = '$capiTableName' ";
            $result = $this->db->SQLexecute($sql);


            if (!$rs = $this->db->SQLexecute($sql)) {
                $this->db->SQLrollback();
                return "{success:false,error:'Get Capi tables id'}";

            }
            $capiTableId = $this->db->fetchAssoc($result);

            $updateProject = "UPDATE _Projects SET id_capiTable = ".$capiTableId['id'].",is_nfield = 1,name_nfield_project = '$nfieldSurveyName',id_nfield_project = '$nfieldSurveyId' WHERE id = '$projectId'";
            if (!$this->db->SQLexecute($updateProject)) {

                return "{success:false,error:'Error in update projects'}";

            }

            //NON TESTING

            $sqlGetTitle = "SELECT c.proj_name as title FROM _Projects as c WHERE c.id = '$projectId' ";
            $result = $this->db->SQLexecute($sqlGetTitle);
            if (!$result) {
                return "{success:false,error:'Error in get title of projects'}";

            }
            $projectTableTitle = $this->db->fetchAssoc($result);

            $projectSamplePointsCreateSql = $this->createProjectSamplePointsTable($projectTableTitle['title'].'___sample');

             $tablePreffix = 'capi___';
            //Pripravimo inserte za tabelo projektov in vzorčnih točk
            $sqlSamplePoints = "SELECT s.id as id_sample FROM $tablePreffix$capiTableName as s";
            $result = $this->db->SQLexecute($sqlSamplePoints);
            if (!$result) {
                return "{success:false,error:'Error in get capi table'}";
            }
            $samplePointsIds = [];
            while($rowSamplePoints = $this->db->fetchAssoc($result))
            {
                $samplePointsIds[] = $rowSamplePoints;
            }


            $projectSamplePointsInsertSql = $this->createProjectSamplePointsInsertSql($capiTableId['id'],$samplePointsIds,$projectTableTitle['title'].'___sample');
            $sqlProjectSamplePoints[] =  $projectSamplePointsCreateSql;
            $sqlProjectSamplePoints[] = $projectSamplePointsInsertSql;


            foreach ($sqlProjectSamplePoints as $sqlStatement) {

                if(is_array($sqlStatement)){
                    foreach($sqlStatement as $statement){
                        If (!$this->db->SQLexecute($statement)) {

                            return "{success:false,error:'Error adding sql'}";

                        }
                    }
                }
                else{
                    If (!$this->db->SQLexecute($sqlStatement)) {
                        return "{success:false,error:'Error in get capi table'}";

                    }
                }
            }

            return "{success:true}";
        }
        catch(Exception $e){
            return "{success:false,error:'Exception error ".mysql_real_escape_string($e)."'}";

        }
    }

    private function createProjectSamplePointsInsertSql($capiTableId,$data,$table){

        $dataSamplePoints = [];
        $tdisable = 0;
        foreach($data as $d){

            $dataSamplePoints[] = " INSERT INTO `".$table."` (tdisable,id_sample,id_sampleTable,user_id,user_type,planner_visit_time,num_interviews) VALUE( " .$tdisable.','.$d['id_sample'].','.$capiTableId.','.$tdisable.','.$tdisable.','.$tdisable.','.$tdisable. ")";
        }


        return $dataSamplePoints;
    }


    private function createSamplePointsInsertSql($data,$table){
        $dataSamplePoints = [];

        foreach($data as $d){
            //var_dump($d['settlement']);exit;
            $dataSamplePoints[] = " INSERT INTO `".$table."`(id_municipality,municipality,
                        id_settlement,settlement,id_street,street,address,addon_address,zip,town,name,
                        x_coordinate,y_coordinate,id_region,population,type_settlement,id_import,time_import,proj_reserved,tdisable)
                      VALUE(
               ".$d['id_municipality'].',
              "'.$d['municipality'].'",
              '.intval($d['id_settlement']).',
              "'.$d['settlement'].'",
              '.$d['id_street'].',
              "'.$d['street'].'",
              '.intval($d['address']).',
              "'.$d['addon_address'].'",
              '.$d['zip'].',
              "'.$d['town'].'",
              "'.$d['name'].'",
              '.floatval($d['x_coordinate']).',
              '.floatval($d['y_coordinate']).',
              '.intval($d['id_region']).',
              '.$d['population'].',
              '.$d['type_settlement'].',
              '.$d['id_import'].',
              '.$d['time_import'].',
              '.$d['proj_reserved'].',
              '.$d['tdisable']. ")";
        }
        return $dataSamplePoints;
    }
    private function createCapiTablesInsertSql($tableName,$name){
        $capi_table = "INSERT INTO `".$tableName."`(id_sup,orderBy,name,is_leaf) VALUES( 0 ,1,'$name',true ) ";
        return $capi_table;
    }
    //$projectTitle___sample
    private function createProjectSamplePointsTable($projectTitle){

        $samplePointsTable = "

              CREATE TABLE  IF NOT EXISTS `$projectTitle` (
			  `id` int(7) NOT NULL AUTO_INCREMENT,
			  `tdisable` tinyint(4) NOT NULL DEFAULT '0',
			  `id_sample` int(11) NOT NULL,
			  `id_sampleTable` int(11) NOT NULL,
			  `user_id` int(11) NOT NULL,
			  `user_type` tinyint(4) NOT NULL,
			  `planner_visit_time` int(11) NOT NULL,
			  `num_interviews` smallint(6) NOT NULL,
			  PRIMARY KEY (`id`),
			  KEY (`id_sample`),
			  KEY (`id_sampleTable`),
			  KEY (`user_id`),
			  KEY (`user_type`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        return $samplePointsTable;

    }

    private function createSamplePointsTable($nameTable){

        $samplePointsTable = "

              CREATE TABLE  IF NOT EXISTS `capi___$nameTable` (
			  `id` int(7) NOT NULL AUTO_INCREMENT,
			  `id_municipality` int(3) NOT NULL DEFAULT '0',
			  `municipality` varchar(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `id_settlement` int(4) NOT NULL DEFAULT '0',
			  `settlement` varchar(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `id_street` int(4) NOT NULL DEFAULT '0',
			  `street` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `address` int(4) NOT NULL DEFAULT '0',
			  `addon_address` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `zip` int(4) NOT NULL DEFAULT '0',
			  `town` varchar(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
			  `x_coordinate` int(9) NOT NULL DEFAULT '0',
			  `y_coordinate` int(9) NOT NULL DEFAULT '0',
			  `id_region` int(3) NOT NULL DEFAULT '0',
			  `population` int(11) NOT NULL DEFAULT '0',
			  `type_settlement` int(1) NOT NULL DEFAULT '0',
			  `id_import` int(11) default '0',
			  `time_import` int(11) NOT NULL DEFAULT '0',
			  `proj_reserved` int(5) NOT NULL DEFAULT '0',
			  `tdisable` tinyint(1) NOT NULL DEFAULT '0',
			  PRIMARY KEY (`id`),
			  KEY (`id_municipality`),
			  KEY (`id_settlement`),
			  KEY (`id_street`),
			  KEY (`address`),
			  KEY (`zip`),
			  KEY (`x_coordinate`),
			  KEY (`y_coordinate`),
			  KEY (`id_region`),
			  KEY (`population`),
			  KEY (`type_settlement`),
			  KEY (`id_import`),
			  KEY (`proj_reserved`),
			  KEY (`tdisable`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";

        return $samplePointsTable;
    }
    private function getPrepareSamplePointsDataToInsert($data){
        $sample_points = json_decode($data,true);
        $arrays = [];

        foreach($sample_points as $sample_point)
        {

            $gps = $this->getGpsCoordinate($sample_point);

            $address = $this->getStreet($sample_point);
            $town = $this->getTown($sample_point);
            $region = $this->getRegion($sample_point);

            $parseSamplePoint = array(
                'street'=>$sample_point['Name'],
                'x_coordinate'=>$gps[0] = ($gps[0]=='NULL'?0:$gps[0]),
                'y_coordinate'=>$gps[1] = ($gps[1]=='NULL'?0:$gps[1]),
                'town'=>$town = ($town == 'NULL'?'/':$town),
                'id_region'=>$region = ($region== 'NULL'?0:$region),
                'addon_address'=>$address = ($address == 'NULL'?'/':$address),
                'id_municipality'=>1,
                'municipality'=>'/',
                'id_settlement'=>0,
                'settlement'=>'/',
                'id_street'=>0,
                'address'=>0,
                'zip'=>0,
                'name'=>$sample_point['Name'] = ($sample_point['Name'] == 'NULL' ? '/' :$sample_point['Name']),
                'population'=>0,
                'type_settlement'=>0,
                'id_import'=>0,
                'time_import'=>0,
                'proj_reserved'=>0,
                'tdisable'=>0

            );
            $arrays[] = $parseSamplePoint;
        }
        return $arrays;
    }

    private function getGpsCoordinate($vars){

        $gpsText = explode(';',$vars['Description']);

        $gps = explode(',',$gpsText[2]);

        $gps[0] = substr($gps[0],6,strlen($gps[0]));

        return $gps;
    }

    private function getStreet($vars){
        $explode = explode(";",$vars['Description']);
        $street = explode(':',$explode[1]);
        $street = explode(',',$street[1]);

        return $street[1];
    }

    private function getTown($vars){
        $explode = explode(";",$vars['Description']);
        $town = explode(':',$explode[1]);
        $town = explode(',',$town[1]);

        return trim($town[1]);
    }

    private function getRegion($vars){
        $explode = explode(";",$vars['Description']);
        $town = explode(':',$explode[1]);
        $town = explode(',',$town[1]);
        return trim($town[1]);
    }

}
$autoloader = new Autoloader();
$action = 'createSamplePointsByProjectAction';

$db->ChangeDatabase(DB_WARPIT_WEBCATI_BASE);
$comm = $_POST['action'].'Action';
$manipulator = new NfieldSurveyManager($db,$tableName);
if(method_exists($manipulator,$comm))
    echo $manipulator->$comm();
else
{
    echo '{success:false,error:"Unknown action!"}';
}