ZZFX/zz-fx/Model/retrieval/site.php

<?php
/**
 *
 * ============================================================================
 * * 版权所有 蜘蛛出行 * *
 * 网站地址: http://www.zhizhuchuxing.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！您只能在不用于商业目的的前提下对程序代码进行修改和
 * 使用；不允许对程序代码以任何形式任何目的的再发布。
 * ============================================================================
 * Author By: 张帅
 * PhpStorm site1.php
 * Create By 2016/12/1 14:15 $
 */

header("Access-Control-Allow-Origin:*");

$user_type = 0;
$pdo=conn();


$back = $_POST['back'];
$operate = $_POST['operate'];

$channel_id = isset($_COOKIE['org_id']) ? $_COOKIE['org_id'] : 0;

//判断去程或返程 1去 2返
if($back == 1){
    //判断触发事件
    if($operate == 'initial'){
        $run_id = $_POST['run_id'];
        $start_area_id = $_POST['start_area_id'];
        $end_area_id = $_POST['end_area_id'];
        $run = getGoRunDetail($run_id, $start_area_id, $end_area_id, $channel_id);
    }elseif($operate == 'start'){
        $run_id = $_POST['run_id'];
        $start_res_id = $_POST['start_res_id'];
        $end_area_id = $_POST['end_area_id'];
        $line_type = $_POST['line_type'];
        $run = getGoRunDetailByStart($run_id, $start_res_id, $end_area_id, $channel_id,$line_type);
    }else{
        $run_id = $_POST['run_id'];
        $start_res_id = $_POST['start_res_id'];
        $end_res_id = $_POST['end_res_id'];
        $line_type = $_POST['line_type'];
        $run = getGoRunDetailByEnd($run_id, $start_res_id, $end_res_id, $channel_id,$line_type);
    }
}else{
    //判断触发事件
    if($operate == 'initial'){
        $go_run_id = $_POST['go_run_id'];
        $go_start_res_id = $_POST['go_start_res_id'];
        $go_end_res_id = $_POST['go_end_res_id'];
        $run_id = $_POST['run_id'];
        $start_area_id = $_POST['start_area_id'];
        $end_area_id = $_POST['end_area_id'];
        $run = getBackRunDetail($run_id, $start_area_id, $end_area_id, $channel_id,$go_run_id,$go_start_res_id,$go_end_res_id);
    }elseif($operate == 'start'){
        $go_run_id = $_POST['go_run_id'];
        $go_start_res_id = $_POST['go_start_res_id'];
        $go_end_res_id = $_POST['go_end_res_id'];
        $run_id = $_POST['run_id'];
        $start_res_id = $_POST['start_res_id'];
        $end_area_id = $_POST['end_area_id'];
        $line_type = $_POST['line_type'];
        $run = getBackRunDetailByStart($run_id, $start_res_id, $end_area_id, $channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id);
    }else{
        $go_run_id = $_POST['go_run_id'];
        $go_start_res_id = $_POST['go_start_res_id'];
        $go_end_res_id = $_POST['go_end_res_id'];
        $run_id = $_POST['run_id'];
        $start_res_id = $_POST['start_res_id'];
        $end_res_id = $_POST['end_res_id'];
        $line_type = $_POST['line_type'];
        $run = getBackRunDetailByEnd($run_id, $start_res_id, $end_res_id, $channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id);
    }
}
$json['code'] = '0';
$json['info'] = '返回数据成功';
$json['list'] = $run;

echo json_encode($json);die;
print_r($run);die;

#region 去程的方法
/**
 * Function Description:获取初始化数据
 * Function Name: getRunDetail
 * @param $run_id
 * @param $start_area_id
 * @param $end_area_id
 * @param $channel_id
 *
 * @return array|mixed|string
 *
 * @author 张帅
 */
