PHP DBUtil::execute方法代码示例

 public function actionDeleteimga()
 {
     $imageName = $_GET['xximage'];
     $targetFile = Yii::app()->params['uploadPath'] . $imageName;
     $sql = "delete from tbl_make_goods_image_relation where ImageUrl= '{$imageName}' ";
     $bools = DBUtil::execute($sql);
     $bool = false;
     if ($bools) {
         if (file_exists($targetFile)) {
             $bool = unlink($targetFile);
         }
         echo json_encode($bool);
         Yii::app()->end();
     } else {
         echo json_encode($bool);
         Yii::app()->end();
     }
 }

 public function actionAddimg()
 {
     $urlimg = explode(',', $_GET['urlimg']);
     //根据逗号拆分，得到图片信息的字符串
     $organID = Commonmodel::getOrganID();
     $sql = "insert into tbl_dealer_goods_image_relation (OrganID,GoodsID,ImageUrl,CreateTime,ImageName) values";
     foreach ($urlimg as $k => $value) {
         if ($value) {
             //去掉初始值0
             $addimg = explode(';', $value);
             //根据分号拆分，得到图片的相关信息
             if ($k != 1) {
                 $sql .= ",";
             }
             $sql .= "(";
             $sql .= $organID;
             $sql .= ",";
             $sql .= $addimg[1];
             //商品ID
             $sql .= ",'";
             $sql .= $addimg[0];
             //图片url
             $sql .= "',";
             $sql .= time();
             $sql .= ",'";
             $sql .= $addimg[2];
             //图片原名
             $sql .= "')";
         }
     }
     if (DBUtil::execute($sql)) {
         $rs = array('success' => 1, 'errorMsg' => '图片上传成功');
     } else {
         $rs = array('success' => 0, 'errorMsg' => '图片上传失败');
     }
     echo json_encode($rs);
 }

 public function actionUpdatetitleold()
 {
     if (!isset($_GET['do']) || $_GET['do'] != 'unipei') {
         echo '你没有权限访问！';
         return;
     }
     $sql = "select ID,Name,Pinyin,Brand,OENO from tbl_dealer_goods WHERE ISNULL(Title) OR Title = '' OR Title = '0'";
     $datas = DBUtil::queryAll($sql);
     echo '更新的数据：<br>';
     foreach ($datas as $key => $value) {
         $title = $value['Name'] . ' ' . $value['Pinyin'] . ' ' . $value['Brand'] . ' ' . $value['OENO'] . ' ' . $this->getMakeName($value['ID']);
         $updatesql = "UPDATE tbl_dealer_goods SET Title = '" . $title . " '  WHERE ID = " . $value['ID'];
         $bool = DBUtil::execute($updatesql);
         echo $key . ':' . $title . '<br>';
     }
     if ($bool) {
         echo '修改数据成功';
     } else {
         echo '修改数据失败 或没有数据需要更改';
     }
 }

 public function actionDeleteimg()
 {
     $imageName = $_GET['xximage'];
     $targetFile = Yii::app()->params['uploadPath'] . $imageName;
     $sql = "delete from tbl_dealer_organphoto where photoName= '{$imageName}' ";
     $bools = DBUtil::execute($sql);
     $bool = false;
     if ($bools) {
         if (file_exists($targetFile)) {
             $bool = unlink($targetFile);
         }
         echo json_encode($bool);
         exit;
     } else {
         echo json_encode($bool);
         exit;
     }
 }

 /**
  * 前市场车辆养护周期查询日志
  */
 public static function logUserQueryMaintenance($params)
 {
     try {
         //检查参数
         if (!isset($params["userID"]) || empty($params["userID"])) {
             return null;
         }
         if (!isset($params["vehicleID"]) || empty($params["vehicleID"])) {
             return null;
         }
         $userID = $params['userID'];
         $vehicleID = $params['vehicleID'];
         $querytype = 0;
         $nowtime = time();
         //查询前市场厂家，车型
         $sql = "select make,CONCAT(car ,' ',engine) as model from {{jpd_vehicle_mtc}} where vehicleMtcID = :vehicleID ";
         $sqlParams = array(':vehicleID' => $vehicleID);
         $vehicleinfo = DBUtil::query($sql, $sqlParams);
         if ($vehicleinfo) {
             $make = $vehicleinfo['make'];
             $model = $vehicleinfo['model'];
         }
         //插入前市场车型数据
         $sql = "insert into {{jpd_log_userquery_mtc}}(userid,querytype,querytime,vehiclemtcid,make,model)\n\t\t\t\t  values(:userID,:querytype,:querytime,:vehicleID,:make,:model)\t";
         $sqlParams = array(':userID' => $userID, ':querytime' => $nowtime, ':querytype' => $querytype, ':vehicleID' => $vehicleID, ':make' => $make, ':model' => $model);
         $result = DBUtil::execute($sql, $sqlParams);
         return $result;
     } catch (Exception $e) {
     }
 }

