PHP DBUtil::queryAll方法代码示例

 public static function queryFrontVehicleWearpartinfo($params)
 {
     //检查参数
     if (!isset($params["vehicleID"]) || empty($params["vehicleID"])) {
         exit;
     }
     //查询参数
     $vehicleID = (int) $params["vehicleID"];
     //车型易损件更换周期信息
     $sql = "select c.WearpartName, b.ChangeMileage, b.ChangePeriod, b.ChangeAddition, b.ChangeNum, b.OENO, b.Specification" . " from {{vehicle_to_wearpart_config}} a,{{wearpart_config_to_item}} b, {{wearpart_item}} c" . " where a.WearpartConfigID = b.WearpartConfigID" . "   and b.WearpartID = c.WearpartID" . "   and a.vehicleid = :vehicleID";
     $sqlParams = array(':vehicleID' => $vehicleID);
     $result = DBUtil::queryAll($sql, $sqlParams);
     return $result;
 }

 public static function queryFrontVehicleMaintenanceIteminfo($params)
 {
     //检查参数
     if (!isset($params["Code"]) || empty($params["Code"])) {
         exit;
     }
     //查询参数
     //$vehicleID = (int) $params["vehicleID"];
     //车型保养周期信息息
     $sql = "select distinct c.Code, c.Name, b.Mileage, b.Period, b.Desc, b.InFirst, b.InSecond," . " d.FirstMileage, d.FirstPeriod, d.SecondMileage, d.SecondPeriod, d.IntervalMileage, d.IntervalPeriod" . " from {{vehicle_to_maintenance_config}} a, {{maintenance_config_to_item}} b, {{gcategory}} c, " . "{{maintenance_config}} d, {{front_mtc_relation}} e" . " where a.MaintenanceConfigID = b.MaintenanceConfigID and b.Code = c.Code" . " and a.MaintenanceConfigID = d.MaintenanceConfigID and a.vehicleid = e.MtcID" . " and e.FrontCode = :FrontCode";
     $sqlParams = array(':FrontCode' => $params["Code"]);
     $result = DBUtil::queryAll($sql, $sqlParams);
     return $result;
 }

 public function actionGetcategorylists()
 {
     $criteria = new CDbCriteria();
     $model = new GoodsCategory();
     $organID = Commonmodel::getOrganID();
     //$manufacturer_id=Yii::app()->user->id;
     $sql = "select distinct a.name,a.id as categoryID,a.code,a.desc," . "(select count(distinct(b.id)) from tbl_make_goods b,tbl_make_goods_version c where  c.goods_id=b.id and c.goods_category=a.id and b.organID=a.organID and b.ISdelete='0' and b.NewVersion=c.version_name) as count" . " from tbl_make_goods_category a" . " where a.organID='{$organID}' group by a.name";
     $sql .= ' order by id desc';
     //echo $sql;
     $result = DBUtil::queryAll($sql);
     $total = 0;
     if ($result) {
         $count = count($result);
         $pages = new CPagination($count);
         //设置分页页数
         $pages->pageSize = isset($_GET['rows']) ? intval($_GET['rows']) : 10;
         $pages->applyLimit($criteria);
         $result = Yii::app()->db->createCommand($sql . " LIMIT :offset,:limit");
         //绑定分页参数
         $result->bindValue(':offset', $pages->currentPage * $pages->pageSize);
         $result->bindValue(':limit', $pages->pageSize);
         $result = $result->queryAll();
         $total = $pages->itemcount;
     }
     //系统生成类别代号
     unset($criteria);
     $criteria = new CDbCriteria();
     $criteria->addCondition("organID=" . $organID);
     $criteria->order = 'id desc';
     $model = MakeGoodsCategory::model()->find($criteria);
     if ($model) {
         preg_match('/\\d+/', $model['code'], $res);
         $lastcode = intval($res[0]);
         if ($lastcode < 9) {
             $code = '00' . ($lastcode + 1);
         } elseif ($lastcode < 99) {
             $code = '0' . ($lastcode + 1);
         } else {
             $code = $lastcode + 1;
         }
     } else {
         $code = '001';
     }
     $data['total'] = $total;
     $data['rows'] = is_null($result) ? '' : $result;
     $data['code'] = $code;
     echo json_encode($data);
 }

 public function actionGeturl()
 {
     $organID = Commonmodel::getOrganID();
     $GoodsID = $_GET['GoodsID'];
     $sql = "select ImageUrl,ImageName from tbl_dealer_goods_image_relation where OrganID=" . $organID . " and GoodsID=" . $GoodsID;
     //         $data = DBUtil::queryAll($sql);
     $data = DBUtil::queryAll($sql) ? DBUtil::queryAll($sql) : 0;
     echo json_encode($data);
 }

 public static function queryPartsByPartname($params)
 {
     //检查参数
     if (!isset($params["partname"]) || empty($params["partname"])) {
         return null;
     }
     if (!isset($params['modelId']) || empty($params['modelId'])) {
         return null;
     }
     //查询参数
     $partname = $params["partname"];
     $modelId = $params["modelId"];
     //查询配件
     $sql = " select a.partid as partId, a.name as partName,a.oeno as n_oeno, concat(left(a.OENO,2),'****',right(a.OENO,2)) as oeno, a.jpid as jpno, " . " d.name as makeName, c.modelid as modelId, c.name as modelName, b.groupid as subGroupId, b.name as subGroupName, " . " (select g.name from {{epc_group}} g where g.groupid = b.parentid) as mainGroupName" . " from {{epc_parts}} a, {{epc_group}} b, {{epc_model}} c, {{epc_makes}} d" . " where a.groupid = b.groupid " . "   and b.modelid = c.modelid" . "   and c.makeid = d.makeid" . "   and (a.name like :partname or a.ename like :partcname ) ";
     $partname = '%' . $partname . '%';
     $sqlParams = array(':partname' => $partname, ':partcname' => $partname);
     if (!empty($modelId)) {
         $sql .= " and c.modelid = :modelid";
         $sqlParams[':modelid'] = $modelId;
     }
     $sql .= " limit 51";
     $result = DBUtil::queryAll($sql, $sqlParams);
     return $result;
 }

 static function getVehcondition($params)
 {
     $gmake = $params['gmake'];
     // 厂家
     $gcar = $params['gcar'];
     // 车系
     $gyear = $params['gyear'];
     // 年款
     $gmodel = $params['gmodel'];
     // 车型
     $goodsID = '';
     if (!empty($gmake)) {
         if (!empty($gcar)) {
             // 车型车系
             if (!empty($gyear)) {
                 if (!empty($gmodel)) {
                     //  $sql = "select distinct GoodsID from tbl_dealer_goods_vehicle_relation where Make= '{$gmake}' and (Car = '{$gcar}' or Car = 0) and (Year = '{$gyear}' or Year = 0) and (Model = '{$gmodel}' or Model=0)";
                     $sql = "select distinct GoodsID from tbl_dealer_goods_vehicle_relation where Make= '{$gmake}' and (Car = '{$gcar}') and (Year = '{$gyear}') and (Model = '{$gmodel}')";
                 } else {
                     $sql = "select distinct GoodsID from tbl_dealer_goods_vehicle_relation where Make= '{$gmake}' and (Car = '{$gcar}') and (Year = '{$gyear}')";
                 }
             } else {
                 $sql = "select distinct GoodsID from tbl_dealer_goods_vehicle_relation where Make= '{$gmake}' and (Car = '{$gcar}')";
             }
         } else {
             $sql = "select distinct GoodsID from tbl_dealer_goods_vehicle_relation where Make= '{$gmake}'";
         }
         $goodsIDs = DBUtil::queryAll($sql);
         if (!empty($goodsIDs)) {
             foreach ($goodsIDs as $value) {
                 $goodsID[] = $value['GoodsID'];
             }
         }
     }
     return $goodsID;
 }

 private function checkgoodno()
 {
     $organID = Commonmodel::getOrganID();
     $sql = "select a.id, b.goods_no from tbl_make_goods a,tbl_make_goods_version b \n    \twhere a.organID={$organID} and a.IsSale='0' and a.ISdelete='0' \n    \tand a.id=b.goods_id and a.NewVersion=b.version_name and b.organID={$organID} \n    \tand b.ISdelete='0'\n    \t";
     $datas = array();
     $res = DBUtil::queryAll($sql);
     foreach ($res as $key => $val) {
         $datas[$val['id']] = $val['goods_no'];
     }
     return $datas;
 }

 private function getMakeName($goodsID)
 {
     $sql = "SELECT DISTINCT Marktxt FROM tbl_dealer_goods_vehicle_relation WHERE GoodsID = {$goodsID}";
     $data = DBUtil::queryAll($sql);
     if (!$data) {
         return '';
     }
     $str = '';
     foreach ($data as $value) {
         $str .= ' ' . $value['Marktxt'];
     }
     return $str;
 }

 public function actionGetcontacts()
 {
     $pushID = $_GET['pushID'];
     $sql = "select * from tbl_business_contacts where id in (select ContactID from tbl_push_contact_relation where PushID = {$pushID})";
     $models = DBUtil::queryAll($sql);
     $count = count($models);
     $data = array();
     foreach ($models as $key => $value) {
         $data[$key]['id'] = $value['id'];
         $data[$key]['companyID'] = $value['contact_user_id'];
         $data[$key]['customertype'] = $value['customertype'];
         $data[$key]['cooperationtype'] = $value['cooperationtype'];
         $data[$key]['customercategory'] = $value['customercategory'];
         $data[$key]['name'] = $value['name'];
         $data[$key]['sex'] = $value['sex'];
         $data[$key]['companyname'] = $value['companyname'];
         $data[$key]['phone'] = $value['phone'];
         $data[$key]['email'] = $value['email'];
     }
     $rs = array('total' => $count, 'rows' => $data);
     echo json_encode($rs);
 }

 public function actionDetail()
 {
     $model = Dealer::model()->find("userID=:userID", array(":userID" => $_GET['dealer']));
     // 机构照片
     $organphotoSql = "select id, photoName from tbl_dealer_organphoto where dealerID = " . $_GET['dealer'];
     $organphotos = DBUtil::queryAll($organphotoSql);
     $brands = DealerBrand::model()->findAll("OrganID = {$_GET['dealer']}");
     $data = array();
     foreach ($brands as $key => $brand) {
         $data[$key]['brandname'] = $brand['BrandName'];
     }
     //主营车系
     $dealerv = DealerVehicle::model()->findAll("userID=:userID", array(":userID" => $_GET['dealer']));
     //主营品类
     $cpnames = DealerCpname::model()->findAll("OrganID=:userID", array(":userID" => $_GET['dealer']));
     $this->render("detail", array('model' => $model, 'organphotos' => $organphotos, 'dealerv' => $dealerv, 'cpnames' => $cpnames, 'data' => $data));
 }

 public static function queryGoodsYearModels2($params)
 {
     if (!isset($params['seriesId']) || empty($params['seriesId'])) {
         return array();
     }
     $seriesId = $params['seriesId'];
     $sql = "select a.modelid as modelId, a.name as name, a.alias as alias, a.ename as ename, a.year as year FROM {{goods_model}} a where seriesid =" . $seriesId;
     $models = DBUtil::queryAll($sql);
     $year = array();
     $new_models = array();
     foreach ($models as $key => $value) {
         $year[$key] = $value['year'];
     }
     $unique_year = array_unique($year);
     $i = 0;
     if (!$models) {
         return array();
     }
     foreach ($models as $k => $v) {
         if (!empty($new_models[$i])) {
             if (!in_array($v['year'], $new_models[$i])) {
                 $i++;
             }
         }
         if (in_array($v['year'], $unique_year)) {
             $new_models[$i]['year'] = $v['year'];
             foreach ($models as $key => $model) {
                 if ($model['year'] == $new_models[$i]['year']) {
                     $new_models[$i][$key]['modelId'] = $model['modelId'];
                     $new_models[$i][$key]['name'] = $model['name'];
                     $new_models[$i][$key]['alias'] = $model['alias'];
                     $new_models[$i][$key]['year'] = $model['year'];
                     $new_models[$i][$key]['ename'] = $model['ename'];
                 }
             }
         }
     }
     return $new_models;
 }

 public function actionPromotions()
 {
     $this->pageTitle = Yii::app()->name . ' - 经销商促销查询';
     if (isset($_GET)) {
         $search['keywords'] = $_GET['keywords'];
         $search['provice'] = $_GET['provice'];
         $search['city'] = $_GET['city'];
         $search['make'] = $_GET['make'];
         $search['car'] = $_GET['car'];
         $search['OENO'] = $_GET['OENO'];
         $search['system_type'] = $_GET['system_type'];
         $search['cp_name'] = $_GET['cp_name'];
         $search['is'] = $_GET['is'];
     }
     //organName,FoudingDate,province,city,area,Phone,businessCate,businessBrand
     //$searchsql="SELECT * FROM tbl_dealer AS makeorgan $join where $conditions";
     //$searchsql="SELECT distinct makeorgan.userID, organName,FoudingDate,province,city,area,address,Phone,BusinessBrand,BusinessCate FROM tbl_dealer AS makeorgan $join where $conditions";
     $conditions = " and  1=1 ";
     $join = "";
     if ($search) {
         $keywords = trim($search['keywords']);
         if ($keywords && $keywords != "配件名称、配件品牌") {
             $conditions .= " AND d.userID in (select dp.userID from tbl_dealer_promotion as pd where dp.goodsName like '%{$keywords}%' OR dp.goodsBrand like '%{$keywords}%' )";
         }
         $oeno = trim($search['OENO']);
         if (!empty($oeno) && $oeno != "输入OE号") {
             // OE号
             $conditions .= " AND dp.OENO like '%{$oeno}%' ";
         }
         if ($search['system_type'] != '') {
             if ($search['cp_name'] != '') {
                 $conditions .= " AND d.userID in (select dpc.userid from tbl_dealer_promotion_cpname as dpc where dpc.system_type = '{$search['system_type']}' AND dpc.cp_name ='{$search['cp_name']}' )";
             } else {
                 $conditions .= " AND d.userID in (select dpc.userid from tbl_dealer_promotion_cpname as dpc where dpc.system_type = '{$search['system_type']}') ";
             }
         }
         if ($search['provice'] != '') {
             if ($search['city'] != '') {
                 $conditions .= " AND d.province = '{$search['provice']}' AND d.city ='{$search['city']}' ";
             } else {
                 $conditions .= " AND d.province = '{$search['provice']}' ";
             }
         }
         if ($search['make'] != 0) {
             if ($search['car'] != 0) {
                 $conditions .= " and d.userID in (select dpa.userid from tbl_dealer_vehicle as dpa where dpa.businessCar = {$search['make']} and dpa.businessCarModel = {$search['car']})";
                 //$join.=" INNER JOIN tbl_dealer_vehicle AS carrelation ON carrelation.userID = makeorgan.userID AND carrelation.businessCar = '{$search['make']}' AND carrelation.businessCarModel = '{$search['car']}'";
             } else {
                 $conditions .= " and d.userID in (select dpa.userid from tbl_dealer_vehicle as dpa where dpa.businessCar = '{$search['make']}')";
             }
         }
     }
     $searchsql = "select  distinct d.userID, organName,FoudingDate,province,city,area,address,Phone,BusinessBrand from tbl_dealer as d, tbl_dealer_promotion as dp ,tbl_make_organ_group_relation as mogr where d.userID = dp.userID " . $conditions;
     $pagesize = 10;
     $page = !empty($_GET['page']) ? $_GET['page'] : 1;
     $page = $pagesize * ($page - 1);
     $result = DBUtil::queryAll($searchsql);
     $count = count($result);
     $limit = " limit {$page}, {$pagesize} ";
     $results = DBUtil::queryAll($searchsql . $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('promotions', array('models' => $results, 'page' => $page, 'count' => $count, 'search' => $search));
 }

 /**
  * 下属机构登记
  */
 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 actionImportprice()
 {
     $organID = Commonmodel::getOrganID();
     //上传excel表格
     if ($_POST['leadExcel'] == "true") {
         $filename = iconv("utf-8", "gb2312", $_FILES['inputExcel']['name']);
         $tmp_name = $_FILES['inputExcel']['tmp_name'];
         //$filePath = dirname(Yii::app()->BasePath) . "/themes/default/uploadsfile/dealer/execl/";
         $filePath = Yii::app()->params['uploadPath'] . 'maker/excel/';
         $upload_result = UploadsFile::uploadFile($filename, $tmp_name, $filePath);
         if ($upload_result['success']) {
             //解析Excel文件，返回结果为错误消息，如果不为空则表明发生错误
             $uploadfile = $upload_result['uploadfile'];
             $extend = strtolower(strrchr($uploadfile, '.'));
             if ($extend != '.xlsx' && $extend != '.xls') {
                 unlink($uploadfile);
                 echo json_encode(array('message' => '文件类型错误,只支持excel文件', 'success' => false));
                 exit;
             }
             $readerType = $extend == '.xlsx' ? 'Excel2007' : 'Excel5';
             $objReader = new PHPExcel();
             $objReader = PHPExcel_IOFactory::createReader($readerType);
             //use Excel5 for 5fromat ,use excel2007 for 2007 format
             $objPHPExcel = $objReader->load($uploadfile);
             $objWorksheet = $objPHPExcel->getActiveSheet();
             //取得活动sheet
             if (!$objPHPExcel || !$objWorksheet) {
                 unlink($uploadfile);
                 echo json_encode(array('message' => '加载Excel出错,请稍后再试', 'success' => false));
                 exit;
             }
             $highestRow = $objWorksheet->getHighestRow();
             //取得总行数
             $highestColumn = $objWorksheet->getHighestColumn();
             //取得总列数
             $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
             //总列数
             $result = $this->getCustomtype();
             foreach ($result as $v) {
                 $type[$v['TypeID']] = $v['TypeName'];
             }
             //表头是否为商品编号
             $columnName = $objWorksheet->getCellByColumnAndRow(0, 3)->getValue();
             if ($columnName != '商品编号(必填)' || $highestColumn <= chr(ord('A') + 2) || $highestColumn > chr(ord('A') + 2 + count($type))) {
                 unlink($uploadfile);
                 echo json_encode(array('message' => '表格结构与要求不符！', 'success' => false));
                 exit;
             }
             //获取客户类别及ID
             $first_row = array();
             $typelist = array();
             for ($col = 3; $col < $highestColumnIndex; $col++) {
                 $first_row[$col]['TypeName'] = $objWorksheet->getCellByColumnAndRow($col, 3)->getValue();
                 if (in_array($first_row[$col]['TypeName'], $typelist)) {
                     unlink($uploadfile);
                     echo json_encode(array('message' => '表格内有重复的客户类别:' . $first_row[$col]['TypeName'], 'success' => false));
                     exit;
                 }
                 $first_row[$col]['TypeID'] = array_search($first_row[$col]['TypeName'], $type);
                 if (!$first_row[$col]['TypeID']) {
                     unlink($uploadfile);
                     echo json_encode(array('message' => '表格内有不存在的客户类别:' . $first_row[$col]['TypeName'], 'success' => false));
                     exit;
                 }
                 $typelist[] = $first_row[$col]['TypeName'];
             }
             //获取商品编号、ID
             $goodsnolist = array();
             $Goods_value = array();
             for ($row = 4; $row <= $highestRow; $row++) {
                 $Goods_value[$row]['goodsno'] = $objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
                 if (in_array($Goods_value[$row]['goodsno'], $goodsnolist)) {
                     unlink($uploadfile);
                     echo json_encode(array('message' => '表格内有重复的商品编号:' . $Goods_value[$row]['goodsno'], 'success' => false));
                     exit;
                 }
                 $goodsnolist[] = $Goods_value[$row]['goodsno'];
                 $sql = "select goods_id from tbl_make_goods_version where organID={$organID} and goods_no='{$Goods_value[$row]['goodsno']}'and ISdelete='0'";
                 $tempArr = DBUtil::queryAll($sql);
                 if (!$tempArr) {
                     unlink($uploadfile);
                     echo json_encode(array('message' => '表格内有不存在的商品编号:' . $Goods_value[$row]['goodsno'], 'success' => false));
                     exit;
                 }
                 $Goods_value[$row]['GoodsID'] = $tempArr[0]['goods_id'];
             }
             $sql = "select ID,GoodsID,TypeID,Price from tbl_make_price_relation";
             $priceArr = DBUtil::queryAll($sql);
             $newArr = array();
             if ($priceArr) {
                 foreach ($priceArr as $v) {
                     $newArr[$v['GoodsID'] . 'a' . $v['TypeID']]['ID'] = $v['ID'];
                     $newArr[$v['GoodsID'] . 'a' . $v['TypeID']]['Price'] = $v['Price'];
                 }
             }
             $dSql = 'delete from tbl_make_price_relation where ID in(';
             $iSql = 'insert into tbl_make_price_relation(GoodsID,TypeID,Price) values';
             //将表格内的价格存到数据库
             for ($row = 4; $row <= $highestRow; $row++) {
                 $GoodsID = $Goods_value[$row]['GoodsID'];
//.........这里部分代码省略.........