thinkphp：判断数据是否存在，再作为数据库的判断条件（给数据库查询增加额外的查询条件）

方法一：用thinkphp的语法去写

public function select_endProduceinfo(){
    $like_info = input('post.like_info', '');
    $page = input('post.page', 1);
    $pageSize = input('post.pageSize', 10);
    $start = ($page - 1) * $pageSize;
    $username = input('post.username','');
    $search_line1 = input('post.search_line1','');
    $search_line2 = input('post.search_line2','');
    $search_line3 = input('post.search_line3','');
    $search_line4 = input('post.search_line4','');
    $employee_num = db::table('fa_account_info')->where(['username' => $username])->value('employee_num');
  //全部数据信息  
    $data['info'] = db::table('wip_transactions')
                    ->alias('d') // 设置wip_jobs_all的别名
                    ->join(['wip_jobs_all' => 'a'], 'd.wip_entity_name = a.wip_entity_name')
                    ->join(['sf_item_no' => 'c'], 'a.primary_item = c.item_no')
                    ->join(['hr_employees' => 'e'], 'e.employee_num = d.employee_num')
                    ->field('d.*, c.item_no as item_no, c.item_name as item_name, c.units as uom, e.employee_name as employee_name')
                    ->where(['d.employee_num' => $employee_num])
                    ->where(['d.wip_entity_name'=>['like','%'.$like_info.'%']]);
    // 添加查询条件
    if (!empty($search_line1)) {
        $data['info']->where('d.wip_entity_name', 'LIKE', "%$search_line1%");
    }
    if (!empty($search_line2)) {
        $data['info']->where('d.operation_code', 'LIKE', "%$search_line2%");
    }
     if (!empty($search_line3)) {
        $data['info']->where('item_no', 'LIKE', "%$search_line3%");
    }
     if (!empty($search_line4)) {
        $data['info']->where('item_name', 'LIKE', "%$search_line4%");
    }
    $data['info'] = $data['info']
        ->order(['transaction_date' => 'desc'])
        ->limit($start, $pageSize)
        ->select();
//求出数据总数
    $data['total'] =  db::table('wip_transactions')
                    ->alias('d') // 设置wip_jobs_all的别名
                    ->join(['wip_jobs_all' => 'a'], 'd.wip_entity_name = a.wip_entity_name')
                    ->join(['sf_item_no' => 'c'], 'a.primary_item = c.item_no')
                    ->join(['hr_employees' => 'e'], 'e.employee_num = d.employee_num')
                    ->field('d.*, c.item_no as item_no, c.item_name as item_name, c.units as uom, e.employee_name as employee_name')
                    ->where(['d.employee_num' => $employee_num])
                    ->where(['d.wip_entity_name'=>['like','%'.$like_info.'%']]);  
    // 添加查询条件
     if (!empty($search_line1)) {
        $data['info']->where('d.wip_entity_name', 'LIKE', "%$search_line1%");
    }
    if (!empty($search_line2)) {
        $data['info']->where('d.operation_code', 'LIKE', "%$search_line2%");
    }
     if (!empty($search_line3)) {
        $data['info']->where('item_no', 'LIKE', "%$search_line3%");
    }
     if (!empty($search_line4)) {
        $data['info']->where('item_name', 'LIKE', "%$search_line4%");
    }         
    $data['total'] = $data['total']
        ->order(['transaction_date' => 'desc'])
        ->count();
    //处理数据
    for ($i = 0; $i < count($data['info']); $i++) {
      //计算时差
      $data['info'][$i]['hours_diff'] = number_format(($data['info'][$i]['end_date'] - $data['info'][$i]['begin_date']) / 3600, 4);
      if ($data['info'][$i]['transaction_type'] != '良品') {
        $data['info'][$i]['hours_diff'] = '';
      }
      //处理时间
      $data['info'][$i]['transaction_date'] = date('Y-m-d', $data['info'][$i]['transaction_date']);
      $data['info'][$i]['begin_date'] = date('Y-m-d H:i:s', $data['info'][$i]['begin_date']);
      $data['info'][$i]['end_date'] = date('Y-m-d H:i:s', $data['info'][$i]['end_date']);
      if (!$data['info'][$i]['transaction_quantity']) {
        $data['info'][$i]['transaction_quantity'] = 0;
      }
    }
    echo json_encode($data);
}

