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

<?php

/**
 * Created by PhpStorm.
 * User: Steven
 * Date: 2016/11/3
 * Time: 15:23
 */
class getGroupInfo extends base
{
    /**
     * Function Description:查询直通车次订单信息
     * Function Name: getRunmsg
     * @param $param
     *
     * @return mixed
     *
     * @author 娄梦宁
     */
    public function getRunmsg($param)
    {
        //$run_date = isset($param['run_date']) ? $param['run_date'] : false;
        //$bus_num = isset($param['bus_num']) ? $param['bus_num'] : false;
        /*if (false === $run_date || false === $bus_num) {
            $json["code"] = "2";
            $json["info"] = "缺少必要参数";
            return $json;
        }*/
        $valid = zzcsUtils::validateParams(array('run_id,bus_num,product_id' => 'number'), $param);   //参数验证
        if (!$valid['status']) {
            $result['code'] = (string)$valid['status'];
            $result['info'] = $valid['info'];
            return $result;
        }
        $run_date = isset($param['run_date']) ? $param['run_date'] : false;
        //判断日期格式正确
        $tmp_arr = explode('-', $run_date);
        $bool = checkdate($tmp_arr[1], $tmp_arr[2], $tmp_arr[0]);//判断日期是否是合法日期
        if (!$bool) {
            $json['code'] = '1';
            $json['info'] = '日期格式不正确';
            return $json;
        }
        $run_id = trim($param['run_id']);
        $bus_num = trim($param['bus_num']);
        $run_date = trim($param['run_date']);
        $product_id = $param['product_id'];
        //查询出该车次下所有的父订单(新)---温依莅
        $sql = "
            SELECT *,convert(x.trans_name using gbk) 't1',convert(x.start_station using gbk) 't2'
            from(
                SELECT
                    a.order_id,
                    a.customer_mobile as tel,
                    (DATEDIFF('$run_date',a.prod_start_station_date)+1) as day,
                    IFNULL((SELECT h.line_id FROM order_main g LEFT JOIN opera_line h ON g.parent_prod_id=h.line_id WHERE h.LINE_TYPE=256 and g.PARENT_ORDER_ID=a.order_id limit 1),'') AS tran_id,
                    IFNULL((select res_name from base_resource where res_id=(select parent_id from base_resource where res_id=(SELECT p.prod_start_station_res_id FROM order_main p LEFT JOIN opera_line q ON p.parent_prod_id=q.line_id WHERE q.LINE_TYPE=256 AND p.PARENT_ORDER_ID=a.order_id  limit 1))),'') as trans_name,
                    IFNULL((SELECT o.run_bus_order_id FROM order_main o LEFT JOIN opera_line v ON o.parent_prod_id=v.line_id WHERE v.LINE_TYPE=255 AND o.PARENT_ORDER_ID=a.order_id  and o.run_id=$run_id and o.RUN_BUS_ORDER_ID=$bus_num limit 1),'') AS run_bus_id,
                    a.prod_start_station_res_name AS start_station,
                    b.line_code AS line_codes,
                    IFNULL(SUBSTRING_INDEX(a.order_description,'|',-1),0) AS num 
                FROM 
                    order_main a LEFT JOIN opera_line b
                ON
                    a.parent_prod_id = b.line_id
                WHERE 
                    CONCAT_WS('-',$run_id,$bus_num) in (SELECT CONCAT_WS('-',o.run_id,o.RUN_BUS_ORDER_ID) FROM order_main o LEFT JOIN opera_line v ON o.parent_prod_id=v.line_id WHERE v.LINE_TYPE=255 AND o.PARENT_ORDER_ID=a.order_id)
                    AND a.order_valid_status=1 AND a.order_status<>148
                    AND b.line_type=316 AND b.cancel_flag=0 AND b.if_disabled=0 AND b.is_onsale=1
            ) x
            ORDER BY t1,t2;";
        //查询出该车次下所有的父订单（原）
        /*$sql = "select
              b.parent_prod_id as tran_id,
              a.order_id,
              substring_index(a.ORDER_DESCRIPTION,'|',-1) as num,
              c.line_code as line_codes,
              a.PROD_START_STATION_RES_NAME as start_station,
              b.RUN_BUS_ORDER_ID as run_bus_id,
              b.run_date,
              (DATEDIFF('$run_date',a.prod_start_station_date)+1) as day
              from
              order_main as a
              inner join
              order_main as b on a.order_id=b.PARENT_ORDER_ID
              INNER JOIN
              run_main as d  on b.run_id=d.run_id
              left join
              opera_line c on a.PARENT_PROD_ID=c.line_id
              where d.PROD_ID=0 and b.run_date='" . $run_date . "'  and a.CANCEL_FLAG=0 and b.CANCEL_FLAG=0 and b.RUN_BUS_ORDER_ID=$bus_num and b.ORDER_VALID_STATUS=1  and b.order_status!=148 and b.PARENT_PROD_ID in (select line_id from opera_line where line_type=255)
              GROUP BY a.order_id ";*/
        zzcsUtils::writeLog($sql);
        $runmsg = $this->query($sql);
        /*if (isset($runmsg[0]['run_date']) && isset($runmsg[0]['run_bus_id'])) {
            $rundate = $runmsg[0]['run_date'];
            $bus_num = $runmsg[0]['run_bus_id'];
            //查询同日期其他车次及余位
            $sql = "SELECT
                    a.run_id,
                    b.BUS_ORDER_ID AS run_bus_id,
                    (b.seat_count - b.saled_count) AS stock,
                    count(DISTINCT b.BUS_ORDER_ID) AS assist
                    FROM
                    `run_main` a
                    LEFT JOIN run_bus b ON a.run_id = b.RUN_ID
                    WHERE
                    run_date = '" . $rundate . "'
                    and a.prod_id = 0
                    AND b.BUS_ORDER_ID !='$bus_num'
                    and b.CANCEL_FLAG=0
                    GROUP BY
                    b.BUS_ORDER_ID;
              ";
            $othercar = $this->query($sql);
        }*/

        //处理得到今日组合订单 直通段车次信息
        $sql2 = "SELECT
                    a.order_id,a.parent_order_id,a.run_id,b.BUS_ORDER_ID as run_bus_id,(b.seat_count - b.saled_count) AS stock
                FROM
                    order_main a,run_bus b,
                    opera_line c,run_main d
                WHERE
                        a.run_id = b.run_id
                    AND a.PARENT_PROD_ID = c.line_id
                    AND b.run_id = d.run_id
                    AND d.prod_id = 0
                    AND d.run_date = '$run_date'
                    AND c.line_type = 255
                    AND b.cancel_flag = 0
				group by b.bus_order_id
				having  $product_id=(SELECT l.product_type FROM opera_line l LEFT JOIN order_main m ON l.line_id = m.PARENT_PROD_ID WHERE m.order_id = a.PARENT_ORDER_ID limit 1);";
        $bus_info = $this->query($sql2);
        if (false === $bus_info) {
            $json['code'] = '1';
            $json['info'] = '数据库出错';
            $json['list'] = array();
            return $json;
        }
        //若查询无车次信息
        if (count($bus_info) == 0) {
            //这里如果尚未派车，无车次信息，则返回一个空的bus_info数组
            /*$json['code'] = '1';
            $json['info'] = '无符合条件的车次信息';
            $json['list'] = array();
            return $json;*/
            $bus_info = array();

        }
        /*if(count($runmsg)==0){
            $json['code'] = '1';
            $json['info'] = '返回班次订单信息失败';
            $json['list_order'] = $runmsg;
            return $json;
        }*/
        if (false === $runmsg) {
            $json['code'] = '1';
            $json['info'] = '返回班次人员信息失败';
            $json['list_bus'] = '';
            $json['list_order'] = $runmsg;
            return $json;
        } else {
            //处理run_bus_id返回值
            foreach ($runmsg as $k => $v) {
                if ($v['run_bus_id'] == 0) {
                    $order_info[$k]['run_bus_id'] = '';
                }
            }
            $json['code'] = '0';
            $json['info'] = '返回班次人员信息成功';
            //$json['list_bus'] = $othercar;
            $json['list_bus'] = $bus_info;
            $json['list_order'] = $runmsg;
            return $json;
        }
    }

