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']; 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')); } }