云服务器 99 / 年,新老同享(可以99/年续费),开发者力荐特惠渠道,新客户在享受9折
阿里云推广

thinkphp5的excel导入数据事务处理代码片段

  • 内容
  • 评论
  • 相关
1.功能商品名称不能重复,包含excel里面不能重复,同时也不能和已经导入的商品名字重复。(没有通过数据库唯一约束实现)
2.循环里面一条一条处理,一旦有一条数据处理失败则回滚,都成功才会最终提交。(mysql数据库引擎需要是innodb)

3.如果多行数据有问题,失败问题原因都会展示。(错误累计展示,方便对excel修改,再次导入,抛异常就无法错误数据累计多行展示了)

代码如下:

public function ajax_daoru_good() {
	//平台客户的keynum
	$basekeynum = session("cn_accountinfo.basekeynum");
	$param = Request::instance()->param();
	$file = $_FILES['file'];
	//此处省略把文件读取到数组代码
	$arr = excelToArray($destination);
	//读取到数组
	$error_flag="0";
	$error_msg="";
	foreach ($arr as $key => $v) {
		$id =$v["0"];
		$goodsname =trim($v["1"]);
		$classify_name =trim($v["2"]);
		$goods_thumb = $v["3"];
		$goodspc = $v["4"];
		$o = $v["5"];
		$goodsintegral = $v["6"];
		$status = $v["7"];
		if($goodsname=='') {
			$error_flag=1;
			$error_msg.= "第" . ($key+2) . "行商品名称为空!<br/>";
		}
		if($classify_name=='') {
			$error_flag=1;
			$error_msg.= "第" . ($key+2) . "行分类名称为空!<br/>";
		}
		$goodsinfo = Db::table('client_good')->where("goodsname='$goodsname' and clientkeynum='$basekeynum' ")->find();
		if (!empty($goodsinfo)) {
			$error_flag=1;
			$error_msg.= "第" . ($key+2) . "行商品名称和系统里面重复<br/>";
		}
	}
	if($error_flag=='1') {
		$rt["sta"] = 0;
		$rt["msg"] = $error_msg;
		echo json_encode($rt);
		die;
	}
	//开启事务处理机制
	$trans_result = true;
	Db::startTrans();
	try {
		foreach ($arr as $key => $v) {
			$id =$v["0"];
			$goodsname =trim($v["1"]);
			$classify_name =trim($v["2"]);
			$goods_thumb = $v["3"];
			$goodspc = $v["4"];
			$o = $v["5"];
			$goodsintegral = $v["6"];
			$status = $v["7"];
			$goodsinfo = Db::table('client_good')->where("goodsname='$goodsname' and clientkeynum='$basekeynum' ")->find();
			//echo Db::table('client_good')->getLastSql();
			if (!empty($goodsinfo)) {
				//throw new \Exception(  "第" . ($key+2) . "行商品名称(".$goodsname.")和excel里面其他行重复<br/>" ); 
				$error_msg.=  "第" . ($key+2) . "行商品名称(".$goodsname.")和excel里面其他行重复<br/>";
				$trans_result = false;
			}
			//先查询分类是否存在,不存在则新增
			$classify_info =Db::table('client_good_classifylist')->where("name='$classify_name' and clientkeynum='$basekeynum' ")->find();
			if (!empty($classify_info)) {
				$classify_id=$classify_info['id'];
			} else {
				//新增
				$addclassify['name']=$classify_name;
				$addclassify['o']=0;
				$addclassify['add_time']=time();
				$addclassify['is_del']=1;
				$addclassify['clientkeynum']=$basekeynum;
				$addclassify['type']="商品列表";
				$classify_id=Db::table('client_good_classifylist')->insertGetId( $addclassify);
				if(!$classify_id) {
					//throw new \Exception("第" . ($key+2) . "行分类名称".$classify_name."新增失败<br/> "); 
					$error_msg.= "第" . ($key+2) . "行分类名称".$classify_name."新增失败<br/> ";
					$trans_result = false;
				}
			}
			//新增商品
			$add["goodsname"] = $goodsname;
			$add["classify_id"] = $classify_id;
			$add["classify_name"] = $classify_name;
			$add["goods_thumb"] = $goods_thumb;
			$add["goodsimg"] = $goods_thumb;
			$add["goodspic"] = $goods_thumb;
			$add["goodspic"] = $goods_thumb;
			$add["goodscarousel"] = $goodspc;
			$add["o"] = $o;
			$add["goodsintegral"] = $goodsintegral;
			$add["market_integral"] = $goodsintegral;
			$add["status"] = $status;
			$add["clientkeynum"] = $basekeynum;
			$add["is_check"] =1;
			$add["is_sub"] = 1;
			$goods_id=Db::table('client_good')->insertGetId($add);
			//echo Db::table('client_good')->getLastSql();die;
			if(!$goods_id) {
				//throw new \Exception("第" . ($key+2) . "行商品名称".$goodsname."新增失败<br/>"); 
				$error_msg.= "第" . ($key+2) . "行商品名称".$goodsname."新增失败<br/>";
				$trans_result = false;
			}
		}
	}
	catch ( \Exception $e ) {
		$trans_result = false;
		$msg = $e->getMessage();
	}
	if ( !$trans_result ) {
		// 回滚事务 
		Db::rollback();
		$rt['sta'] = '0';
		$rt['msg'] = '<font color=red>导入失败!错误原因==》</font><br/>'.$msg.$error_msg;
		echo json_encode( $rt );
		die;
	}
	Db::commit();
	$rt["sta"] = 1;
	$rt["msg"] = "导入成功";
	echo json_encode($rt);
	die;
}


本文标签:

版权声明:若无特殊注明,本文皆为《菜鸟站长》原创,转载请保留文章出处。

本文链接:thinkphp5的excel导入数据事务处理代码片段 - http://www.wlphp.com/?post=291

发表评论

电子邮件地址不会被公开。 必填项已用*标注