kailin/app/Http/Controllers/Admin/Orders.php

<?php namespace App\Http\Controllers\Admin;

use App\Http\Requests\Admin\Orders as Request;
use App\Models\Custom;
use App\Models\CustomAddr;
use App\Models\CustomScore;
use App\Models\FilesManager;
use App\Models\Product;
use App\Models\Orders as Model;
use App\Models\OrdersAddr;
use App\Models\OrdersProduct;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

/**
 * 订单管理
 *
 * @author    刘相欣
 *
 */
class Orders extends Auth{
	
	protected function _initialize(){
		parent::_initialize();
		$this->assign('breadcrumb1','销售管理');
		$this->assign('breadcrumb2','订单管理');
	}

	/**
	 * 首页列表
	 * 
	 * */
    public function index(Model $Model,OrdersProduct $OrdersProduct,Product $Product,Custom $Custom){
		// 接受参数
		$code					= request('order_code','');
		$productCode			= request('product_code','');
		$phone					= request('phone','');
		$customCode				= request('custom_code','');
		$productName			= request('product_name','');
		$province				= request('contact_province','');
		$city					= request('contact_city','');
		$area					= request('contact_area','');
		$status					= request('status',0);
		$startTime				= request('start_time','');
		$endTime				= request('end_time','');
		// 编码转ID
		$id						= $code ? $Model->codeToId($code) : 0;
		$productId				= $productCode ? $Product->codeToId($productCode) : 0;
		$uid					= $customCode ? $Custom->codeToId($customCode) : 0;
		// 查询条件
		$map 					= [];
		// 编码ID
		if( $id )				$map[] = ['orders_product.order_id','=',$id];
		if( $uid )				$map[] = ['custom.uid','=',$uid];
		if( $productId )		$map[] = ['orders_product.product_id','=',$productId];
		if( $productName )		$map[] = ['orders_product.product_name','=',$productName];
		if( $phone )			$map[] = ['orders_addr.contact_phone','=',$phone];
		if( $province )			$map[] = ['orders_addr.contact_province','LIKE','%'.$province.'%'];
		if( $city )				$map[] = ['orders_addr.contact_city','LIKE','%'.$city.'%'];
		if( $area )				$map[] = ['orders_addr.contact_area','LIKE','%'.$area.'%'];
		if( $startTime )		$map[] = ['orders_product.insert_time','>=',strtotime($startTime)];
		if( $endTime )			$map[] = ['orders_product.insert_time','<=',strtotime($endTime)];
		if( $status )			$map[] = ['orders_product.status','=',$status];
		// 查询数据
		$list					= $OrdersProduct->query()
									->join('custom','orders_product.custom_uid','=','custom.uid')
									->join('orders_addr','orders_addr.order_id','=','orders_product.order_id')
									->where($map)
									->orderByDesc('id')
									->select([
										'orders_product.*','custom.username as custom_name',
										'orders_addr.contact_name','orders_addr.contact_shop','orders_addr.contact_phone','orders_addr.contact_province','orders_addr.contact_city','orders_addr.contact_area','orders_addr.contact_addr'
									])
									->paginate(request('limit',config('page_num',10)))->appends(request()->all());
		// 循环处理数据
		foreach ($list as $key => $value) {
			// id转编号
			$value['order_code']	= $Model->idToCode($value['order_id']);
			$value['custom_code']	= $Custom->idToCode($value['custom_uid']);
			$value['state']			= $Model->getState($value['status'],'state');
			$value['product_code']	= $Product->idToCode($value['product_id']);
			// 重组
			$list[$key]				= $value;
		}
		// 分配数据
		$this->assign('empty', '<tr><td colspan="20">~~暂无数据</td></tr>');
		$this->assign('list', $list);
		// 加载模板
		return						$this->fetch();
    }


