ZZCS/coding/application/carDispatchManager/singleDispatch.class.php

<?php
//author:fuhc
//date:20161027
//车次调度类

class singleDispatch extends base
{

    private $company_list;  //公司列表

    // 得到车次调度列表
    public function getRunList($web_data)
    {
        $user_id = $this->user_id;
        $dispatch_status = isset($web_data['dispatch_status']) ? $web_data['dispatch_status'] : 0;
        $sort_id = isset($web_data['sort_id']) ? $web_data['sort_id'] : 0;
        $current_page = isset($web_data['current_page']) ? $web_data['current_page'] : 1;
        $page_size = isset($web_data['page_size']) ? $web_data['page_size'] : 10;
        $start_area = isset($web_data['start_area']) ? $web_data['start_area'] : '';
        $end_area = isset($web_data['end_area']) ? $web_data['end_area'] : '';
        $line = isset($web_data['line']) ? $web_data['line'] : '';
        $start_date = isset($web_data['start_date']) ? $web_data['start_date'] : '';
        $end_date = isset($web_data['end_date']) ? $web_data['end_date'] : '';
        $bus_type = isset($web_data['bus_type']) ? $web_data['bus_type'] : '0';

        //得到车次调度列表
        $sql = "CALL SP_GET_RUN_NO_LIST_XM({$user_id},{$current_page},{$page_size},'{$start_area}','{$end_area}','{$line}','{$start_date}','{$end_date}',{$dispatch_status},{$bus_type},{$sort_id})";
        zzcsUtils::writeLog($sql);
        $result = $this->procQuery($sql);
        $data = array();
        if ($result === false) {
            $data['code'] = '500';
            $data['info'] = '数据库存储过程错误';
            return $data;
        }
        $data['code'] = "0";
        $data['info'] = '获得车次调度列表成功';
        if ($result['code'] != "0") {
            $data['code'] = $result['code'];
            $data['info'] = $result['info'];
            return $data;
        }
        $data['page']['current_page'] = $result['data'][0][0]['currpage'];
        $data['page']['page_size'] = $result['data'][0][0]['page_size'];
        $data['page']['total_page'] = $result['data'][0][0]['total_page'];
        $data['page']['total_count'] = $result['data'][0][0]['total'];
        foreach ($result['data'][1] as $k => $v) {
            $tv = (string)$v['send_status'];
            $data['status_count'][$tv] = $v['count'];
        }
        // 线路类型
        $data['bus_group_list'] = array(
            array('group_id' => '1', 'group_name' => '苏州线A'),
            array('group_id' => '2', 'group_name' => '苏州线B'),
            array('group_id' => '3', 'group_name' => '周庄线'),
            array('group_id' => '4', 'group_name' => '杭州线')
        );

        $data['run_list'] = isset($result['data'][2]) ? $result['data'][2] : array();
        return $data;
    }

