zzcx_line/coding/application/lineManager/groupLine.class.php

<?php

/**
 * Created by PhpStorm.
 * User: Steven
 * Date: 2016/10/13
 * Time: 16:18
 * 组合线路票种相关
 */
class groupLine extends base
{
    /**
     * 获取产品线  所有用来拼接组合线路的单一线路  获取运营公司
     * @return mixed
     */
    function getProduct()
    {
        $res = $this->productLine();  //获取产品线

        $extra_sql = "";
        if( isset($_COOKIE["opera_org_id"]) && mb_strlen($_COOKIE["opera_org_id"]) > 0 ) {
            $extra_sql .= ' AND ORG_ID IN( '.$_COOKIE["opera_org_id"].' ) ';
        }
        if (isset($_COOKIE["user_main_corp"]) && mb_strlen($_COOKIE["user_main_corp"]) > 0 && $_COOKIE["user_main_corp"] > 0 ) {
            $extra_sql .= ' AND MAIN_CORP_ID IN( '.$_COOKIE["user_main_corp"].' ) ';
        }
        //获取线路列表
        $sql1 = "SELECT line_id,line_name  FROM  opera_line WHERE CANCEL_FLAG=0 AND LINE_TYPE<>316".$extra_sql;
        $res_line = $this->query($sql1);
        if ($res_line) {
            $code = '0';
            $msg = "获取列表基本信息成功";
        } else {
            $code = '1';
            $msg = "获取列表基本信息失败";
        }
        $res_company = $this->getSupply();  //获取运营公司
        $result['code'] = (string)$code;
        $result['info'] = $msg;
        $result['product'] = $res;
        $result['res_line'] = $res_line;
        $result['company'] = $res_company;
        return $result;
    }


    /**
     * 根据line_id获取上车点
     * @param $param
     * @return mixed
     */
    public function getStartByLine($param)
    {
        $line_id = isset($param['line_id']) ? $param['line_id'] : false;
        if (false === $line_id) {
            $result['code'] = '2';
            $result['info'] = "缺少必要参数";
            return $result;
        }
        if ($line_id == -1) {   //处理提交的上车站点是“请选择”选项
            $result['code'] = '0';
            $result['info'] = "请选择上车站点";
            $result['station'] = array();
            return $result;
        }
        //sql：根据线路ID查询可行的上车点（站点和应用poi）  其中res_id=（站点或应用POI标记，站点ID）   0：不限；1：站点ID； 2：应用POI
        $sql = "SELECT concat(1,',',a.res_id) as res_id,
                    b.res_name     
                FROM opera_station a left join
                     base_resource b on a.res_id = b.res_id and b.res_type_id = 24 
                WHERE a.CANCEL_FLAG=0 AND a.LINE_ID=$line_id AND a.INOUT_TYPE in(108,109)
                union all
                SELECT distinct concat(2,',',a.area_id) as res_area_flag,
                       b.area_name     
                FROM opera_station a left join
                     base_area b on a.area_id = b.id
                WHERE a.CANCEL_FLAG=0 AND a.LINE_ID=$line_id AND a.INOUT_TYPE in(108,109)";
        $res = $this->query($sql);
        array_unshift($res, array('res_id' => '0,0', 'res_name' => '不限'));   //给数组的开头插入一个元素
        if ($res) {
            $code = 0;
            $msg = '获取' . $line_id . '线路对应上车站成功';
        } else {
            $code = 1;
            $msg = '获取' . $line_id . '线路对应上车站失败';
        }
        $result['code'] = (string)$code;
        $result['info'] = $msg;
        $result['station'] = $res;
        return $result;
    }