    /**
     * Function Description:查询接驳车次订单信息
     * Function Name: getTransmsg
     * @param $param
     *
     * @return mixed
     *
     * @author 温依莅
     */
    public function getTransmsg($param)
    {
        //$run_date = isset($param['run_date']) ? $param['run_date'] : false;
        //$bus_num = isset($param['bus_num']) ? $param['bus_num'] : false;
        /*if (false === $run_date || false === $bus_num) {
            $json["code"] = "2";
            $json["info"] = "缺少必要参数";
            return $json;
        }*/
        $valid = zzcsUtils::validateParams(array('run_id,bus_num,product_id' => 'number'), $param);   //参数验证
        if (!$valid['status']) {
            $result['code'] = (string)$valid['status'];
            $result['info'] = $valid['info'];
            return $result;
        }
        $run_date = isset($param['run_date']) ? $param['run_date'] : false;
        //判断日期格式正确
        $tmp_arr = explode('-', $run_date);
        $bool = checkdate($tmp_arr[1], $tmp_arr[2], $tmp_arr[0]);//判断日期是否是合法日期
        if (!$bool) {
            $json['code'] = '1';
            $json['info'] = '日期格式不正确';
            return $json;
        }
        $run_id = trim($param['run_id']);
        $bus_num = trim($param['bus_num']);
        $run_date = trim($param['run_date']);
        $product_id = $param['product_id'];
        //查询出该车次下所有的父订单(新)---温依莅
        $sql = "
            SELECT *
            from(
                SELECT
                    a.order_id,
                    a.customer_mobile as tel,
                    (DATEDIFF('$run_date',a.prod_start_station_date)+1) as day,
                    IFNULL((SELECT h.line_id FROM order_main g LEFT JOIN opera_line h ON g.parent_prod_id=h.line_id WHERE h.LINE_TYPE=256 and g.PARENT_ORDER_ID=a.order_id  limit 1),'') AS tran_id,
                    IFNULL((select res_name from base_resource where res_id=if((select parent_id from base_resource where res_id=(SELECT p.prod_start_station_res_id FROM order_main p LEFT JOIN opera_line q ON p.parent_prod_id=q.line_id WHERE q.LINE_TYPE=256 AND p.PARENT_ORDER_ID=a.order_id  and p.run_id=$run_id and p.RUN_BUS_ORDER_ID=$bus_num limit 1))=0,-1,(select parent_id from base_resource where res_id=(SELECT p.prod_start_station_res_id FROM order_main p LEFT JOIN opera_line q ON p.parent_prod_id=q.line_id WHERE q.LINE_TYPE=256 AND p.PARENT_ORDER_ID=a.order_id  and p.run_id=$run_id and p.RUN_BUS_ORDER_ID=$bus_num limit 1)))),'') as trans_name,
                    IFNULL((SELECT o.run_bus_order_id FROM order_main o LEFT JOIN opera_line v ON o.parent_prod_id=v.line_id WHERE v.LINE_TYPE=256 AND o.PARENT_ORDER_ID=a.order_id  and o.run_id=$run_id and o.RUN_BUS_ORDER_ID=$bus_num limit 1),'') AS run_bus_id,
                    a.prod_start_station_res_name AS start_station,
                    b.line_code AS line_codes,
                    IFNULL(SUBSTRING_INDEX(a.order_description,'|',-1),0) AS num 
                FROM 
                    order_main a LEFT JOIN opera_line b
                ON
                    a.parent_prod_id = b.line_id
                WHERE 
                    CONCAT_WS('-',$run_id,$bus_num) in (SELECT CONCAT_WS('-',o.run_id,o.RUN_BUS_ORDER_ID) FROM order_main o LEFT JOIN opera_line v ON o.parent_prod_id=v.line_id WHERE v.LINE_TYPE=256 AND o.PARENT_ORDER_ID=a.order_id)
                    AND a.order_valid_status=1 AND a.order_status<>148
                    AND b.line_type=316 AND b.cancel_flag=0 AND b.if_disabled=0 AND b.is_onsale=1
            ) x
            ORDER BY x.trans_name,x.start_station;";

        zzcsUtils::writeLog($sql);
        $runmsg = $this->query($sql);
        /*if (isset($runmsg[0]['run_date']) && isset($runmsg[0]['run_bus_id'])) {
            $rundate = $runmsg[0]['run_date'];
            $bus_num = $runmsg[0]['run_bus_id'];
            //查询同日期其他车次及余位
            $sql = "SELECT
                    a.run_id,
                    b.BUS_ORDER_ID AS run_bus_id,
                    (b.seat_count - b.saled_count) AS stock,
                    count(DISTINCT b.BUS_ORDER_ID) AS assist
                    FROM
                    `run_main` a
                    LEFT JOIN run_bus b ON a.run_id = b.RUN_ID
                    WHERE
                    run_date = '" . $rundate . "'
                    and a.prod_id = 0
                    AND b.BUS_ORDER_ID !='$bus_num'
                    and b.CANCEL_FLAG=0
                    GROUP BY
                    b.BUS_ORDER_ID;
              ";
            $othercar = $this->query($sql);
        }*/
        //处理得到今日组合订单 接驳段车次信息
        /*$sql2 = "select distinct b.run_id,
                      b.run_bus_order_id as run_bus_id,
                      (s.SEAT_COUNT-s.SALED_COUNT) AS stock
                from opera_tickets a, run_bus s,order_main b, opera_line l
                where a.ticket_id = b.prod_id
                  and s.run_id = b.RUN_ID
                  and s.bus_order_id = b.run_bus_order_id
                  and b.parent_prod_id = l.LINE_ID
                  and l.line_type = 256
                  and l.cancel_flag =0
                  and a.line_id in (select line_id from opera_line where line_type = 316 and product_type = $product_id and cancel_flag = 0)
                  and b.run_date = '$run_date' order by  b.run_bus_order_id,s.SEAT_COUNT;";*/
        $sql2 = "select b.run_id,
                      b.bus_order_id as run_bus_id,
                      (b.SEAT_COUNT-b.SALED_COUNT) AS stock
                from run_bus b left join 
                     run_main m on b.run_id = m.run_id 
                where m.run_date = '$run_date' and b.group_line_flag = 1 and b.cancel_flag = 0 and m.prod_id > 0
                  and b.run_id in (
                select distinct run_id from order_main where parent_order_id > 0 and prod_id in (select ticket_id from opera_tickets t, opera_line l where t.line_id = l.line_id and l.line_type = 316 and l.product_type = $product_id)
                ) order by b.bus_order_id";
        $bus_info = $this->query($sql2);
        if (false === $bus_info) {
            $json['code'] = '1';
            $json['info'] = '获取班次失败';
            $json['list'] = array();
            return $json;
        }
        //若查询无车次信息
        if (count($bus_info) == 0) {
            //这里如果尚未派车，无车次信息，则返回一个空的bus_info数组
            /*$json['code'] = '1';
            $json['info'] = '无符合条件的车次信息';
            $json['list'] = array();
            return $json;*/
            $bus_info = array();
        }

        if (false === $runmsg) {
            $json['code'] = '1';
            $json['info'] = '返回班次人员信息失败';
            $json['list_bus'] = '';
            $json['list_order'] = $runmsg;
            return $json;
        } else {
            //处理run_bus_id返回值
            foreach ($runmsg as $k => $v) {
                if ($v['run_bus_id'] == 0) {
                    $order_info[$k]['run_bus_id'] = '';
                }
            }
            $json['code'] = '0';
            $json['info'] = '返回班次人员信息成功';
            //$json['list_bus'] = $othercar;
            $json['list_bus'] = $bus_info;
            $json['list_order'] = $runmsg;
            return $json;
        }
    }