    //根据车牌号和公司查询车辆
    public function getVehicleList($param)
    {
        #region 获取渠道权限
        $user_id = $this->user_id;
        $opera_org_id_sql = "select opera_org_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'];
        if($opera_org_id == ''){
            $and_sql = '';
        }else{
            $and_sql = " and org_id in (" . $opera_org_id . ") ";
        }
        #endregion

        $keyword = isset($param['keyword']) ? "'" . $param['keyword'] . "'" : "''";
        $company = isset($param['company']) ? (empty($param['company']) ? "0" : $param['company']) : "0";
        $salecount = isset($param['salecount']) ? (empty($param['salecount']) ? "0" : $param['salecount']) : "0";
        $run_date = isset($param['run_date']) ? (empty($param['run_date']) ? "" : $param['run_date']) : "";
        $run_time = isset($param['run_time']) ? (empty($param['run_time']) ? "" : $param['run_time']) : "";
        if (date('Y-m-d', strtotime($run_date)) == date('Y-m-d', time())) {  //派今天的车
            $sql_car_list = "select bus_id as res_id,
                           bus_no as vihicle_number,
                           ifnull((select res_name from base_resource where res_id = base_bus.brand_id and res_type_id = 134 and cancel_flag =0),'') as vihicle_brand,
                           seat_desc as vihicle_seat,
                           ifnull((select supplier_name as res_name from base_supplier where id = base_bus.org_id and cancel_flag =0),'') as company_name,
                           seat_count,
                           bus_state as bus_status
                        from base_bus
                        where bus_state = 336 and cancel_flag = 0
                            and bus_id not in (	select b.send_bus_res_id 
                                                                    from run_main m, run_bus b 
                                                                    where m.run_id = b.run_id 
                                                                        and m.run_date = '{$run_date}'  
                                                                        and b.run_bus_status in (138,139,140)
                                                                        and b.send_bus_res_id > 0 
                                                                 )
                       and seat_count >= $salecount
                       " . $and_sql . "
                     and if(length(trim({$keyword}))=0,0=0,bus_no like concat('%',{$keyword},'%'))
                      and if({$company}<=0,0=0,org_id = {$company})  
                    order by seat_count desc;";
        } else {
            $sql_car_list = "select bus_id as res_id,
                           bus_no as vihicle_number,
                           ifnull((select res_name from base_resource where res_id = a.brand_id and res_type_id = 134 and cancel_flag =0),'') as vihicle_brand,
                           seat_desc as vihicle_seat,
                           ifnull((select res_name from base_resource where res_id = a.org_id and res_type_id = 18 and cancel_flag =0),'') as company_name,
                           seat_count,
                           bus_state as bus_status
                        from base_bus a
                        where a.bus_state = 336 and a.cancel_flag = 0
                            and a.bus_id not in (	select b.send_bus_res_id
                                                                        from run_main m, run_bus b, run_station s
                                                                        where m.run_id = b.run_id 
                                                                            and m.run_id = s.RUN_ID
                                                                            and m.prod_id = s.prod_id
                                                                            and m.run_date = '{$run_date}' 
                                                                            and m.prod_id > 0
                                                                            and b.send_bus_res_id > 0 
                                                                        group by m.run_id,b.send_bus_res_id
                                                                        having max(START_MINUTES) > (select min(prod_start_station_time_minutes) 
                                                                         from order_main 
                                                                         where parent_order_id = 0 and prod_start_station_date = '{$run_date}' 
                                                                           and parent_prod_id in (select line_id from opera_line where line_type = 316 and cancel_flag = 0) and order_valid_status = 1 and cancel_flag = 0)
                                           )
                      and seat_count >= $salecount
                      " . $and_sql . "
                     and if(length(trim({$keyword}))=0,0=0,bus_no like concat('%',{$keyword},'%'))
                      and if({$company}<=0,0=0,org_id = {$company})  
                    order by seat_count desc;";
        }
        zzcsUtils::writeLog($sql_car_list);
        $result = $this->query($sql_car_list);
        if (false === $result) {
            $json['code'] = '1';
            $json['info'] = '数据库错误';
        } else {
            $json['code'] = '0';
            $json['info'] = '返回数据成功';
            $json['vehicle_list'] = $result;
        }
        return $json;
    }

    //根据司机姓名手机号和公司查询司机
    public function getDriverList($param)
    {
        #region 获取渠道权限
        $user_id = $this->user_id;
        $opera_org_id_sql = "select opera_org_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'];
        if($opera_org_id == ''){
            $and_sql = '';
        }else{
            $and_sql = " and org_id in (" . $opera_org_id . ") ";
        }
        #endregion

        $keyword = isset($param['keyword']) ? "'" . $param['keyword'] . "'" : "''";
        $company = isset($param['company']) ? (empty($param['company']) ? '0' : $param['company']) : '0';
        $run_date = isset($param['run_date']) ? (empty($param['run_date']) ? "" : $param['run_date']) : "";
        $run_time = isset($param['run_time']) ? (empty($param['run_time']) ? "" : $param['run_time']) : "";
        if ($run_date == '' && $run_time == '') {
            $driver_status = "'0' as driver_status";
        } else {
            $driver_status = "(select count(id) FROM run_bus WHERE SEND_BUS_DRIVER_RES_ID = base_driver.driver_id AND CANCEL_FLAG = 0 AND RUN_ID in(SELECT run_id FROM RUN_MAIN WHERE RUN_DATE = '" . $run_date . "' AND RUN_TIME <= '" . $run_time . "') LIMIT 1) as driver_status";
        }
        $sql = "select 
                    driver_id as res_id,
                    driver_name,
                    driver_number,
                    ifnull(phone_no,'') as driver_phone,
                    (select supplier_name from base_supplier where id = base_driver.org_id) as company_name,
                    " . $driver_status . "
                from base_driver
                where cancel_flag = 0
                " . $and_sql . "
                AND IF(length(trim(" . $keyword . "))<=0,0=0,(driver_name LIKE CONCAT('%'," . $keyword . ",'%') OR phone_no LIKE CONCAT('%'," . $keyword . ",'%')))
                AND IF(" . $company . "<=0,0=0,org_id=" . $company . ")";
        zzcsUtils::writeLog($sql);
        $result = $this->query($sql);
        if (false === $result) {
            $json['code'] = '1';
            $json['info'] = '数据库错误';
        } else {
            $json['code'] = '0';
            $json['info'] = '返回数据成功';
            $json['driver_list'] = $result;
        }
        return $json;
    }