    /**
     * 根据上车站获取下车站点
     * @param $param
     * @return mixed
     */
    public function getEndStation($param)
    {
        $para_line_id = isset($param['line_id']) ? $param['line_id'] : false;
        $res_id = isset($param['res_id']) ? $param['res_id'] : false;
        if ($para_line_id === false || $res_id === false) {
            $result['code'] = '2';
            $result['info'] = "缺少必要参数";
            return $result;
        }
        if ($res_id == -1) {
            $result['code'] = '0';
            $result['info'] = "请选择上车站点";
            return $result;
        }
        $para = explode(",", $res_id);
        $para_flag = $para[0];
        $para_start_id = $para[1];
        $sql = "CALL SP_GET_END_STATION($para_line_id,$para_flag,$para_start_id)";
        zzcsUtils::writeLog($sql);
        $res = $this->procQuery($sql);
        if ($res['code'] != 0) {  //存储过程执行出错
            $json["code"] = (string)$res['code'];
            $json["info"] = $res['info'];
            return $json;
        }
        //数据查询成功
        $json["code"] = '0';
        $json["info"] = '下车站点获取成功';
        $json["station"] = $res['data'][0];
        return $json;

    }

    /**
     * 添加组合线路
     * @param $param
     * @return mixed
     */
    public function addGroupLine($param)
    {
        //{行程第几日，第几条，线路ID，出发站点或应用POI标志，出发res_id，目的站点或应用POI标志，目的RES_ID,出发时间} 站点或应用POI标志： 1：站点 2：应用POI, 出发时间需要换算成距离0点的分钟数
        $user_id = $this->user_id;
        $line_name = isset($param['line_name']) ? $param['line_name'] : false;  //线路名称
        $line_code = isset($param['line_code']) ? $param['line_code'] : false;   //线路编号
        $producttype = isset($param['product_type']) ? $param['product_type'] : false;   //产品线
        $expiredtype = isset($param['expired_type']) ? $param['expired_type'] : false;  //售卖截止类型
        $expiredtime = isset($param['expired_time']) ? $param['expired_time'] : false;   //售卖截止时间
        $orgid = isset($param['org_id']) ? $param['org_id'] : false;                        //运营公司
        $station_list = isset($param['station_list']) ? $param['station_list'] : false;    //行程字符串
        if ($line_name === false || $line_code === false || $producttype === false || $expiredtype === false || $expiredtime === false || $orgid === false || $station_list === false) {
            $result['code'] = '2';
            $result['info'] = "缺少必要参数";
            return $result;
        }
        $sql_check = 'SELECT line_id count from opera_line WHERE cancel_flag = 0 AND LINE_CODE=\'' . $line_code . '\'';
        $res_check = $this->query($sql_check);
        if (count($res_check) > 0) {
            $result['code'] = '3';
            $result['info'] = "线路编号已存在";
            return $result;
        }

        $station_list = str_replace("},", "}", $station_list);
        $sql = "CALL SP_ADD_LINE_GROUP(" . $user_id . ",'" . $line_name . "','" . $line_code . "'," . $producttype . "," . $expiredtype . ",'" . $expiredtime . "','" . $orgid . "','" . $station_list . "')";
        zzcsUtils::writeLog($sql);
        $res = $this->procQuery($sql);
        if ($res['code'] != 0) //存储过程执行出错
        {
            $result['code'] = (string)$res['code'];
            $result['info'] = $res['info'];
            return $result;
        }
        $result['code'] = '0';
        $result['info'] = '组合线路添加成功';
        $result['line_id'] = $res['data'][0][0]['line_id'];
        return $result;
    }

