ZZCS/coding/application/financeManager/payment.class.php

<?php

/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2017/5/22
 * Time: 16:45
 */
class payment extends base
{
    //获取应收订单列表
    public function getList($post)
    {
        $getWhere = $this->getWhere($post);
        if ($getWhere['param']['current_page'] == 1) {
            /**==========获取记录数和总金额============**/
            $sql_1 = "
            SELECT SUM(x.cnt) 'cnt',FORMAT(SUM(x.base_price),2) 'total_price',SUM(x.reparations_price) 'reparations_price'
            FROM (
            SELECT COUNT(1) cnt,SUM(a.TOTAL_COST_PRICE) 'base_price',
            SUM(
                (SELECT IFNULL(SUM(v.reparations),0)  from order_main u
                JOIN order_finance_reparations v on u.order_id = v.order_id
                WHERE u.RUN_ID = a.RUN_ID and u.RUN_BUS_ORDER_ID = a.BUS_ORDER_ID
                )
            )
	        as 'reparations_price'
            from bus_cost a
            LEFT JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
            LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
            WHERE {$getWhere['bus_where']}
            union ALL
            SELECT COUNT(1) cnt,SUM(a.BASE_PRICE) 'base_price',
            IFNULL((SELECT reparations from order_finance_reparations WHERE order_id=a.ORDER_ID),0) 'reparations_price'
            from order_main a
            LEFT JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
            WHERE {$getWhere['order_where']}
            ) x
           ";
            $getTotal = $this->query($sql_1);
            $data['page']['total_count'] = $getTotal[0]['cnt'];
            $data['page']['total_page'] = (string)ceil($getTotal[0]['cnt'] / $getWhere['param']['page_size']);
            $data['sum_info'] = $getTotal[0];
        }

        /**==========获取列表============**/
        $offset = ($getWhere['param']['current_page'] - 1) * $getWhere['param']['page_size'];
        $sql_2 = "      
        SELECT a.ID 'order_id','' as 'outside_order_no',a.START_TIME 'create_time',
        d.LINE_NAME 'parent_prod_name','' as 'order_prod_type',
        (SELECT SUPPLIER_NAME FROM base_supplier WHERE a.BUS_ORG_ID = ID) 'supplier_name' ,
        a.TOTAL_COST_PRICE 'base_price',
        (SELECT IFNULL(SUM(v.reparations),0)  from order_main u
        JOIN order_finance_reparations v on u.order_id = v.order_id
        WHERE u.RUN_ID = a.RUN_ID and u.RUN_BUS_ORDER_ID = a.BUS_ORDER_ID
        ) as 'reparations_price',
        IFNULL(b.payment_status,1) 'status',
        CASE IFNULL(b.payment_status,1)
                    WHEN 1 THEN '待结算'
                    WHEN 2 THEN '已关账'
                    WHEN 3 THEN '结算中'
                    WHEN 4 THEN '已结算'
                    ELSE '待结算' END AS 'status_des' ,
        a.START_TIME as 'run_date',
        '巴士' as 'prod_type_des',
        '' as 'order_description',
          '' as 'prodNum',
          '' as 'prodName'
        from bus_cost a
        LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
        LEFT JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
        WHERE {$getWhere['bus_where']}
        union ALL
        SELECT a.ORDER_ID 'order_id',a.OUTSIDE_ORDER_NO,a.CREATE_TIME 'create_time',
        (SELECT CONCAT(PARENT_PROD_NAME, '<br />',PROD_START_STATION_RES_NAME,' - ',PROD_END_STATION_RES_NAME) from order_main WHERE PARENT_ORDER_ID = a.ORDER_ID LIMIT 1) 'parent_prod_name',
        a.ORDER_PROD_TYPE 'order_prod_type',
        (SELECT SUPPLIER_NAME from base_supplier WHERE ID = a.PROD_TOP_ORG_ID) 'supplier_name',
        a.BASE_PRICE 'base_price',
        IFNULL((SELECT reparations from order_finance_reparations WHERE order_id=a.ORDER_ID),0) 'reparations_price',
        IFNULL(b.payment_status,1) 'status',
        CASE IFNULL(b.payment_status,1)
                    WHEN 1 THEN '待结算'
                    WHEN 2 THEN '已关账'
                    WHEN 3 THEN '结算中'
                    WHEN 4 THEN '已结算'
                    ELSE '待结算' END AS 'status_des' ,
        (SELECT MAX(RUN_DATE) from order_main WHERE a.ORDER_ID = PARENT_ORDER_ID) 'run_date',
         CASE  
        WHEN  a.ORDER_PROD_TYPE in (25,26) THEN  '酒店'
        WHEN  a.ORDER_PROD_TYPE in (81,82,38,369) THEN  '车票'
        WHEN  a.ORDER_PROD_TYPE in (258,282,311) THEN  '门票'
        else '' END as 'prod_type_des',
        a.ORDER_DESCRIPTION 'order_description',
        '' as 'prodNum',
        '' as 'prodName'
        from order_main a
        LEFT JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
        WHERE {$getWhere['order_where']}
        LIMIT {$getWhere['param']['page_size']} offset $offset;
    ";
        $list = $this->query($sql_2);
        /**==========设置返回值============**/
        if (empty($list) == false) {
            foreach ($list as $key => $value) {
                if(empty($value['order_description'])) {
                    continue;
                }
                $list[$key]['prodNum'] = 0;
                $list[$key]['prodName'] = '';
                $scripTionArr = explode('|', $value['order_description']);
                foreach ($scripTionArr as $vl) {
                    if (empty($vl)) {
                        continue;
                    }
                    $vls = explode(',', $vl);
                    if (empty($vls['2'])) {
                        $list[$key]['prodNum'] = $vls[0];
                    } else {
                        $list[$key]['prodNum'] = $vls['2'];
                        $list[$key]['prodName'] = $vls['0'];
                    }
                }
                unset($list[$key]['order_description']);
            }
        }
        $json['code'] = '0';
        $json['info'] = '获取列表成功';
        $data['order_list'] = $list;
        $data['page']['page_size'] = $getWhere['param']['page_size'];
        $data['page']['current_page'] = $getWhere['param']['current_page'];
        $json['data'] = $data;
        return $json;
    }

