private function upImage($upfile)
{
$organID = Commonmodel::getOrganID();
$DetectionImg = $upfile;
if (!empty($DetectionImg['name'])) {
$fileDir = "dealer/" . $organID . '/';
$filePath = Yii::app()->params['uploadPath'] . $fileDir;
if (!file_exists($filePath)) {
mk_dir($filePath, '777', true);
}
// $tp = array("image/gif", "image/pjpeg", "image/jpeg", "image/png");
// //检查上传文件是否在允许上传的类型
// if (!in_array($DetectionImg["type"], $tp)) {
// $message = '对不起,您上传图片的格式不正确';
// }
if ($DetectionImg["size"] > 1024 * 1024 * 2) {
$message = '对不起,您上传图片的太大!!';
}
if ($message) {
$rs = array('success' => 0, 'errorMsg' => $message);
echo json_encode($rs);
exit;
}
$imageName = $fileDir . UploadsFile::uploadImage($DetectionImg, $DetectionImg['name'], $DetectionImg['tmp_name'], $filePath);
}
return $imageName;
}
/**
* 批量上传商品
*/
public function actionUploadgoods()
{
$this->pageTitle = Yii::app()->name . '-' . "商品管理";
//文件模板为product
$template = "makergoods";
$userID = Yii::app()->user->id;
$organID = Commonmodel::getOrganID();
//上传文件
if ($_POST['leadExcel'] == "true") {
$filename = rand(1000, 10000) . Yii::app()->user->id . strrchr($_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/dealer/execl/";
$filePath = Yii::app()->params['uploadPath'] . 'maker/excel/' . $organID . '/';
$upload_result = UploadsFile::uploadFile($filename, $tmp_name, $filePath);
//如果上传成，则解析Excel文件
if ($upload_result['success']) {
//解析Excel文件，返回结果为错误消息，如果不为空则表明发生错误
$uploadfile = $upload_result['uploadfile'];
$dataImport = new MakeGoodsImport();
$dataImport->GoodsHeadArr(Yii::app()->request->getParam('importCategory'));
$createtime = time();
$data = array('OrganID' => $organID, 'UserID' => $userID, 'CreateTime' => $createtime);
$result = $dataImport->parse($uploadfile, $template, $data);
//如果不成功则返回错误结果
if ($result['success'] == false) {
echo json_encode(array('message' => $result['error'], 'success' => false));
Yii::app()->end();
}
$templaterRows = $result['templaterRows'];
$Sdata = $result['Sdata'];
// $bool = false;
$message = array();
if ($Sdata && $templaterRows) {
$Cpname = DealerCpname::model()->find("OrganID=:organID and CpNameID=:CpNameID", array(':organID' => $organID, ":CpNameID" => Yii::app()->request->getParam('importCategory')));
$com = 0;
$errkey = 0;
//调用商品编号验证唯一性
$res = $this->checkgoodno();
$version_sql = "insert into tbl_make_goods_version (\n version_name, organID,userID,goods_oe,\n goods_brand,goods_id,goods_no,goods_name,benchmarking_brand,benchmarking_sn,\n standard_id,inventory,senddays,description\n ) values";
$value_sql = "insert into tbl_make_goods_values (organID,userID,standard_id,value,version_name,goods_id,template_id)\n values";
$excel_no = array();
foreach ($Sdata as $key => $dval) {
$makegoods = new MakeGoods();
$makegoodsvesion = new MakeGoodsVersion();
$vesionAtt = $makegoodsvesion->attributes;
$arr = array();
$continue = false;
$k = 0;
$dval[$k] = ltrim($dval[$k]);
if (in_array($dval[$k], $res)) {
$message[$errkey] = "第" . ($com + 1) . "条数据导入失败 原因：商品编号已存在";
$errkey++;
$continue = true;
}
if (!empty($excel_no) && in_array($dval[$k], $excel_no)) {
$message[$errkey] = "第" . ($com + 1) . "条数据导入失败 原因：Excel表格中有重复的商品编号";
$errkey++;
$continue = true;
}
if (empty($dval[$k]) && $dval[$k] != 0) {
$message[$errkey] = "第" . ($com + 1) . "条数据导入失败 原因：商品编号为空";
$errkey++;
$continue = true;
}
foreach ($templaterRows as $pkey => $pval) {
// if ($pval == 'goods_no') {
// $dval[$k] = ltrim($dval[$k]);
// if (empty($dval[$k])) {
// $message[$errkey] = "第" . ($com + 1) . "条数据导入失败 原因：商品编号为空";
// $errkey++;
// $continue = true;
// } else {
// if(in_array($dval[$k],$res))
// {
// $message[$errkey] = "第" . ($com + 1) . "条数据导入失败 原因：商品编号已存在";
// $errkey++;
// $continue = true;
// }
// // $ifcontinue = MakeGoodsVersion::model()->find("goods_no=:NO and ISdelete=0 and organID=:organID", array(":NO" => $dval[$k],':organID'=>$organID));
// }
// }
// if (!empty($ifcontinue)) {
// $message[$errkey] = "第" . ($com + 1) . "条数据导入失败 原因：商品编号已存在";
// $errkey++;
// $continue = true;
// }
// $ifcontinue = "";
if ($pval == 'goods_name') {
$dval[$k] = ltrim($dval[$k]);
if (empty($dval[$k]) && $dval[$k] != 0) {
$message[$errkey] = "第" . ($com + 1) . "条数据导入失败 原因：商品名称为空";
$errkey++;
$continue = true;
}
}
if ($pval == 'goods_oe') {
$dval[$k] = ltrim($dval[$k]);
$dval[$k] = str_replace("，", ",", $dval[$k]);
if (empty($dval[$k]) && $dval[$k] != 0) {
$message[$errkey] = "第" . ($com + 1) . "条数据导入失败 原因：商品OE号为空";
$errkey++;
$continue = true;
}
}
if (substr($pval, 0, 1) == '_') {
$pval = substr($pval, 1);
$arr[$k]['value'] = $dval[$k];
$arr[$k]['template_id'] = $pval;
} else {
if ($pval == 'brand') {
$vesionAtt["goods_brand"] = $this->getBrandID($dval[$k]);
} elseif ($pval == 'inventory') {
if ($dval[$k] == '有') {
$vesionAtt[$pval] = 1;
} else {
$vesionAtt[$pval] = 0;
}
} else {
$vesionAtt[$pval] = $dval[$k];
}
}
$k++;
}
$com++;
if ($continue) {
continue;
}
$excel_no[$k] = $dval[$k];
//$makegoodsvesion->attributes = $vesionAtt;
$time = time();
$goods_sql = "insert into tbl_make_goods (create_time,updatetime,organID,userID,NewVersion)\n\t\t\t\t\t\t\t\tvalue ({$time},{$time},{$organID},{$userID},'V1.0.0')";
$goods_res = DBUtil::execute($goods_sql);
$a = 1;
$lastGoodsID = Yii::app()->db->getLastInsertID();
if ($goods_res) {
$a = 2;
//执行版本表
$version_sql .= " ('V1.0.0',{$organID},{$userID},'{$vesionAtt['goods_oe']}','{$vesionAtt['goods_brand']}',{$lastGoodsID},\n\t\t\t\t\t\t\t\t'{$vesionAtt['goods_no']}','{$vesionAtt['goods_name']}','{$vesionAtt['benchmarking_brand']}',\n \t '{$vesionAtt['benchmarking_sn']}',{$Cpname->CpNameID},'{$vesionAtt['inventory']}','{$vesionAtt['senddays']}'\n \t ,'{$vesionAtt['description']}'),";
//生产参数值sql
foreach ($arr as $kv => $vval) {
$value_sql .= " ({$organID},{$userID},'{$Cpname->CpNameID}','{$vval['value']}','V1.0.0','{$lastGoodsID}','{$vval['template_id']}'),";
}
//插入适用车型
if ($vesionAtt[carmodel] || $vesionAtt[carmodel] == 0) {
$pinyin = F::pinyin1($vesionAtt[carmodel]);
$carmodel_sql = "insert into tbl_make_goods_vehicle (CreateTime,UpdateTime,OrganID,UserID,VersionName,GoodsID,`Name`,PinYin)\n value ({$time},{$time},{$organID},{$userID},'V1.0.0',{$lastGoodsID},'{$vesionAtt['carmodel']}','{$pinyin}')";
$carmodel_res = DBUtil::execute($carmodel_sql);
}
}
}
}
if ($a == 2) {
$version_sql = rtrim($version_sql, ",");
$version_sql = preg_replace('/\\\\/', '/', $version_sql);
$version_res = DBUtil::execute($version_sql . ';');
$value_sql = rtrim($value_sql, ",");
$value_sql = preg_replace('/\\\\/', '/', $value_sql);
$value_res = DBUtil::execute($value_sql . ';');
}
// $insert_sql = $result['sql'];
//
// $sql_result = DBUtil::execute($insert_sql);
//如果SQL执行不成功则返回错误结果
if (!empty($message)) {
// foreach ($message as $key => $val) {
// $resDa.="<span class='errcolor'>$val</span><br>";
//
// }
echo json_encode(array('success' => false, 'message' => $message));
Yii::app()->end();
} else {
// 上传成功，则把上传成功的数据展示出来
echo json_encode(array('success' => true, 'message' => '导入商品成功'));
Yii::app()->end();
}
} else {
$message = $upload_result['error'];
echo json_encode(array('success' => false, 'message' => $message));
Yii::app()->end();
}
}
}
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'));
}
}
/**
* 经销商上传下属机构信息
*/
public function actionSubdealerupload()
{
//文件模板为product
$template = "subdealer";
$dealerID = Commonmodel::getOrganID();
//上传文件
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'] . '/dealer/execl/';
$upload_result = UploadsFile::uploadFile($filename, $tmp_name, $filePath);
//var_dump($upload_result);
//如果上传成，则解析Excel文件
if ($upload_result['success']) {
//解析Excel文件，返回结果为错误消息，如果不为空则表明发生错误
$uploadfile = $upload_result['uploadfile'];
$dataImport = new DataImport();
$endtime = time() + 24 * 60 * 60 * 2 * 7;
$data = array('flag' => '1', 'UserID' => $dealerID);
$result = $dataImport->parse($uploadfile, $template, $data);
//如果不成功则返回错误结果
if (!$result['success']) {
$message = $result['error'];
//var_dump($message);
$this->render('batchimport', array('message' => $message));
exit;
}
//var_dump($result);
$insert_sql = $result['sql'];
$sql_result = DbUtil::execute($insert_sql);
//如果SQL执行不成功则返回错误结果
if ($sql_result && !$sql_result['result']) {
$this->render('batchimport', array('message' => $sql_result['error']));
exit;
}
//查询上传成功的产品信息
$message = "文件上传成功！";
$this->redirect('subdealer');
} else {
$message = $upload_result['error'];
$this->render('batchimport', array('message' => $message));
}
}
}
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"));
}
}