    /**
     * 根据line_id获取组合线路基本信息
     * @param $param
     * @return string
     */
    public function getGroupLineByID($param)
    {
        $line_id = isset($param['line_id']) ? $param['line_id'] : false;
        if ($line_id === false) {
            $result['code'] = '2';
            $result['info'] = "缺少必要参数";
            return $result;
        }
        //sql:根据线路ID 获取组合线路的基本信息
        $sql = "SELECT a.line_id,b.line_name,b.line_code,b.product_type,c.max_day_num,b.org_id,b.sale_expired_type,b.sale_expired_time,a.sub_line_id,a.day_num,a.seq_id,a.start_choice_flag,a.start_res_id,a.start_area_id,a.end_choice_flag,a.end_order_id,a.end_res_id,a.end_area_id,a.start_time,a.start_minutes  
                FROM opera_line_group a,opera_line b,(select line_id,max(day_num) as max_day_num from opera_line_group group by line_id) c 
                WHERE a.line_id = b.line_id and a.line_id = c.line_id AND a.LINE_ID = $line_id ORDER BY DAY_NUM,SEQ_ID";
        $res = $this->query($sql);
        $arr = array();
        if (!$res) {
            $result['code'] = '1';
            $result['info'] = "获取{$line_id}线路基本信息失败";
        }
        $arr['line_id'] = $res[0]['line_id']; //线路ID
        $arr['line_name'] = $res[0]['line_name']; //线路名称
        $arr['line_code'] = $res[0]['line_code'];//线路代码
        $arr['product_type'] = $res[0]['product_type'];   //产品线
        $arr['max_day_num'] = $res[0]['max_day_num']; //行程
        $arr['org_id'] = $res[0]['org_id'];   //运营公司
        $arr['sale_expired_type'] = $res[0]['sale_expired_type']; //售卖截止类型
        $arr['sale_expired_time'] = $res[0]['sale_expired_time']; //售卖截止时间
        foreach ($res as $k => $v) {
            $line['day_num'] = $v['day_num']; //第几天
            $line['seq_id'] = $v['seq_id']; //顺序
            $line['sub_line_id'] = $v['sub_line_id']; //组合线路的实际线路ID
            #region 根据线路ID获取上车站点列表
            $param_line_id['line_id'] = $v['sub_line_id'];
            $base_start_station = $this->getStartByLine($param_line_id);
            $line['start_station'] = $base_start_station['station'];
            #endregion
            $line['start_time'] = $v['start_time'];   //出发时间
            //出发站点 start_choice_flag 区分是站点还是应用POI   start_res_id=站点类型，站点ID；   站点类型：0：不限；1：站点ID  2:应用POI
            $line['start_res_id'] = ($v['start_choice_flag'] == 0) ? '0,0' : (($v['start_choice_flag'] == 1) ? ($v['start_choice_flag'] . ',' . $v['start_res_id']) : (($v['start_choice_flag'] == 2) ? ($v['start_choice_flag'] . ',' . $v['start_area_id']) : 0)); //出发站点 start_choice_flag 区分是站点还是应用POI  1：选择站点， 2：选择应用POI
            #region 根据线路ID和上车站点获取下车站点列表
            $param_res['line_id'] = $v['sub_line_id'];
            $param_res['res_id'] = $line['start_res_id'];
            $end_station = $this->getEndStation($param_res);
            $line['end_station'] = $end_station['station'];
            #endregion
            //目的地站点 start_choice_flag 区分是站点还是应用POI   end_res_id=站点类型，站点ID；   站点类型：1：站点ID  2:应用POI
            //$line['end_res_id'] = ($v['end_choice_flag'] == 0) ? '0,0' : (($v['end_choice_flag'] == 1) ? ($v['end_choice_flag'] . ',' . $v['end_res_id']) : (($v['end_choice_flag'] == 2) ? ($v['end_choice_flag'] . ',' . $v['end_area_id']) : 0));  //目的地站点
            $line['end_res_id'] = ($v['end_choice_flag'] == 1) ? $v['end_choice_flag'] . ',' . $v['end_res_id'] : (($v['end_choice_flag'] == 2) ? $v['end_choice_flag'] . ',' . $v['end_area_id'] : 0);
            $arr['line']["{$v['day_num']}"][] = $line;
        }
        $sql1 = "SELECT line_id,line_name  FROM  opera_line WHERE CANCEL_FLAG=0 AND LINE_TYPE<>316";  //获取线路列表
        $res_line = $this->query($sql1);

        $result['code'] = '0';
        $result['info'] = "获取{$line_id}线路基本信息成功";
        $result['line_info'] = $arr;
        //其他基本信息
        $result['res_line'] = $res_line;    //获取线路
        $result['product'] = $this->productLine();  //获取产品线
        $result['company'] = $this->getSupply();  //获取运营公司/
        return $result;
    }