    /**
     * Function Description:修改车次
     * Function Name: uptRunId
     * @param $param
     *
     * @return int
     *
     * @author 娄梦宁
     */
    public function uptRunId($param)
    {
        if (isset($param['orderid']) && isset($param['run_id']) && isset($param['p_num']) && isset($param['bus_num']) && isset($param['bus_ori_str']) && isset($param['alter_str'])) {
            //传入父订单ID,以英文逗号分隔
            $idstr = $param['orderid'];
            //要添加乘客的巴士班次
            $run_id = $param['run_id'];
            //源车次信息
            $bus_ori_str = $param['bus_ori_str'];
            //各订单人数信息
            $alter_str = $param['alter_str'];
            //已选择的人数
            $p_num = $param['p_num'];
            //要添加乘客的巴士车次号
            $bus_num = $param['bus_num'];
            $userid = $this->user_id;
            $uptdate = date("Y-m-d H:i:s", time());


            $ori_order_arr = explode(',', $idstr);
            $ori_bus_arr = explode(',', $bus_ori_str);
            $pure_bus_arr = array_filter($ori_bus_arr);//去掉类似z(j)线路没分车之前，车次为空的情况
            $pure_bus_str = implode(',', $pure_bus_arr);

            $alter_arr = explode(',', $alter_str);
            //此处根据run_id 判断是该run_id是组合线路的接驳段256还是直通段255--温依莅
            $sql = "select b.run_date,c.line_type from order_main a,run_main b,opera_line c where a.run_id=b.run_id and a.PARENT_PROD_ID=c.line_id and a.run_id=$run_id limit 1;";
            $temp = $this->query($sql);
            if (false === $temp) {
                $json['code'] = '1';
                $json['info'] = '获取失败';
                $json['list'] = array();
                return $json;
            } else if (count($temp) == 0) {
                $json['code'] = '1';
                $json['info'] = '无符合条件信息';
                $json['list'] = array();
                return $json;
            }
            $line_type = $temp[0]['line_type'];
            ##得到该run_id班次的出发日期，在下面人工干预时，对于两日行程的车次只干预当天的车次信息.
            $run_date = $temp[0]['run_date'];

            //此处检查该run_id,bus_order_id的车次run_bus_status 状态是否是138，如果不是表示不可再调整
            /*如果$pure_bus_arr非空,则判断；否则不判断，说明全是z(j)类型未分配的车次*/
            if (!empty($pure_bus_arr)) {
                $chk_run_sql = "select run_bus_status from run_bus where run_id=$run_id and BUS_ORDER_ID in ($pure_bus_str) limit 1;";
                $run_bus_status = $this->query($chk_run_sql);
                if ($run_bus_status[0]['run_bus_status'] != 138) {
                    $json['code'] = '1';
                    $json['info'] = '该班次已发车，不可修改';
                    return $json;
                }
            }


//          //检查剩余座位是否大于已选择人数
            $sql = "select (seat_count-saled_count) as num1 from run_bus
                where run_id=" . $run_id . " and bus_order_id=$bus_num and cancel_flag=0 limit 1 ";
            $num = $this->query($sql);
            if ($num[0]['num1'] >= 1) {
                $seatnum = $num[0]['num1'];
            } else {
                $json['code'] = '1';
                $json['info'] = '剩余座位不够';
                return $json;
            }
            if ($p_num > $seatnum) {
                $json['code'] = '1';
                $json['info'] = '可用剩余座位不够';
                return $json;
            } else {
                //-----如果该订单z(j)类型没有在直通段派车（order_main相应直通段子订单无对应的run_bus_order_id值）
                #处理$idstr--订单集合 和 $bus_ori_str车次集合

                $sql = '';
                foreach ($ori_order_arr as $k => $v) {
                    if ($ori_bus_arr[$k] == '') {       //如果该订单尚未分配车次，则只增加新车次

                    } else {                         //如果已分配车次，则去掉原车次的saled_count
                        //操作成功后修改减员巴士已售座位
                        $sql .= "UPDATE run_bus
                                SET saled_count = (saled_count-" . $alter_arr[$k] . "),
                                    real_total_count = (real_total_count-" . $alter_arr[$k] . "),
                                    UPDATE_USER_ID=$userid,
                                    UPDATE_TIME='$uptdate'
                                WHERE
                                    bus_order_id = " . $ori_bus_arr[$k] . " and  run_id = $run_id;";
                    }
                }
                //-----
                //操作成功后修改减员巴士已售座位
                /* $sql = "UPDATE run_bus
                         SET saled_count = (saled_count-" . $p_num . "),
                             real_total_count = (real_total_count-" . $p_num . "),
                             UPDATE_USER_ID=$userid,
                             UPDATE_TIME='$uptdate'
                         WHERE
                             bus_order_id = (
                                 SELECT DISTINCT
                                     run_bus_order_id
                                 FROM
                                     order_main
                                 WHERE
                                     parent_order_id in (" . $idstr . ")
                                     and run_date = (select  DISTINCT run_date from order_main where run_id=$run_id)
                                     and PARENT_PROD_ID in (select line_id from opera_line where line_type=$line_type)
                             ) and  run_id = '$run_id';";*/
                //修改新增乘客巴士已售座位
                $sql .= "UPDATE run_bus
                         SET saled_count = (saled_count +" . $p_num . "),
                         real_total_count = (real_total_count +" . $p_num . "),
                         UPDATE_USER_ID=$userid,
                         UPDATE_TIME='$uptdate'
                        WHERE
                            run_id = " . $run_id . "
                            and bus_order_id=$bus_num ;";
                //修改子订单以及车次号
                $sql .= "update order_main
                          set
                          RUN_ID=" . $run_id . ",
                          RUN_BUS_ORDER_ID=" . $bus_num . ",
                          UPDATE_USER_ID=$userid,
                          UPDATE_TIME='$uptdate'
                          where
                          parent_order_id in (" . $idstr . ")
                          and run_date ='$run_date'
                         and PARENT_PROD_ID in (select line_id from opera_line where line_type=$line_type);";
                zzcsUtils::writeLog($sql);
                $uptsaled = $this->exec($sql);
                if ($uptsaled !== false) {
                    $json['code'] = '0';
                    $json['info'] = '修改成功';
                    return $json;
                } else {
                    $json['code'] = '2';
                    $json['info'] = '修改巴士已售座位失败';
                    return $json;
                }
            }
        } else {
            $json['code'] = '4';
            $json['info'] = '缺少必要参数';
            return $json;
        }
    }
}