function getGoRunDetail($run_id, $start_area_id, $end_area_id, $channel_id)
{
    //获取班次详情
    $run_info = getRunInfo($run_id);

    $poi = getPoiInfoArr();
    $poi_info = $poi['poi_info_array'];
    //print_r($poi_info);die;
    $run['run_id'] = $run_info['run_id'];
    $run['start_area_id'] = $start_area_id;
    $run['start_area_name'] = $poi_info[$start_area_id]['area_name'];
    $run['end_area_id'] = $end_area_id;
    $run['end_area_name'] = $poi_info[$end_area_id]['area_name'];
    $run['run_date'] = $run_info['run_date'];
    $run['run_time'] = $run_info['run_time'];
    $run['line_type'] = $run_info['line_type'];
    $line_type = $run['line_type'];

    //获取上车站点
    $start_res_arr = getStartStation($run_id, $start_area_id);
    $run['start_res_arr'] = $start_res_arr;
    //获取默认选中的上车站
    $start_res_id = $start_res_arr[0]['res_id'];
    $run['select_start_res']['res_id'] = $start_res_arr[0]['res_id'];
    $run['select_start_res']['res_name'] = $start_res_arr[0]['res_name'];
    $run['select_start_res']['res_time'] = $start_res_arr[0]['res_time'];
    //获取下车站点
    $end_res_arr = getEndStation($run_id, $start_res_id, $end_area_id);
    $run['end_res_arr'] = $end_res_arr;
    //获取默认选中的上车站
    $end_res_id = $end_res_arr[0]['res_id'];
    $run['select_end_res']['res_id'] = $end_res_arr[0]['res_id'];
    $run['select_end_res']['res_name'] = $end_res_arr[0]['res_name'];
    $run['select_end_res']['res_time'] = $end_res_arr[0]['res_time'];
    //获取票种信息
    $ticket_info = getTicketInfo($run_id, $start_res_id, $end_res_id,$channel_id,$line_type);

    $run['ticket_info'] = $ticket_info[0];
    return $run;
}

/**
 * Function Description:通过上车站点获取初始化数据
 * Function Name: getRunDetailByStart
 * @param $run_id
 * @param $start_res_id
 * @param $end_area_id
 * @param $channel_id
 *
 * @return array|mixed|string
 *
 * @author 张帅
 */
function getGoRunDetailByStart($run_id, $start_res_id, $end_area_id, $channel_id,$line_type)
{
    //获取下车站点
    $end_res_arr = getEndStation($run_id, $start_res_id, $end_area_id);
    $run['end_res_arr'] = $end_res_arr;
    //获取默认选中的上车站
    $end_res_id = $end_res_arr[0]['res_id'];
    $run['select_end_res']['res_id'] = $end_res_arr[0]['res_id'];
    $run['select_end_res']['res_name'] = $end_res_arr[0]['res_name'];
    $run['select_end_res']['res_time'] = $end_res_arr[0]['res_time'];
    //获取票种信息
    $ticket_info = getTicketInfo($run_id, $start_res_id, $end_res_id,$channel_id,$line_type);

    $run['ticket_info'] = $ticket_info[0];
    return $run;
}

/**
 * Function Description:通过下车站点获取初始化数据
 * Function Name: getRunDetailByEnd
 * @param $run_id
 * @param $start_res_id
 * @param $end_res_id
 * @param $channel_id
 *
 * @return array|mixed|string
 *
 * @author 张帅
 */
function getGoRunDetailByEnd($run_id, $start_res_id, $end_res_id, $channel_id,$line_type)
{
    //获取票种信息
    $ticket_info = getTicketInfo($run_id, $start_res_id, $end_res_id,$channel_id,$line_type);

    $run['ticket_info'] = $ticket_info[0];
    return $run;
}
#endregion

/**
 * Function Description:获取返程初始化数据
 * Function Name: getRunDetail
 * @param $run_id
 * @param $start_area_id
 * @param $end_area_id
 * @param $channel_id
 *
 * @return array|mixed|string
 *
 * @author 张帅
 */