    /**
     * @param $post
     * @return mixed
     */
    public function closesAmount($post)
    {
        $getWhere = $this->getWhere($post);
        $sql_1 = "
            SELECT SUM(x.cnt) cnt
            FROM (
            SELECT COUNT(1) cnt
            from bus_cost a
            LEFT JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
            LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
            WHERE  {$getWhere['bus_where']}
			and IFNULL(b.payment_status,1) != 1
            union ALL
            SELECT COUNT(1) cnt
            from order_main a
            LEFT JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
            WHERE  {$getWhere['order_where']}
			and IFNULL(b.payment_status,1) != 1
            ) x
            ";
        $getTotal = $this->query($sql_1);
        if ($getTotal['0']['cnt'] >= 1) {
            $json['code'] = '1';
            $json['info'] = '关账失败！所选订单中有非待结算状态订单！';
            return $json;
        }
        /**==========插入新记录=============**/

        $sql1 = "
            INSERT into order_finance_status(order_id, update_time, type)
            SELECT z.* FROM (
                SELECT a.ID,NOW(),2
                from bus_cost a
                LEFT JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
                LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
                WHERE  {$getWhere['bus_where']}
                and b.payment_status is null
                union ALL
                SELECT a.ORDER_ID,NOW(),1
                from order_main a
                LEFT JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
                WHERE  {$getWhere['order_where']}
                and b.payment_status is null
            ) z
            ";
        $this->insert($sql1);
        /**==========更新旧新记录=============**/
        $sql2 = "
            update order_main a
            JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
            set b.payment_status = 2
            WHERE {$getWhere['order_where']};
            update bus_cost a
            JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
            LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
            SET b.payment_status = 2
            WHERE  {$getWhere['bus_where']};  
        ";
        $this->exec($sql2);
        $json['code'] = '0';
        $json['info'] = '已关账';
        return $json;
    }