	/**
	 * 状态
	 * 
	 * */
	public function set_status( Request $request, Model $Model,OrdersProduct $OrdersProduct,CustomScore $CustomScore){
		// 验证参数
		$request->scene('set_status')->validate();
		// 接收参数
		$id				= request('id',0);
		$status			= request('status',0);
		// 获取产品和数量
		$oldData 		= $Model->query()->find($id,['id','order_score','custom_uid']);
		// 如果用户不存在
		if( !$oldData )	return json_send(['code'=>'error','msg'=>'订单不存在']);
		// 组合数据，写入订单表，子表
		DB::beginTransaction();
		try{
			// 查询数据
			$result			= $Model->edit($id,['status'=>$status]);
			// 提示新增失败
			if( !$result )	{
				// 回退数据
				DB::rollBack();
				// 提示信息
				return		json_send(['code'=>'error','msg'=>'设置失败']);
			}
			// 查询数据
			$result			= $OrdersProduct->query()->where([['order_id','=',$id]])->update(['status'=>$status,'update_time'=>time()]);
			// 提示新增失败
			if( !$result )	{
				// 回退数据
				DB::rollBack();
				// 提示信息
				return json_send(['code'=>'error','msg'=>'设置失败']);
			}
			// 如果取消订单
			if( $status == 4 ) {
				// 赠送积分
				if( $oldData['order_score'] > 0 ) $CustomScore->trade($oldData['custom_uid'],$oldData['id'],($oldData['order_score']*-1),6,1);
			}
			// 提交数据
			DB::commit();
			// 告知结果
			return			json_send(['code'=>'success','msg'=>'设置成功','path'=>'']);
			// 返回结果
		} catch (\Throwable $th) {
			// 回退数据
			DB::rollBack();
			// 下单失败提示
			return			json_send(['code'=>'error','msg'=>'设置失败','data'=>['error'=>$th->getMessage().$th->getLine()]]);
		}
	}
	

	/**
	 * 表格导入
	 * 
	 * */
	public function import_execl( Request $request,Model $Model,Custom $Custom,OrdersAddr $OrdersAddr,OrdersProduct $OrdersProduct, FilesManager $FilesManager,CustomAddr $CustomAddr){
		// 验证参数
		$request->scene('import_execl')->validate();
		// 获取表格信息
		$file								= request()->file('order_file');
		// 返回结果
		$sheetList							= $FilesManager->excelToOrder($file);
		// 如果不存在结果
		if( isset($sheetList['error']) )	return json_send(['code'=>'error','msg'=>$sheetList['error']]);
		// 订单列表
		$orderList							= [];
		// 当前时间
		$time								= time();
		// 循环表格数据
		foreach ($sheetList as $value) {
			// 状态更改
			$value['status']				= $Model->getWeibanStatus($value['status']);
			// 客户手机号
			$orderList[$value['weizan_orderid']]['custom']				= ['phone'=>$value['contact_phone'],'username'=>$value['buyer_nick']];
			// 组合成订单的收件地址
			$orderList[$value['weizan_orderid']]['contact']				= [
																			'contact_name'=>$value['contact_name'],
																			'contact_phone'=>$value['contact_phone'],
																			'contact_province'=>$value['contact_province'],
																			'contact_city'=>$value['contact_city'],
																			'contact_area'=>$value['contact_area'],
																			'contact_addr'=>$value['contact_addr']
																		];
			
			// 组合成订单的收件地址
			$orderList[$value['weizan_orderid']]['product'][]			= [
																			'status'=>$value['status'],
																			'product_name'=>$value['product_name'],
																			'sku_attr_names'=>$value['sku_attr_names'],
																			'buy_num'=>$value['buy_num'],
																			'pay_total'=>$value['pay_total'],
																			'price_total'=>$value['pay_total'],
																			'insert_time'=>$value['insert_time'],
																			'update_time'=>$time,
																		];
			// 组合成订单的需要的数据
			if( !isset($orderList[$value['weizan_orderid']]['order']) )	$orderList[$value['weizan_orderid']]['order'] = ['weizan_orderid'=>$value['weizan_orderid'],'status'=>$value['status'],'pay_total'=>0,'price_total'=>0,'insert_time'=>$value['insert_time']];
			// 价格
			$orderList[$value['weizan_orderid']]['order']['pay_total']  = $orderList[$value['weizan_orderid']]['order']['pay_total'] + $value['pay_total'];
			$orderList[$value['weizan_orderid']]['order']['price_total']  = $orderList[$value['weizan_orderid']]['order']['pay_total'];
		}
		// 新增地址
		$newAddrList						= [];
		// 要更新的订单子表
		$orderProduct						= [];
		// 循环订单列表
		foreach ($orderList as $value) 		{
			// 获取手机号，查询是否用客户
			$custom							= $Custom->getOneByPhone($value['custom']['phone']);
			// 如果存在手机号
			$uid							= $custom ? $custom['uid'] : $Custom->add($value['custom']);
			// 如果客户存在
			if( !$uid )						return json_send(['code'=>'error','msg'=>$value['custom']['username'].'【'.$value['custom']['phone'].'】'.'无法创建用户']);
			// 通过订单号查询是否存在系统订单
			$orderId						= $Model->query()->where([['weizan_orderid','=',$value['order']['weizan_orderid']]])->value('id');
			// 客户ID
			$value['order']['custom_uid'] 	= $uid;
			// 存在订单获取订单ID，不存在则新增
			$orderId						= $orderId ? $Model->edit($orderId,$value['order']) : $Model->add($value['order']);
			// 如果客户存在
			if( !$orderId )					return json_send(['code'=>'error','msg'=>$orderId.'订单写入失败']);
			// 联系地址
			$contact						= $value['contact'];
			// 存在详细地址，才创建地址库
			if( $contact['contact_name'] && $contact['contact_phone'] && $contact['contact_province'] && $contact['contact_city'] && $contact['contact_area'] && $contact['contact_addr'] )	{
				// 收件地址是否存在
				$oldAddr					= $CustomAddr->query()->where([['custom_uid','=',$uid]])->first();
				// 如果不存在地址
				if( !$oldAddr )				$CustomAddr->add(['custom_uid'=>$uid,'contact_name'=>$contact['contact_name'],'contact_phone'=>$contact['contact_phone'],'contact_province'=>$contact['contact_province'],'contact_city'=>$contact['contact_city'],'contact_area'=>$contact['contact_area'],'contact_addr'=>$contact['contact_addr']]);
			}
			// 订单地址库
			$addrId							= $OrdersAddr->query()->where([['order_id','=',$orderId]])->value('id');
			// 订单ID
			$value['contact']['order_id']	= $orderId;
			// 订单地址ID
			$value['contact']['id'] 		= (int)$addrId;
			// 不存在地址的话
			$newAddrList[] 					= $value['contact'];
			// 循环子订单
			foreach ( $value['product'] as 	$product ) {
				// 数据结果
				$product['order_id']		= $orderId;
				$product['custom_uid']		= $uid;
				$product['id']				= (int) $OrdersProduct->query()->where([['order_id','=',$orderId],'product_name'=>$product['product_name'],'sku_attr_names'=>$product['sku_attr_names']])->value('id');
				$orderProduct[]				= $product;
			}
		}
		// 新地址写入
		$OrdersProduct->query()->upsert($orderProduct,'id',['product_name','sku_attr_names','buy_num','pay_total','price_total','update_time']);
		// 新地址写入
		$OrdersAddr->query()->upsert($newAddrList,'id',['contact_name','contact_phone','contact_province','contact_city','contact_area','contact_addr']);
		// 提示成功
		return								json_send(['code'=>'success','msg'=>'订单导入成功','path'=>'']);
	}

