PHP 商品SKU

发表于 | 分类于

商品SKU
ps: 暂时只有新增, 后续补充编辑

创建相关表
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
CREATE TABLE `goods` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品ID',
`goods_title` VARCHAR(128) NOT NULL COMMENT '商品标题',
`generate_time` DATETIME NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';

CREATE TABLE `goods_sku` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'SKU ID',
`goods_id` INT(11) NOT NULL COMMENT '商品ID',
`title` VARCHAR(255) NOT NULL COMMENT '集成名称',
`price` DECIMAL(11,2) NOT NULL COMMENT '价格',
`stock` INT(11) NOT NULL COMMENT '库存',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品SKU信息表';
 *
CREATE TABLE `goods_sku_serial` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`goods_id` INT(11) NOT NULL COMMENT '商品ID',
`goods_sku_id` INT(11) NOT NULL COMMENT '商品SKU ID',
`goods_spec_type_id` INT(11) NOT NULL COMMENT '商品规格类型ID',
`goods_spec_type_name` VARCHAR(32) NOT NULL COMMENT '商品规格类型名称',
`goods_spec_value_id` INT(11) NOT NULL COMMENT '商品规格类型值ID',
`goods_spec_value_name` VARCHAR(32) NOT NULL COMMENT '商品规格类型值名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='SKU与规格关系表';

CREATE TABLE `goods_spec_type` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '规格类型ID',
`goods_id` INT(11) NOT NULL COMMENT '商品ID',
`type_name` VARCHAR(32) NOT NULL COMMENT '规格类型名称',
PRIMARY KEY (`id`) USING BTREE,
KEY `goods_id` (`goods_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品规格类型表';

CREATE TABLE `goods_spec_value` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '规格项ID',
`goods_id` INT(11) NOT NULL COMMENT '商品ID',
`spec_type_id` INT(11) NOT NULL COMMENT '规格类型ID',
`value_name` VARCHAR(32) NOT NULL COMMENT '规格项名称',
PRIMARY KEY (`id`) USING BTREE,
KEY `goods_id` (`goods_id`) USING BTREE,
KEY `spec_type_id` (`spec_type_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品规格项表';
前端代码
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
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>SKU</title>
    <link href="static/plugin/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <link href="static/plugin/font-awesome/css/font-awesome.min.css" rel="stylesheet">
    <link href="static/plugin/toastr/toastr.min.css" rel="stylesheet">
    <script src="static/jquery-3.3.1.min.js"></script>
    <script src="static/plugin/bootstrap/js/bootstrap.min.js"></script>
    <script src="static/plugin/toastr/toastr.min.js"></script>
    <style>
        a, a:focus, a:hover {text-decoration: none;}
        .c-red {
            color: red;
        }
        #goods-spec {
            /*display: none;*/
        }
        #goods-spec .spec {
            margin-bottom: 15px;
            padding: 10px;
            border: 1px solid #E7EAEC;
        }
        #goods-spec .spec .spec-items .spec-item {
            position: relative;
            float: left;
            width: 250px;
            margin: 0 10px 10px 0;
        }
        #goods-spec .spec .spec-items .spec-item:last-child {
            margin-bottom: -5px;
        }
        #goods-spec .spec-btn-table {
            margin-bottom: 15px;
        }
        #goods-spec .spec-btn-table > tbody > tr > td {
            padding-left: 0;
            border: 0;
        }
        #goods-spec .spec-btn-table .alert-danger {
            margin-bottom: 0;
        }
        #goods-spec .table > thead > tr > th, #goods-spec .table > tbody > tr > td {
            vertical-align: middle;
        }
        #goods-spec .spec-btn-table > tbody > tr:last-child {
            display: none;
        }
        #goods-spec .spec-btn-table a {
            margin-right: 10px;
        }
        #goods-spec .sku .sku-title {
            padding-bottom: 10px;
            text-align: center;
        }
        #goods-spec .sku .table {
            table-layout: fixed;
        }
        #goods-spec .sku .table input {
            font-weight: normal;
        }
    </style>
