PriceImportController.php
3.75 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
<?php
namespace backend\controllers;
use yii\web\Controller;
use PHPExcel_IOFactory;
use yii\web\Response;
/**
* Class PriceImportController
*
* @package backend\controllers
*/
class PriceImportController extends Controller
{
public function actionIndex()
{
return $this->render('index');
}
public function actionUpload()
{
\Yii::$app->response->format = Response::FORMAT_JSON;
$error = false;
$files = [];
$uploaddir = \Yii::getAlias('@storage/');
foreach ($_FILES as $file) {
if (move_uploaded_file($file[ 'tmp_name' ], $uploaddir . 'import_prices.xlsx')) {
$files[] = $uploaddir . $file[ 'name' ];
} else {
$error = true;
}
}
$data = ( $error ) ? [ 'error' => 'There was an error uploading your files' ] : [ 'files' => $files ];
$this->importPrices();
return $data;
}
protected function importPrices()
{
$xlsx = PHPExcel_IOFactory::load(\Yii::getAlias('@storage/import_prices.xlsx'));
$xlsx->setActiveSheetIndex(0);
$sheet = $xlsx->getActiveSheet();
$rowIterator = $sheet->getRowIterator();
$j = 0;
$insert = [];
foreach ($rowIterator as $row) {
$j++;
$cellIterator = $row->getCellIterator();
$row = [];
$i = 0;
foreach ($cellIterator as $cell) {
/**
* @var \PHPExcel_Cell $cell
*/
$i++;
$row[ $i ] = $cell->getValue();
if ($i > 4) {
break;
}
}
$insert[] = [
$row[ 1 ],
$row[ 2 ],
$row[ 3 ],
$row[ 4 ] ?? null,
];
if (empty($row[ 1 ])) {
break;
}
}
\Yii::$app->db->createCommand()
->truncateTable('price_upload')
->execute();
\Yii::$app->db->createCommand()
->batchInsert(
'price_upload',
[
'sku',
'stock',
'price',
'price_old',
],
$insert
)
->execute();
$transaction = \Yii::$app->db->beginTransaction();
try {
\Yii::$app->db->createCommand(
'UPDATE variant SET price=pu.price
, price_old=pu.price_old , stock=pu.stock
FROM (
SELECT * FROM price_upload
) pu
WHERE variant.sku = pu.sku'
)
->execute();
$transaction->commit();
return true;
} catch (\Exception $e) {
$transaction->rollBack();
throw $e;
} catch (\Throwable $e) {
$transaction->rollBack();
return false;
}
}
}