	/**
	 * 导出表格导入
	 * 
	 * */
	public function down_excel(Model $Model,OrdersProduct $OrdersProduct,Product $Product,Custom $Custom){
		// 接受参数
		$code					= request('order_code','');
		$productCode			= request('product_code','');
		$customCode				= request('custom_code','');
		$productName			= request('product_name','');
		$phone					= request('phone','');
		$province				= request('contact_province','');
		$city					= request('contact_city','');
		$area					= request('contact_area','');
		$status					= request('status',0);
		$startTime				= request('start_time','');
		$endTime				= request('end_time','');
		// 编码转ID
		$id						= $code ? $Model->codeToId($code) : 0;
		$productId				= $productCode ? $Product->codeToId($productCode) : 0;
		$uid					= $customCode ? $Custom->codeToId($customCode) : 0;
		// 查询条件
		$map 					= [];
		// 编码ID
		if( $id )				$map[] = ['orders_product.order_id','=',$id];
		if( $uid )				$map[] = ['custom.uid','=',$uid];
		if( $productId )		$map[] = ['orders_product.product_id','=',$productId];
		if( $productName )		$map[] = ['orders_product.product_name','=',$productName];
		if( $phone )			$map[] = ['orders_addr.contact_phone','=',$phone];
		if( $province )			$map[] = ['orders_addr.contact_province','LIKE','%'.$province.'%'];
		if( $city )				$map[] = ['orders_addr.contact_city','LIKE','%'.$city.'%'];
		if( $area )				$map[] = ['orders_addr.contact_area','LIKE','%'.$area.'%'];
		if( $startTime )		$map[] = ['orders_product.insert_time','>=',strtotime($startTime)];
		if( $endTime )			$map[] = ['orders_product.insert_time','<=',strtotime($endTime)];
		if( $status )			$map[] = ['orders_product.status','=',$status];
		// 查询数据
		$list					= $OrdersProduct->query()
									->join('custom','orders_product.custom_uid','=','custom.uid')
									->join('orders_addr','orders_addr.order_id','=','orders_product.order_id')
									->where($map)
									->orderByDesc('orders_product.id')
									->select([
										'orders_product.id as id',
										'orders_product.order_id',
										'orders_product.custom_uid',
										'orders_product.product_name',
										'orders_product.sku_attr_names as product_spec',
										'orders_product.product_thumb',
										'orders_product.buy_num',
										'orders_product.price_total',
										'orders_product.coupon_total',
										'orders_product.pay_total',
										'orders_product.status',
										'orders_product.insert_time',
										'custom.username as custom_name','custom.weiban_extid as weiban_extid',
										'orders_addr.contact_name','orders_addr.contact_shop','orders_addr.contact_phone','orders_addr.contact_province','orders_addr.contact_city','orders_addr.contact_area','orders_addr.contact_addr'
									])->get()->toArray();
		// 返回结果
		$data						= [];
		// 循环处理数据
		foreach ($list as $value) {
			// id转编号
			$value['order_id']		= $Model->idToCode($value['order_id']);
			$value['status']		= $Model->getState($value['status'],'state');
			$value['custom_uid']	= $Custom->idToCode($value['custom_uid']);
			$value['product_price']	= $value['buy_num'] ? ($value['price_total'] / $value['buy_num']) : $value['buy_num'];
			$value['pay_price']		= $value['buy_num'] ? ($value['pay_total'] / $value['buy_num']) : $value['buy_num'];
			// 重组
			$data[$value['order_id']]['order_id'] = $value['order_id'];
			$data[$value['order_id']]['custom_uid'] = $value['custom_uid'];
			$data[$value['order_id']]['custom_name'] = $value['custom_name'];
			$data[$value['order_id']]['weiban_extid'] = $value['weiban_extid'];
			$data[$value['order_id']]['status'] = $value['status'];
			$data[$value['order_id']]['insert_time'] = $value['insert_time'];
			// 地址
			$data[$value['order_id']]['contact_name'] = $value['contact_name'];
			$data[$value['order_id']]['contact_phone'] = $value['contact_phone'];
			$data[$value['order_id']]['contact_province'] = $value['contact_province'];
			$data[$value['order_id']]['contact_city'] = $value['contact_city'];
			$data[$value['order_id']]['contact_area'] = $value['contact_area'];
			$data[$value['order_id']]['contact_addr'] = $value['contact_addr'] .($value['contact_shop'] ? '【'.$value['contact_shop'].'】' : '');
			// 子订单
			$data[$value['order_id']]['product'][] 	  = ['product_name'=>$value['product_name'],'product_spec'=>$value['product_spec'],'product_thumb'=>$value['product_thumb'],'product_price'=>$value['product_price'],'pay_price'=>$value['pay_price'],'buy_num'=>$value['buy_num'],'pay_total'=>$value['pay_total'],'price_total'=>$value['price_total'],'coupon_total'=>$value['coupon_total']];
		}

		try {
			// 去下载
			$this->toDown($data);
		} catch (\Throwable $th) {
			echo $th->getMessage();
		}
		
	}