function getBackRunDetail($run_id, $start_area_id, $end_area_id, $channel_id,$go_run_id,$go_start_res_id,$go_end_res_id)
{
    //获取班次详情
    $run_info = getRunInfo($run_id);

    $poi = getPoiInfoArr();
    $poi_info = $poi['poi_info_array'];
    //print_r($poi_info);die;
    $run['run_id'] = $run_info['run_id'];
    $run['start_area_id'] = $start_area_id;
    $run['start_area_name'] = $poi_info[$start_area_id]['area_name'];
    $run['end_area_id'] = $end_area_id;
    $run['end_area_name'] = $poi_info[$end_area_id]['area_name'];
    $run['run_date'] = $run_info['run_date'];
    $run['run_time'] = $run_info['run_time'];
    $run['line_type'] = $run_info['line_type'];
    $line_type = $run['line_type'];

    //获取上车站点
    $start_res_arr = getStartStation($run_id, $start_area_id);
    $run['start_res_arr'] = $start_res_arr;
    //获取默认选中的上车站
    $start_res_id = $start_res_arr[0]['res_id'];
    $run['select_start_res']['res_id'] = $start_res_arr[0]['res_id'];
    $run['select_start_res']['res_name'] = $start_res_arr[0]['res_name'];
    $run['select_start_res']['res_time'] = $start_res_arr[0]['res_time'];
    //获取下车站点
    $end_res_arr = getEndStation($run_id, $start_res_id, $end_area_id);
    $run['end_res_arr'] = $end_res_arr;
    //获取默认选中的上车站
    $end_res_id = $end_res_arr[0]['res_id'];
    $run['select_end_res']['res_id'] = $end_res_arr[0]['res_id'];
    $run['select_end_res']['res_name'] = $end_res_arr[0]['res_name'];
    $run['select_end_res']['res_time'] = $end_res_arr[0]['res_time'];
    //获取票种信息
    $ticket_info = getBackTicketInfo($run_id, $start_res_id, $end_res_id,$channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id);


    $run['ticket_info'] = $ticket_info[0];
    return $run;
}

/**
 * Function Description:通过上车站点获取初始化数据 --返程
 * Function Name: getRunDetailByStart
 * @param $run_id
 * @param $start_res_id
 * @param $end_area_id
 * @param $channel_id
 *
 * @return array|mixed|string
 *
 * @author 张帅
 */
function getBackRunDetailByStart($run_id, $start_res_id, $end_area_id, $channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id)
{
    //获取下车站点
    $end_res_arr = getEndStation($run_id,$start_res_id, $end_area_id);
    $run['end_res_arr'] = $end_res_arr;
    //获取默认选中的上车站
    $end_res_id = $end_res_arr[0]['res_id'];
    $run['select_end_res']['res_id'] = $end_res_arr[0]['res_id'];
    $run['select_end_res']['res_name'] = $end_res_arr[0]['res_name'];
    $run['select_end_res']['res_time'] = $end_res_arr[0]['res_time'];
    //获取票种信息
    $ticket_info = getBackTicketInfo($run_id, $start_res_id, $end_res_id,$channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id);

    $run['ticket_info'] = $ticket_info[0];
    return $run;
}

/**
 * Function Description:通过下车站点获取初始化数据 --返程
 * Function Name: getRunDetailByEnd
 * @param $run_id
 * @param $start_res_id
 * @param $end_res_id
 * @param $channel_id
 *
 * @return array|mixed|string
 *
 * @author 张帅
 */
function getBackRunDetailByEnd($run_id, $start_res_id, $end_res_id, $channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id)
{
    //获取票种信息
//    $ticket_info = getTicketInfo($run_id, $start_res_id, $end_res_id,$channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id);
    $ticket_info = getBackTicketInfo($run_id, $start_res_id, $end_res_id,$channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id);

    $run['ticket_info'] = $ticket_info[0];
    return $run;
}

/**
 * Function Description:获取班次信息详情
 * Function Name: getRunInfo
 * @param $run_id
 *
 * @return mixed
 *
 * @author 张帅
 */
function getRunInfo($run_id)
{
    global $pdo;
    $sql = 'SELECT
                    r.run_id,r.run_date,r.run_time,l.line_name,l.line_code,l.line_id,l.line_type
                FROM
                    run_main AS r,
                    opera_line AS l
                WHERE
                    r.prod_id = l.line_id
                AND r.run_id = ' . $run_id . '
                AND l.cancel_flag = 0
                AND l.is_onsale = 1
                AND l.if_disabled = 0
                AND r.run_status = 138
                LIMIT 1';
    $result = $pdo->query($sql);
    $run_info = $result->fetchAll(PDO::FETCH_ASSOC);
    return $run_info[0];
}

