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

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

header("Access-Control-Allow-Origin:*");
//载入配置文件和连接数据库

//区分是普通乘客还是分销员 0普通，1分销
$user_type = 0;

$user_id=$_COOKIE['user_id'];
if (!$user_id) {
    echo json_encode(array("code" => 1,"iflogin"=>1, "info" => "用户未登录"));
    exit ;
}

//判断出发地 目的地 时间是否为空
$start_area = empty($_POST['start_area'])?"0":$_POST['start_area'];
$end_area = empty($_POST['end_area'])?"0":$_POST['end_area'];
$run_date = empty($_POST['go_time'])?date("Y-m-d",time()+24*3600):$_POST['go_time'];
$current_day = date("Y-m-d");
$limit_time = date("H:i", strtotime("+10 minutes"));

$bus_type = $_POST['bus_type'];
if($bus_type == 255){
    $bus_type = "255,0,368";
}
$current_page = $_POST['currpage'];
$page_size = $_POST['pagesize'];

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

$list = runList($bus_type,$run_date,$start_area,$end_area,$current_page,$page_size, $channel_id);
$run_list = $list['list'];
if(count($run_list) == 0){
    $json['code'] = 0;
    $json['info'] = $CODE[0];
    $json['list'] = $list['list'];
    $json['totalnum'] = $list['totalnum'];
    echo json_encode($json);exit();
}
foreach($run_list as $key => $vel){
    if($vel['num'] > 20){
        $run_list[$key]['num'] = '充足';
    }elseif ($vel['num'] <= 0){
        $run_list[$key]['num'] = '售罄';
    }
}
$json['code'] = 0;
$json['info'] = $CODE[0];
$json['list'] = $run_list;
$json['totalnum'] = $list['totalnum'];
echo json_encode($json);exit();

/**
 * Function Description:获取班次列表
 * Function Name: runList
 * @param $line_type
 * @param $run_date
 * @param $start_area
 * @param $end_area
 * @param $current_page
 * @param $page_size
 *
 * @return mixed
 *
 * @author 张帅
 */