</head>
<body>
    <div class="container">
        <form class="form-horizontal" action="sku.php" method="post">
            <div id="goods-spec">
                <div class="alert alert-info">
                    1. 更改规格及规格项后请点击下方的【刷新规格项目表】来更新数据<br>
                    2. 每一种规格代表不同型号,例如颜色为一种规格,尺寸为一种规格,如果设置多规格,手机用户必须每一种规格都选择一个规格项,才能添加购物车或购买
                </div>
                <div class="specs"></div>
                <table class="table spec-btn-table">
                    <tbody>
                    <tr>
                        <td>
                            <a href="javascript:;" class="btn btn-primary" id="add-spec" title="添加规格"><i class="fa fa-plus"></i> 添加规格</a>
                            <a href="javascript:;" title="刷新规格项目表" class="btn btn-danger refresh-spec"><i class="fa fa-refresh"></i> 刷新规格项目表</a>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <div class="alert alert-danger">警告：规格数据有变动，请重新点击上方 [刷新规格项目表] 按钮！</div>
                        </td>
                    </tr>
                    </tbody>
                </table>
                <div class="sku"></div>
            </div>
            <button type="submit" class="btn btn-success">Submit</button>
        </form>
    </div>
    <script>
        $(function() {
            toastr.options = {
                closeButton: true,
                progressBar: true,
            };

            $('form').on('submit', function () {
                $.ajax({
                    url: 'sku.php',
                    type: 'POST',
                    data: $('form').serialize(),
                    success: function () {
                    }
                });

                return false;
            })

            $(document).on('input propertychange change', '.specs input', function () {
                window.optionchanged = true;
                $('.spec-btn-table > tbody > tr:last-child').show();
            });

            let goodsSku = {};
            // 添加规格
            $('#add-spec').on('click', function () {
                let specid = (new Date()).valueOf().toString(16);
                let html = `
                <div class="spec" id="spec-${specid}">
                    <div class="form-group">
                        <div class="col-sm-12">
                            <div class="input-group">
                                <input type="text" name="spec[${specid}]" value="" class="form-control spec-title" placeholder="规格名称 (比如: 颜色)"/>
                                <div class="input-group-btn">
                                    <a href="javascript:;" specid="${specid}" class="btn btn-info add-spec-item"><i class="fa fa-plus"></i> 添加规格项</a>
                                    <a href="javascript:;" specid="${specid}" class="btn btn-danger remove-spec"><i class="fa fa-remove"></i></a>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="form-group">
                        <div class="col-md-12">
                            <div id="spec-items-${specid}" class="spec-items"></div>
                        </div>
                    </div>
                </div>
            `;
                $('.specs').append(html);
            });

            // 删除规格
            $(document).on('click', '.remove-spec', function () {
                $('#spec-' + $(this).attr('specid')).remove();
            });

            // 添加规格项
            $(document).on('click', '.add-spec-item', function () {
                let specid = $(this).attr('specid');
                let specitemid = (new Date()).valueOf().toString(16);
                let html = `
                <div class="spec-item" data-specitemid="${specitemid}">
                    <div class="input-group">
                        <input type="text" class="form-control spec-item-title" name="spec_item[${specid}][${specitemid}]" value="" placeholder="规格项" />
                        <span class="input-group-addon"><a href="javascript:;" class="remove-spec-item"><i class="fa fa-times"></i></a></span>
                    </div>
                </div>
            `;
                $('#spec-items-' + specid).append(html);
                let len = $('#spec-items-' + specid + ' .spec-item-title').length -1;
                $('#spec-items-' + specid + ' .spec-item-title:eq(' + len +  ')').focus();
            });

            // 删除规格项
            $(document).on('click', '.remove-spec-item', function () {
                $(this).closest('.spec-item').remove();
            });

            // 规格项目批量设置
            $(document).on('click', '.sku-all', function() {
                let cls = '.sku-' + $(this).data('text');
                $(cls).val($(cls + '-all').val());
            });

            // 刷新规格项目表
            $('.refresh-spec').on('click', refreshSpec);

            // 刷新规格项目表
            function refreshSpec() {
                let html = '<table class="table table-bordered table-condensed"><thead><tr class="active">';
                let specs = [];
                let specFlag = true;

                // 未添加规格
                if ($('.spec').length <= 0) {
                    return;
                }

                // 获取规格/规格项JSON数据
                $('.spec').each(function(i) {
                    let _this = $(this);
                    let specTitle = _this.find('.spec-title').val();
                    if (!specTitle || specTitle === 'undefined') {
                        specFlag = false;
                        _this.find('.spec-title').focus();
                        toastr.error('规格名称不能为空!');
                        return;
                    }

                    let spec = {title: specTitle};


                    if (_this.find('.spec-item').length <= 0) {
                        specFlag = false;
                        toastr.error('请先添加规格项!');
                        return;
                    }

                    let items = [];
                    _this.find('.spec-item').each(function() {
                        let __this = $(this);
                        let itemTitle = __this.find('.spec-item-title').val();
                        if (!itemTitle || itemTitle === 'undefined') {
                            specFlag = false;
                            __this.find('.spec-item-title').focus();
                            toastr.error('规格项不能为空!');
                            return;
                        }

                        let item = {
                            id: __this.data('specitemid'),
                            title: itemTitle,
                        };
                        items.push(item);
                    });
                    spec.items = items;
                    specs.push(spec);
                });
                if (!specFlag) return;

                // 计算规格的所有组合,并生成rowSpans
                let specLen = specs.length;
                // 规格的所有组合
                let skuNums = 1;
                // 规格表格数组
                let specTable = new Array(specLen);
                // 单元格的rowSpan
                let rowSpans = new Array(specLen);
                for (let i = 0; i < specLen; i++) {
                    html += '<th>' + specs[i].title + '</th>';

                    let itemLen = specs[i].items.length;
                    if (itemLen <= 0) {
                        itemLen = 1;
                    }

                    skuNums *= itemLen;
                    specTable[i] = new Array(skuNums);
                    for (let j = 0; j < skuNums; j++) {
                        specTable[i][j] = new Array();
                    }

                    itemLen = specs[i].items.length;
                    rowSpans[i] = 1;
                    for (let j = i + 1; j < specLen; j++) {
                        rowSpans[i] *= specs[j].items.length;
                    }
                }

                html += `
                <th>
                    <div class="sku-title"><span class="c-red">* </span>价格 (元)</div>
                    <div class="input-group">
                        <input type="text" value="" class="form-control input-sm sku-price-all">
                        <span class="input-group-addon">
                            <a href="javascript:;" class="fa fa-angle-double-down sku-all" data-text="price" title="批量设置"></a>
                        </span>
                    </div>
                </th>
                <th>
                    <div class="sku-title"><span class="c-red">* </span>数量 (件)</div>
                    <div class="input-group">
                        <input type="text" value="" class="form-control input-sm sku-stock-all">
                        <span class="input-group-addon">
                            <a href="javascript:;" class="fa fa-angle-double-down sku-all" data-text="stock" title="批量设置"></a>
                        </span>
                    </div>
                </th>
            `;
                html += '</tr></thead>';

                // 规格表格数据填充
                for (let m = 0; m < specLen; m++) {
                    let itemIndex = 0, k = 0;
                    for (let j = 0; j < skuNums; j++) {
                        let rowSpan = rowSpans[m];
                        let specItem = specs[m].items[itemIndex] || {};
                        let specItemTitle = !specItem.title || specItem.title === 'undefined' ? '' : specItem.title;
                        let tdData = {
                            id: specItem.id,
                            title: specItemTitle,
                            html: '',
                        };
                        if (j % rowSpan === 0) {
                            tdData.html = '<td rowspan="' + rowSpan + '">' + specItemTitle + '</td>';
                        }

                        specTable[m][j] = tdData;

                        k++;
                        if (k === rowSpan) {
                            itemIndex++;
                            if (itemIndex > specs[m].items.length - 1) {
                                itemIndex = 0;
                            }

                            k = 0;
                        }
                    }
                }

                // 生成规格项目表
                let specTableHtml = '';
                for (let i = 0; i < skuNums; i++) {
                    specTableHtml += '<tr>';
                    let ids = [], titles = [];
                    for (let j = 0; j < specLen; j++) {
                        specTableHtml += specTable[j][i].html;
                        ids.push(specTable[j][i].id);
                        titles.push(specTable[j][i].title);
                    }

                    ids = ids.join('_');
                    let skuData = {
                        id: goodsSku.hasOwnProperty(ids) ? goodsSku[ids].id : 0,
                        price: goodsSku.hasOwnProperty(ids) ? goodsSku[ids].price : '',
                        stock: goodsSku.hasOwnProperty(ids) ? goodsSku[ids].stock : '',
                    };
                    specTableHtml += `
                        <td rowspan="1">
                            <input type="hidden" name="sku[${ids}][id]" value="${skuData.id}" class="form-control" />
                            <input type="text" name="sku[${ids}][price]" value="${skuData.price}" class="form-control sku-price" />
                        </td>
                        <td rowspan="1"><input type="text" name="sku[${ids}][stock]" value="${skuData.stock}" class="form-control sku-stock" /></td>
                    `;
                    specTableHtml += '</tr>';
                }

                html += specTableHtml;
                html += '</table>';

                $('.sku').html(html);

                // 刷新后重置
                window.optionchanged = false;
                $('.spec-btn-table > tbody > tr:last-child').hide();
            }
        });
    </script>
