ZZCS/coding/application/orderManager/lineProduct.class.php

<?php

/**
 * Created by PhpStorm.
 * User: 张帅
 * Date: 2016/10/15
 * Time: 17:50
 */

/**
 * Class singleProduct   线路选择产品相关
 */
class lineProduct extends base
{
    /**
     * 获取单一线路产品
     */
    public function getProductList($param)
    {
        #region 获取渠道权限
        $user_id = $this->user_id;
        $opera_org_id_sql = "select opera_org_id,main_corp_id from base_user where id = " . $user_id . " and cancel_flag = 0";
        $opera_org_id_result = $this->query($opera_org_id_sql);
        $opera_org_id = $opera_org_id_result[0]['opera_org_id'];
        $main_corp_id = $opera_org_id_result[0]['main_corp_id'];
        $and_sql = '';
//        if ($opera_org_id != '') {
//            $and_sql .= " and a.org_id in (" . $opera_org_id . ") ";
//        }

        if($main_corp_id != 0){
            $and_sql .= " p.main_corp_id in ( 0, " . $main_corp_id . ") ";
        } else {
            $and_sql .= " p.main_corp_id in ( 0, 1 ) ";
        }

        $to_main_corp_id = $main_corp_id == 0 ? 1 : $main_corp_id;
        $select_agent_sql = " SELECT line_id,ticket_id FROM opera_tickets_agent WHERE to_main_corp_id={$to_main_corp_id} AND authority_status=1 AND is_onsale=1 ";
        $agent_array = $this->query($select_agent_sql);
        $agent_ticket_array = array();
        foreach( $agent_array as $agent_info ) {
            $agent_ticket_array[] = $agent_info["ticket_id"];
        }
        if( count($agent_ticket_array) > 0 ) {
            $and_sql = "( ".$and_sql." OR r.prod_id IN(".implode(",", $agent_ticket_array).") )";
        }
        #endregion

        $page_size = isset($param['page_size']) ? trim($param['page_size']) : false;//每页展示条数
        $current_page = isset($param['current_page']) ? trim($param['current_page']) : false;//当前页
        $line_code = isset($param['line_code']) ? (empty($param['line_code']) ? '' : trim($param['line_code'])) : false;//线路编码或名称
        $start_area = isset($param['start_area']) ? (empty($param['start_area']) ? "''" : "'" . trim($param['start_area']) . "'") : false;//出发地
        $end_area = isset($param['end_area']) ? (empty($param['end_area']) ? "''" : "'" . trim($param['end_area']) . "'") : false;//目的地
        $run_date = isset($param['run_date']) ? (empty($param['run_date']) ? date('Y-m-d', time()) : trim($param['run_date'])) : false;//出发日期
        $line_type = isset($param['line_type']) ? trim($param['line_type']) : false;//线路类型
        $now_date = date('Y-m-d', time());
        if (false===$line_code || false===$start_area || false===$end_area || false===$run_date || false===$line_type) {
            $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 p.ORG_ID IN( ' . $_COOKIE["opera_org_id"] . ' ) ';
//        }

        $search_line_sql = " and p.line_type = " . $line_type;
        if( $line_type == 255  ) {
            $search_line_sql = " and p.line_type IN (255,368)";
        }
        $search_line_sql .= " and ".$and_sql;
        $start_row = ($current_page - 1) * $page_size;
        $run_x='run_'.date('Ym',strtotime($run_date));
        $sql = "select  a.run_id,
                        a.prod_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,
                        (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,concat(substring_index(a.start_time,',',1),'-',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 seat,
                        max(a.price) as price,
                        (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 stock
                from (
                        select distinct m.run_id,
                                        p.org_id,
                                        p.main_corp_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,
                                        (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 max(cus_price) from opera_tickets where ticket_id = r.prod_id and cancel_flag = 0) as price
                        from opera_line p, run_main m, run_prod r, opera_tickets as ot     
                        where 
                          p.line_id = m.prod_id
                        and m.run_id = r.run_id
                        and p.line_id = r.parent_prod_id
                        and ot.ticket_id = r.prod_id
                        {$search_line_sql}
                        and p.cancel_flag = 0
                        and p.is_onsale = 1
                        and p.if_disabled = 0
                        and ot.is_onsale = 1
                        and r.cancel_flag = 0" . $extra_sql . "
                        and m.run_status not in(137,142)
                        and m.run_date = '" . $run_date . "'
                        and IF('" . $line_code . "'='',0=0,(p.line_name LIKE CONCAT('%','" . $line_code . "','%') OR p.line_code LIKE CONCAT('%','" . $line_code . "','%')))
                        and r.start_station_area_id in (select id from base_area where cancel_flag = 0 and area_name like CONCAT('%'," . $start_area . ",'%'))
                        and r.end_station_area_id in (select id from base_area where cancel_flag = 0 and area_name like CONCAT('%'," . $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.prod_name,run_date,start_time,end_time
                LIMIT " . $start_row . "," . $page_size;
        /*原where条件
         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 = " . $line_type . "
                        and p.cancel_flag = 0
                        and p.is_onsale = 1
                        and p.if_disabled = 0
                        and r.cancel_flag = 0" . $extra_sql . "
                        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('" . $line_code . "'='',0=0,(p.line_name LIKE CONCAT('%','" . $line_code . "','%') OR p.line_code LIKE CONCAT('%','" . $line_code . "','%')))
                        and r.start_station_area_id in (select id from base_area where cancel_flag = 0 and area_name like CONCAT('%'," . $start_area . ",'%'))
                        and r.end_station_area_id in (select id from base_area where cancel_flag = 0 and area_name like CONCAT('%'," . $end_area . ",'%'))
                        ) a
         */
        zzcsUtils::writeLog($sql);
        $product_list = $this->query($sql);
        if (count($product_list) == 0) {
            $json['code'] = '0';
            $json['info'] = '没有符合的线路';
            $json['product_list'] = array();
            $json['page']['page_size'] = $page_size;
            $json['page']['current_page'] = "0";
            $json['page']['total_count'] = "0";
            $json['page']['total_page'] = "0";
            return $json;
        }
        foreach ($product_list as $k => $v) {
            if (!$v['price']) {
                $product_list[$k]['price'] = '0.00';
            }
            $tmp_stock=999;
            //库存判断
            $sql="select station_order_id,(select res_name from base_resource where res_id=station_res_id) station_name from run_station where area_id= {$v['start_area_id']} and run_id ={$v['run_id']}";
            $start_area_list = $this->query($sql);
            $sql="select station_order_id,(select res_name from base_resource where res_id=station_res_id) station_name from run_station where area_id= {$v['end_area_id']} and run_id ={$v['run_id']}";
            $end_area_list = $this->query($sql);
            foreach($start_area_list as $start_k=>$start_v){
                foreach($end_area_list as $end_k=>$end_v){
                    if($start_v<$end_v) {
                        $tmp_cnt_use = $end_v['station_order_id'] - $start_v['station_order_id'];
                        $sql = "select count(1) ct from  (select count(1) as cnt from {$run_x} where run_id={$v['run_id']} and order_id>={$start_v['station_order_id']}  and order_id <{$end_v['station_order_id']} and seat_type=72 and seat_status=0 GROUP BY seat_seq_id) a where a.cnt={$tmp_cnt_use}";
                        $tmp_cnt = $this->query($sql);
                        $product_list[$k]['station_stock_list'][] = array(
                            'station_name' => $start_v['station_name'] . '——' . $end_v['station_name'],
                            'stock' => $tmp_cnt[0]['ct'],
                    );
                        if($tmp_stock>=$tmp_cnt[0]['ct']){
                            $tmp_stock=$tmp_cnt[0]['ct'];
                        }
                    }
                }
            }
            $product_list[$k]['stock']=$tmp_stock;
            if ($tmp_stock < 0) {
                $product_list[$k]['stock'] = 0;
            }
            if ($v['stock'] > 20) {
                $product_list[$k]['stock'] = '充足';
            }
        }
        $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
                                {$search_line_sql}
                                and p.cancel_flag = 0
                                and r.cancel_flag = 0
                                and p.is_onsale = 1
                                and p.if_disabled = 0
                                and m.run_status not in(137,142)
                                and m.run_date = '" . $run_date . "'
                                and IF('" . $line_code . "'='',0=0,(p.line_name LIKE CONCAT('%','" . $line_code . "','%') OR p.line_code LIKE CONCAT('%','" . $line_code . "','%')))
                                and r.start_station_area_id in (select id from base_area where cancel_flag = 0 and area_name like CONCAT('%'," . $start_area . ",'%'))
                                and r.end_station_area_id in (select id from base_area where cancel_flag = 0 and area_name like CONCAT('%'," . $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";
        $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['product_list'] = $product_list;
        $json['page']['page_size'] = $page_size;
        $json['page']['current_page'] = $current_page;
        $json['page']['total_count'] = $total_count;
        $json['page']['total_page'] = $total_page;
        return $json;
    }

    /**
     * 获取巴士线路产品基础配置
     */
    public function getProductListBase($param)
    {
        $line_type = isset($param['line_type']) ? trim($param['line_type']) : false;//线路类型
        if (!$line_type) {
            $json['code'] = '2';
            $json['info'] = '缺少必要参数';
            return $json;
        }

        $sql = "SELECT
                    area_id,
                    inout_type,
                    (select area_name from base_area where id = area_id LIMIT 1) as area_name
                FROM
                    opera_line AS l
                LEFT JOIN opera_station AS s ON l.line_id = s.line_id
                WHERE
                    l.cancel_flag = 0
                AND l.if_disabled = 0
                AND l.is_onsale = 1
                AND s.cancel_flag = 0
                AND l.line_type = " . $line_type . "
                GROUP BY
                    area_id,inout_type";
        $area_list = $this->query($sql);
        foreach ($area_list as $k => $v) {
            if ($v['inout_type'] == 108 || $v['inout_type'] == 108) {
                $area_one['id'] = $v['area_id'];
                $area_one['name'] = $v['area_name'];
                $start_area[$v['area_id']] = $area_one;
            }
            if ($v['inout_type'] == 109 || $v['inout_type'] == 110) {
                $area_one['id'] = $v['area_id'];
                $area_one['name'] = $v['area_name'];
                $end_area[$v['area_id']] = $area_one;
            }
        }
        $json['code'] = '0';
        $json['info'] = '返回可选地点列表成功';
        $json['start_area'] = array_merge($start_area);
        $json['end_area'] = array_merge($end_area);
        return $json;
    }

    /**
     * 获取组合线路产品线
     * @return mixed
     */
    function groupProductHeadInfo()
    {
        //得到产品线
        $res = $this->productLine();
        if ($res) {
            //无信息
            $json["code"] = '0';
            $json["info"] = '产品线返回';
            $json["product_line"] = $res;
            return $json;
        } else {
            $json["code"] = '1';
            $json["info"] = '数据库异常';
            return $json;
        }
    }

    /**
     * hyk
     * 组合线路产品列表
     * @param $web_data
     * @return array
     */
    function groupProductList($web_data)
    {
        //hyk
        //组合线路产品列表
        $run_date = isset($web_data["run_date"]) ? trim($web_data["run_date"]) : false;
        $current_page = isset($web_data['current_page']) ? trim($web_data['current_page']) : false;
        $page_size = isset($web_data['page_size']) ? trim($web_data['page_size']) : false;
        $product_type = isset($web_data['product_type']) ? trim($web_data['product_type']) : false;
        $line = isset($web_data['line']) ? trim($web_data['line']) : false;
        if ($product_type === false || $current_page === false || $page_size === false || $run_date === false || $product_type === false || $line === false) {
            //参数不完整;
            $json["code"] = '2';
            $json["info"] = "缺少必要参数";
            return $json;
        }
        if (empty($product_type)) {
            $product_type = 0;
        }
        $json = array();
        $sql = "call sp_choice_kicket_group (" . $this->user_id . "," . $current_page . "," . $page_size . ",'" . $line . "'," . $product_type . ",'" . $run_date . "')";
        zzcsUtils::writeLog($sql);
        $reset = $this->procQuery($sql);
        if ($reset === false) {
            $json["code"] = $reset['code'];
            $json["info"] = $reset['info'];
            return $json;
        } else {
            //数据查询成功
            $json["code"] = '0';
            $json["info"] = '组合产品列表';
            $json['page'] = array(
                'current_page' => $reset['data'][0][0]["currpage"],
                'total_page' => $reset['data'][0][0]["total_page"],
                'total_count' => $reset['data'][0][0]["total"],
                'page_size' => $reset['data'][0][0]["page_size"]
            );
            if (isset($reset['data'][1])) {
                $json["product_list"] = $reset['data'][1];
            } else {
                $json["product_list"] = array();
            }
            return $json;
        }
    }
}

?>