function runList($line_type,$run_date,$start_area,$end_area,$current_page,$page_size, $channel_id = 0){

    global $pdo;
    global $bus_type;
    //筛掉不能在分销现实的线路
    $user_id = $_COOKIE['user_id'];
    if($user_id == 136){
        $line_id_arr = array(213344,213343,213342,213341,213340,213339,1579,1578,451180);
    }else{
        $line_id_arr = array(213344,213343,213342,213341,213340,213339,1579,1578,451180);
    }

    $start_area = execStation($start_area,$line_type);
    $end_area = execStation($end_area,$line_type);
    if($user_id == 141 && $bus_type != 256){
        $start_area = '3376,3240,100437,3353';
        $end_area = '3376,3240,100437,3353,791';
    }elseif ($user_id == 121 && $bus_type != 256){
        $start_area = '3343,3240,100437,3353';
        $end_area = '3343,3240,100437,3353';
    }elseif ($user_id == 461 && $bus_type != 256){
        $start_area = '3341,100091,3308';
        $end_area = '3341,100091,3308';
    }

    if($start_area != '0'){
        $start_area1 = explode(',',$start_area);
        $start_area1 = '\'' . implode('',$start_area1) . '\'';
    }else{
        $start_area1 = "0";
    }
    if($end_area != '0'){
        $end_area1 = explode(',',$end_area);
        $end_area1 = '\'' . implode('',$end_area1) . '\'';
    }else{
        $end_area1 = "0";
    }

    //获取例外
    $sql = " SELECT ticket_id FROM opera_tickets_distrib WHERE SUPPLIER_ID={$channel_id} AND AUTHORITY_STATUS=0 ";
    $pre_result = $pdo->query($sql);
    $nosell_ticket_array = $pre_result->fetchAll(PDO::FETCH_ASSOC);
    $not_include_sql = "";
    if( $nosell_ticket_array != false ) {
        $nosell_ticket_id = array();
        foreach( $nosell_ticket_array as $nosell_info ) {
            $nosell_ticket_id[] = $nosell_info["ticket_id"];
        }
        $not_include_sql = " and r.prod_id NOT IN (".implode(",",$nosell_ticket_id).") ";
    }

    $now_date = date('Y-m-d', time());
    $start_row = ($current_page - 1) * $page_size;
    $sql = "select  a.run_id,
                    a.prod_id as line_id,
                    a.start_area_id,
                    (select area_name from base_area where id = a.start_area_id LIMIT 1) as start_area_name,
                    a.end_area_id,
                    if(a.line_type = 368,(SELECT line_name from opera_line where line_id = a.prod_id),(select area_name from base_area where id = a.end_area_id LIMIT 1)) as end_area_name,
                    a.property,
                    a.prod_name,
                    a.run_date,
                    if(INSTR(a.start_time,',')>0,substring_index(a.start_time,',',1),a.start_time) as start_time,
                    if(INSTR(a.end_time,',')>0,concat(substring_index(a.end_time,',',1),'-',substring_index(a.end_time,',',-1)),a.end_time) as end_time,
                    (select type_name from dict_type where id = a.seat_type ) as site_type,
                    min(a.price) as price,
                    min(a.price2) as price2,
                    a.sale_type,
                    (select min(total_count-saled_count) from run_stock where run_id = a.run_id and cancel_flag = 0 and seq_id >= a.start_order_id and seq_id < a.end_order_id  and seat_type = a.seat_type) as num
            from (
                    select distinct m.run_id,
                                    p.line_id as prod_id,
                                    p.line_code as property,
                                    p.line_name as prod_name,
                                    m.run_date,
                                    r.start_station_area_id as start_area_id,
                                    r.end_station_area_id as end_area_id,      
                                    r.seat_type,
                                    p.line_type,
                                    (select min(station_order_id) from run_station where run_id = m.run_id and area_id = r.start_station_area_id and station_inout_type <> 114 ) as start_order_id,
                                    (select max(station_order_id) from run_station where run_id = m.run_id and area_id = r.end_station_area_id  and station_inout_type <> 114 ) as end_order_id,
                                    (select group_concat(distinct start_time) from run_station where run_id = m.run_id and area_id = r.start_station_area_id ) as start_time,
                                    (select group_concat(distinct start_time) from run_station where run_id = m.run_id and area_id = r.end_station_area_id  ) as end_time,
                                    (select min(prod_price) from opera_tickets where ticket_id = r.prod_id and cancel_flag = 0) as price,
                                    (select prod_price from opera_tickets_distrib where supplier_id = {$channel_id} and ticket_id = r.prod_id and cancel_flag = 0 and authority_status = 1 ) as price2,
                                    (select sale_type from opera_tickets_distrib where supplier_id = {$channel_id} and ticket_id = r.prod_id and cancel_flag = 0 and authority_status = 1 ) as sale_type
                    from opera_line p, run_main m, run_prod r     
                    where 
                      p.line_id = m.prod_id
                    and m.run_id = r.run_id
                    and p.line_id = r.parent_prod_id
                    and p.line_type in(" . $line_type . ")
                    and p.cancel_flag = 0
                    and p.line_id not in(" . implode(',',$line_id_arr) . ") {$not_include_sql}
                    and p.is_onsale = 1
                    and p.if_disabled = 0
                    AND (SELECT ticket_type FROM opera_tickets WHERE ticket_id = r.prod_id AND cancel_flag = 0) = 1
                    and r.cancel_flag = 0
                    and m.run_status = 138
                    and m.run_date >= '" . $now_date . "'
                    and m.run_date = '" . $run_date . "' ".
//                    "
//                    and case p.sale_expired_type when 321 then if(date_add(date_add(m.run_date,interval -1 day), interval (substring_index(p.sale_expired_time,':',1)*60 + substring_index(p.sale_expired_time,':',-1)) minute) > now(),0=0,0=1)
//                                   when 322 then if(date_add(m.run_date, interval m.run_minutes -(substring_index(p.sale_expired_time,':',1)*60 + substring_index(p.sale_expired_time,':',-1)) minute)>= now(),0=0,0=1)
//                                   else 0=0 end
//                    ".   comment by qius on 20170517 暂时不引入提前关班的情况
                    "
                    and IF(TRIM(" . $start_area1 . ")=0,0=0,(r.start_station_area_id in(" . $start_area . ")))
                    and IF(TRIM(" . $end_area1 . ")=0,0=0,(r.end_station_area_id in(" . $end_area . ")))
                    ) a
        group by a.prod_id,a.start_area_id,a.end_area_id,a.run_date,a.start_time,a.end_time,a.seat_type
        order by a.start_time asc
        LIMIT " . $start_row . "," . $page_size;

    writeLog($sql);//test
    $result1 = $pdo->query($sql);
    $list = $result1->fetchAll(PDO::FETCH_ASSOC);
    if (count($list) == 0) {
        $result['list'] = array();
        $result['totalnum'] = 0;
        return $result;
    }
    $count_sql = "select count(run_id) as total_count
                  from 
                      (select  
                            run_id
                      from (
                            select distinct m.run_id,
                                            p.line_id as prod_id,
                                            r.start_station_area_id as start_area_id,
                                            r.end_station_area_id as end_area_id,
                                            r.seat_type,
                                            (select group_concat(distinct start_time) from run_station where run_id = m.run_id and area_id = r.start_station_area_id   ) as start_time,
                                            (select group_concat(distinct start_time) from run_station where run_id = m.run_id and area_id = r.end_station_area_id  ) as end_time
                            from opera_line p, run_main m, run_prod r
                            where
                              p.line_id = m.prod_id
                            and m.run_id = r.run_id
                            and p.line_id = r.parent_prod_id
                            and p.line_type in(" . $line_type . ")
                            and p.cancel_flag = 0
                            and r.cancel_flag = 0
                            and p.line_id not in(" . implode(',',$line_id_arr) . ") {$not_include_sql}
                            and p.is_onsale = 1
                            and p.if_disabled = 0
                            AND (SELECT ticket_type FROM opera_tickets WHERE ticket_id = r.prod_id AND cancel_flag = 0) = 1
                            and m.run_status = 138
                            and m.run_date >= '" . $now_date . "'
                            and m.run_date = '" . $run_date . "' ".
//                            "
//                            and case p.sale_expired_type when 321 then if(date_add(date_add(m.run_date,interval -1 day), interval (substring_index(p.sale_expired_time,':',1)*60 + substring_index(p.sale_expired_time,':',-1)) minute) > now(),0=0,0=1)
//                                   when 322 then if(date_add(m.run_date, interval m.run_minutes -(substring_index(p.sale_expired_time,':',1)*60 + substring_index(p.sale_expired_time,':',-1)) minute)>= now(),0=0,0=1)
//                                   else 0=0 end
//                            ".
                            "
                            and IF(TRIM(" . $start_area1 . ")=0,0=0,(r.start_station_area_id in(" . $start_area . ")))
                            and IF(TRIM(" . $end_area1 . ")=0,0=0,(r.end_station_area_id in(" . $end_area . ")))
                            ) a
                      group by a.prod_id,a.start_area_id,a.end_area_id,a.start_time,a.end_time,a.seat_type) as b";
    $result2 = $pdo->query($count_sql);
    $list_count = $result2->fetchAll(PDO::FETCH_ASSOC);
    $list_count = $list_count[0]['total_count'];
    $result['list'] = $list;
    $result['totalnum'] = $list_count;
    return $result;
}