	/**
	 * 去下载
	 */
	private function  toDown($data){
		// 创建新的电子表格对象
		$spreadsheet			= new Spreadsheet();
		// 设置合并单元格的行和列，例如合并A1到B2的单元格
		$sheet					= $this->setStyle($spreadsheet);
		// 从第二行写入
		$row					= 2;
		// 循环写入
		foreach ($data as $key => $value) {
			// 如果有多个商品
			$count				= count($value['product']);
			// 如果有多个商品
			if( $count > 1 )	{
				// 合并单元格
				$sheet->mergeCells('A'.$row.':'.'A'.($row+$count-1));
				$sheet->mergeCells('B'.$row.':'.'B'.($row+$count-1));
				$sheet->mergeCells('C'.$row.':'.'C'.($row+$count-1));
				$sheet->mergeCells('D'.$row.':'.'D'.($row+$count-1));
				$sheet->mergeCells('E'.$row.':'.'E'.($row+$count-1));
				$sheet->mergeCells('F'.$row.':'.'F'.($row+$count-1));
				$sheet->mergeCells('G'.$row.':'.'G'.($row+$count-1));
				$sheet->mergeCells('H'.$row.':'.'H'.($row+$count-1));
				$sheet->mergeCells('I'.$row.':'.'I'.($row+$count-1));
				$sheet->mergeCells('J'.$row.':'.'J'.($row+$count-1));
				$sheet->mergeCells('R'.$row.':'.'R'.($row+$count-1));
				$sheet->mergeCells('S'.$row.':'.'S'.($row+$count-1));
			}
			// 单元格内容写入
			$sheet->setCellValue('A'.$row, $value['order_id']);
			$sheet->setCellValue('B'.$row, $value['custom_uid']);
			$sheet->setCellValue('C'.$row, $value['custom_name']);
			$sheet->setCellValue('D'.$row, $value['status']);
			$sheet->setCellValue('E'.$row, $value['contact_name']);
			$sheet->setCellValue('F'.$row, $value['contact_phone']);
			$sheet->setCellValue('G'.$row, $value['contact_province']);
			$sheet->setCellValue('H'.$row, $value['contact_city']);
			$sheet->setCellValue('I'.$row, $value['contact_area']);
			$sheet->setCellValue('J'.$row, $value['contact_addr']);
			$sheet->setCellValue('R'.$row, $value['weiban_extid']);
			$sheet->setCellValue('S'.$row, date('Y-m-d H:i:s',$value['insert_time']));
			// 循环产品
			foreach ($value['product'] as $v) {
				$sheet->setCellValue('K'.$row, $v['product_name']);
				$sheet->setCellValue('L'.$row, $v['product_spec']);
				$sheet->setCellValue('M'.$row, $v['product_price']);
				$sheet->setCellValue('N'.$row, $v['pay_price']);
				$sheet->setCellValue('O'.$row, $v['buy_num']);
				$sheet->setCellValue('P'.$row, $v['coupon_total']);
				$sheet->setCellValue('Q'.$row, $v['pay_total']);
				// 函数自增
				$row++;
			}
		}
		// 
		// 创建内容
		$writer 				= IOFactory::createWriter($spreadsheet, 'Xlsx');
		header('Pragma: public');
		header('Content-type:application/vnd.ms-excel');
		header('Content-Disposition: inline;filename=下载订单.xlsx');
		// 输出数据流
		return $writer->save('php://output');
	}