    /**
     * 组合线路基本信息的修改
     * @param $param
     * @return string
     */
    public function updateGroupLine($param)
    {
        //{行程第几日，第几条，线路ID，出发站点或应用POI标志，出发res_id，目的站点或应用POI标志，目的RES_ID,出发时间} 站点或应用POI标志： 1：站点 2：应用POI, 出发时间需要换算成距离0点的分钟数
        $line_id = isset($param['line_id']) ? $param['line_id'] : false;   //线路
        $line_name = isset($param['line_name']) ? $param['line_name'] : false;  //线路名称
        $line_code = isset($param['line_code']) ? $param['line_code'] : false;   //线路编号
        $producttype = isset($param['product_type']) ? $param['product_type'] : false;   //产品线
        $expiredtype = isset($param['expired_type']) ? $param['expired_type'] : false;  //售卖截止类型
        $expiredtime = isset($param['expired_time']) ? $param['expired_time'] : false;   //售卖截止时间
        $orgid = isset($param['org_id']) ? $param['org_id'] : false;                        //运营公司
        $station_list = isset($param['station_list']) ? $param['station_list'] : false;    //行程字符串
        if ($line_name === false || $line_code === false || $producttype === false || $expiredtype === false || $expiredtime === false || $orgid === false || $station_list === false) {
            $result['code'] = '2';
            $result['info'] = "缺少必要参数";
            return $result;
        }
        $sql_check = 'SELECT line_id count from opera_line WHERE cancel_flag = 0 AND LINE_CODE=\'' . $line_code . '\' AND LINE_ID<>' . $line_id;
        $res_check = $this->query($sql_check);
        if (count($res_check) > 0) {
            $result['code'] = '3';
            $result['info'] = "线路编号已存在";
            return $result;
        }

        $station_list = str_replace("},", "}", $station_list);
        //station_list 格式{行程第几日，线路ID，出发站点或应用POI标志，出发res_id，目的站点或应用POI标志，目的RES_ID,出发时间} 站点或应用POI标志： 1：站点 2：应用POI, 出发时间需要换算成距离0点的分钟数
        $sql = "CALL sp_update_line_group(" . $this->user_id . "," . $line_id . ",'" . $line_name . "','" . $line_code . "'," . $producttype . "," . $expiredtype . ",'" . $expiredtime . "','" . $orgid . "','" . $station_list . "')";
        zzcsUtils::writeLog($sql);
        $res = $this->procQuery($sql);
        if ($res['code'] != 0) {  //存储过程执行出错
            $result['code'] = (string)$res['code'];
            $result['info'] = $res['info'];
            return $result;
        }
        $result['code'] = '0';
        $result['info'] = '组合线路修改成功';
        return $result;
    }