//.........这里部分代码省略.........
                 $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'];
                 for ($col = 3; $col < $highestColumnIndex; $col++) {
                     $pric = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
                     // $price = $pric ? $pric : 'NULL';
                     $TypeID = $first_row[$col]['TypeID'];
                     $key = $GoodsID . 'a' . $TypeID;
                     //删除价格
                     if (array_key_exists($key, $newArr)) {
                         if ($pric != $newArr[$key]['Price']) {
                             $id = $newArr[$key]['ID'];
                             $dSql .= "{$id},";
                             $iSql .= isset($pric) ? "({$GoodsID},{$TypeID},{$pric})," : '';
                         }
                     } else {
                         if (isset($pric)) {
                             $iSql .= "({$GoodsID},{$TypeID},{$pric}),";
                         }
                     }
                 }
             }
             $dSql = substr($dSql, 0, -1) . ')';
             $iSql = substr($iSql, 0, -1);
             $del = DBUtil::execute($dSql);
             $ins = DBUtil::execute($iSql);
             if ($del['result'] || $ins['result']) {
                 echo json_encode(array('message' => '商品价格修改成功！', 'success' => true));
                 unlink($uploadfile);
             } else {
                 echo json_encode(array('message' => '改价失败：没有价格变化！', 'success' => false));
                 unlink($uploadfile);
             }
         } else {
             $message = $upload_result['error'];
             echo json_encode(array('message' => $message, 'success' => false));
             exit;
         }
     } else {
         $this->redirect(array('makeprice/price'));
     }
 }

 public function actionImport()
 {
     $this->pageTitle = Yii::app()->name . '-' . "服务报价管理";
     $template = "serviceitems";
     $OrganID = Commonmodel::getOrganID();
     //获取当前修理厂的ID
     //上传文件
     if ($_FILES['inputExcel']['name']) {
         $filename = iconv("utf-8", "gb2312", $_FILES['inputExcel']['name']);
         $tmp_name = $_FILES['inputExcel']['tmp_name'];
         //$filePath = dirname(Yii::app()->BasePath) . "/themes/default/uploadsfile/servicer/excel/";
         $filePath = Yii::app()->params['uploadPath'] . "servicer/excel/";
         $upload_result = UploadsFile::uploadFile($filename, $tmp_name, $filePath);
         //如果上传成功，则解析Excel文件
         if ($upload_result['success']) {
             //解析Excel文件，返回结果为错误消息，如果不为空则表明发生错误
             $uploadfile = $upload_result['uploadfile'];
             $dataImport = new ServiceImport();
             $data = array('OrganID' => $OrganID);
             $result = $dataImport->parse($uploadfile, $template, $data);
             //如果不成功则返回错误结果
             if (!$result['success']) {
                 $message = $result['error'];
                 $this->render('index', array('message' => $message));
                 exit;
             }
             $insert_sql = $result['sql'];
             $sql_result = DBUtil::execute($insert_sql);
             //如果SQL执行不成功则返回错误结果
             if ($sql_result && !$sql_result['result']) {
                 $this->render('index', array('message' => $sql_result['error']));
                 exit;
             } else {
                 $message = "succeed";
                 $this->redirect(array('index', 'message' => $message, 'success' => TRUE));
             }
         } else {
             $message = $upload_result['error'];
             $this->render('index', array('message' => $message));
         }
     } else {
         $this->redirect(array("index"));
     }
 }