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