    //根据导游姓名手机号和公司查询导游
    public function getGuideList($param)
    {
        #region 获取渠道权限
        $user_id = $this->user_id;
        $opera_org_id_sql = "select opera_org_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'];
        if($opera_org_id == ''){
            $and_sql = '';
        }else{
            $and_sql = " and org_id in (" . $opera_org_id . ") ";
        }
        #endregion

        $keyword = isset($param['keyword']) ? "'" . $param['keyword'] . "'" : "''";
        $company = isset($param['company']) ? (empty($param['company']) ? '0' : $param['company']) : '0';
        $run_date = isset($param['run_date']) ? (empty($param['run_date']) ? "" : $param['run_date']) : "";
        $run_time = isset($param['run_time']) ? (empty($param['run_time']) ? "" : $param['run_time']) : "";
        if ($run_date == '' && $run_time == '') {
            $guide_status = "'0' as guide_status";
        } else {
            $guide_status = "(select count(id) FROM run_bus WHERE SEND_TOUR_GUIDE_RES_ID = base_guide.guide_id AND CANCEL_FLAG = 0 AND RUN_ID in(SELECT run_id FROM RUN_MAIN WHERE RUN_DATE = '" . $run_date . "' AND RUN_TIME <= '" . $run_time . "') LIMIT 1) as guide_status";
        }
        $sql = "select 
                    guide_id as res_id,
                    guide_name,
                    phone_no as guide_phone,
                    (select supplier_name from base_supplier where id = base_guide.org_id) as company_name,
                    " . $guide_status . "
                from base_guide
                where cancel_flag = 0
                " . $and_sql . "
                AND IF(length(trim(" . $keyword . "))<=0,0=0,(guide_name LIKE CONCAT('%'," . $keyword . ",'%') OR phone_no LIKE CONCAT('%'," . $keyword . ",'%')))
                AND IF(" . $company . "<=0,0=0,org_id=" . $company . ")";
        zzcsUtils::writeLog($sql);
        $result = $this->query($sql);
        if (false === $result) {
            $json['code'] = '1';
            $json['info'] = '数据库错误';
        } else {
            $json['code'] = '0';
            $json['info'] = '返回数据成功';
            $json['guide_list'] = $result;
        }
        return $json;
    }

    //得到公司列表
    public function getCompanyList()
    {
        #region 获取渠道权限
        $user_id = $this->user_id;
        $opera_org_id_sql = "select opera_org_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'];
        if($opera_org_id == ''){
            $and_sql = '';
        }else{
            $and_sql = " and s.id in (" . $opera_org_id . ") ";
        }
        #endregion

        if (!$this->company_list) {

            $sql = "SELECT 
                        s.id as res_id,
                        s.supplier_name as res_name
                    from base_supplier as s,
                         base_supplier_purchase as p
                    WHERE 
                        s.id = p.supplier_id
                    and s.cancel_flag = 0 
                    and p.cancel_flag = 0
                    and p.product_type = 259
                    " . $and_sql . "
                    group by s.id";
            $result = $this->query($sql);
            $data = array();
            $data['code'] = '0';
            $data['info'] = '';
            $data['company_list'] = array();
            if ($result === false) {
                $data['code'] = '1';
                $data['info'] = '获取公司列表失败';
            } else {
                $data['code'] = '0';
                $data['info'] = '获取公司列表成功';
                $data['company_list'] = $result;
            }
        }
        if ($data['code'] != '0') {
            $this->company_list = array();
        } else {
            $this->company_list = $data;
        }
        return $this->company_list;
    }