    //生成对账单
    public function addBalance($post)
    {
        $getWhere = $this->getWhere($post);
        $check = $this->checkCanCloses($getWhere);
        if ($check == false) {
            $json['code'] = '1';
            $json['info'] = '关账失败！所选订单中有非关账状态订单！';
            return $json;
        }
        /**============生成对账单===================**/
        $time = microtime(true) * 10000;
        $sql = "
        INSERT into order_finance_balance_account(org_id,org_name,price,
        reparations_price,type,batch_id,start_date,end_date,account_bank,account_name,account_num,sett_frequency,order_type)
        SELECT x.org_id,(SELECT SUPPLIER_NAME from base_supplier WHERE ID = x.org_id) 'org_name',
		SUM(x.price),SUM(x.reparations),1,concat('{$time}','',x.org_id),'{$getWhere['param']['start_date']}','{$getWhere['param']['end_date']}',
		c.ACCOUNT_BANK,c.ACCOUNT_NAME,c.ACCOUNT_NUM,d.TYPE_NAME,1        
		from(
			SELECT a.BUS_ORG_ID 'org_id',SUM(a.TOTAL_COST_PRICE) 'price',
			 (SELECT IFNULL(SUM(v.reparations),0)  from order_main u
                JOIN order_finance_reparations v on u.order_id = v.order_id
                WHERE u.RUN_ID = a.RUN_ID and u.RUN_BUS_ORDER_ID = a.BUS_ORDER_ID
             ) as 'reparations'
			from bus_cost a
			LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
			left JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
			WHERE {$getWhere['bus_where']}
			GROUP BY a.BUS_ORG_ID
			union ALL
			SELECT a.PROD_TOP_ORG_ID 'org_id',SUM(a.BASE_PRICE) 'price',IFNULL(SUM(d.reparations),0) 'reparations'
			from order_main a
			left JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
			LEFT JOIN order_finance_reparations d on a.order_id=d.order_id
			WHERE {$getWhere['order_where']}
			GROUP BY a.PROD_TOP_ORG_ID			
		)x
		LEFT join base_supplier c on c.ID=x.org_id
		LEFT JOIN dict_type d on c.SETT_FREQUENCY = d.ID
		GROUP BY x.org_id
     ";
        $this->insert($sql);
        /**============关联对账单记录==============**/
        $sql2 = "
            update order_main a
            JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
            set b.payment_status = 3 ,b.payment__batch_id = CONCAT('{$time}','',a.PROD_TOP_ORG_ID)
            WHERE {$getWhere['order_where']};
            update bus_cost a
            JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
            LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
            SET b.payment_status = 3 ,b.payment__batch_id = CONCAT('{$time}','',a.BUS_ORG_ID)
            WHERE  {$getWhere['bus_where']};      
        ";
        $this->edit($sql2);
        $json['code'] = '0';
        $json['info'] = '已生成对账单';
        return $json;
    }

    //取消关账
    public function cancelCloses($post)
    {
        $getWhere = $this->getWhere($post);
        $check = $this->checkCanCloses($getWhere);
        if ($check == false) {
            $json['code'] = '1';
            $json['info'] = '关账失败！所选订单中有非关账状态订单！';
            return $json;
        }
        /**============取消关账==============**/
        $sql2 = "
            update order_main a
            JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
            set b.payment_status = 1 
            WHERE {$getWhere['order_where']};
            update bus_cost a
            JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
            LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
            SET b.payment_status = 1
            WHERE  {$getWhere['bus_where']};
            ";
        $this->edit($sql2);
        $json['code'] = '0';
        $json['info'] = '已取消关账';
        return $json;

    }