    /**
     * 张帅
     * 组合线路线路列表
     */
    public function getLineList($post)
    {
        $page_size = isset($post['page_size']) ? trim($post['page_size']) : false;
        $current_page = isset($post['current_page']) ? trim($post['current_page']) : false;
        $line_code = isset($post['line_code']) ? (empty($post['line_code']) ? "''" : "'" . trim($post['line_code']) . "'") : false;
        $supply_id = isset($post['supply_id']) ? trim($post['supply_id']) : false;
        $line_status = isset($post['line_status']) ? trim($post['line_status']) : false;
        $line_type = isset($post['line_type']) ? trim($post['line_type']) : false;
        $product_type = isset($post['product_type']) ? trim($post['product_type']) : false;
        $start_row = ($current_page - 1) * $page_size;
        if ($page_size === false || $current_page === false || $line_code === false || $supply_id === false || $line_type === false || $product_type === false) {
            $json['code'] = '2';
            $json['info'] = '缺少必要参数';
            return $json;
        }
        $extra_sql = "";
        if( isset($_COOKIE["opera_org_id"]) && mb_strlen($_COOKIE["opera_org_id"]) > 0 ) {
            $extra_sql .= ' AND l.org_id IN( '.$_COOKIE["opera_org_id"].' ) ';
        }
        if (isset($_COOKIE["user_main_corp"]) && mb_strlen($_COOKIE["user_main_corp"]) > 0 && $_COOKIE["user_main_corp"] > 0 ) {
            $extra_sql .= ' AND MAIN_CORP_ID IN( ' . $_COOKIE["user_main_corp"] . ' ) ';
        }

        $sql = "SELECT
                    l.line_id,l.line_code,l.line_name,l.product_type,
                    (SELECT type_name FROM dict_type WHERE id = l.product_type) as product_type_name,
                    IFNULL((SELECT type_name FROM dict_type WHERE id = l.line_sub_type),'') AS line_sub_type,
                    IFNULL((SELECT supplier_name FROM base_supplier WHERE id = l.org_id ), '' ) AS org_name,
                    CASE l.if_disabled WHEN 0 THEN '启用' ELSE '停用' END AS disabled_status,
                    count(t.ticket_id) AS cnt
                FROM
                    opera_line AS l LEFT JOIN       
                    (SELECT ticket_id,line_id FROM opera_tickets WHERE cancel_flag = 0) AS t
                    ON l.line_id = t.line_id
                WHERE
                    l.cancel_flag = 0
                AND l.line_type = " . $line_type . "
                AND IF(TRIM(" . $line_code . ")='',0=0,(l.line_code LIKE CONCAT('%'," . $line_code . ",'%') OR l.line_name LIKE CONCAT('%'," . $line_code . ",'%')))
                AND IF(TRIM(" . $supply_id . ")=-1,0=0,l.org_id = " . $supply_id . ")
                AND IF(TRIM(" . $product_type . ")=-1,0=0,l.product_type = " . $product_type . ")
                AND IF(TRIM(" . $line_status . ")=-1,0=0,l.if_disabled = " . $line_status . ")".$extra_sql."
                GROUP BY
                    l.line_id,l.line_code,l.line_name,l.line_sub_type,l.org_id,l.if_disabled
                ORDER BY
                    l.line_id DESC
                LIMIT " . $start_row . "," . $page_size;
        $line_list = $this->query($sql);

        if (count($line_list) == 0) {
            $json['code'] = '0';
            $json['info'] = '没有符合的线路';
            $json['line_list'] = array();
            $json['page']['page_size'] = $page_size;
            $json['page']['current_page'] = $current_page;
            $json['page']['total_count'] = '0';
            $json['page']['total_page'] = '0';
            return $json;
        }

        $count_sql = "SELECT
                            count(line_id) as total_count
                        FROM
                            opera_line
                        WHERE
                            cancel_flag = 0
                        AND line_type = " . $line_type . "
                        AND IF(TRIM(" . $line_code . ")='',0=0,(line_code LIKE CONCAT('%'," . $line_code . ",'%') OR line_name LIKE CONCAT('%'," . $line_code . ",'%')))
                        AND IF(TRIM(" . $supply_id . ")=-1,0=0,org_id = " . $supply_id . ")
                        AND IF(TRIM(" . $product_type . ")=-1,0=0,product_type = " . $product_type . ")
                        AND IF(TRIM(" . $line_status . ")=-1,0=0,if_disabled = " . $line_status . ") ".$extra_sql;
        zzcsUtils::writeLog($count_sql);
        $total_count = $this->query($count_sql);
        $total_count = $total_count[0]['total_count'];
        $total_page = ceil($total_count / $page_size);
        $json['code'] = '0';
        $json['info'] = '返回线路列表成功符合的线路';
        $json['line_list'] = $line_list;
        $json['page']['page_size'] = (string)$page_size;
        $json['page']['current_page'] = (string)$current_page;
        $json['page']['total_count'] = (string)$total_count;
        $json['page']['total_page'] = (string)$total_page;
        return $json;
    }
}