    /*
     * 车辆派遣
     * @bus_id run_bus表中的id，参数名称有点乱
     * @res_id bus_base表中的bus_id
     * @user_id user_id
     */
    public function vehicleDispatch($param)
    {
        $user_id = $this->user_id;
        $bus_id = isset($param['bus_id']) ? (empty($param['bus_id']) ? '' : $param['bus_id']) : '';//这里的bus_id是run_bus表里的第一列唯一ID值
        $res_id = isset($param['res_id']) ? (empty($param['res_id']) ? '' : $param['res_id']) : '';
        if ('' == $bus_id || '' == $res_id) {
            $data['code'] = '1';
            $data['info'] = '缺少必要参数';
            return $data;
        }
        //这里加一个判断，判断该bus_id的车次已售座位数是否小于等于 所拍车的座位数--如果是，则返回错误信息---温依莅
        $sql="select ((select seat_count from base_bus where bus_id=$res_id limit 1)-(select SALED_COUNT from run_bus where id=$bus_id limit 1)) as diff_num;";
        $res = $this->query($sql);
        if(false===$res){
            $data['code'] = '1';
            $data['info'] = '获取数据失败';
            return $data;
        }
        $diff_num=$res[0]['diff_num'];//表示 所选车的座位数-所选车次的已售人数；如果该数值小于0，则不可派该车。
        if($diff_num<0){
            $data['code'] = '2';
            $data['info'] = '所选车座位数小于该车次已售人数，请选择其他车辆';
            return $data;
        }
        //----//
        $sql = "CALL SP_VEHICLE_DISPATCH($user_id,$bus_id,$res_id)";
        zzcsUtils::writeLog($sql);
        $result = $this->procQuery($sql);
        $data = array();
        if ($result === false) {
            $data['code'] = '500';
            $data['info'] = '数据库存储过程错误';
            return $data;
        }
        $data['code'] = isset($result['data'][0][0]['errorcode']) ? $result['data'][0][0]['errorcode'] : '1';
        $data['info'] = isset($result['data'][0][0]['errorinfo']) ? $result['data'][0][0]['errorinfo'] : '未从数据库获得返回值';
        return $data;
    }

    //司机派遣
    public function driverDispatch($param)
    {
        $user_id = $this->user_id;
        $bus_id = isset($param['bus_id']) ? (empty($param['bus_id']) ? '' : $param['bus_id']) : '';
        $res_id = isset($param['res_id']) ? (empty($param['res_id']) ? '' : $param['res_id']) : '';
        if ('' == $bus_id || '' == $res_id) {
            $data['code'] = '1';
            $data['info'] = '缺少必要参数';
            return $data;
        }
        $sql = "CALL SP_DRIVER_DISPATCH($user_id,$bus_id,$res_id)";
        zzcsUtils::writeLog($sql);
        $result = $this->procQuery($sql);
        $data = array();
        if ($result === false) {
            $data['code'] = '500';
            $data['info'] = '数据库存储过程错误';
            return $data;
        }
        $data['code'] = isset($result['data'][0][0]['errorcode']) ? $result['data'][0][0]['errorcode'] : '1';
        $data['info'] = isset($result['data'][0][0]['errorinfo']) ? $result['data'][0][0]['errorinfo'] : '未从数据库获得返回值';
        return $data;
    }

    //导游派遣
    public function guideDispatch($param)
    {
        $user_id = $this->user_id;
        $bus_id = isset($param['bus_id']) ? (empty($param['bus_id']) ? '' : $param['bus_id']) : '';
        $res_id = isset($param['res_id']) ? (empty($param['res_id']) ? '' : $param['res_id']) : '';
        if ('' == $bus_id || '' == $res_id) {
            $data['code'] = '1';
            $data['info'] = '缺少必要参数';
            return $data;
        }
        $sql = "CALL SP_GUIDE_DISPATCH($user_id,$bus_id,$res_id)";
        zzcsUtils::writeLog($sql);
        $result = $this->procQuery($sql);
        $data = array();
        if ($result === false) {
            $data['code'] = '500';
            $data['info'] = '数据库存储过程错误';
            return $data;
        }
        $data['code'] = isset($result['data'][0][0]['errorcode']) ? $result['data'][0][0]['errorcode'] : '1';
        $data['info'] = isset($result['data'][0][0]['errorinfo']) ? $result['data'][0][0]['errorinfo'] : '未从数据库获得返回值';
        return $data;
    }

    //重置车辆-司机-导游的派遣
    public function resetDispatch($param)
    {
        $user_id = $this->user_id;
        $idstr = isset($param['idstr']) ? $param['idstr'] : '';
        if ('' == $idstr) {
            $data['code'] = '1';
            $data['info'] = '缺少必要参数';
            return $data;
        }
        $sql = "CALL sp_update_run_dispatch_xm(" . $user_id . ",'" . $idstr . "')";
        zzcsUtils::writeLog($sql);
        $result = $this->procQuery($sql);
        $data = array();
        if ($result === false) {
            $data['code'] = '500';
            $data['info'] = '数据库存储过程错误';
            return $data;
        }
        $data['code'] = isset($result['data'][0][0]['errorcode']) ? $result['data'][0][0]['errorcode'] : '1';
        $data['info'] = isset($result['data'][0][0]['errorinfo']) ? $result['data'][0][0]['errorinfo'] : '未从数据库获得返回值';
        return $data;
    }
}