</body>
</html>
服务端代码
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
<?php
$postData = $_POST;
// 商品规格
$specArray = $_POST['spec'];
// 商品规格项
$specItemArray = $_POST['spec_item'];
// 商品SKU
$skuArray = $_POST['sku'];
// SKU所有组合数量
$skuNums = 1;
// 商品规格及规格项校验
foreach ($specArray as $specId => $specName) {
    $specItem = $specItemArray[$specId] ?? [];
    if (!$specItem || empty($specName)) {
        exit('商品规格参数错误');
    }

    foreach ($specItem as $itemName) {
        if (empty($itemName)) {
            exit('商品规格参数错误');
        }
    }

    $skuNums *= count($specItem);
}

// 商品SKU一次不能生成太多,容易造成内存溢出
if (count($skuArray) > 500) {
    exit('商品规格项目生成数量过多,请求失败');
}

// 商品SKU
if (count($skuArray) !== $skuNums) {
    exit('商品规格参数错误');
}

// TODO 若严格点校验的话,可通过商品规格和规格项生成SKU,再进行验证
foreach ($skuArray as &$sku) {
    $sku = [
        'price' => bcadd($sku['price'], 0, 2),
        'stock' => (int)$sku['stock'],
    ];
}
unset($sku);

try {
    $config = [
        'host' => 'localhost',
        'dbname' => 'test',
        'username' => 'root',
        'password' => 'root',
    ];
    $prefix = '';

    $conn = new PDO("mysql:host={$config['host']};dbname={$config['dbname']}", $config['username'], $config['password']);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // 开启事务
    $conn->beginTransaction();

    // 保存商品
    $sth = $conn->prepare("INSERT INTO {$prefix}goods(goods_title, generate_time) VALUES(:goodsTitle, :generateTime)");
    $sth->execute([
        ':goodsTitle' => 'test' . mt_rand(),
        ':generateTime' => date('Y-m-d H:i:s')
    ]);
    // 商品ID
    $goodsId = $conn->lastInsertId();

    // 商品规格项
    $specValueArray = [];
    // 保存商品规格及规格项
    foreach ($specArray as $specId => $specName) {
        // 保存商品规格类型
        $sth = $conn->prepare("INSERT INTO {$prefix}goods_spec_type(goods_id, type_name) VALUES(:goodsId, :specName)");
        $sth->execute([
            ':goodsId' => $goodsId,
            ':specName' => $specName,
        ]);
        // 规格类型ID
        $specTypeId = $conn->lastInsertId();

        foreach ($specItemArray[$specId] as $specValueId => $specValueName) {
            // 保存商品规格项
            $sth = $conn->prepare("INSERT INTO {$prefix}goods_spec_value(goods_id, spec_type_id, value_name) VALUES(:goodsId, :specTypeId, :specValueName)");
            $sth->execute([
                ':goodsId' => $goodsId,
                ':specTypeId' => $specTypeId,
                ':specValueName' => $specValueName,
            ]);
            // 规格项ID
            $specTypeValueId = $conn->lastInsertId();
            // SKU信息
            $specValueArray[$specValueId] = [
                'specId' => $specTypeId,
                'specName' => $specName,
                'specValueId' => $specTypeValueId,
                'specValueName' => $specValueName,
            ];
        }
    }

    // 保存商品SKU
    foreach ($skuArray as $specValueIds => $sku) {
        $specValueIdArray = explode('_', $specValueIds);
        $skuTitle = '';
        foreach ($specValueIdArray as $specValueId) {
            $skuTitle .= $specValueArray[$specValueId]['specName'] . ':' . $specValueArray[$specValueId]['specValueName'] . '  ';
        }

        // 保存商品SKU信息
        $sth = $conn->prepare("INSERT INTO {$prefix}goods_sku(goods_id, title, price, stock) VALUES(:goodsId, :skuTitle, :skuPrice, :skuStock)");
        $sth->execute([
            ':goodsId' => $goodsId,
            ':skuTitle' => $skuTitle,
            ':skuPrice' => $sku['price'],
            ':skuStock' => $sku['stock']
        ]);
        // SKU ID
        $skuId = $conn->lastInsertId();

        // 保存商品SKU与规格关系
        foreach ($specValueIdArray as $specValueId) {
            $specValue = $specValueArray[$specValueId];
            $sth = $conn->prepare("INSERT INTO {$prefix}goods_sku_serial(goods_id, goods_sku_id, goods_spec_type_id, goods_spec_type_name, goods_spec_value_id, goods_spec_value_name) VALUES(:goodsId, :skuId, :specTypeId, :specTypeName, :specValueId, :specValueName)");
            $sth->execute([
                ':goodsId' => $goodsId,
                ':skuId' => $skuId,
                ':specTypeId' => $specValue['specId'],
                ':specTypeName' => $specValue['specName'],
                ':specValueId' => $specValue['specValueId'],
                ':specValueName' => $specValue['specValueName'],
            ]);
        }
    }

    // 提交事务
    $conn->commit();
} catch (Exception $e) {
    // 事务回滚
    isset($conn) && $conn->rollBack();
    exit("ERROR: {$e->getMessage()}");
}
0%