/**
 * Function Description:筛选可查询站点
 * Function Name: execStation
 * @param $area
 * @param $line_type
 *
 * @return array
 *
 * @author 张帅
 */
function execStation($area,$line_type){
    //筛选可查询站点
    $user_id = $_COOKIE['user_id'];
    global $hotel_authority;
    $poi = getPoiInfoArr();
    $poi_info = $poi['poi_info_array'];
    if($area == '0'){
        if ($line_type == '256' && isset($hotel_authority[$user_id])) {
            if (is_array($hotel_authority[$user_id])) {
                foreach ($hotel_authority[$user_id] as $v) {
                    $list[] = $v;
                }
            } else {
                $list[] = $hotel_authority[$user_id];
            }
            $list[] = 3240;
            $list[] = 100437;
            $area_str = implode(',',$list);
        } else {
            $area_str = $area;
        }
    }else{
        if (($line_type == '256' && isset($hotel_authority[$user_id])) || $poi_info[$area]['son_area_id'] == '') {
            $area_str = $area;
        }else{
            $area_str = $area . ',' . $poi_info[$area]['son_area_id'];
        }
    }
    return $area_str;

}

/**
 * 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  
                        (select help_topic_id from mysql.help_topic where help_topic_id between 1 and 20 ) as sequence,
                        (select help_topic_id from mysql.help_topic where help_topic_id between 1 and 20 )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     
                                    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 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)
                )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.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_array = getPoiInfo($area_id_array);
    } else {
        $poi_info_array = array();
    }

    //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;
}