/**
 * Function Description:获取上车站点数据
 * Function Name: getStartStation
 * @param $run_id
 * @param $start_area_id
 *
 * @return array
 *
 * @author 张帅
 */
function getStartStation($run_id, $start_area_id)
{
    global $pdo;
    $sql = 'SELECT
                    run_prod.start_station_res_id as res_id,
                    (SELECT res_name FROM base_resource WHERE res_id = run_prod.start_station_res_id LIMIT 1) as res_name,
                    (SELECT start_time from run_station where run_id = run_prod.run_id and station_res_id = run_prod.start_station_res_id LIMIT 1) as res_time
                FROM
                    run_prod
                LEFT JOIN run_station as rs ON rs.run_id = run_prod.run_id AND rs.station_res_id = run_prod.start_station_res_id  
                WHERE
                    run_prod.run_id = ' . $run_id . '
                AND run_prod.start_station_area_id = ' . $start_area_id . '
                AND run_prod.cancel_flag = 0
                AND rs.station_inout_type <> 114 
                AND (SELECT ticket_type FROM opera_tickets WHERE ticket_id = run_prod.prod_id AND cancel_flag = 0) = 1
                GROUP BY run_prod.start_station_res_id';
    $result = $pdo->query($sql);
    $start_station = $result->fetchAll(PDO::FETCH_ASSOC);
    $user_id =$_COOKIE['user_id'];
    $status = 0;
    if($user_id == 141){
        foreach ($start_station as $k => $v) {
            if($v['res_id'] == 9680){
                $status = 1;
                $station_arr = $v;
            }
        }
    }elseif($user_id == 121){
        foreach ($start_station as $k => $v) {
            if ($v['res_id'] == 9321) {
                $status = 1;
                $station_arr = $v;
            }
        }
    }
    if($status == 1){
        $start_station = array();
        $start_station[0] = $station_arr;
    }
    return $start_station;
}

/**
 * Function Description:获取下车站点数据
 * Function Name: getEndStation
 * @param $run_id
 * @param $run_id
 * @param $start_res_id
 * @param $end_area_id
 *
 * @return array
 *
 * @author 张帅
 */
function getEndStation($run_id, $start_res_id, $end_area_id)
{
    global $pdo;
    $sql = 'SELECT
                run_prod.end_station_res_id as res_id,
                (SELECT res_name FROM base_resource WHERE res_id = run_prod.end_station_res_id LIMIT 1) as res_name,
                (SELECT start_time from run_station where run_id = run_prod.run_id and station_res_id = run_prod.end_station_res_id LIMIT 1) as res_time
            FROM
                run_prod
            LEFT JOIN run_station as rs ON rs.run_id = run_prod.run_id AND rs.station_res_id = run_prod.end_station_res_id    
            WHERE
                run_prod.run_id = ' . $run_id . '
            AND run_prod.end_station_area_id = ' . $end_area_id . '
            AND run_prod.cancel_flag = 0
            AND rs.station_inout_type <> 114
            AND run_prod.start_station_res_id = ' . $start_res_id . '
            AND (SELECT ticket_type FROM opera_tickets WHERE ticket_id = run_prod.prod_id AND cancel_flag = 0) = 1
            GROUP BY run_prod.end_station_res_id';
    $result = $pdo->query($sql);
    $end_station = $result->fetchAll(PDO::FETCH_ASSOC);
    $user_id =$_COOKIE['user_id'];
    $status = 0;
    if($user_id == 141){
        foreach ($end_station as $k => $v) {
            if($v['res_id'] == 9680){
                $status = 1;
                $station_arr = $v;
            }
        }
    }elseif($user_id == 121){
        foreach ($end_station as $k => $v) {
            if ($v['res_id'] == 9321) {
                $status = 1;
                $station_arr = $v;
            }
        }
    }
    if($status == 1){
        $end_station = array();
        $end_station[0] = $station_arr;
    }
    return $end_station;
}

/**
 * Function Description:获取票种信息
 * Function Name: getTicketInfo
 * @param $run_id
 * @param $start_res_id
 * @param $end_res_id
 *
 * @return array
 *
 * @author 张帅
 */
