/** * 判断商品编号是否存在 */ private function Isexist($goodsNo, $id = 0) { $organID = Commonmodel::getOrganID(); if ($id == 0) { // 添加 $model = DealerGoods::model()->findAll("GoodsNO= '{$goodsNo}' and OrganID= {$organID}"); if (count($model) > 0) { // 大于0 则已存在 return false; } else { // 可以添加 return true; } } else { // 修改 $model = DealerGoods::model()->findAll("ID !=" . $id . " AND GoodsNO='{$goodsNo}' and OrganID= {$organID}"); if (count($model) > 0) { // 大于0 则已存在 return false; } else { // 可以添加 return true; } } }
/** * * 获取商品信息 * @param type array() $prams * Description 根据传入的阐述 * 参数传入方法: array('OrganID'=>'机构ID','goodsName'=>'商品名称',...); * 如果需要分页,array('rows'=>'每页显示条数',...); 返回结果 获取总条数: $data[0]['count']; * @return array * 使用方法 : 1、DealerGoods::getGoodsInfo(); * 3、DealerGoods::getGoodsInfo(array('goodsName'=>'商品001')); 等 */ public static function getGoodsInfo($params = '') { $criteria = new CDbCriteria(); $criteria->select = "*"; $criteria->order = "t.ID DESC"; //排序条件:update_time,id倒叙 // $criteria->with = array('goodsspec', 'goodspack'); $criteria->condition = 't.ISdelete =1'; // 查询条件 if (is_array($params)) { $organID = $params['OrganID']; // 机构ID $rows = $params['rows']; $curpage = $params['page']; // 每页显示条数 $goodsNO = $params['goodsNO']; // 商品编号 $goodsName = $params['goodsName']; // 商品名称 $brandID = $params['BrandID']; // 商品品牌ID $brand = $params['BrandName']; // 商品品牌名 $OENO = $params['OENO']; // 商品OE号 $IsSale = $params['IsSale']; // 是否上架 $IsPro = $params['IsPro']; // 是否促销 // $gbigparts = $params['gbigparts']; // 大类 // $gsubparts = $params['gsubparts']; // 子类 // $gcpname = $params['gcpname']; // 标准名称 $gcpnametxt = $params['gcpnametxt']; // 标准名称 $gmake = $params['gmake']; // 厂家 $gcar = $params['gcar']; // 车系 $gyear = $params['gyear']; // 年款 $gmodel = $params['gmodel']; // 车型 if (empty($IsSale)) { // 是否上架 $criteria->addCondition("t.IsSale = 1", "AND"); } elseif ($IsSale == 'all') { $criteria->addBetweenCondition('IsSale', 0, 1, "AND"); } else { if ($IsSale == 2) { $criteria->addCondition("IsSale = 0", "AND"); } else { $criteria->addCondition("t.IsSale = 1", "AND"); } } if (empty($IsPro) || $IsPro == 'all') { // 是否促销 $criteria->addBetweenCondition('t.IsPro', 0, 1, "AND"); } else { if ($IsPro == 2) { // 不是促销的 $criteria->addCondition("t.IsPro = 0", "AND"); } else { // 是促销的 // $Times = time() - 24 * 60 * 60 * 7 * 2 ; $criteria->addCondition("t.IsPro = 1", "AND"); // $criteria->addCondition("ProTime < $Times", "AND"); } } if (!empty($goodsNO)) { // 商品编号 $criteria->addSearchCondition("t.GoodsNO", "{$goodsNO}", "AND"); } if (!empty($goodsName)) { // 商品名称 $criteria->addSearchCondition("t.Name", "{$goodsName}", "AND"); } if (!empty($brandID)) { // 品牌ID $criteria->addCondition("t.BrandID = {$brandID} ", "AND"); } if (!empty($brand)) { // 品牌名称 $criteria->addCondition("t.Brand = '{$brand}' ", "AND"); } if (!empty($organID)) { // 机构名称 $criteria->addCondition("t.OrganID = {$organID} ", "AND"); } // if (!empty($gbigparts)) { // if (!empty($gcpname)) { // $criteria->addCondition("t.BigParts = $gbigparts ", "AND"); // $criteria->addCondition("t.CpName = $gcpname ", "AND"); // } else { // $criteria->addCondition("t.BigParts = $gcpname ", "AND"); // } // } if (!empty($gcpnametxt) && $gcpnametxt != "请选择标准名称") { $criteria->addCondition("t.CpNameTxt = '{$gcpnametxt}'", "AND"); // $criteria->addCondition("t.CpNameTxt = '空气滤清器'", "AND"); } if ($OENO) { $OENOs = self::getOENOcondition($OENO); if ($OENOs) { $criteria->addInCondition('t.ID', $OENOs, "AND"); } else { $criteria->addInCondition('t.ID', array(0), "AND"); } } if (!empty($gmake)) { // 车型车系 $arr = array(); $arr['gmake'] = $gmake ? $gmake : 0; $arr['gcar'] = $gcar ? $gcar : 0; $arr['gyear'] = $gyear ? $gyear : 0; $arr['gmodel'] = $gmodel ? $gmodel : 0; $goodsIDS = self::getVehcondition($arr); //var_dump($goodsIDS); exit; if ($goodsIDS) { $criteria->addInCondition('t.ID', $goodsIDS, "AND"); } else { $criteria->addInCondition('t.ID', array(0), "AND"); } // $MakeInfo = D::queryFrontMakeInfo($gmake); // $SeriesInfo = D::queryFrontSeriesInfo($gcar); } // var_dump($params); } else { if (!empty($params) && !is_array($params)) { return '参数传入错误'; } } $count = DealerGoods::model()->count($criteria); // $rows = $rows ? $rows : 10; // $offset = $curpage * $rows + 1; // $criteria->limit = $rows; //取1条数据,如果小于0,则不作处理 // $criteria->offset = $offset; $pages = new CPagination($count); $pages->pageSize = $rows ? $rows : 10; $pages->applyLimit($criteria); $goodses = DealerGoods::model()->findAll($criteria); // var_dump($goodses); $data = array(); $data[0]['count'] = $count; foreach ($goodses as $key => $goods) { $data[$key]['ID'] = $goods['ID']; $data[$key]['OrganID'] = $goods['OrganID']; $organInfo = self::getOrganName($goods['OrganID']); $data[$key]['OrganName'] = $organInfo['organName']; $data[$key]['Name'] = $goods['Name']; $data[$key]['goodsName'] = F::msubstr($goods['Name'], 0, 20); $data[$key]['gName'] = mb_substr($goods['Name'], 0, 20, 'utf-8'); $data[$key]['Pinyin'] = $goods['Pinyin']; $data[$key]['Pinyin2'] = F::msubstr($goods['Pinyin'], 0, 10); $data[$key]['Brand'] = self::getBrandByID($goods['BrandID']); $data[$key]['Brand'] = $data[$key]['Brand'] ? $data[$key]['Brand'] : $goods['Brand']; $data[$key]['goodsBrand'] = $goods['BrandID']; $data[$key]['GoodsNO'] = $goods['GoodsNO']; $data[$key]['OENO'] = $goods['OENO']; $data[$key]['OENOS'] = self::getOENOSByGoodsID($goods['ID']) ?: $goods['OENO']; // 车型车系 $vehs = explode('、', self::getVehicleByGoodsID($goods['ID'])); // 'seriesName'=>'','makeId'=>'','makeName'=>''); //检索商品本身的适用车型,如果有子集的车型则显示有子集的车型 $data[$key]['Vehicle'] = self::getCurrentVehicle($arr); $data[$key]['Vehicle'] = $data[$key]['Vehicle'] ? $data[$key]['Vehicle'] : $vehs[0]; $data[$key]['Vehicles'] = self::getVehicleByGoodsID($goods['ID']); $data[$key]['PartsLevel'] = $goods['PartsLevel']; $data[$key]['Memo'] = $goods['Memo']; $data[$key]['Price'] = $goods['Price']; // 参考价 if ($goods['IsPro'] == 1) { if (empty($goods['ProPrice']) || $goods['ProPrice'] == 0) { $data[$key]['ProPrice'] = $goods['Price']; // 促销价 } else { $data[$key]['ProPrice'] = $goods['ProPrice']; // 促销价 } } $data[$key]['LogisticsPrice'] = $goods['LogisticsPrice']; // 物流价 //$data[$key]['BigParts'] = Commonmodel::getCategory($goods['BigParts']); //$data[$key]['SubParts'] = Commonmodel::getCategory($goods['SubParts']); //$data[$key]['CpName'] = Commonmodel::getCategory($goods['CpName']); $data[$key]['CpNameTxt'] = $goods['CpNameTxt']; $data[$key]['mainCategory'] = $goods['BigParts']; $data[$key]['subCategory'] = $goods['SubParts']; $data[$key]['leafCategory'] = $goods['CpName']; // $data[$key]['sutecar'] = F::msubstr($this->getcar($goods['ID'])); $data[$key]['IsSale'] = $goods['IsSale'] == 1 ? '已上架' : '已下架'; $data[$key]['IsUpSale'] = $goods['IsSale']; $data[$key]['proTime'] = date("Y-m-d", $goods['ProTime']) . '--' . date("Y-m-d", $goods['ProTime'] + 60 * 60 * 24 * 14); // 促销时间 // 商品属性 $data[$key]['Weight'] = ""; $data[$key]['Length'] = ""; $data[$key]['Wide'] = ""; $data[$key]['Height'] = ""; $data[$key]['Volume'] = ""; $data[$key]['ValidityDate'] = ""; $data[$key]['ValidityType'] = ""; $data[$key]['Specifica'] = ""; $data[$key]['Unit'] = ""; $data[$key]['BganCompany'] = ""; $data[$key]['BganGoodsNO'] = ""; $data[$key]['PartsNO'] = ""; $data[$key]['JiapartsNO'] = ""; // 嘉配号 $data[$key]['ImageUrl'] = ""; // 图像名称 $data[$key]['DetectionImg'] = ""; // 检测图像 $cmodel = DealerGoodsSpec::model()->findByAttributes(array('GoodsID' => $goods['ID'])); if ($cmodel) { $data[$key]['Weight'] = $cmodel->Weight; $data[$key]['Length'] = $cmodel->Length; $data[$key]['Wide'] = $cmodel->Wide; $data[$key]['Height'] = $cmodel->Height; $data[$key]['Volume'] = $cmodel->Volume; $data[$key]['ValidityType'] = $cmodel->ValidityType; $data[$key]['ValidityDate'] = $cmodel->ValidityDate; if ($cmodel->ValidityType == 1) { $data[$key]['Validity'] = '不保修'; } if ($cmodel->ValidityType == 2) { $data[$key]['Validity'] = '保装车'; } if ($cmodel->ValidityType == 3) { $data[$key]['Validity'] = $cmodel->ValidityDate; } $data[$key]['Specifica'] = $cmodel->Specifica; $data[$key]['Unit'] = $cmodel->Unit; $data[$key]['BganCompany'] = $cmodel->BganCompany; $data[$key]['BganGoodsNO'] = $cmodel->BganGoodsNO; $data[$key]['PartsNO'] = $cmodel->PartsNO; $data[$key]['JiapartsNO'] = $cmodel->JiapartsNO; // 嘉配号 $data[$key]['ImageUrl'] = $cmodel->ImageUrl; // 图像名称 $data[$key]['DetectionImg'] = $cmodel->DetectionImg; // 检测图像 } // 商品包装 $data[$key]['pWeight'] = ""; $data[$key]['pVolume'] = ""; $data[$key]['MinQuantity'] = ""; $cmodel = DealerGoodsPack::model()->findByAttributes(array('GoodsID' => $goods['ID'])); if ($cmodel) { $data[$key]['pWeight'] = $cmodel->Weight; $data[$key]['pVolume'] = $cmodel->Volume; $data[$key]['MinQuantity'] = $cmodel->MinQuantity; } } return $data; }
function parse($excelfile, $template, $data = array()) { try { //获取上传文件的文件名扩展名 $extend = strtolower(strrchr($excelfile, '.')); $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($excelfile); if (!$objPHPExcel) { $error = '加载Excel出错'; return array('success' => false, 'error' => $error); } $objWorksheet = $objPHPExcel->getActiveSheet(); //取得活动sheet if (!$objWorksheet) { $error = '加载Excel出错'; return array('success' => false, 'error' => $error); } $title = $objWorksheet->getTitle(); //取得sheet名称 $highestRow = $objWorksheet->getHighestRow(); //取得总行数 $highestColumn = $objWorksheet->getHighestColumn(); //取得总列数 $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //总列数 //执行结果 $error = ""; $first_row = array(); for ($col = 0; $col < $highestColumnIndex; $col++) { $first_row[$col] = $objWorksheet->getCellByColumnAndRow($col, 1)->getValue(); if ($first_row[$col] == '商品编号') { $GoodsNO_col = $col; } } //验证表结构,表名称和字段列表 if (!$this->validateExcel($template, $first_row)) { $error = "Excel内容与模板不符合"; //$error = $first_row; return array('success' => false, 'error' => $error); } //生成插入语句的头部 $sql_header = $this->generateSqlHeader($template, $first_row); if ($sql_header == "") { $error = "SQL语句头部生成失败"; return array('success' => false, 'error' => $error); } //生成SQL语句 $sql = $sql_header; $i = 0; $list = array(); for ($row = 2; $row <= $highestRow; $row++) { //每行的第一列数据不能为空 $first_value = $objWorksheet->getCellByColumnAndRow(0, $row)->getValue(); $GoodsNO_value = $objWorksheet->getCellByColumnAndRow($GoodsNO_col, $row)->getValue(); if (in_array($GoodsNO_value, $list)) { $success = false; $error = '导入的商品编号重复'; return array('success' => $success, 'error' => $error); } if (empty($first_value)) { continue; } $data_new = array(); $sql_data = '('; ////注意highestColumnIndex的列数索引从0开始 for ($col = 0; $col < $highestColumnIndex; $col++) { $data_new[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); if ($col == $GoodsNO_col) { $criteria = new CDbCriteria(); $criteria->addCondition("GoodsNO = '{$data_new[$col]}'"); $criteria->addCondition("OrganID =" . Commonmodel::getOrganID()); $criteria->addCondition("ISdelete = 1"); // echo Commonmodel::getOrganID();exit; $bool = DealerGoods::model()->find($criteria); if (!empty($bool)) { $success = false; $error = '商品编号已存在'; return array('success' => $success, 'error' => $error); } } // if($col==4){ // 大类 // $data_new[$col] = DealerBigparts::getBigpartsID( $data_new[$col]); // } // if($col==5){ // 子类 // $data_new[$col] = DealerSubparts::getSubpartsID($data_new[$col]); // } // if($col==6){ // 标准名称 // $data_new[$col] = DealerCpname::getCpNameID($data_new[$col]) ; // } $sql_data .= "'" . trim($data_new[$col]) . "',"; } if ($template == "dealergoods") { // $sql_data .= "'" . $data['OrganID'] . "','" . $data['UserID'] . "','" . $data['CreateTime'] . "'"; } $sql_data .= ")"; //var_dump($sql_data);exit; //验证数据是否正确 //$this->validateData($data_new[$col]); $sql .= $sql_data . ','; $list[$i] = $GoodsNO_value; $i++; } $sql = rtrim($sql, ",") . ";"; //返回结果数据 $success = false; if ($error == "" && $sql != "") { $success = true; } } catch (Exception $e) { $success = false; $error = '解析Excel出错' . $e->getMessage(); } //echo $sql;exit; return array('success' => $success, 'error' => $error, 'sql' => $sql); }
private function procount() { $organID = Commonmodel::getOrganID(); $progoods = DealerGoods::model()->count(array("condition" => "IsPro = 1 and IsSale = 1 and ISdelete = 1 and t.OrganID = " . $organID)); return $progoods; }