    //获取查询条件
    public function getWhere($post)
    {
        /**==========接收参数============**/
        $param = array(
            'date_type' => empty($post['date_type']) ? '' : $post['date_type'],//时间类型
            'start_date' => empty($post['start_date']) ? '' : $post['start_date'],//开始时间
            'end_date' => empty($post['end_date']) ? '' : $post['end_date'],//接收时间
            'org_id' => empty($post['org_id']) ? '' : $post['org_id'],//渠道商ID
            'settlement_status' => empty($post['settlement_status']) ? '0' : $post['settlement_status'],//结算状态值
            'prod_type' => empty($post['prod_type']) ? '0' : $post['prod_type'],//订单产品类型
            'more_search' => empty($post['more_search ']) ? '' : $post['more_search '],//更多查询条件
            'current_page' => empty($post['current_page']) ? '1' : $post['current_page'],//当前页
            'page_size' => empty($post['page_size']) ? '10' : $post['page_size'],//每页展示条数
        );
        /**==========拼接订单的查询条件============**/
        $order_where = " a.CANCEL_FLAG=0 and (a.order_status  in(146,147,314) or a.CHANNEL_ORDER_STATUS in (551,554,553)) and a.ORDER_PROD_TYPE not in (81,82,38,369)  and a.PARENT_ORDER_ID = 0 ";//获取所有已完成的有效的主订单
        //结算时间
        if ($param['date_type'] == '2' && $param['start_date']) {
            $order_where .= " and (SELECT  MIN(RUN_DATE) from order_main WHERE a.ORDER_ID = PARENT_ORDER_ID) >= '{$param['start_date']}'";//大于等于起始日期
        }
        if ($param['date_type'] == '2' && $param['end_date']) {
            $order_where .= " and (SELECT  MIN(RUN_DATE) from order_main WHERE a.ORDER_ID = PARENT_ORDER_ID) < date_add('{$param['end_date']}', INTERVAL 1 day)";//小于日期
        }
        //收款状态
        if ($param['settlement_status']) {
            $order_where .= " and IFNULL(b.payment_status,1) = {$param['settlement_status']}";
        }
        //预定时间
        if ($param['date_type'] == '1' && $param['start_date']) {
            $order_where .= " and a.CREATE_TIME >= DATE_FORMAT('{$param['start_date']}' ,'%Y-%m-%d %H:%i:%s')";//大于等于起始日期
        }
        if ($param['date_type'] == '1' && $param['end_date']) {
            $order_where .= " and a.CREATE_TIME < date_add(DATE_FORMAT('{$param['end_date']}' ,'%Y-%m-%d %H:%i:%s'), INTERVAL 1 day)";//小于结束日期的第二天凌晨
        }
        //渠道商ID
        if ($param['org_id']) {
            $order_where .= " and a.PROD_TOP_ORG_ID = '{$param['org_id']}'";
        }
        //订单产品类型
        if ($param['prod_type']) {
            $order_where .= " and a.ORDER_PROD_TYPE in ({$param['prod_type']})";
        }
        //更多查询
        if ($param['more_search']) {
            $order_where .= " and (a.ORDER_ID like '%{$param['more_search']}%' or a.PARENT_PROD_NAME like '%{$param['more_search']}%')";
        }
        /**==========拼接巴士bus的查询条件============**/
        $bus_where = " a.TOTAL_COST_PRICE > 0  ";//获取所有巴士
        //结算-预定时间 巴士的出发时间即预定时间
        if ($param['start_date']) {
            $bus_where .= " and a.START_TIME >= '{$param['start_date']}'";//大于等于起始日期
        }
        if ($param['end_date']) {
            $bus_where .= " and a.START_TIME < date_add('{$param['end_date']}', INTERVAL 1 day)";//小于日期
        }
        //收款状态
        if ($param['settlement_status']) {
            $bus_where .= " and IFNULL(b.payment_status,1) = {$param['settlement_status']}";
        }

        //渠道商ID
        if ($param['org_id']) {
            $bus_where .= " and and a.BUS_ORG_ID = '{$param['org_id']}'";
        }
        //订单产品类型
        if (!empty($param['prod_type']) && $param['prod_type'] != '81,82,38,369') {//不是车的则不统计
            $bus_where .= " and a.ID = 0 ";
        }
        //更多查询
        if ($param['more_search']) {
            $bus_where .= " and (a.id like '%{$param['more_search']}%' or d.LINE_NAME like '%{$param['more_search']}%')";
        }
        return array('order_where' => $order_where, 'bus_where' => $bus_where, 'param' => $param);
    }

