ExportOrder.php
10.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
<?php
namespace addons\groupon\library\traits\export;
use addons\groupon\exception\Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
/**
* 导出面单
*/
trait ExportOrder
{
public function exportOutput ($orderStoreExpress) {
$id = $orderStoreExpress->id;
$store = $orderStoreExpress->store;
$orders = \app\admin\model\groupon\order\Order::with(['user', 'item' => function ($query) use ($id) {
$query->where('store_express_id', $id);
}])->whereExists(function ($query) use ($id) {
$itemTableName = (new \app\admin\model\groupon\order\OrderItem())->getQuery()->getTable();
$orderTableName = (new \app\admin\model\groupon\order\Order())->getQuery()->getTable();
$query = $query->table($itemTableName)->where($itemTableName . '.order_id=' . $orderTableName . '.id');
$query = $query->where('store_express_id', $id);
return $query;
})->select();
$orders = collection($orders)->toArray();
// 处理订单，按照用户进行分组
$userOrders = [];
foreach ($orders as $key => $order) {
$user = $order['user'];
if (!isset($userOrders[$user['id']])) {
$userOrders[$user['id']] = [
'user' => $user,
'total' => [
'pay_fee' => 0,
'goods_num' => 0,
'total_num' => 0,
],
'goods' => []
];
}
$goods = $userOrders[$user['id']]['goods'];
$goodsPayFee = 0;
foreach ($order['item'] as $k => $item) {
$goodsKey = $item['goods_id'] . '-' . $item['goods_sku_price_id'];
if (isset($goods[$goodsKey])) {
$goods[$goodsKey]['goods_num'] = $goods[$goodsKey]['goods_num'] + $item['goods_num'];
$goods[$goodsKey]['discount_fee'] = $goods[$goodsKey]['discount_fee'] + $item['discount_fee'];
} else {
$goods[$goodsKey] = $item;
}
$goodsPayFee += $item['pay_price'];
}
$userOrders[$user['id']]['goods'] = $goods;
$userOrders[$user['id']]['total']['pay_fee'] = $userOrders[$user['id']]['total']['pay_fee'] + $goodsPayFee;
}
// 商品样数
$goods_total_num = 0;
foreach ($userOrders as $key => $userOrder) {
$userOrders[$key]['goods'] = array_values($userOrder['goods']);
$userOrders[$key]['total']['goods_num'] = count($userOrder['goods']); // 当前用户商品样数
$userOrders[$key]['total']['total_num'] = array_sum(array_column($userOrder['goods'], 'goods_num')); // 商品总数
$goods_total_num = $goods_total_num + $userOrders[$key]['total']['goods_num'];
}
$userOrders = array_values($userOrders);
$spreadsheet = new Spreadsheet();
// 获取活动 sheet
$sheet = $spreadsheet->getActiveSheet(0);
// 设置表格全部上下居中
$defaultStyle = $spreadsheet->getDefaultStyle();
$defaultStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
$defaultStyle->getFont()->getColor()->setRGB('333333');
// 设置列宽
$sheet->getColumnDimension('A')->setWidth(7);
$sheet->getColumnDimension('B')->setWidth(34);
$sheet->getColumnDimension('C')->setWidth(10);
$sheet->getColumnDimension('D')->setWidth(11);
$sheet->getColumnDimension('E')->setWidth(11);
$sheet->getColumnDimension('F')->setWidth(0); // 预留列
$sheet->getColumnDimension('G')->setWidth(13);
$line = 1;
// 大标题
// 合并单元格
$sheet->mergeCells('A' . $line . ':G' . $line);
$sheet->getRowDimension($line)->setRowHeight(40);
$ATitle = $sheet->getCell('A' . $line);
$ATitle->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$ATitle->getStyle('A' . $line)->getFont()->setSize(22)->setBold(true);
$ATitle->setValue($store['name'] . ' - 自提点订单');
$line++;
// 团长信息
$sheet->mergeCells('A' . $line . ':G' . $line);
$sheet->getStyle('A' . $line . ':G' . $line)->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN);
$AStore = $sheet->getCell('A' . $line);
$AStore->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
$AStore->getStyle('A' . $line)->getFont()->setSize(16)->setBold(true);
$AStore->setValue($store['realname'] . '/' . substr($store['phone'], 0, 3) . '****' . substr($store['phone'], 7));
$line++;
// 门店地址
$sheet->mergeCells('A' . $line . ':G' . $line);
$AAddress = $sheet->getCell('A' . $line);
$AAddress->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
$AAddress->getStyle('A' . $line)->getFont()->setSize(14);
$AAddress->setValue($store['address']);
$line++;
// 运单统计
$sheet->mergeCells('A' . $line . ':B' . $line);
$sheet->getRowDimension($line)->setRowHeight(40);
$ATotalOrder = $sheet->getCell('A' . $line);
$ATotalOrder->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT)->setVertical(Alignment::VERTICAL_BOTTOM);
$ATotalOrder->getStyle('A' . $line)->getFont()->setSize(12);
$ATotalOrder->setValue('用户数：' . count($userOrders));
$sheet->mergeCells('C' . $line . ':D' . $line);
$CTotalGoods = $sheet->getCell('C' . $line);
$CTotalGoods->getStyle('C' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT)->setVertical(Alignment::VERTICAL_BOTTOM);
$CTotalGoods->getStyle('C' . $line)->getFont()->setSize(12);
$CTotalGoods->setValue('商品总量：' . $goods_total_num);
$sheet->mergeCells('E' . $line . ':G' . $line);
$ESend = $sheet->getCell('E' . $line);
$ESend->getStyle('E' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT)->setVertical(Alignment::VERTICAL_BOTTOM);
$ESend->getStyle('E' . $line)->getFont()->setSize(12);
$ESend->setValue('发货时间：' . date('Y-m-d', $orderStoreExpress['updatetime']));
$line++;
$sheet->mergeCells('A' . $line . ':G' . $line);
$sheet->getRowDimension($line)->setRowHeight(6);
$line++;
// 订单数据
foreach ($userOrders as $userOrder) {
// 购买信息
$sheet->getRowDimension($line)->setRowHeight(30);
$sheet->getStyle('A' . $line . ':G' . $line)->getFont()->setSize(14);
$sheet->getStyle('A' . $line . ':G' . $line)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
$sheet->mergeCells('A' . $line . ':B' . $line);
$AUser = $sheet->getCell('A' . $line);
$AUser->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
$AUser->getStyle('A' . $line)->getFont()->setSize(15)->setBold(true);
if ($userOrder['user']) {
$user = $userOrder['user'];
$nickname = mb_strlen($user['nickname']) > 7 ? mb_substr($user['nickname'], 0, 6) . '**' : $user['nickname'];
$AUser->setValue($nickname . ($user['mobile'] ? ' / ' . substr($user['mobile'], 0, 3) . '****' . substr($user['mobile'], 7) : ''));
}
$sheet->mergeCells('C' . $line . ':G' . $line);
$CTotal = $sheet->getCell('C' . $line);
$CTotal->getStyle('C' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$CTotal->getStyle('C' . $line)->getFont()->setSize(14);
$CTotal->setValue('共 ' . $userOrder['total']['goods_num'] . ' 种商品，共 ' . $userOrder['total']['total_num'] . ' 件，实付 ' . $userOrder['total']['pay_fee'] . ' 元');
$line++;
$sheet->mergeCells('A' . $line . ':G' . $line);
$sheet->getRowDimension($line)->setRowHeight(6);
$line++;
// 订单商品信息
$sheet->getStyle('A' . $line . ':G' . ($line + $userOrder['total']['goods_num']))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
$sheet->setCellValue('A' . $line, '序号');
$sheet->setCellValue('B' . $line, '商品名称');
$sheet->setCellValue('C' . $line, '单价');
$sheet->setCellValue('D' . $line, '优惠');
$sheet->setCellValue('E' . $line, '数量');
$sheet->setCellValue('F' . $line, '');
$sheet->setCellValue('G' . $line, '是否提货');
foreach ($userOrder['goods'] as $key => $goods) {
$line++;
$sheet->setCellValue('A' . $line, ($key + 1));
$sheet->getStyle('B' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
$goods_title = $goods['goods_title'] . ($goods['goods_sku_text'] ? '-' . $goods['goods_sku_text'] : '');
$goods_title = mb_strlen($goods_title) > 16 ? mb_substr($goods_title, 0, 14) . '**' : $goods_title;
$sheet->setCellValue('B' . $line, $goods_title);
$sheet->setCellValue('C' . $line, $goods['goods_price'] . '元');
$sheet->setCellValue('D' . $line, $goods['discount_fee'] . '元');
$sheet->setCellValue('E' . $line, $goods['goods_num'] . '件');
$sheet->setCellValue('F' . $line, '');
$sheet->setCellValue('G' . $line, '');
}
$line++;
$sheet->mergeCells('A' . $line . ':G' . $line);
$sheet->getRowDimension($line)->setRowHeight(6);
$line++;
}
ob_end_clean();
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $store['name'] . ' - ' . date('Y-m-d', $orderStoreExpress['updatetime']) . ' - 门店订单.xls"');
header("Content-Disposition:attachment;filename=" . $store['name'] . '-' . date('Y-m-d', $orderStoreExpress['updatetime']) . " - 门店订单.xls"); //attachment新窗口打印inline本窗口打印
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
}
}