function getTicketInfo($run_id, $start_res_id, $end_res_id,$channel_id,$line_type)
{
    global $pdo;
    $sql = "SELECT
                ticket_id,seat_type,
                (SELECT type_name FROM dict_type WHERE id = seat_type LIMIT 1) AS seat_type_name,
                human_type,
                (SELECT type_name FROM dict_type WHERE id = human_type LIMIT 1) AS human_type_name,
                prod_price, cus_price,
                (
                    SELECT
                        min(total_count - saled_count)
                    FROM
                        run_stock
                    WHERE
                        run_id = " . $run_id . "
                    AND cancel_flag = 0
                    AND seq_id >= (SELECT seq_id FROM run_stock WHERE run_id = " . $run_id . " AND cancel_flag = 0 AND res_id = " . $start_res_id . " AND seat_type = opera_tickets.seat_type limit 1)
                    AND seq_id < (SELECT seq_id FROM run_stock WHERE run_id = " . $run_id . " AND cancel_flag = 0 AND res_id = " . $end_res_id . " AND seat_type = opera_tickets.seat_type limit 1)
                    AND seat_type = opera_tickets.seat_type
                ) AS stock
            FROM
                opera_tickets
            WHERE
                ticket_type = 1
            AND ticket_id IN (
                                SELECT
                                    prod_id
                                FROM
                                    run_prod
                                WHERE
                                    run_id = " . $run_id . "
                                AND start_station_res_id = " . $start_res_id . "
                                AND end_station_res_id = " . $end_res_id . "
                                )
            AND cancel_flag = 0";
    $result = $pdo->query($sql);
    $ticket_info = $result->fetchAll(PDO::FETCH_ASSOC);
    if (count($ticket_info) == 0) {
        return array();
    }

    foreach ($ticket_info as $k => $v) {
        $sale_type = getTicketChannelSaleType($channel_id, $v['ticket_id'], $line_type);//获取销售类型
        if ($sale_type)//如果该销售方式被关掉
        {
            if ($sale_type['sale_type'] == "177") {
                if ($sale_type['price'] == '0') {
                    $ticket_info[$k]['price'] = $v['prod_price'];
                } else {
                    $ticket_info[$k]['price'] = $sale_type['price'];
                }
            } else {
                $ticket_info[$k]['price'] = $v['cus_price'];
            }

            //$ticket_info[$k]['price'] = $v['cus_price'];//暂时全部都是销售价格
        } else {
            unset($ticket_info[$k]);
        }
    }

    return $ticket_info;
}

/**
 * 获取返程票种详情
 */
