ThinkPHP5.0

sql查询

    /** 
     * 获取订单配送留言板列表
     *
     * @param int $deliveryOrderId 配送订单ID
     * @param int $orderId 订单ID
     * @param int $size 每页显示数量
     * @param int $current 当前页码
     * @return void
     */
    public function order_delivery_message_board_list($deliveryOrderId,$orderId=0,$size=10, $current=1)
    {
        list($uid, $organization_id, $group_id, $user_rs) = $this->get_user_info();
        $header = $this->request->header(); //获取header信息
        $os = isset($header['platform'])?$header['platform']:'';
        var_dump($os);

        $list = Db::name('order_delivery_message_board')
        ->alias('a')
        ->field("a.id,organization_id as organizationId,uname,role_id as roleId,role_name as roleName,order_id as orderId,order_type as orderType,delivery_order_id as deliveryOrderId,images,message_type as messageType,message,view_msg as viewMsg,create_time as createTime")
        ->where('a.organization_id', $organization_id)
        ->where(function($query)use($deliveryOrderId){
            if($deliveryOrderId)
            {
                $query->where('a.delivery_order_id', '=', $deliveryOrderId);
            }
        })
        ->where(function($query)use($orderId){
            if($orderId)
            {
                $query->where('a.order_id', '=', $orderId);
            }
        })
        ->where(function($query)use($orderNamesArr){
            if(count($orderNamesArr)>0)
            {
                 foreach ($orderNamesArr as $index => $orderName) {
                     $query->whereOr('order_name', 'like', '%' . $orderName . '%');
                }
            }
        })
        ->where(' ( ( a.follow_uid IN (0,'.$uid.') and find_in_set(4, a.operation_roles)>0 ) or ( a.second_follow_uid='.$uid.' and find_in_set(4, a.second_operation_roles)>0 ) ) ')
        ->whereOr(function($query)use($transfer_order_ids){
            if($transfer_order_ids)
            {
                $query->where('a.order_id', 'IN', $transfer_order_ids);
                $query->where('a.follow_status', '>=', 10);
                $query->where('b.status', '>=', 10);
            }
        })
        ->where(function($query)use($fTime, $keyword, $surgeryStartDate, $surgeryEndDate,$saleUid){
            if($keyword)
            {
                $query->where('b.order_number|b.hospital_name|b.hospital_department_name|b.hospital_doctor_name|b.patient_name|b.surgerys|b.sale_user_name', 'LIKE', '%'.$keyword.'%');
            }
            if($surgeryStartDate && $surgeryEndDate)
            {
                $query->where('b.surgery_time', '>=', $surgeryStartDate.' 00:00:00');
                $query->where('b.surgery_time', '<=', $surgeryEndDate.' 23:59:59');
            }
            if($saleUid)
            {
                $query->where('b.sale_uid', $saleUid);
            }
            if($fTime)
            {
                if(substr($fTime,0,1)=='m')
                {
                    $num = substr($fTime, 1);
                    $query_start = date('Y-m-01 00:00:00');
                    $query_end = date('Y-m-'.date('t').' 23:59:59');

                    if($num>1)
                    {
                        $query_start = date('Y-m-01 00:00:00', strtotime('-'.($num-1).' month'));
                        $query_end = date('Y-m-d H:i:s');
                    }
                    $query->where('b.create_time', '>=', $query_start)->where('b.create_time', '<=', $query_end);
                }

                if(substr($fTime,0,1)=='y')
                {
                    $num = substr($fTime, 1);

                    $query_start = date($num.'-01-01 00:00:00');
                    $query_end = date($num.'-12-31 23:59:59');
                    $query->where('b.create_time', '>=', $query_start)->where('b.create_time', '<=', $query_end);
                }
            }
        })

        ->where('organization_id', 'in', [1,2])
        ->order('create_time asc')
        // ->fetchSql(true) // 为true 打印sql，而不是真的执行，但不能配合paginate()使用
        ->paginate(['list_rows'=>intval($size), 'page'=>intval($current)]);
        // var_dump($list);die;

        $list = $list->toArray();

        $list['total'] = intval($list['total']);
        $list['last_page'] = intval($list['last_page']);
        $data['records'] = $list['data'];
        $data['total'] = $list['total'];
        $data['size'] = intval($list['per_page']);
        $data['current'] = $list['current_page'];
        $data['pages'] = $list['last_page'];
        $this->success('处理成功', $data);
    }

}