/**
* 判断商品编号是否存在
*/
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;
}
