PHP DbUtil::queryAll方法代码示例

 public function actionSearch()
 {
     $sqldealer = "select dealer.id,dealer.BusinessScope,dealer.organName,dealer.jiapartsID,dealer.province,dealer.city,dealer.area,dealer.address,dealer.Phone,dealer.keyword,dealer.BusinessBrand,dealer.userID,dealer.ContactPhone from tbl_dealer as dealer ";
     $searchSql = " where !ISNULL(dealer.organName)";
     if (!empty($_GET['search'])) {
         $search = $_GET['search'];
         $search['mainCategory'] = $_GET['jpmall_maincate'];
         $search['subCategory'] = $_GET['jpmall_subcate'];
         $search['leafCategory'] = $_GET['jpmall_cpname'];
         $search['cpname'] = $_GET['cpname'];
         if (!empty($search['brand'])) {
             $searchSql .= " AND dealer.userID in (select brand.OrganID from tbl_dealer_brand as brand where brand.BrandName  like '%{$search['brand']}%' ) ";
         }
         if (!empty($search['province'])) {
             if (!empty($search['city']) && $search['city'] != '0') {
                 $searchSql .= " AND dealer.province = '{$search['province']}' AND dealer.city = '{$search['city']}'";
             } else {
                 $searchSql .= " AND dealer.province = '{$search['province']}'";
             }
         }
         if ($search['mainCategory'] && $search['subCategory'] && $search['leafCategory']) {
             $searchSql .= " AND dealer.userID in (select mogr.OrganID from tbl_dealer_cpname as mogr where\n                        (mogr.BigpartsID = '{$search['mainCategory']}' and mogr.SubCodeID = '' and mogr.CpNameID = '')\n                     or (mogr.BigpartsID = '{$search['mainCategory']}' and mogr.SubCodeID = '{$search['subCategory']}' and mogr.CpNameID = '')\n                     or (mogr.BigpartsID = '{$search['mainCategory']}' and mogr.SubCodeID = '{$search['subCategory']}' and mogr.CpNameID = '{$search['leafCategory']}'))";
         } elseif ($search['subCategory'] && $search['mainCategory']) {
             $searchSql .= " AND dealer.userID in (select mogr.OrganID from tbl_dealer_cpname as mogr where\n                     (mogr.BigpartsID = '{$search['mainCategory']}' and mogr.SubCodeID = '{$search['subCategory']}') \n                   or (mogr.BigpartsID = '{$search['mainCategory']}' and mogr.SubCodeID = '')) ";
         } elseif ($search['mainCategory']) {
             $searchSql .= " AND dealer.userID in (select mogr.OrganID from tbl_dealer_cpname as mogr where mogr.BigpartsID = '{$search['mainCategory']}' ) ";
         }
         if (!empty($search['payway'])) {
             //echo $search['payway'];
             // 查询授权的经销商ID
             $organID = Commonmodel::getOrganID();
             $sql = "select dealer_id from tbl_make_empower_dealer where up_userID = {$organID}";
             if ($search['payway'] == 2) {
                 // 未授权经销商
                 $searchSql .= " AND dealer.userID NOT IN (select ed.DealerID from tbl_make_promit_brand ed where ed.OrganID = {$organID} ) ";
             } else {
                 if ($search['payway'] == 3) {
                     // 授权经销商
                     $searchSql .= " AND dealer.userID IN (select ed.DealerID from tbl_make_promit_brand ed where ed.OrganID = {$organID} ) ";
                 }
             }
         }
         //			if(!empty($search['vehicleMake'])){
         //				$carcriteria=new CDbCriteria();
         //				if (!empty($search['vehicleModel'])){
         //					$carcriteria->addCondition("t.businessCar = '".$search['vehicleMake']."'",'AND');
         //					$carcriteria->addCondition("t.businessCarModel = '".$search['vehicleModel'])."'",'AND');
         ////					$searchSql.=" AND vehicle.businessCar = '".$search['vehicleMake']."' AND vehicle.businessCarModel= '".$search['vehicleModel']."'";
         //				}else {
         //					$carcriteria->addCondition("t.businessCar = '".$search['vehicleMake']."'",'AND');
         ////					$searchSql.=" AND vehicle.businessCar = '".$search['vehicleMake']."'";
         //				}
         //				$carcriteria->distinct=true;
         //				$carcriteria->select='t.userid';
         //				$carmodels=DealerVehicle::model()->findAll($carcriteria);
         //				if (!empty($carmodels)){
         //					foreach ($carmodels as $car){
         //						$users[]=$car->userid;
         //					}
         //				}
         //				$criteria->addInCondition('userID', $users);
         //			}
     }
     $sqldealer .= $searchSql;
     $pagesize = 10;
     $result = DBUtil::queryAll($sqldealer);
     $count = count($result);
     $pagess = 1;
     if ($count % $pagesize) {
         $pagess = floor($count / $pagesize) + 1;
     }
     $page = !empty($_GET['page']) ? $_GET['page'] : 1;
     if ($page <= 1) {
         $page = 1;
     }
     if ($page > $pagess) {
         $page = $pagess;
     }
     $page = $pagesize * ($page - 1);
     $limit = " limit {$page}, {$pagesize} ";
     $dealers = DbUtil::queryAll($sqldealer . $limit);
     $pageData = array('total_rows' => $count, 'parameter' => '', 'list_rows' => $pagesize, 'page_name' => 'page', 'ajax_func_name' => '', 'method' => '');
     $page = new Pagination($pageData);
     $page = $page->show(1);
     $this->render("search", array('search' => $search, 'dealers' => $dealers, 'count' => $count, 'page' => $page, 'pagesize' => $pagesize));
 }

 /**
  * 下属机构登记
  */
 public function actionSubdealer()
 {
     $userID = Commonmodel::getOrganID();
     if ($_POST['search']) {
         $search = $_POST['search'];
         $where = "";
         $where .= " organName LIKE '%{$search}%' OR grade LIKE '%{$search}%' OR allowCate LIKE '%{$search}%' OR allowBrand LIKE '%{$search}%' OR allowProvince LIKE '%{$search}%' OR person LIKE '%{$search}%' OR phone LIKE '%{$search}%' and";
     }
     // 下级经销商
     $sqlsubdealer = "select * from tbl_dealer_subdealer where " . $where . " userID = {$userID} order by id desc ";
     $pagesize = 3;
     $page = !empty($_GET['page']) ? $_GET['page'] : 1;
     $page = $pagesize * ($page - 1);
     $result = DBUtil::queryAll($sqlsubdealer);
     $count = count($result);
     $limit = " limit {$page}, {$pagesize} ";
     $subdealers = DbUtil::queryAll($sqlsubdealer . $limit);
     $pageData = array('total_rows' => $count, 'parameter' => '', 'list_rows' => $pagesize, 'page_name' => 'page', 'ajax_func_name' => '', 'method' => '');
     $page = new Pagination($pageData);
     $page = $page->show(1);
     // var_dump($subdealers);
     $this->render('subdealer', array('subdealers' => $subdealers, 'page' => $page));
 }

 public function actionIndex()
 {
     $sqldealer = "select dealer.id,dealer.organName,dealer.jiapartsID,dealer.province,dealer.city,dealer.area,dealer.address,dealer.Phone,dealer.keyword,dealer.BusinessBrand,dealer.userID,dealer.ContactPhone from tbl_dealer as dealer ";
     $searchSql = " where !ISNULL(dealer.organName)";
     if ($_GET['search']) {
         $search['keyword'] = $keyword = trim($_GET['keyword'] == "OE号/商品名称/商品品牌" ? '' : $_GET['keyword']);
         //            $search['MainGroup'] = $MainGroup = $_GET['MainGroup'];
         //            $search['SubGroup'] = $SubGroup = $_GET['SubGroup'];
         //            $search['vehicleMake'] = $vehicleMake = $_GET['vehicleMake'];
         //            $search['vehicleModel'] = $vehicleModel = $_GET['vehicleModel'];
         $search['makecar'] = $makecar = trim($_GET['makecar'] == "请选择适用车系" ? '' : $_GET['makecar']);
         // 适用车系
         $search['jpmall_make'] = $make = $_GET['select_make'];
         $search['jpmall_series'] = $series = $_GET['select_series'];
         $search['jpmall_year'] = $year = $_GET['select_year'];
         $search['jpmall_model'] = $model = $_GET['select_model'];
         $search['province'] = $pro = $_GET['province'];
         $search['city'] = $city = $_GET['city'];
         $search['cpname'] = $cpname = $_GET['cpname'];
         //            $search['system_type'] = $system_type = $_GET['system_type'];
         $search['category'] = $category = $_GET['category'] == "请选择标准名称" ? '' : $_GET['category'];
         //当前页面的搜索
         //  if ($_GET['mainCategory']) {
         $search['mainCategory'] = $mainCategory = $_GET['jpmall_maincate'];
         $search['subCategory'] = $subCategory = $_GET['jpmall_subcate'];
         $search['leafCategory'] = $leafCategory = $_GET['jpmall_cpname'];
         //   }
         //首页的搜索
         if ($_GET['mainCategorySOrgan']) {
             $search['mainCategory'] = $mainCategory = $_GET['mainCategorySOrgan'];
             $search['subCategory'] = $subCategory = $_GET['subCategorySOrgan'];
             $search['leafCategory'] = $leafCategory = $_GET['leafCategorySOrgan'];
         }
         if ($_GET['SOrgan']) {
             $SOrgan = $_GET['SOrgan'];
             $search['make'] = $make = $SOrgan['make'];
             $search['series'] = $series = $SOrgan['series'];
             $search['year'] = $year = $SOrgan['year'];
             $search['model'] = $model = $SOrgan['model'];
         }
         $search['is'] = $_GET['is'];
         if (!empty($keyword) && $keyword != '0') {
             if ($keyword == "OE号，商品名称，商品品牌") {
                 $search['keyword'] = '';
             } else {
                 $sql = " select distinct a.id as goodsID " . "  from tbl_goods a ,tbl_goods_version b,tbl_goods_template d, tbl_goods_standard e" . '  where a.id=b.goods_id and a.NewVersion=b.version_name' . "  and b.templet_id=d.id" . "  and e.id=d.standard_id" . "  and (a.oe like '%{$search['keyword']}%'" . "  or (b.name like '%{$search['keyword']}%')" . "  or (a.brand like '%{$search['keyword']}%'))" . "  and a.ISdelete='N' and a.IsSale='Y'  " . "  group by a.id order by a.id desc";
                 $result = Yii::app()->db->createCommand($sql)->queryAll();
                 foreach ($result as $key => $val) {
                     $arr[$key] = $val['goodsID'];
                 }
                 if (empty($result)) {
                     $arr = array('11111111111', '222222222222');
                 }
                 if (!empty($arr)) {
                     $result = implode(',', $arr);
                     $sql = "select distinct a.dealer_id \n\t\t\t\t  \t\t\tfrom tbl_make_empower_dealer a,\n\t\t\t\t  \t\t\t\ttbl_make_empower_category b,\n\t\t\t\t  \t\t\t tbl_make_empower_category_relation c \n\t\t\t\t  \t\t\t where a.category=c.cate_id and a.category=b.id and c.cate_id=b.id\n\t\t\t\t  \t\t\t and b.userID=a.up_userID and goods_id in ({$result})";
                     $result = Yii::app()->db->createCommand($sql)->queryAll();
                     foreach ($result as $key => $val) {
                         $arr[$key] = $val['dealer_id'];
                     }
                     if (!empty($arr)) {
                         $result = implode(',', $arr);
                         $searchSql .= " and dealer.id in ({$result})";
                     }
                 }
             }
         }
         if ($search['jpmall_make'] != '') {
             $searchSql .= " and dealer.userID in (select userid from tbl_dealer_vehicle where businessMake = '{$make}' )";
         }
         if ($search['jpmall_series'] != '') {
             $searchSql .= " and dealer.userID in (select userid from tbl_dealer_vehicle where businessCar = '{$series}' )";
         }
         if ($search['jpmall_year'] != '') {
             $searchSql .= " and dealer.userID in (select userid from tbl_dealer_vehicle where businessYear = '{$year}' )";
         }
         if ($search['jpmall_model'] != '') {
             $searchSql .= " and dealer.userID in (select userid from tbl_dealer_vehicle where businessCarModel = '{$model}')";
         }
         //            if (!empty($vehicleMake) && $vehicleMake != '0') { // 车品牌
         //                if (!empty($vehicleModel) && $vehicleModel != '0') {
         //                    $searchSql .=" and dealer.userID in (select userid from tbl_dealer_vehicle where businessCar = '{$vehicleMake}' AND businessCarModel = '{$vehicleModel}' )";
         //                }else
         //                    $searchSql .=" and dealer.userID in (select userid from tbl_dealer_vehicle where businessCar = '{$vehicleMake}' )";
         //            }
         if (!empty($pro) && $pro != '0') {
             if (!empty($city) && $city != '0') {
                 $searchSql .= " AND dealer.province = '{$pro}' AND dealer.city = '{$city}'";
             } else {
                 $searchSql .= " AND dealer.province = '{$pro}'";
             }
         } else {
             // 默认所在城市
         }
         //            if ($search['system_type'] != '') {
         //                if ($search['cpname'] != '') {
         //                    $searchSql .= " AND dealer.userID in (select mogr.userID from tbl_make_organ_group_relation as mogr where mogr.father_code = '{$search['system_type']}' AND mogr.children_code ='{$search['cpname']}' )";
         //                } else {
         //                    $searchSql .= " AND dealer.userID in (select mogr.userID from tbl_make_organ_group_relation as mogr where mogr.father_code = '{$search['system_type']}') ";
         //                }
//.........这里部分代码省略.........

 public function actionServicersearch()
 {
     $this->pageTitle = Yii::app()->name . '-' . '地区修理厂';
     $sql = "select * from tbl_service as ser";
     $searchSql = " where !ISNULL(ser.serviceName)";
     if ($_GET) {
         $search['keyword'] = $_GET['keyWord'] == "机构名称或关键词" ? '' : $_GET['keyWord'];
         $search['province'] = $_GET['sprovince'];
         $search['city'] = $_GET['scity'];
         $search['area'] = $_GET['sarea'];
         if ($search['keyword']) {
             $searchSql .= " AND (ser.serviceName like '%{$search['keyword']}%' \n\t\t\t\tor ser.serviceIntro like '%{$search['keyword']}%' or ser.serviceContact like '%{$search['keyword']}%'\n\t\t\t\tor ser.serviceEmail like '%{$search['keyword']}%' or ser.serviceCellPhone like '%{$search['keyword']}%'\n\t\t\t\tor ser.serviceTelePhone like '%{$search['keyword']}%' or ser.serviceQQ like '%{$search['keyword']}%'\n\t\t\t\tor ser.serviceFounded like '%{$search['keyword']}%')";
         }
         if ($search['province']) {
             $searchSql .= " AND serviceProvince = {$search['province']}";
         }
         if ($search['city']) {
             $searchSql .= " AND serviceCity = {$search['city']}";
         }
         if ($search['area']) {
             $searchSql .= " AND serviceArea = {$search['area']}";
         }
     }
     // 合并sql语句
     $sql .= $searchSql;
     $pagesize = 10;
     $result = DBUtil::queryAll($sql);
     $count = count($result);
     $pagess = 1;
     if ($count % $pagesize) {
         $pagess = floor($count / $pagesize) + 1;
     }
     $page = !empty($_GET['page']) ? $_GET['page'] : 1;
     if ($page <= 1) {
         $page = 1;
     }
     if ($page > $pagess) {
         $page = $pagess;
     }
     $page = $pagesize * ($page - 1);
     $limit = " limit {$page}, {$pagesize} ";
     $service = DbUtil::queryAll($sql . $limit);
     $pageData = array('total_rows' => $count, 'parameter' => '', 'list_rows' => $pagesize, 'page_name' => 'page', 'ajax_func_name' => '', 'method' => '');
     $page = new Pagination($pageData);
     $page = $page->show(1);
     $this->render("servicersearch", array('search' => $search, 'service' => $service, 'count' => $count, 'page' => $page, 'pagesize' => $pagesize));
 }