    //检测是否可以生成对账单或取消关账
    public function checkCanCloses($getWhere)
    {
        /**============判断是否可以生成对账单===================**/

        $sql_1 = "
            SELECT SUM(x.cnt) cnt
            FROM (
            SELECT COUNT(1) cnt
            from bus_cost a
            LEFT JOIN order_finance_status b on a.ID =  b.order_id and b.type=2
            LEFT JOIN  opera_line d ON a.LINE_ID = d.LINE_ID
            WHERE  {$getWhere['bus_where']}
			and IFNULL(b.payment_status,1) != 2
            union ALL
            SELECT COUNT(1) cnt
            from order_main a
            LEFT JOIN order_finance_status b ON a.ORDER_ID = b.order_id and b.type=1
            WHERE  {$getWhere['order_where']}
			and IFNULL(b.payment_status,1) != 2
            ) x
        ";
        $getTotal = $this->query($sql_1);
        if ($getTotal['0']['cnt'] >= 1) {
            return false;
        }
        return true;
    }
    /////*********************对账单列表***********************/
    //获取账单总记录数
    public function getBalance($post)
    {
        $param = array(
            'current_page' => empty($post['current_page']) ? '1' : $post['current_page'],//当前页
            'page_size' => empty($post['page_size']) ? '10' : $post['page_size'],//每页展示条数
            'org_id' => empty($post['org_id']) ? '' : $post['org_id'],//供应商名称
            'batch_id' => empty($post['batch_id']) ? '' : $post['batch_id'],//对账单号
            'status' => empty($post['status']) ? '' : $post['status'],//对账状态1待核对，2待结算，3已结算,4已取消
        );
        $where = array();
        $where[] = ' type = 1 ';
        if (!empty($param['org_id'])) {
            $where[] = " org_id = {$param['org_id']} ";
        }
        if (!empty($param['batch_id'])) {
            $where[] = " batch_id like '%{$param['batch_id']}%' ";
        }
        if (!empty($param['status'])) {
            $where[] = " status = {$param['status']} ";
        }
        $whereStr = '';
        if (count($where)) {
            $whereStr .= implode(' and ', $where);
        }
        /**============第一页时返回总记录数和总页数==============**/
        if ($param['current_page'] == 1) {
            $sql_1 = "SELECT count(1) cnt
            FROM order_finance_balance_account
            WHERE $whereStr
            ";
            $getTotal = $this->query($sql_1);
            $data['page']['total_count'] = $getTotal[0]['cnt'];
            $data['page']['total_page'] = (string)ceil($getTotal[0]['cnt'] / $param['page_size']);
        }
        /**============获取分页列表数据==============**/
        $offset = ($param['current_page'] - 1) * $param['page_size'];
        $sql_2 = "SELECT id,org_id,org_name,price,commission_price,reparations_price,date,start_date,end_date,
        account_bank,account_num,account_name,sett_frequency,batch_id,
        CASE  status
        WHEN 1 THEN '待核对'
        WHEN 2 THEN '待结算'
         WHEN 3 THEN '已结算'
         WHEN 4 THEN '已取消'
         ELSE 'error' END as 'status_des',
         status
        from order_finance_balance_account
        WHERE $whereStr
        ORDER BY id DESC
        LIMIT {$param['page_size']} OFFSET {$offset}";
        $list = $this->query($sql_2);
        $json['code'] = '0';
        $json['info'] = '获取列表成功';
        $data['order_list'] = $list;
        $data['page']['page_size'] = $param['page_size'];
        $data['page']['current_page'] = $param['current_page'];
        $json['data'] = $data;
        return $json;

    }