	/**
	 * 设置表格样式
	 * 
	 */
	private function setStyle(Spreadsheet $spreadsheet){
		// 选择当前活动的工作表
		$sheet					= $spreadsheet->getActiveSheet();
		// 宽
		$sheet->getColumnDimension('A')->setWidth(15);
		$sheet->getColumnDimension('B')->setWidth(15);
		$sheet->getColumnDimension('C')->setWidth(15);
		$sheet->getColumnDimension('D')->setWidth(15);
		$sheet->getColumnDimension('E')->setWidth(15);
		$sheet->getColumnDimension('F')->setWidth(15);
		$sheet->getColumnDimension('G')->setWidth(15);
		$sheet->getColumnDimension('H')->setWidth(15);
		$sheet->getColumnDimension('I')->setWidth(15);
		$sheet->getColumnDimension('J')->setWidth(50);
		$sheet->getColumnDimension('K')->setWidth(80);
		$sheet->getColumnDimension('L')->setWidth(80);
		$sheet->getColumnDimension('M')->setWidth(10);
		$sheet->getColumnDimension('N')->setWidth(10);
		$sheet->getColumnDimension('O')->setWidth(10);
		$sheet->getColumnDimension('P')->setWidth(10);
		$sheet->getColumnDimension('Q')->setWidth(10);
		$sheet->getColumnDimension('R')->setWidth(50);
		$sheet->getColumnDimension('S')->setWidth(20);
		// 默认高度
		$sheet->getDefaultRowDimension()->setRowHeight(18);
		// 加粗第一行
		$sheet->getStyle('A:S')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
		$sheet->getStyle('A1:S1')->getFont()->setBold(true);
		$sheet->getStyle('A1:S1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FF00FF00'); // ARGB颜色代码，例如绿色
		// 设置表格标题
		$sheet
		->setCellValue('A1', '订单ID')
		->setCellValue('B1', '客户ID')
		->setCellValue('C1', '客户昵称')
		->setCellValue('D1', '订单状态')
		->setCellValue('E1', '收货人')
		->setCellValue('F1', '收货人手机号')
		->setCellValue('G1', '省')
		->setCellValue('H1', '市')
		->setCellValue('I1', '区县')
		->setCellValue('J1', '收货地址')
		->setCellValue('K1', '产品名称')
		->setCellValue('L1', '产品规格')
		->setCellValue('M1', '产品单价')
		->setCellValue('N1', '折后单价')
		->setCellValue('O1', '产品数量')
		->setCellValue('P1', '优惠金额')
		->setCellValue('Q1', '产品金额')
		->setCellValue('R1', '微伴ID')
		->setCellValue('S1', '下单时间');
		// 返回结果
		return 					$sheet;
	}

}