方法二：嵌套mysql的语法

public function select_POorder_deatil(){
    $page = input('post.page', 1);
    $pageSize = input('post.pageSize', 10);
    $start = ($page - 1) * $pageSize;
    $order_number = input('post.order_number','');
    //头信息
    $header_info = "
            SELECT  pha.po_num, pha.status,order_type, pha.note,pha.payment_term ,pha.order_date,pha.app_remark, pha.creation_date, pha.tax_amount,
                    pha.tax_flag,pha.all_line_amount, pha.created_by, pha.youhui_amount, vendor_name,pha.po_all_amount,pha.vendor_code,
                    pha.payment_type,pha.tax_name 
            FROM    po_headers_all pha, vendors v
            WHERE   v.vendor_code = pha.vendor_code
            AND     po_num= '" .$order_number."'
        ";
    $data['header_info'] =  Db::query($header_info);
    //处理日期
    for($i=0;$i<count($data['header_info']);$i++){
        $data['header_info'][$i]['creation_date'] = date('Y-m-d H:i:s', $data['header_info'][$i]['creation_date']);
        $data['header_info'][$i]['order_date'] = date('Y-m-d', $data['header_info'][$i]['order_date']);
    }
    //行信息
    $line_info = "
            SELECT  a.line,a.stockid,b.item_desc,b.item_name,a.uom,a.price,a.quantity,a.po_num,a.line_amount,a.last_update_date,
                    a.creation_date,ifnull(quantity_received,0) quantity_received,ifnull(quantity_accepted,0) quantity_accepted,
                    ifnull(quantity_deliveried,0) quantity_deliveried,ifnull(quantity_cancelled,0) quantity_cancelled,
                    ifnull(quantity_rejected,0) quantity_rejected,ifnull(quantity_billed,0) quantity_billed,a.created_by,
                    (select locationname from locations d where d.loccode=a.subinventory_code) locationname,a.need_date
            FROM    po_lines_all a,sf_item_no b
            where   a.stockid=b.item_no   
            and     po_num = '" .$order_number."' 
            ";
    $line_info .="  
                    ORDER BY a.line ASC
                    LIMIT $start, $pageSize 
                ";    
    $data['line_info'] =  Db::query($line_info);
     //处理日期
    for($i=0;$i<count($data['line_info']);$i++){
        $data['line_info'][$i]['creation_date'] = date('Y-m-d H:i:s', $data['line_info'][$i]['creation_date']);
    }
    //行总数
    $line_count = "
            SELECT COUNT(*) AS count
            FROM (
                SELECT  a.line,a.stockid,b.item_desc,b.item_name,a.uom,a.price,a.quantity,a.po_num,a.line_amount,a.last_update_date,
                    a.creation_date,ifnull(quantity_received,0) quantity_received,ifnull(quantity_accepted,0) quantity_accepted,
                    ifnull(quantity_deliveried,0) quantity_deliveried,ifnull(quantity_cancelled,0) quantity_cancelled,
                    ifnull(quantity_rejected,0) quantity_rejected,ifnull(quantity_billed,0) quantity_billed,a.created_by,
                    (select locationname from locations d where d.loccode=a.subinventory_code) locationname,a.need_date
            FROM    po_lines_all a,sf_item_no b
            where   a.stockid=b.item_no   
            and     po_num = '" .$order_number."' 
            ) AS subquery;";
    $result = Db::query($line_count);
    $data['line_info_total'] = isset($result[0]['count']) ? $result[0]['count'] : 0;    
    echo json_encode($data);
}