    //取消对账
    public function cancelBalance($post)
    {
        $id = empty($post['id']) ? '' : $post['id'];//账单ID
        //校验对账单状态
        $flag = $this->checkBalanceStatus($id, array(1));
        if ($flag == false) {
            $json['code'] = '2';
            $json['info'] = '失败！待核对的账单才可以执行取消操作！';
            return $json;
        }
        //更改状态
        $sql = "
            update order_finance_balance_account a
            join order_finance_status b on a.batch_id=b.payment__batch_id
            set a.`status` =4,b.payment_status = 2
            WHERE a.`status` in(1)  and a.id = $id
        ";
        $flag = $this->edit($sql);
        if ($flag) {
            $json['code'] = '0';
            $json['info'] = '取消成功！';
        } else {
            $json['code'] = '1';
            $json['info'] = '取消失败！';
        }
        return $json;
    }

    //确认对账
    public function confirmedBalance($post)
    {
        $id = empty($post['id']) ? '' : $post['id'];//账单ID
        //校验对账单状态
        $flag = $this->checkBalanceStatus($id, array(1));
        if ($flag == false) {
            $json['code'] = '2';
            $json['info'] = '失败！待核对的账单才可以确认核对！';
            return $json;
        }
        //更改状态
        $sql = "
            update order_finance_balance_account a
            set a.`status` =2
            WHERE a.`status` = 1  and a.id = $id
        ";
        $flag = $this->edit($sql);
        if ($flag) {
            $json['code'] = '0';
            $json['info'] = '成功！';
        } else {
            $json['code'] = '1';
            $json['info'] = '失败！';
        }
        return $json;
    }

    //确认结算
    public function balancing($post)
    {
        $id = empty($post['id']) ? '' : $post['id'];//账单ID
        //校验对账单状态
        $flag = $this->checkBalanceStatus($id, array(2));
        if ($flag == false) {
            $json['code'] = '2';
            $json['info'] = '失败！已核对的账单才可以结算！';
            return $json;
        }
        //更改状态
        $sql = "
            update order_finance_balance_account a
            join order_finance_status b on a.batch_id=b.payment__batch_id
            set a.`status` =3,b.payment_status =4
            WHERE a.`status` in(2)  and a.id = $id
        ";
        $flag = $this->edit($sql);
        if ($flag) {
            $json['code'] = '0';
            $json['info'] = '成功！';
        } else {
            $json['code'] = '1';
            $json['info'] = '失败！';
        }
        return $json;
    }

