<?php
//Этот скрипт обслуживает рейсы назначение, список, и.т.д.
@session_start();

function sendError($msg)
{
    $json='{"errorCode":1,"errorMessage":'.json_encode($msg,JSON_UNESCAPED_UNICODE).'}';
    header('Content-Type: application/json');
    echo $json;
    exit;
}

require_once("../../monitoring/config.php");
require_once("../../resources/metadata/include/tools.php");

$db = connectToDB();
//$db->exec('SET NAMES utf8');
//$db->exec("SET time_zone = '+00:00'");

$fn=0;
if(isset($_GET['fn'])) { $fn=$_GET['fn']; }
if($fn=='0') //Вернуть список объектов в соответствии с фильтром
{
    if(!isset($HTTP_RAW_POST_DATA))
    {	$HTTP_RAW_POST_DATA = file_get_contents("php://input");
    }
    try
    {
        $object = json_decode($HTTP_RAW_POST_DATA);
    }catch (Exception $e)
    {
        $json='{"errorMessage":"'.$e->getMessage().'"}';
        header('Content-Type: application/json');
        echo $json;
        exit;
    }
    
    if(!property_exists($object,'carrier_id') or $object->carrier_id=='') $object->carrier_id='null';
    if(!property_exists($object,'company_id') or $object->company_id=='') $object->company_id='null';
    if(!property_exists($object,'status') or $object->status=='') $object->status='null';
    if(!property_exists($object,'route_id') or $object->route_id=='') $object->route_id='null';   
    
    $sql = 'SELECT
                t.id,
                tt.name as trip_type_name,
                r.name,
                c.name as company_name,
                cr.name as carrier_name,
                o.name as object_name,
                o.places as object_places,
                t.direction,
                t.date_start,
                t.date_start+(select max("time") from main.routes_checkpoints where del=false and route_id=t.route_id) as date_end,
                t.passenger,
                t.description,
                COALESCE((select count(*) from main.trips_checkpoints where del=false and "time" is not null and trip_id=t.id),0) as real_checkpoints,
                COALESCE((select count(1) from main.routes r0 join main.routes_checkpoints rc0 on r0.id=rc0.route_id where r0.del=false and rc0.del=false and r0.id=t.route_id and rc0.direction=t.direction group by r0.id,rc0.direction),0) as plan_checkpoints
            from
                main.trips t
                join main.routes r on r.id=t.route_id
                left join main.companies c on c.id=t.company_id
                left join main.companies cr on cr.id=t.company_carrier_id
                left join main.objects o on o.id=t.object_id
                left join main.trips_types tt on tt.id=t.trip_type_id  
            where
                t.del=false
                and ('.$object->route_id.' is null or '.$object->route_id.'=t.route_id)
                and ('.$object->carrier_id.' is null or '.$object->carrier_id.'=t.company_carrier_id)
                and ('.$object->company_id.' is null or '.$object->company_id.'=t.company_id)
            order by t.date_start,r.name';
    
    //select r.id,rc.direction,count(1) from main.routes r join main.routes_checkpoints rc on r.id=rc.route_id where r.del=false and rc.del=false group by r.id,rc.direction;
    
    try
    {
        $res = $db->query($sql);
    }catch (Exception $e)
    {
        header('Content-Type: application/json');
        echo "{\"error\":\"".$e->getMessage()."\"}";
        exit;
    }
    
    //Массив объектов
    $json ='[';
    //Перебираю и последовательно отправляю не отправленные пломбы
    while ($row = $res->fetch(PDO::FETCH_ASSOC))
    {
        $json .="\n";
        $json .='{';
        $json .='"id":"'.$row['id']."\",\n";
        $json .='"trip_type_name":'.json_encode($row['trip_type_name'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"name":'.json_encode($row['name'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"company_name":'.json_encode($row['company_name'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"carrier_name":'.json_encode($row['carrier_name'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"object_name":'.json_encode($row['object_name'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"object_places":'.json_encode($row['object_places'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"direction":'.json_encode($row['direction'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"date_start":'.json_encode($row['date_start'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"date_end":'.json_encode($row['date_end'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"passenger":'.json_encode($row['passenger'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"description":'.json_encode($row['description'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"real_checkpoints":'.json_encode($row['real_checkpoints'],JSON_UNESCAPED_UNICODE).",\n";
        $json .='"plan_checkpoints":'.json_encode($row['plan_checkpoints'],JSON_UNESCAPED_UNICODE)."\n";
        $json .='},';
    }
    if($json[strlen($json) - 1]==','){
        $json=substr ( $json , 0, strlen($json)-1 );
    }
    $json .=']';
    
    header('Content-Type: application/json');
    echo $json;
    exit;
}else if($fn=='1') //Удалить запись
{
    if(!isset($HTTP_RAW_POST_DATA))
    {	$HTTP_RAW_POST_DATA = file_get_contents("php://input");
    }
    $object = json_decode($HTTP_RAW_POST_DATA);
    if($object==null) $object = (object)[];
    
    if(!property_exists($object,'id') or $object->id=='') $object->id=null; //Поле заполнено если редактирвание записи
    if($object->id!=null)
    {
        try
        {
            $sql="update main.trips set del=true where id=".$object->id;
            $db->query($sql);
        }catch (Exception $e)
        {
            sendError($e->getMessage());
        }

        $json='{}';
        header('Content-Type: application/json');
        echo $json;
        exit;
    }else
    {
        $json='{"errorMessage":"ID is not set!"}';
        header('Content-Type: application/json');
        echo $json;
        exit;
    }
    
}else if($fn=='2') //HTML формочка для создания/редактирования новой записи (чтобы не заморачиваться с заполнением выпадающих полей записей через javascript)
{
    if(!isset($HTTP_RAW_POST_DATA))
    {	$HTTP_RAW_POST_DATA = file_get_contents("php://input");
    }
    $object = json_decode($HTTP_RAW_POST_DATA);
    if($object==null) $object = (object)[];
    
    if(!property_exists($object,'id') or $object->id=='') $object->id=null; //Поле заполнено если редактирвание записи
    if($object->id!=null)
    {
        $sql = 'SELECT * from main.trips t 
            where
                t.id='.$object->id;
        try
        {
            $res = $db->query($sql);
        }catch (Exception $e)
        {
            header('Content-Type: application/json');
            echo "{\"error\":\"".$e->getMessage()."\"}";
            exit;
        }
        //Переписываю значения в объект
        while ($row = $res->fetch(PDO::FETCH_ASSOC))
        {
            $object->route_id=$row['route_id'];
            $object->company_id=$row['company_id'];
            $object->carrier_id=$row['company_carrier_id']; //Как общий так и конкретный для данного маршрута            
            
            $object->trip_type_id=$row['trip_type_id'];
            $object->object_id=$row['object_id'];
            $object->direction=$row['direction'];
            //$object->date_start=$row['date_start'];
            $object->date=$row['date_start'];
            //$object->date_end=$row['date_end'];
            $object->passenger=$row['passenger'];
            $object->file_name=$row['file_name'];
            $object->description=$row['description'];
        }
    }
    if(!property_exists($object,'trip_type_id') or $object->trip_type_id=='') $object->trip_type_id=null;
    if(!property_exists($object,'direction') or $object->direction==='') $object->direction=null; //Направление
    if(!property_exists($object,'object_id')) $object->object_id=''; //Автобус
    if(!property_exists($object,'date')) $object->date=''; //Дата время начала маршрута план	
    if(!property_exists($object,'passenger')) $object->passenger=''; //Пассажиров
    if(!property_exists($object,'route_id')) $object->route_id='';
    if(!property_exists($object,'carrier_id')) $object->carrier_id='';
    
    if(!property_exists($object,'file_name')) $object->file_name=''; //Сопроводительные документы
    if(!property_exists($object,'description')) $object->description=''; //Описание
    
    
    
    $html='<input style="display:none;" id="e_trip_id" value="'.$object->id.'" type="text">';

    $html.='       
        <table class="SEdit">
        <caption></caption>
        <thead>
        <tr>
        <th style="width:40%">'.trt('Name').'</th>
        <th style="width:60%">'.trt('Value').'</th>
        </tr>
        </thead>
        <tbody>
            <tr style="background-color:#EEEEEE"><td style="font-weight: bold;">'.trt('Trip_type').'</td><td><select style="width: 100%;" id="e_trip_type_id"><option value=""></option>';
    if($object->trip_type_id===1)
    {
        $html.='<option value="1" selected="true">'.trt('Primary').'</option>';
        $html.='<option value="2">'.trt('Optional').'</option>';
    }else if($object->trip_type_id===2)
    {
        $html.='<option value="1">'.trt('Primary').'</option>';
        $html.='<option value="2" selected="true">'.trt('Optional').'</option>';
    }else
    {
        $html.='<option value="1">'.trt('Primary').'</option>';
        $html.='<option value="2">'.trt('Optional').'</option>';
    }

    $html.='     </select></td></tr>
            <tr style="background-color:#FFFFFF"><td style="font-weight: bold;">'.trt('Route').'</td><td><select style="width: 100%;" id="e_trip_route_id"><option value=""></option>
    ';
    //Выбираю маршруты только для текущего автовакзала
    $sql="select id,name from main.routes where del=false and id in (select route_id from main.routes_companies where del=false and company_id=(select company_id from main._users where id=".$_SESSION['USER_ID'].")) order by name;";
    $res = $db->query($sql);
    while ($row = $res->fetch(PDO::FETCH_ASSOC))
    {
        if($row['id']==$object->route_id)
        {
            $html.='<option value="'.$row['id'].'" selected>'.$row['name'].'</option>';
        }else
        {
            $html.='<option value="'.$row['id'].'">'.$row['name'].'</option>';
        }
    }
    $html.='</select></td></tr>
            <tr style="background-color:#EEEEEE"><td style="font-weight: bold;">'.trt('Direction').'</td><td><select style="width: 100%;" name="trip_direction" id="e_trip_direction"><option value=""></option>
    ';
    if($object->direction===1 or $object->direction===true)
    {
        $html.='<option value="1" selected>Прямой</option><option value="0">Обратный</option>';
    }else if($object->direction===0 or $object->direction===false)
    {
        $html.='<option value="1">Прямой</option><option value="0" selected>Обратный</option>';
    }else
    {
        $html.='<option value="1">Прямой</option><option value="0">Обратный</option>';
    }
    $html.='</select></td></tr>
            <tr style="background-color:#FFFFFF"><td style="font-weight: bold;">'.trt('Carrier').'</td><td><select style="width: 100%;" name="trip_carrier_id" id="e_trip_carrier_id"><option value=""></option>
    ';
    //Выбираю перевозчика для заданого маршрута (и для заданого автовакзала)
    $sql='
        select c.* from
            main.companies c
            join (
            	select company_id from main.trips_schedules where del=false and route_id in (select route_id from main.routes_companies where del=false and company_id=(select company_id from main._users where del=false and id='.$_SESSION['USER_ID'].'))
            	union
            	select company_id from main.routes_companies where del=false and route_id in (select route_id from main.routes_companies where del=false and company_id=(select company_id from main._users where del=false and id='.$_SESSION['USER_ID'].'))
            )t on c.id=t.company_id
        where company_id!=(select company_id from main._users where del=false and id='.$_SESSION['USER_ID'].')
        order by c.name
    ';
    $res = $db->query($sql);
    while ($row = $res->fetch(PDO::FETCH_ASSOC))
    {
        if($row['id']==$object->carrier_id)
        {
            $html.='<option value="'.$row['id'].'" selected>'.$row['name'].'</option>';
        }else
        {
            $html.='<option value="'.$row['id'].'">'.$row['name'].'</option>';
        }
    }
    $html.='
                </select></td></tr>
            <tr style="background-color:#EEEEEE"><td>'.trt('Bus').'</td><td><select style="width: 100%;" name="object_id" id="e_trip_object_id"><option value=""></option>
    ';
    //Выбираю перевозчика для заданого маршрута
    $res = $db->query("select id,name from main.objects where del=false order by name;");
    while ($row = $res->fetch(PDO::FETCH_ASSOC))
    {
        if($row['id']==$object->object_id)
        {
            $html.='<option value="'.$row['id'].'" selected>'.$row['name'].'</option>';
        }else
        {
            $html.='<option value="'.$row['id'].'">'.$row['name'].'</option>';
        }
    }
    $html.='
            </select></td></tr>
            <tr style="background-color:#FFFFFF"><td style="font-weight: bold;">Дата время начала маршрута план</td><td><table><tbody><tr><td style="padding: 0px; width: 100%;"><input style="width: 100%;" name="trip_date_start" id="e_trip_date_start" value="'.$object->date.'" type="text"></td><td style="padding: 0px;"><img id="e_trip_date_start_s" src="../resources/monitoring/images/datepicker.jpg" style="cursor: pointer; padding-left:1px;"></td></tr></tbody></table></td></tr>
            <tr style="background-color:#FFFFFF"><td>'.trt('Passengers').'</td><td><input style="width: 100%; height: 22px;" name="trip_passenger" id="e_trip_passenger" value="'.$object->passenger.'" type="text"></td></tr>
            <tr style="background-color:#FFFFFF"><td>Сопроводительные документы</td><td><table><tbody><tr><td style="padding: 0px; width: 100%;"><input style="width: 100%;opacity:0.5;" name="trip_file_name" id="e_trip_file_name" value="'.$object->file_name.'" type="text" readonly></td><td style="padding: 0px 0px 0px 1px;"><input class="button-secondary" id="e_trip_btn_select_file" type="button" value="'.trt('Choose').'"></td><td style="padding: 0px 0px 0px 1px;"><input class="button-secondary" id="e_trip_btn_download_file" type="button" value="'.trt('Download').'"></td><td style="padding: 0px 0px 0px 1px;"><input class="button-secondary" id="e_trip_btn_delete_file" type="button" value="'.trt('Delete').'"></td></tr></tbody></table></td></tr>
            <tr style="background-color:#EEEEEE"><td>'.trt('Description').'</td><td><textarea id="e_trip_description" style="width: 100%;" rows="4" name="trip_description">'.$object->description.'</textarea></td></tr>
            <tr><td style="padding: 5px; vertical-align: bottom; background-color: rgb(245, 245, 245);" colspan="2" align="right"><input class="button-secondary" style="width: 90px;" value="Применить" type="button" onclick="g_trips.saveTrip();"><input class="button-secondary" style="width: 90px;" value="Отмена" type="button" onclick="alert(\'test\');"></td></tr>
            </tbody>
        </table>
    ';   

    header('Content-Type: text/plain');
    echo $html;
    exit;
}else if($fn=='3') //Создать/изменить запись в базе
{
    if(!isset($HTTP_RAW_POST_DATA))
    {	$HTTP_RAW_POST_DATA = file_get_contents("php://input");
    }
    try
    {
        $object = json_decode($HTTP_RAW_POST_DATA);
    }catch (Exception $e)
    {
        $json='{"errorMessage":"'.$e->getMessage().'"}';
        header('Content-Type: application/json');
        echo $json;
        exit;
    }
    //if($object==null) $object = (object)[];
    
    if(!property_exists($object,'id') or $object->id=='') $object->id=null;
    if(!property_exists($object,'trip_type_id') or $object->trip_type_id=='') $object->trip_type_id=null;
    if(!property_exists($object,'route_id') or $object->route_id=='') $object->route_id=null;   
    if(!property_exists($object,'direction') or $object->direction==='') $object->direction=null;
    if(!property_exists($object,'carrier_id') or $object->carrier_id==='') $object->carrier_id=null;
    if(!property_exists($object,'date_start') or $object->date_start==='') $object->date_start=null;
    if(!property_exists($object,'passenger') or $object->passenger==='') $object->passenger=null;
    if(!property_exists($object,'date')) $object->date='';
    if(!property_exists($object,'route_id')) $object->route_id='';
    if(!property_exists($object,'file_name')) $object->file_name='';
    if(!property_exists($object,'description')) $object->description='';
    
    
    if($object->id==null) //Новая запись
    {
        $sql='insert into main.trips(trip_type_id,route_id,direction,company_carrier_id,object_id,date_start,passenger,file_name,description)values(:trip_type_id,:route_id,:direction,:company_carrier_id,:object_id,:date_start,:passenger,:file_name,:description) RETURNING id;';

        $stmt = $db->prepare($sql);
        if($stmt === false) sendError('Error preparing Statement');
        
        $stmt->bindParam(':trip_type_id', $object->trip_type_id, PDO::PARAM_STR);
        $stmt->bindParam(':route_id', $object->route_id, PDO::PARAM_INT);
        $stmt->bindParam(':direction', $object->direction, PDO::PARAM_BOOL);
        $stmt->bindParam(':company_carrier_id', $object->carrier_id, PDO::PARAM_INT); //перевозчик
        $stmt->bindParam(':object_id', $object->object_id, PDO::PARAM_INT); //Автобус перевозчика
        $stmt->bindParam(':date_start', $object->date_start, PDO::PARAM_STR);
        $stmt->bindParam(':passenger', $object->passenger, PDO::PARAM_INT); //Пассажиров
        $stmt->bindParam(':file_name', $object->file_name, PDO::PARAM_STR);
        $stmt->bindParam(':description', $object->description, PDO::PARAM_STR);
        try
        {
            $res = $stmt->execute();
        } catch (Exception $e)
        {
            $json='{"errorCode":1,"errorMessage":'.json_encode($e->getMessage(),JSON_UNESCAPED_UNICODE).'}';
            header('Content-Type: application/json');
            echo $json;
            exit;
        }        
        $id='';
        if($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $id=$row['id'];
        }
        $stmt=null;

        //Если запись добавлена информируем автобусный парк, чтобы он назначил автобус на заданый рейс
        if($id!='')
        {
            $route_name='';
            $sql="select id,name from main.routes where id=".$object->route_id;
            $res = $db->query($sql);
            while ($row = $res->fetch(PDO::FETCH_ASSOC))
            {
                $route_name=$row['name'];
            }
            $sql='insert into main.messages(subject,"text",company_carrier_id,action_name,action_settings)values(:subject,:text,:company_carrier_id,:action_name,:action_settings);';
            $stmt = $db->prepare($sql);
            if($stmt === false) sendError('Error preparing Statement');

            //Так как bindParam принимает ссылки на переменные то создаём эти переменные
            $subject='Warning';
            $text='Вам был назначен рейс "'.$route_name.'" начало рейса в "'.$object->date_start.'", пожалуйста назначте автобус на это рейс!';
            $action_name='Edit_trip';
            $action_settings='{"trip_id":'.$id.'}';

            $stmt->bindParam(':subject', $subject, PDO::PARAM_STR);            
            $stmt->bindParam(':text', $text, PDO::PARAM_STR);
            $stmt->bindParam(':company_carrier_id', $object->carrier_id, PDO::PARAM_INT);
            $stmt->bindParam(':action_name', $action_name, PDO::PARAM_STR);
            $stmt->bindParam(':action_settings', $action_settings, PDO::PARAM_STR);
            
            try
            {
                $res = $stmt->execute();
            } catch (Exception $e)
            {
                $json='{"errorCode":1,"errorMessage":'.json_encode($e->getMessage(),JSON_UNESCAPED_UNICODE).'}';
                header('Content-Type: application/json');
                echo $json;
                exit;
            }
        }        
        
    }else //Обновляем старую запись
    {
        $sql='update main.trips set trip_type_id=:trip_type_id,route_id=:route_id,direction=:direction,company_carrier_id=:company_carrier_id,object_id=:object_id,date_start=:date_start,passenger=:passenger,file_name=:file_name,description=:description where id=:id';
        $stmt = $db->prepare($sql);
        if($stmt === false) sendError('Error preparing Statement');

        $stmt->bindParam(':trip_type_id', $object->trip_type_id, PDO::PARAM_STR);
        $stmt->bindParam(':route_id', $object->route_id, PDO::PARAM_INT);
        $stmt->bindParam(':direction', $object->direction, PDO::PARAM_BOOL);
        $stmt->bindParam(':company_carrier_id', $object->carrier_id, PDO::PARAM_INT); //перевозчик
        $stmt->bindParam(':object_id', $object->object_id, PDO::PARAM_INT); //Автобус перевозчика
        $stmt->bindParam(':date_start', $object->date_start, PDO::PARAM_STR);
        $stmt->bindParam(':passenger', $object->passenger, PDO::PARAM_INT); //Пассажиров
        $stmt->bindParam(':file_name', $object->file_name, PDO::PARAM_STR);
        $stmt->bindParam(':description', $object->description, PDO::PARAM_STR);
        $stmt->bindParam(':id', $object->id, PDO::PARAM_INT);
        
        try
        {
            $res = $stmt->execute();
        } catch (Exception $e)
        {
            $json='{"errorCode":1,"errorMessage":'.json_encode($e->getMessage(),JSON_UNESCAPED_UNICODE).'}';
            header('Content-Type: application/json');
            echo $json;
            exit;
        }
    }
    
    $json='{}';
    header('Content-Type: application/json');
    echo $json;
    exit;
    
}else if($fn=='4') //Загрузить файл на сервер
{
    session_write_close(); //Разблокируем сессионный файл так как запросы могут быть достаточно долгими
    $dir = $_SERVER['DOCUMENT_ROOT']."/temp/";
    
    //Так как у файлов могут быть одинаковые имена считаем CRC и переименовываем файл отправля пользователю новое название файла
    //В базе данных название файла будет преобразовываться так: "файл.txt" -> "файл_crc32.txt"
    if(isset($_FILES['file']))
    {
        if(file_exists($_FILES['file']['tmp_name']))
        {
            $hash = hash_file( 'crc32', $_FILES['file']['tmp_name'] );
            $new_name=delPHPExt($dir.$hash.'.'.getExtension($_FILES['file']['name']));
            if(move_uploaded_file($_FILES['file']['tmp_name'],$new_name))
            {
                //Отправляем новое название файла клиенту
                print "ok=".beforeLast($_FILES['file']['name'],'.').'_'.$hash.'.'.getExtension($_FILES['file']['name'])."\n";
            }
        }else { print "ok=\n File \"".file_exists($_FILES['file']['tmp_name'])."\" not find"; }
    }
    
    
    print '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">';
    print '<html>';
    print '	<head>';
    print '		<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />';
    print '	</head>';
    print '	<body>';
    print '		<form name="form" enctype="multipart/form-data" action="/monitoring/pscripts/trips.php?fn=4" method="post">';
    print '			<input type="hidden" name="state" value=""/>';
    print '			<input type="file" name="file"><br/>';
    print '			<input type="submit" value="Send File">';
    print '			<input type="reset"  value="Reset">';
    print '		</form>';
    print '	</body>';
    print '</html>';
    
}else if($fn=='5') //Скачать файл с сервера
{
    $dir = $_SERVER['DOCUMENT_ROOT']."/temp/";
    
    $fname='';
    if(isset($_GET['fname'])) { $fname=$_GET['fname']; }
    $name=beforeLast($fname,'_').'.'.afterLast($fname,'.');
    $fname=$dir.afterLast($fname,'_');

    header("Content-type: application/octet-stream");
    header('Content-Disposition: attachment; filename="'.$name.'"');
    header('Content-Length: '.filesize($fname));
    readfile($fname);
    exit;
}