function getBackTicketInfo($back_run_id, $back_start_res_id, $back_end_res_id,$channel_id,$line_type,$go_run_id,$go_start_res_id,$go_end_res_id){
    global $pdo;
    $go_ticket_info = getTicketInfo($go_run_id,$go_start_res_id,$go_end_res_id,$channel_id,$line_type);
    $back_ticket_info = getTicketInfo($back_run_id, $back_start_res_id, $back_end_res_id,$channel_id,$line_type);
    foreach ($go_ticket_info as $k => $v) {
        $go_ticket_array[$v['seat_type'] . "-" . $v['human_type']] = $v;
        $ticket_array_id[] = $v['ticket_id'];
    }
    foreach ($back_ticket_info as $k => $v) {
        $back_ticket_array[$v['seat_type'] . "-" . $v['human_type']] = $v;
        $ticket_array_id[] = $v['ticket_id'];
    }

    //获取促销优惠
    $sql = "SELECT
                ticket_id,
                off_value
            FROM
                opera_tickets_discount
            WHERE
                ticket_id IN (" . implode(",", $ticket_array_id) . ")
            AND supplier_id = " . $channel_id . "
            AND is_onsale = 1
            AND cancel_flag = 0
            AND discount_type = 332
            AND discount_day_type = 0
            GROUP BY ticket_id";
    $result = $pdo->query($sql);
    $discount_info = $result->fetchAll(PDO::FETCH_ASSOC);
    
    $discount_info_array = array();
    if (count($discount_info) > 0) {
        foreach ($discount_info as $k => $v) {
            $discount_info_array[$v['ticket_id']] = $v['off_value'];
        }
    }

    foreach ($go_ticket_array as $k => $v) {
        if (isset($back_ticket_array[$k])) {
            $go_discount = '0';
            if (isset($discount_info_array[$go_ticket_array[$k]['ticket_id']])) {
                $go_discount = $discount_info_array[$go_ticket_array[$k]['ticket_id']];
            }
            $back_discount = '0';
            if (isset($discount_info_array[$back_ticket_array[$k]['ticket_id']])) {
                $back_discount = $discount_info_array[$back_ticket_array[$k]['ticket_id']];
            }

            $ticket_one = array(
                'go_run_id' => $go_run_id,
                'go_ticket_id' => $go_ticket_array[$k]['ticket_id'],
                'go_price' => $go_ticket_array[$k]['price'],
                'go_discount_price' => $go_ticket_array[$k]['price'] - $go_discount,
                'go_discount' => $go_discount,
                'go_stock' => $go_ticket_array[$k]['stock'],
                'back_run_id' => $back_run_id,
                'back_ticket_id' => $back_ticket_array[$k]['ticket_id'],
                'back_price' => $back_ticket_array[$k]['price'],
                'back_discount_price' => $back_ticket_array[$k]['price'] - $back_discount,
                'back_discount' => $back_discount,
                'back_stock' => $back_ticket_array[$k]['stock'],
                'seat_type' => $go_ticket_array[$k]['seat_type'],
                'seat_type_name' => $go_ticket_array[$k]['seat_type_name'],
                'human_type' => $go_ticket_array[$k]['human_type'],
                'human_type_name' => $go_ticket_array[$k]['human_type_name'],
                'price' => $go_ticket_array[$k]['price'] + $back_ticket_array[$k]['price'] - $go_discount - $back_discount,
                'stock' => min($go_ticket_array[$k]['stock'], $back_ticket_array[$k]['stock'])
            );
            $ticket_array[] = $ticket_one;
        }
    }
    return $ticket_array;
}

/**
 * Function Description:获取渠道价
 * Function Name: getTicketChannelSaleType
 * @param $channel_id
 * @param $ticket_id
 * @param $line_type
 *
 * @return bool
 *
 * @author 张帅
 */
function getTicketChannelSaleType($channel_id, $ticket_id, $line_type)
{
    global $pdo;
    $channel_id = $_COOKIE['org_id'];
    $sql = "select supplier_name from base_supplier WHERE id = " . $channel_id . " and cancel_flag = 0";
    $result = $pdo->query($sql);
    $supplier_count = $result->fetchAll(PDO::FETCH_ASSOC);
    if(count($supplier_count) == 0){
        $channel_id = 39;
    }

    $sql = "SELECT
                sale_type,
                '0' as price
            FROM
                base_supplier_sale as a
            WHERE
                NOT EXISTS
                (
                    SELECT
                        supplier_id
                    FROM
                        opera_tickets_distrib
                    WHERE
                        supplier_id = a.supplier_id
                    AND ticket_id = " . $ticket_id . "
                    AND cancel_flag = 0
                )
            AND supplier_id = " . $channel_id . "
            AND parent_type = 310
            AND product_type IN (" . $line_type . ", 0)
            AND cancel_flag = 0
            UNION ALL
                SELECT
                    sale_type,
                    prod_price as price
                FROM
                    opera_tickets_distrib
                WHERE
                    supplier_id = " . $channel_id . "
                AND ticket_id = " . $ticket_id . "
                AND authority_status = 1
                AND cancel_flag = 0";
    $result = $pdo->query($sql);
    $sale_type = $result->fetchAll(PDO::FETCH_ASSOC);

    if (count($sale_type) == 0) {
        return false;
    }
    $sale_type = $sale_type[0];
    return $sale_type;
}










#region 站点详情
//------------------------------------------------------------------------------------------------------------------------------------------------------------
/**
 * 站点详情
 */
/**
 * Function Description:获取所有线路中开始POI-结束POI的连接关系
 * Function Name: getPoiId
 *
 * @return mixed
 *
 * @author 张帅
 */