    //导出对账订单记录
    public function excelBalance()
    {
        $id = empty($_GET['id']) ? '' : $_GET['id'];//账单ID
        $sql1 = "select org_name,start_date,end_date,order_type from order_finance_balance_account WHERE id = {$id}";
        $info = $this->query($sql1);
        if (empty($info)) {
            return false;
        }

        $sql = "
          select z.* FROM (
              SELECT c.ID '订单ID','' as '渠道订单ID',c.START_TIME '出游时间',a.start_date '预定时间',
                '' as 'order_description',
                d.LINE_NAME '产品名称','巴士成本' as '产品类型','' as '房型/票种',0 as '间夜/票数',
                c.TOTAL_COST_PRICE '成本',
               (SELECT IFNULL(SUM(v.reparations),0)  from order_main u
                JOIN order_finance_reparations v on u.order_id = v.order_id
                WHERE u.RUN_ID = c.RUN_ID and u.RUN_BUS_ORDER_ID = c.BUS_ORDER_ID
                ) 
                as '赔款',
                (SELECT SUPPLIER_NAME FROM base_supplier WHERE c.BUS_ORG_ID = ID) '供应商'             
                from order_finance_balance_account a
                JOIN order_finance_status b on a.batch_id = b.payment__batch_id and b.type=2
                JOIN bus_cost c on b.order_id = c.ID
                LEFT JOIN opera_line d on c.LINE_ID = d.LINE_ID
                WHERE c.ID={$id}            
                UNION ALL            
                SELECT
                c.ORDER_ID '订单ID',c.OUTSIDE_ORDER_NO '渠道订单ID',
                (SELECT MAX(RUN_DATE) from order_main WHERE c.ORDER_ID = PARENT_ORDER_ID) '出游时间',    
                c.CREATE_TIME '预定时间',      
                c.ORDER_DESCRIPTION 'order_description',      
                (SELECT CONCAT(PARENT_PROD_NAME, '<br />',PROD_START_STATION_RES_NAME,' - ',PROD_END_STATION_RES_NAME) from order_main WHERE PARENT_ORDER_ID = c.ORDER_ID LIMIT 1)  '产品名称',
                CASE  
                WHEN  c.ORDER_PROD_TYPE in (25,26) THEN  '酒店'
                WHEN  c.ORDER_PROD_TYPE in (81,82,38,369) THEN  '车票'
                WHEN  c.ORDER_PROD_TYPE in (258,282,311) THEN  '门票'
                else '' END as '产品类型',
                '' as '房型/票种',0 as '间夜/票数',
                c.ORDER_PRICE '订单金额',
                IFNULL((SELECT reparations from order_finance_reparations WHERE order_id=c.ORDER_ID),0) as '赔款',
                a.org_name '供应商'
                from order_finance_balance_account a
                JOIN order_finance_status b on a.batch_id = b.payment__batch_id and b.type=1
                JOIN order_main c on b.order_id = c.ORDER_ID
                WHERE a.id={$id}
            ) z
            ORDER BY  z.出游时间 
        ";
        $list = $this->query($sql);
        if (empty($list[0]['订单ID'])) {
            return false;
        }
        if (empty($list) == false) {
            foreach ($list as $key => $value) {
                $order_description = $value['order_description'];
                unset($list[$key]['order_description']);
                if(empty($order_description)) {
                    continue;
                }
                $list[$key]['间页/票数'] = 0;
                $list[$key]['房型/票种'] = '';
                $scripTionArr = explode('|', $order_description);
                foreach ($scripTionArr as $vl) {
                    if (empty($vl)) {
                        continue;
                    }
                    $vls = explode(',', $vl);
                    if (empty($vls['2'])) {
                        $list[$key]['间页/票数'] = $vls[0];
                    } else {
                        $list[$key]['间页/票数'] = $vls['2'];
                        $list[$key]['房型/票种'] = $vls['0'];
                    }
                }
            }
        }
        /**======生成excel=========**/

        ob_clean();
        $fileName = '【' . $info[0]['org_name'] . '】应付账单-' . $info[0]['start_date'] . '~' . $info[0]['end_date'] . '.csv';
        $fileName = iconv("utf-8", "gb2312", $fileName);
        header("Content-type:text/csv");
        header("Content-Disposition:attachment;filename=" . $fileName);
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Expires:0');
        header('Pragma:public');
        $data = '';
        foreach ($list as $key => $val) {
            if ($key == 0) {
                $data .= implode(',', array_keys($val)) . "\n";
            }
            $data .= '"' . implode('","', array_values($val)) . "\"\n";
        }
        $data = chr(0xEF) . chr(0xBB) . chr(0xBF) . $data;
        echo $data;
        return '';
    }

    //检测对账单状态
    public function checkBalanceStatus($id, $status)
    {
        $sql = "SELECT `status`
        from order_finance_balance_account
        WHERE id = $id";
        $info = $this->query($sql);
        if (empty($info[0]['status']) || !in_array($info[0]['status'], $status)) {
            return false;
        }
        return true;
    }
}