function getPoiId()
{
    global $pdo;
    $sql = 'SELECT 
                start_area,
                end_area,
                line_type
            FROM
                (
                    SELECT 
                        SUBSTRING_INDEX(SUBSTRING_INDEX(b.start_area, \',\', sequence.help_topic_id),\',\' ,-1) AS start_area,
                        SUBSTRING_INDEX(SUBSTRING_INDEX(b.end_area, \',\', sequence1.help_topic_id),\',\' ,-1) AS end_area,
                        line_type
                    FROM  
                        mysql.help_topic as sequence,
                        mysql.help_topic as sequence1,
                        (
                            SELECT @id := @id +1 as id,
                                if(length(start_parent)=0,start_area_id,concat(start_area_id,\',\',start_parent)) as start_area,
                                if(length(end_parent)=0,end_area_id,concat(end_area_id,\',\',end_parent)) as end_area,
                                line_type
                            FROM (
                                    SELECT distinct 
                                        t.start_station_area_id as start_area_id,
                                        replace(substring(v1.parent_area_id_list,2,length(v1.parent_area_id_list)-2),\'}{\',\',\') AS start_parent,
                                        t.end_station_area_id as end_area_id,
                                        replace(substring(v2.parent_area_id_list,2,length(v2.parent_area_id_list)-2),\'}{\',\',\') AS end_parent,
                                        l.line_type
                                    FROM
                                        opera_line AS l,
                                        opera_tickets AS t,
                                        base_area_view as v1,
                                        base_area_view as v2,
                                        base_area as a1,
                                        base_area as a2
                                    WHERE
                                        l.line_id = t.line_id
                                    and v1.area_id = t.start_station_area_id
                                    and v2.area_id = t.end_station_area_id
                                    and a1.id = t.start_station_area_id
                                    and a2.id = t.end_station_area_id
                                    and a1.cancel_flag = 0
                                    and a2.cancel_flag = 0
                                    AND l.cancel_flag = 0
                                    AND l.if_disabled = 0
                                    AND l.line_type in(255,256)
                                    AND l.is_onsale = 1
                                    AND t.cancel_flag = 0
                                    AND t.start_station_area_id != 0
                                    AND t.end_station_area_id != 0
                                  ) a, 
                                  (select @id:= 0) as i
                        ) b
                    WHERE 
                        sequence.help_topic_id BETWEEN 1  AND ( SELECT 1 + LENGTH(b.start_area) - LENGTH(REPLACE(b.start_area, \',\', \'\')))
                    AND sequence1.help_topic_id BETWEEN 1  AND ( SELECT 1 + LENGTH(b.end_area) - LENGTH(REPLACE(b.end_area, \',\', \'\')))
                    GROUP BY
                        SUBSTRING_INDEX(SUBSTRING_INDEX(b.start_area, \',\', sequence.help_topic_id),\',\' ,-1),
                        SUBSTRING_INDEX(SUBSTRING_INDEX(b.end_area, \',\', sequence1.help_topic_id),\',\' ,-1),
                        line_type
                )as tb';
    $result = $pdo->query($sql);
    $rowset = $result->fetchAll(PDO::FETCH_ASSOC);
    return $rowset;
}

/**
 * Function Description:获取poi详情
 * Function Name: getPoiInfo
 * @param $poi_id
 *
 * @return array
 *
 * @author 张帅
 */
function getPoiInfoDb($poi_id)
{
    global $pdo;
    $sql = 'SELECT
                a.id as area_id,
                a.area_name,
                a.poi_type,
                REPLACE(substring(v.parent_area_id_list,2,length(v.parent_area_id_list)-2),\'}{\',\',\') AS parent_area_id,
                REPLACE(REPLACE(substring(v.parent_area_name_list,2),\'}{\',\',\'),\'}\',\'\') AS parent_area_name,
                v.area_level
            FROM
                base_area AS a,
                base_area_view AS v
            WHERE
                a.id = v.area_id
            AND a.cancel_flag = 0    
            AND a.id in (' . $poi_id . ')';
    $result = $pdo->query($sql);
    $rowset = $result->fetchAll(PDO::FETCH_ASSOC);
    return $rowset;
}

/**
 * Function Description:获取站点详情
 * Function Name: getPoiInfoArr
 *
 * @return array|mixed
 *
 * @author 张帅
 */
function getPoiInfoArr()
{
    $result = get_memcache('FX_POI_ARR_LIST');
    if ($result) {
        return $result;
    }

    //1.获取POIid数组
    $poi_id = getPoiIdArr();

    //2.收集所有的poi_id
    $area_id_array = array();
    foreach ($poi_id as $key => $value) {
        if (!isset($area_id_array[$value['start_area']])) {
            $area_id_array[$value['start_area']] = $value['start_area'];
        }
        if (!isset($area_id_array[$value['end_area']])) {
            $area_id_array[$value['end_area']] = $value['end_area'];
        }
    }
    $poi_id = array_merge($poi_id);

    //3.根据poi_id获取poi详细信息
    if (count($area_id_array) > 0) {
        $poi_info_array1 = getPoiInfo($area_id_array);
    } else {
        $poi_info_array1 = array();
    }
    foreach ($poi_info_array1 as $key => $vel){
        $poi_info_array[$vel['area_id']] = $vel;
    }

    //4.根据线路类型及上下车类型对poi分组
    $poi_line_inout = getPoiByLineInout($poi_id);

    $result['poi_id'] = $poi_id;
    $result['poi_info_array'] = $poi_info_array;
    $result['poi_line_inout'] = $poi_line_inout;

    set_memcache('FX_POI_ARR_LIST', $result, 3600);
    return $result;
}

/**
 * Function Description:获取poi站点数组
 * Function Name: getPoiIdArr
 *
 * @return array
 *
 * @author 张帅
 */
function getPoiIdArr()
{
    $poi_array = getPoiId();
    return $poi_array;
}

/**
 * Function Description:获取poi详细信息
 * Function Name: getPoiInfo
 * @param $area_id_array
 *
 * @return array
 *
 * @author 张帅
 */
function getPoiInfo($area_id_array)
{
    $area_id_array = implode(',', $area_id_array);
    //1.获取POI的详细信息
    $poi_array = getPoiInfoDb($area_id_array);

    //2.整理poi数组
    $poi_info_array = array();
    foreach ($poi_array as $key => $vel) {
        $poi_info_array[$vel['area_id']] = $vel;
    }
    //3.整理poi的父子关系
    foreach ($poi_info_array as $key => $vel) {
        if (!empty($vel['parent_area_id'])) {
            $parent_id_arr = explode(',', $vel['parent_area_id']);
            $parent_name_arr = explode(',', $vel['parent_area_name']);
            $parent_area_arr = array();
            foreach ($parent_id_arr as $k => $v) {
                $parent_area_arr[$k]['area_id'] = $v;
                $parent_area_arr[$k]['area_name'] = $parent_name_arr[$k];
                $poi_info_array[$v]['son_area_id'][$key] = $key;
            }
            $poi_info_array[$key]['parent_area_arr'] = $parent_area_arr;
        } else {
            $poi_info_array[$key]['parent_area_arr'] = array();
        }
    }
    foreach ($poi_info_array as $key => $vel) {
        if (!isset($vel['son_area_id'])) {
            $poi_info_array[$key]['son_area_id'] = '';
        } else {
            $poi_info_array[$key]['son_area_id'] = implode(',', $vel['son_area_id']);
        }
    }

    return $poi_info_array;
}

/**
 * Function Description:根据线路类型及上下车类型对poi分组
 * Function Name: getPoiByLineInout
 * @param $poi_id
 *
 * @return mixed
 *
 * @author 张帅
 */
function getPoiByLineInout($poi_id)
{
    $poi_group = array();
    foreach ($poi_id as $k => $v) {
        if (!isset($poi_group[$v['line_type']]['start_area'][$v['start_area']])) {
            $poi_group[$v['line_type']]['start_area'][$v['start_area']] = $v['start_area'];
        }
        if (!isset($poi_group[$v['line_type']]['end_area'][$v['end_area']])) {
            $poi_group[$v['line_type']]['end_area'][$v['end_area']] = $v['end_area'];
        }
    }
    return $poi_group;
}

#endregion