-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathindex.html
More file actions
1505 lines (1117 loc) · 237 KB
/
index.html
File metadata and controls
1505 lines (1117 loc) · 237 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
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
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 3.8.0">
<link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
<link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
<link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
<link rel="mask-icon" href="/images/logo.svg" color="#222">
<link rel="stylesheet" href="/css/main.css">
<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">
<script id="hexo-configurations">
var NexT = window.NexT || {};
var CONFIG = {"hostname":"yoursite.com","root":"/","scheme":"Muse","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
</script>
<meta name="description" content="Growing Path">
<meta property="og:type" content="website">
<meta property="og:title" content="Joyce' Blog">
<meta property="og:url" content="http://yoursite.com/index.html">
<meta property="og:site_name" content="Joyce' Blog">
<meta property="og:description" content="Growing Path">
<meta property="og:locale" content="zh-CN">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Joyce' Blog">
<meta name="twitter:description" content="Growing Path">
<link rel="canonical" href="http://yoursite.com/">
<script id="page-configurations">
// https://hexo.io/docs/variables.html
CONFIG.page = {
sidebar: "",
isHome : true,
isPost : false,
lang : 'zh-CN'
};
</script>
<title>Joyce' Blog</title>
<noscript>
<style>
.use-motion .brand,
.use-motion .menu-item,
.sidebar-inner,
.use-motion .post-block,
.use-motion .pagination,
.use-motion .comments,
.use-motion .post-header,
.use-motion .post-body,
.use-motion .collection-header { opacity: initial; }
.use-motion .site-title,
.use-motion .site-subtitle {
opacity: initial;
top: initial;
}
.use-motion .logo-line-before i { left: initial; }
.use-motion .logo-line-after i { right: initial; }
</style>
</noscript>
</head>
<body itemscope itemtype="http://schema.org/WebPage">
<div class="container use-motion">
<div class="headband"></div>
<header class="header" itemscope itemtype="http://schema.org/WPHeader">
<div class="header-inner"><div class="site-brand-container">
<div class="site-nav-toggle">
<div class="toggle" aria-label="切换导航栏">
<span class="toggle-line toggle-line-first"></span>
<span class="toggle-line toggle-line-middle"></span>
<span class="toggle-line toggle-line-last"></span>
</div>
</div>
<div class="site-meta">
<a href="/" class="brand" rel="start">
<span class="logo-line-before"><i></i></span>
<h1 class="site-title">Joyce' Blog</h1>
<span class="logo-line-after"><i></i></span>
</a>
</div>
<div class="site-nav-right">
<div class="toggle popup-trigger">
</div>
</div>
</div>
<nav class="site-nav">
<ul id="menu" class="main-menu menu">
<li class="menu-item menu-item-home">
<a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>
</li>
<li class="menu-item menu-item-tags">
<a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>
</li>
<li class="menu-item menu-item-categories">
<a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>
</li>
<li class="menu-item menu-item-archives">
<a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>
</li>
</ul>
</nav>
</div>
</header>
<div class="back-to-top">
<i class="fa fa-arrow-up"></i>
<span>0%</span>
</div>
<main class="main">
<div class="main-inner">
<div class="content-wrap">
<div class="content index posts-expand">
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2021/02/22/sql查询标注进度/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2021/02/22/sql查询标注进度/" class="post-title-link" itemprop="url">sql查询标注进度</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2021-02-22 22:27:53 / 修改时间：23:49:29" itemprop="dateCreated datePublished" datetime="2021-02-22T22:27:53+08:00">2021-02-22</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<p>创建视图 url_info_sencond 用以表示第二次分配的url，筛选条件：</p>
<p><img src="//yoursite.com/2021/02/22/sql查询标注进度/Users\asus\AppData\Roaming\Typora\typora-user-images\image-20210222215647289.png" alt="image-20210222215647289"></p>
<p>查询第二次分配的标注进度：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line"><span class="string">`user`</span>.id <span class="keyword">AS</span> <span class="string">`用户ID`</span>,</span><br><span class="line"><span class="string">`user`</span>.username <span class="keyword">AS</span> <span class="string">`用户姓名`</span>,</span><br><span class="line"><span class="keyword">Count</span>(<span class="keyword">distinct</span> person_url_relation.rel_person_id) <span class="keyword">AS</span> <span class="string">`已标注患者总数`</span>,</span><br><span class="line"><span class="keyword">Count</span>(<span class="keyword">distinct</span> person_trace.trace_id) <span class="keyword">AS</span> <span class="string">`已标注轨迹总数`</span>,</span><br><span class="line"><span class="keyword">Count</span>(<span class="keyword">distinct</span> url_info_second.url_id) <span class="keyword">AS</span> <span class="string">`已分配的url数`</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line"><span class="string">`user`</span></span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> url_info_second <span class="keyword">ON</span> url_info_second.user_id = <span class="string">`user`</span>.id</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> person_url_relation <span class="keyword">ON</span> person_url_relation.rel_url_id = url_info_second.url_id</span><br><span class="line"><span class="keyword">LEFT</span> <span class="keyword">JOIN</span> person_trace <span class="keyword">ON</span> person_url_relation.rel_person_id = person_trace.person_id</span><br><span class="line"><span class="keyword">WHERE</span> <span class="string">`user`</span>.id > <span class="number">14</span> </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line"><span class="string">`user`</span>.id</span><br></pre></td></tr></table></figure>
<p>v2:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> </span><br><span class="line">(</span><br><span class="line"> <span class="keyword">SELECT</span></span><br><span class="line"> <span class="string">`user`</span>.id <span class="keyword">AS</span> user_id,</span><br><span class="line"> <span class="string">`user`</span>.username <span class="keyword">AS</span> user_name,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> person_url_relation.rel_person_id) <span class="keyword">AS</span> totalP,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> person_trace.trace_id) <span class="keyword">AS</span> totalT,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> url_info_second.url_id) <span class="keyword">AS</span> totalU</span><br><span class="line"> <span class="keyword">FROM</span></span><br><span class="line"> <span class="string">`user`</span></span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> url_info_second <span class="keyword">ON</span> url_info_second.user_id = <span class="string">`user`</span>.id</span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> person_url_relation <span class="keyword">ON</span> person_url_relation.rel_url_id = url_info_second.url_id</span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> person_trace <span class="keyword">ON</span> person_url_relation.rel_person_id = person_trace.person_id</span><br><span class="line"> <span class="keyword">WHERE</span> <span class="string">`user`</span>.id > <span class="number">14</span> </span><br><span class="line"> <span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line"> <span class="string">`user`</span>.id</span><br><span class="line">) <span class="keyword">AS</span> finshed_t <span class="keyword">WHERE</span> finshed_t.totalU <> <span class="number">0</span></span><br></pre></td></tr></table></figure>
<p>统计第二次未完成标注的url数量：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="string">`user`</span>.id <span class="keyword">as</span> <span class="string">'用户ID'</span>, <span class="keyword">IFNULL</span>(k.unfinshed, <span class="number">0</span>) <span class="keyword">as</span> <span class="string">'未完成标注的url数'</span></span><br><span class="line"><span class="keyword">FROM</span> <span class="string">`user`</span> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> </span><br><span class="line">(</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">url_info_second.user_id <span class="keyword">AS</span> user_id,</span><br><span class="line"><span class="keyword">Count</span>(<span class="keyword">distinct</span> url_info_second.url_id) <span class="keyword">AS</span> unfinshed</span><br><span class="line"><span class="keyword">FROM</span> url_info_second</span><br><span class="line"><span class="keyword">WHERE</span> url_condition = <span class="string">'待标注'</span></span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line">url_info_second.user_id</span><br><span class="line">)<span class="keyword">AS</span> k <span class="keyword">ON</span> k.user_id = <span class="string">`user`</span>.id</span><br><span class="line"><span class="keyword">WHERE</span> <span class="string">`user`</span>.id > <span class="number">14</span></span><br></pre></td></tr></table></figure>
<p>合并以上两个结果：[Err] 1248 - Every derived table must have its own alias</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">user_id1 <span class="keyword">AS</span> <span class="string">`用户ID`</span>,</span><br><span class="line">user_name <span class="keyword">AS</span> <span class="string">`用户名`</span>,</span><br><span class="line">totalP <span class="keyword">AS</span> <span class="string">`已标注患者人数`</span>,</span><br><span class="line">totalT <span class="keyword">AS</span> <span class="string">`已标注轨迹总数`</span>,</span><br><span class="line">totalU <span class="keyword">AS</span> <span class="string">`已分配的url数`</span>,</span><br><span class="line">unfinshed <span class="keyword">AS</span> <span class="string">`未完成标注的url数`</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">(</span><br><span class="line"> <span class="keyword">SELECT</span> * <span class="keyword">FROM</span></span><br><span class="line"> (</span><br><span class="line"> <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> </span><br><span class="line"> (</span><br><span class="line"> <span class="keyword">SELECT</span></span><br><span class="line"> <span class="string">`user`</span>.id <span class="keyword">AS</span> user_id1,</span><br><span class="line"> <span class="string">`user`</span>.username <span class="keyword">AS</span> user_name,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> person_url_relation.rel_person_id) <span class="keyword">AS</span> totalP,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> person_trace.trace_id) <span class="keyword">AS</span> totalT,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> url_info_second.url_id) <span class="keyword">AS</span> totalU</span><br><span class="line"> <span class="keyword">FROM</span></span><br><span class="line"> <span class="string">`user`</span></span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> url_info_second <span class="keyword">ON</span> url_info_second.user_id = <span class="string">`user`</span>.id</span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> person_url_relation <span class="keyword">ON</span> person_url_relation.rel_url_id = url_info_second.url_id</span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> person_trace <span class="keyword">ON</span> person_url_relation.rel_person_id = person_trace.person_id</span><br><span class="line"> <span class="keyword">WHERE</span> <span class="string">`user`</span>.id > <span class="number">14</span> </span><br><span class="line"> <span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line"> <span class="string">`user`</span>.id</span><br><span class="line"> ) <span class="keyword">AS</span> finshed_t <span class="keyword">WHERE</span> finshed_t.totalU <> <span class="number">0</span></span><br><span class="line"> )<span class="keyword">AS</span> finshed</span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> </span><br><span class="line"> (</span><br><span class="line"> <span class="keyword">SELECT</span> * <span class="keyword">FROM</span></span><br><span class="line"> (</span><br><span class="line"> <span class="keyword">SELECT</span> <span class="string">`user`</span>.id <span class="keyword">as</span> user_id2, <span class="keyword">IFNULL</span>(k.unfinshed, <span class="number">0</span>) <span class="keyword">as</span> unfinshed</span><br><span class="line"> <span class="keyword">FROM</span> <span class="string">`user`</span> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> </span><br><span class="line"> (</span><br><span class="line"> <span class="keyword">SELECT</span></span><br><span class="line"> url_info_second.user_id <span class="keyword">AS</span> user_id,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> url_info_second.url_id) <span class="keyword">AS</span> unfinshed</span><br><span class="line"> <span class="keyword">FROM</span> url_info_second</span><br><span class="line"> <span class="keyword">WHERE</span> url_condition = <span class="string">'待标注'</span></span><br><span class="line"> <span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line"> url_info_second.user_id</span><br><span class="line"> )<span class="keyword">AS</span> k <span class="keyword">ON</span> k.user_id = <span class="string">`user`</span>.id</span><br><span class="line"> <span class="keyword">WHERE</span> <span class="string">`user`</span>.id > <span class="number">14</span> </span><br><span class="line"> ) <span class="keyword">AS</span> unfinished_url</span><br><span class="line"> ) <span class="keyword">ON</span> </span><br><span class="line"> finished.user_id1 = unfinshed_url.user_id2</span><br><span class="line">) <span class="keyword">as</span> t</span><br></pre></td></tr></table></figure>
<p>统计失效url：待审核但无关联人员：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span></span><br><span class="line">(</span><br><span class="line"> <span class="keyword">SELECT</span></span><br><span class="line"> <span class="string">`user`</span>.id <span class="keyword">AS</span> user_id,</span><br><span class="line"> <span class="string">`user`</span>.username <span class="keyword">AS</span> user_name,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> url_info_second.url_id) <span class="keyword">AS</span> totalU</span><br><span class="line"> <span class="keyword">FROM</span></span><br><span class="line"> <span class="string">`user`</span></span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> url_info_second <span class="keyword">ON</span> url_info_second.user_id = <span class="string">`user`</span>.id</span><br><span class="line"> <span class="keyword">WHERE</span> <span class="string">`user`</span>.id > <span class="number">14</span> </span><br><span class="line"> <span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line"> <span class="string">`user`</span>.id</span><br><span class="line">) <span class="keyword">AS</span> finshed_t <span class="keyword">WHERE</span> finshed_t.totalU <> <span class="number">0</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> </span><br><span class="line">(</span><br><span class="line"> <span class="keyword">SELECT</span></span><br><span class="line"> url_id, user_id,</span><br><span class="line"> <span class="keyword">Count</span>(<span class="keyword">distinct</span> person_url_relation.rel_person_id) <span class="keyword">AS</span> totalP</span><br><span class="line"> <span class="keyword">FROM</span> url_info_second</span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> person_url_relation <span class="keyword">ON</span> person_url_relation.rel_url_id = url_info_second.url_id</span><br><span class="line"> <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> person_trace <span class="keyword">ON</span> person_url_relation.rel_person_id = person_trace.person_id</span><br><span class="line"> <span class="keyword">WHERE</span> url_condition = <span class="string">'待审核'</span></span><br><span class="line"> <span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line"> url_id</span><br><span class="line">) <span class="keyword">AS</span> u_p_count</span><br></pre></td></tr></table></figure>
<p>创建 view 时，select 语句无法嵌套</p>
<p>存在问题的url数量：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> user_id,<span class="keyword">COUNT</span>(url_id)</span><br><span class="line"><span class="keyword">from</span> url_person_count <span class="keyword">WHERE</span> totalP = <span class="number">0</span></span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> user_id</span><br></pre></td></tr></table></figure>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2020/12/28/HTTP连接池/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2020/12/28/HTTP连接池/" class="post-title-link" itemprop="url">HTTP连接池</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2020-12-28 20:31:13" itemprop="dateCreated datePublished" datetime="2020-12-28T20:31:13+08:00">2020-12-28</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<p>高频流数据传输</p>
<h2 id="HTTP连接池"><a href="#HTTP连接池" class="headerlink" title="HTTP连接池"></a>HTTP连接池</h2><p><a href="https://www.cnblogs.com/xrq730/p/10963689.html" target="_blank" rel="noopener">https://www.cnblogs.com/xrq730/p/10963689.html</a></p>
<h3 id="使用线程池"><a href="#使用线程池" class="headerlink" title="使用线程池"></a>使用线程池</h3><p>减少线程创建和和销毁的时间、空间开销</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">This is a demo for testRunWithThreadPool!</span><br><span class="line">cost: 36622ms</span><br><span class="line">This is a demo for testRunWithoutThreadPool!</span><br><span class="line">cost: 1783040ms</span><br></pre></td></tr></table></figure>
<h3 id="HTTP连接池-1"><a href="#HTTP连接池-1" class="headerlink" title="HTTP连接池"></a>HTTP连接池</h3><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">This is a demo for HttpClientWithoutPoolTest!</span><br><span class="line">233ms 163ms 241ms 242ms 129ms </span><br><span class="line">This is a demo for HttpClientWithPoolTest!</span><br><span class="line">219ms 64ms 54ms 65ms 37ms</span><br></pre></td></tr></table></figure>
<h4 id="长短连接"><a href="#长短连接" class="headerlink" title="长短连接"></a>长短连接</h4><p>HTTP1.1支持在一个TCP连接上传送多个HTTP请求和响应（复用TCP通道），减少了建立和关闭连接的消耗延迟，一定程度上弥补了HTTP1.0每次请求都要创建连接的缺点，这就是长连接，HTTP1.1默认使用长连接。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Keep-Alive: timeout=<span class="number">5</span>,max=<span class="number">100</span></span><br><span class="line"><span class="comment">//表示tcp连接最多保持5秒，长连接接受100次请求就断开</span></span><br></pre></td></tr></table></figure>
<ul>
<li><code>timeout</code>：指定了一个空闲连接需要保持打开状态的最小时长（以秒为单位）。需要注意的是，如果没有在传输层设置 keep-alive TCP message 的话，大于 TCP 层面的超时设置会被忽略。</li>
<li><code>max</code>：在连接关闭之前，在此连接可以发送的请求的最大值。在非管道连接中，除了 0 以外，这个值是被忽略的，因为需要在紧跟着的响应中发送新一次的请求。HTTP 管道连接则可以用它来限制管道的使用。</li>
</ul>
<p>此外，建立连接使用到了syns queue（半连接队列）与accept queue（全连接队列），不使用长连接而每次连接都重新握手的话，队列一满服务端将会发送一个ECONNREFUSED错误信息给到客户端，这次请求失效；即使不失效，后来的请求需要等待前面的请求处理，排队也会增加响应的时间。</p>
<ul>
<li>http的keep-alive是为了复用已有连接</li>
<li>tcp的keep-alive是为了保证对端还存活</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">private</span> CloseableHttpClient httpClient = <span class="keyword">null</span>;</span><br><span class="line"><span class="meta">@Before</span></span><br><span class="line"><span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">before</span><span class="params">()</span> </span>{</span><br><span class="line"> initHttpClient();</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">private</span> CloseableHttpClient httpClient = HttpClients.custom().build();</span><br></pre></td></tr></table></figure>
<p><strong>连接池中的连接数量</strong>：假设每秒的调用量12次，根据接口的一个平均响应时长适当加一点余量，差不多设置在15~30比较合适，根据线上运行的实际情况再做调整。</p>
<p><strong>http连接池缺点</strong>：过多的长连接会占用服务器资源，导致其他服务受阻；只适用于请求是经常访问同一主机(或同一个接口)的情况下。</p>
<p>HTTPS协议在通信层和应用层之间多了一层TLS ( Transport Layer Security，传输层安全性协议)</p>
<p>多路复用是<strong>HTTP2</strong>针对HTTP1.1的一大改进，可以在一个tcp连接上同时发起多个请求和接受多个响应，无需一个长连接占着一个请求。</p>
<h4 id="线程池类-ThreadPoolExecutor"><a href="#线程池类-ThreadPoolExecutor" class="headerlink" title="线程池类 ThreadPoolExecutor"></a>线程池类 ThreadPoolExecutor</h4><p>ThreadPoolExecutor#execute方法：线程池会对当前自身状态做出判断来决定是否创建新的worker来立即执行task，或者是将task放置在workQueue队列中。</p>
<p>对于线程来讲，如果不需要它返回结果则实现Runnable，而如果需要执行结果的话则可以实现Callable。在线程池同样execute提供一个不需要返回结果的任务执行，而对于需要结果返回的则可调用其submit方法。</p>
<p>ThreadPoolExecutor#runWorker，Worker在执行完任务后，还会循环获取任务队列里的任务执行（其中的getTask方法），也就是说Worker不仅仅是在执行完给它的任务就释放或者结束，它不会闲着，而是继续从任务队列中获取任务，直到任务队列中没有任务可执行时，它才退出循环完成任务。</p>
<p>并发机制的三个特性：原子性、可见性、有序性。synchronized关键字可以保证可见性和有序性却无法保证原子性。AtomicInteger的作用就是为了保证原子性。</p>
<p>new Thread(Runnable target) 将当前线程作为父线程，并继承父线程的线程分组、守护线程标识、classloader、栈大小等，并将传入的Runnable参数作赋值给this.target，在run()方法中，调用了target.run()</p>
<h4 id="http请求步骤"><a href="#http请求步骤" class="headerlink" title="http请求步骤"></a>http请求步骤</h4><ul>
<li>使用帮助类HttpClients创建CloseableHttpClient对象.</li>
<li>基于要发送的HTTP请求类型创建HttpGet或者HttpPost实例.</li>
<li>使用addHeader方法添加请求头部,诸如User-Agent, Accept-Encoding等参数.</li>
<li>可调用HttpGet、HttpPost共同的setParams(HetpParams params)方法来添加请求参数；对于HttpPost对象而言，也可调用setEntity(HttpEntity entity)方法来设置请求参数。</li>
<li>通过执行此HttpGet或者HttpPost请求获取CloseableHttpResponse实例</li>
<li>从此CloseableHttpResponse实例中获取状态码,错误信息,以及响应页面等等.</li>
<li>释放连接。无论执行方法是否成功，都必须释放连接</li>
</ul>
<h4 id="Java"><a href="#Java" class="headerlink" title="Java"></a>Java</h4><h5 id="注解"><a href="#注解" class="headerlink" title="注解"></a>注解</h5><p>@Before：初始化方法 对于每一个测试方法都要执行一次<br>@After：释放资源 对于每一个测试方法都要执行一次<br>@Test：测试方法，在这里可以测试期望异常和超时时间<br>@Test(expected=ArithmeticException.class)检查被测方法是否抛出ArithmeticException异常<br>@Ignore：忽略的测试方法<br>@BeforeClass：针对所有测试，只执行一次，且必须为static void<br>@AfterClass：针对所有测试，只执行一次，且必须为static void<br>一个JUnit4的单元测试用例执行顺序为： @BeforeClass -> @Before -> @Test -> @After -> @AfterClass;<br>每一个测试方法的调用顺序为： @Before -> @Test -> @After; </p>
<h5 id="diamond-运算符报错"><a href="#diamond-运算符报错" class="headerlink" title="diamond 运算符报错"></a>diamond 运算符报错</h5><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">java: -source 1.5 中不支持 diamond 运算符</span><br><span class="line">(请使用 -source 7 或更高版本以启用 diamond 运算符)</span><br></pre></td></tr></table></figure>
<p><a href="https://blog.csdn.net/liu16659/article/details/80230164" target="_blank" rel="noopener">https://blog.csdn.net/liu16659/article/details/80230164</a></p>
<p>RPC（Remote Procedure Call, 远程过程调用）TPS 系统吞吐量 Reaction Time 响应时间</p>
<h2 id="消息队列"><a href="#消息队列" class="headerlink" title="消息队列"></a>消息队列</h2><p>（activeMQ，rabbitMQ，kafaKa，RocketMQ，Redis等）</p>
<p>适用场景：以书架作类比，解耦、提速、广播、削峰这些方面的收益，超过放置书架、监控书架这些成本。</p>
<p>高并发、分布式架构下优于线程池</p>
<p>主要特点：<strong>异步、削峰、解耦</strong></p>
<p>将比较耗时而且不需要即时（同步）返回结果的操作作为消息放入消息队列。</p>
<p>简单实现消息队列 代码：<a href="https://www.cnblogs.com/jimisun/p/10108067.html" target="_blank" rel="noopener">https://www.cnblogs.com/jimisun/p/10108067.html</a></p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2020/12/28/cc-develop-note-iv/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2020/12/28/cc-develop-note-iv/" class="post-title-link" itemprop="url">cc-develop-note-iv</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2020-12-28 20:31:06 / 修改时间：20:31:07" itemprop="dateCreated datePublished" datetime="2020-12-28T20:31:06+08:00">2020-12-28</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<h2 id="评教模块"><a href="#评教模块" class="headerlink" title="评教模块"></a>评教模块</h2><p><img src="//yoursite.com/2020/12/28/cc-develop-note-iv/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201214162924955.png" alt="image-20201214162924955"></p>
<p>教学中心，新增评教一栏，教师可以查看研究生的评教结果（选课人数、评教人数、分项平均分、总平均分、学生文字评价）</p>
<p>backend/modules/api/v1/models/lessonVote/AcademicMasterStudentVote.php</p>
<h3 id="前端"><a href="#前端" class="headerlink" title="前端"></a>前端</h3><p>src\views\teacher\TeachingCenter.vue</p>
<p>src\router\fullpath.js中设置路径、组件</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">{</span><br><span class="line"> path: <span class="string">"/vote-result/:lessonID"</span>,</span><br><span class="line"> name: <span class="string">"评教结果查看"</span>,</span><br><span class="line"> component: <span class="function"><span class="params">resolve</span> =></span></span><br><span class="line"> <span class="built_in">require</span>([</span><br><span class="line"> <span class="string">"@/views/lesson/AllStudentHomeworkInfoList"</span></span><br><span class="line"> ], resolve)</span><br><span class="line">},</span><br></pre></td></tr></table></figure>
<p><strong>404：</strong>路由需要设置权限，路由需要对应到菜单<code>/vote-result/:LessonID</code></p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">getTableData(){</span><br><span class="line"> <span class="comment">// 获取评教选课人数、评教人数、分项平均分、总平均分、学生文字评价</span></span><br><span class="line"> <span class="keyword">this</span>.axios.get(<span class="string">'/lesson-vote/get-vote-analysis'</span>, {</span><br><span class="line"> params: {</span><br><span class="line"> LessonID: <span class="keyword">this</span>.$route.params.lessonID</span><br><span class="line"> }</span><br><span class="line"> }).then(<span class="function"><span class="params">res</span> =></span> {</span><br><span class="line"> <span class="keyword">if</span> (res.data.success) {</span><br><span class="line"> <span class="keyword">this</span>.tableData.totalStudentNum=res.data.SelectNum</span><br><span class="line"> <span class="keyword">this</span>.tableData.votedStudentNum=res.data.voteNum</span><br><span class="line"> <span class="keyword">this</span>.tableData.eachTermAvgScore=res.data.SGScoreArr</span><br><span class="line"> <span class="keyword">this</span>.tableData.allTermAvgScore=res.data.avgScore</span><br><span class="line"> <span class="keyword">this</span>.tableData.allVoteText=res.data.proposal</span><br><span class="line"> <span class="built_in">console</span>.log(<span class="keyword">this</span>.tableData)</span><br><span class="line"> } <span class="keyword">else</span> {</span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> type: <span class="string">"error"</span>,</span><br><span class="line"> message: res.data</span><br><span class="line"> })</span><br><span class="line"> }</span><br><span class="line"> }).catch(util.catchError)</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<h3 id="数据库"><a href="#数据库" class="headerlink" title="数据库"></a>数据库</h3><p>重新设计数据库表 cc_academic_master_lesson_vote_analysis 和 cc_academic_master_sg_lesson_avgscore，把评教的分析结果存起来，前端查询时直接查表，如果没有该项课程的分析结果，再去执行类似models/lessonVote/AcademicMasterStudentVote.php里的sql语句。</p>
<p>文字评价直接查 cc_academic_master_student_vote 表</p>
<p>是否查表：LID 是否已存在</p>
<h3 id="后端"><a href="#后端" class="headerlink" title="后端"></a>后端</h3><h4 id="编写Model"><a href="#编写Model" class="headerlink" title="编写Model"></a>编写Model</h4><h5 id="分项平均分"><a href="#分项平均分" class="headerlink" title="分项平均分"></a>分项平均分</h5><p>backend/modules/api/v1/models/lessonVote/AcademicMasterSGLessonAvgScore.php</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//获取21项SG的平均分，data 以字典存储，key：SGName，value：AvgScore</span></span><br><span class="line"><span class="keyword">public</span> <span class="function"><span class="keyword">function</span> <span class="title">getSGVoteItemAvgArray</span><span class="params">($LessonID)</span></span></span><br><span class="line"><span class="function"></span>{</span><br><span class="line"> <span class="comment">// 21条记录</span></span><br><span class="line"> $SGLessonAvgScore=AcademicMasterSGLessonAvgScore::findAll([<span class="string">'LessonID'</span>=>$LessonID]);</span><br><span class="line"> $res=[];</span><br><span class="line"> <span class="keyword">if</span>($SGLessonAvgScore===<span class="keyword">null</span>) {</span><br><span class="line"> <span class="comment">// 需要先进行平均分计算</span></span><br><span class="line"> $StudentVoteIDArr=AcademicMasterStudentVote::findAll([<span class="string">'LessonID'</span>=>$LessonID])[<span class="string">'StudentVoteID'</span>];</span><br><span class="line"> $SGItemScoreArr=AcademicMasterSGItemScore::find()->where([<span class="string">'in'</span>,<span class="string">'StudentVoteID'</span>,$StudentVoteIDArr])->all();</span><br><span class="line"> $avgScoreArr=[];</span><br><span class="line"> <span class="keyword">for</span> ($i=<span class="number">1</span>;$i<<span class="number">22</span>;$i++)</span><br><span class="line"> {</span><br><span class="line"> $avgScore[$i]=<span class="number">0.0</span>;</span><br><span class="line"> }</span><br><span class="line"> $count=count($SGItemScoreArr)/<span class="number">21.0</span>;</span><br><span class="line"> <span class="keyword">foreach</span> ($SGItemScoreArr <span class="keyword">as</span> $SGItem){</span><br><span class="line"> $avgScoreArr[$SGItem[<span class="string">'SGVoteID'</span>]]+=$SGItem[<span class="string">'Score'</span>];</span><br><span class="line"> }</span><br><span class="line"> <span class="keyword">for</span> ($i=<span class="number">1</span>;$i<<span class="number">22</span>;$i++)</span><br><span class="line"> {</span><br><span class="line"> $avgScore[$i]=$avgScoreArr[$i]/$count;</span><br><span class="line"> }</span><br><span class="line"> <span class="comment">// 保存21条记录</span></span><br><span class="line"> $transaction=Yii::$app->db->beginTransaction(<span class="string">'SERIALIZABLE'</span>);</span><br><span class="line"> <span class="keyword">try</span> {</span><br><span class="line"> <span class="keyword">for</span> ($i = <span class="number">1</span>; $i < <span class="number">22</span>; $i++)</span><br><span class="line"> {</span><br><span class="line"> $model = <span class="keyword">new</span> AcademicMasterSGLessonAvgScore();</span><br><span class="line"> $model[<span class="string">'LessonID'</span>]=$LessonID;</span><br><span class="line"> $model[<span class="string">'SGVoteID'</span>]=$i;</span><br><span class="line"> $model[<span class="string">'SGVoteItemAvg'</span>]=$avgScoreArr[$i];</span><br><span class="line"></span><br><span class="line"> <span class="keyword">if</span> (!$model->save()){</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">false</span>;</span><br><span class="line"> <span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">'毕业状态保存失败！'</span>);</span><br><span class="line"> }</span><br><span class="line"> }</span><br><span class="line"> $transaction->commit();</span><br><span class="line"> } <span class="keyword">catch</span> (\<span class="keyword">Exception</span> $exception) {</span><br><span class="line"> $transaction->rollBack();</span><br><span class="line"> $res[<span class="string">'data'</span>] = $exception->getMessage();</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line"> }</span><br><span class="line"> }</span><br><span class="line"> <span class="keyword">foreach</span> ($SGLessonAvgScore <span class="keyword">as</span> $item) {</span><br><span class="line"> $SGVoteName=AcademicMasterSGVote::findOne($item[<span class="string">'SGVoteID'</span>])[<span class="string">'SGVoteName'</span>];</span><br><span class="line"> $SGVoteItemAvg=$item[<span class="string">'SGVoteItemAvg'</span>];</span><br><span class="line"> $res[<span class="string">'data'</span>][$SGVoteName]=$SGVoteItemAvg;</span><br><span class="line"> }</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">true</span>;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<h5 id="人数及总平均分"><a href="#人数及总平均分" class="headerlink" title="人数及总平均分"></a>人数及总平均分</h5><p>backend/modules/api/v1/models/lessonVote/AcademicMasterLessonVoteAnalysis.php</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">static</span> <span class="function"><span class="keyword">function</span> <span class="title">getAcademicMasterLessonVoteAnalysis</span><span class="params">($LessonID)</span></span></span><br><span class="line"><span class="function"></span>{</span><br><span class="line"> <span class="comment">// 获取评教计算结果</span></span><br><span class="line"> $LessonVoteAnalysis=AcademicMasterLessonVoteAnalysis::findOne([<span class="string">'LessonID'</span>=>$LessonID]);</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">false</span>;</span><br><span class="line"> <span class="keyword">if</span>($LessonVoteAnalysis===<span class="keyword">null</span>)</span><br><span class="line"> { <span class="comment">// 需要进行计算</span></span><br><span class="line"> $sql = <span class="string">"</span></span><br><span class="line"><span class="string"> SELECT</span></span><br><span class="line"><span class="string"> count(cc_lesson_student.StudentID) AS 'SelectNum',</span></span><br><span class="line"><span class="string"> c.`VoteNum`,</span></span><br><span class="line"><span class="string"> c.`avgScore`</span></span><br><span class="line"><span class="string"> FROM</span></span><br><span class="line"><span class="string"> (</span></span><br><span class="line"><span class="string"> SELECT</span></span><br><span class="line"><span class="string"> count( k.sum ) AS 'VoteNum',</span></span><br><span class="line"><span class="string"> sum( k.sum ) / count( k.sum ) AS 'avgScore'</span></span><br><span class="line"><span class="string"> FROM</span></span><br><span class="line"><span class="string"> (</span></span><br><span class="line"><span class="string"> SELECT</span></span><br><span class="line"><span class="string"> t.StudentID,</span></span><br><span class="line"><span class="string"> sum( Score ) / 105 * 100 AS sum </span></span><br><span class="line"><span class="string"> FROM</span></span><br><span class="line"><span class="string"> (</span></span><br><span class="line"><span class="string"> SELECT</span></span><br><span class="line"><span class="string"> cc_lesson_student.StudentID,</span></span><br><span class="line"><span class="string"> cc_academic_master_student_vote.StudentVoteID </span></span><br><span class="line"><span class="string"> FROM</span></span><br><span class="line"><span class="string"> cc_lesson_student</span></span><br><span class="line"><span class="string"> LEFT OUTER JOIN cc_academic_master_student_vote ON cc_lesson_student.StudentID = cc_academic_master_student_vote.StudentID</span></span><br><span class="line"><span class="string"> WHERE</span></span><br><span class="line"><span class="string"> cc_lesson_student.LessonID=:LessonID and cc_academic_master_student_vote.LessonID=:LessonID</span></span><br><span class="line"><span class="string"> ) AS t,</span></span><br><span class="line"><span class="string"> cc_academic_master_sg_item_score </span></span><br><span class="line"><span class="string"> WHERE</span></span><br><span class="line"><span class="string"> t.StudentVoteID = cc_academic_master_sg_item_score.StudentVoteID </span></span><br><span class="line"><span class="string"> GROUP BY</span></span><br><span class="line"><span class="string"> t.StudentVoteID </span></span><br><span class="line"><span class="string"> ) AS k</span></span><br><span class="line"><span class="string"> )as c, cc_lesson_student</span></span><br><span class="line"><span class="string"> WHERE cc_lesson_student.LessonID=:LessonID"</span>;</span><br><span class="line"> $db = Yii::$app->db;</span><br><span class="line"> $data = $db->createCommand($sql)</span><br><span class="line"> ->bindValue(<span class="string">':LessonID'</span>, $LessonID)</span><br><span class="line"> ->queryAll();</span><br><span class="line"> $model=<span class="keyword">new</span> AcademicMasterLessonVoteAnalysis();</span><br><span class="line"> $model[<span class="string">'LessonID'</span>]=$LessonID;</span><br><span class="line"> $model[<span class="string">'SelectNum'</span>]=$data[<span class="string">'SelectNum'</span>];</span><br><span class="line"> $model[<span class="string">'VoteNum'</span>]=$data[<span class="string">'VoteNum'</span>];</span><br><span class="line"> $model[<span class="string">'AvgScore'</span>]=$data[<span class="string">'AvgScore'</span>];</span><br><span class="line"> <span class="keyword">if</span> (!$model->save()) {</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">false</span>;</span><br><span class="line"> $res[<span class="string">'data'</span>] = $model->errors;</span><br><span class="line"> }</span><br><span class="line"> $LessonVoteAnalysis=AcademicMasterLessonVoteAnalysis::findOne([<span class="string">'LessonID'</span>=>$LessonID]);</span><br><span class="line"> }</span><br><span class="line"> $res[<span class="string">'data'</span>]=$LessonVoteAnalysis;</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">true</span>;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>编写SQL语句时结合Navicat视图</p>
<h5 id="文字评教"><a href="#文字评教" class="headerlink" title="文字评教"></a>文字评教</h5><p>backend/modules/api/v1/models/lessonVote/AcademicMasterStudentVote.php</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 根据LessonID返回文字评价</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">static</span> <span class="function"><span class="keyword">function</span> <span class="title">getVoteProposal</span><span class="params">($LessonID)</span></span></span><br><span class="line"><span class="function"></span>{</span><br><span class="line"> $proposal=[];</span><br><span class="line"> $sql = <span class="string">"</span></span><br><span class="line"><span class="string"> SELECT</span></span><br><span class="line"><span class="string"> cc_academic_master_student_vote.Proposal1, </span></span><br><span class="line"><span class="string"> cc_academic_master_student_vote.Proposal2</span></span><br><span class="line"><span class="string"> FROM</span></span><br><span class="line"><span class="string"> cc_academic_master_student_vote</span></span><br><span class="line"><span class="string"> WHERE</span></span><br><span class="line"><span class="string"> cc_academic_master_student_vote.LessonID =:LessonID"</span>;</span><br><span class="line"> $db = Yii::$app->db;</span><br><span class="line"> $data = $db->createCommand($sql)</span><br><span class="line"> ->bindValue(<span class="string">':LessonID'</span>, $LessonID)</span><br><span class="line"> ->queryAll();</span><br><span class="line"> <span class="keyword">return</span> array_filter(array_merge($data[<span class="string">'Proposal1'</span>],$data[<span class="string">'Proposal2'</span>]));</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>PHP 要过滤数组中的所有值为空的元素(false、null、’’、0)，可直接用 <a href="http://php.net/manual/zh/function.array-filter.php" target="_blank" rel="noopener">array_filter()</a> 函数。</p>
<h4 id="编写controller"><a href="#编写controller" class="headerlink" title="编写controller"></a>编写controller</h4><p>backend/modules/api/v1/controllers/LessonVoteController.php</p>
<p>路由<code>/lesson-vote</code>默认对应LessonVoteController，在main.php里写是为了美化url，路由就是 lesson-votes 了</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 获取评教分析</span></span><br><span class="line"><span class="keyword">public</span> <span class="function"><span class="keyword">function</span> <span class="title">actionGetVoteAnalysis</span><span class="params">()</span></span></span><br><span class="line"><span class="function"></span>{</span><br><span class="line"> $request = \Yii::$app->request;</span><br><span class="line"> <span class="keyword">if</span> ($request->getIsOptions()) {</span><br><span class="line"> <span class="keyword">return</span> <span class="keyword">$this</span>->ResponseOptions(<span class="keyword">$this</span>->verbs()[<span class="string">'get-vote-analysis'</span>]);</span><br><span class="line"> }</span><br><span class="line"> $LessonID = $request->get(<span class="string">"LessonID"</span>);</span><br><span class="line"> <span class="comment">// 获取SG分项平均分</span></span><br><span class="line"> $SGVoteItemAvgArray=AcademicMasterSGLessonAvgScore::getSGVoteItemAvgArray($LessonID);</span><br><span class="line"> <span class="comment">// 获取评教总平均分及人数</span></span><br><span class="line"> $LessonVoteAnalysis=AcademicMasterLessonVoteAnalysis::getAcademicMasterLessonVoteAnalysis($LessonID);</span><br><span class="line"> <span class="comment">// 获取文字评价</span></span><br><span class="line"> $proposal=AcademicMasterStudentVote::getVoteProposal($LessonID);</span><br><span class="line"></span><br><span class="line"> $res[<span class="string">'success'</span>]=<span class="keyword">true</span>;</span><br><span class="line"> <span class="keyword">if</span>(!$LessonVoteAnalysis[<span class="string">'success'</span>]||!$SGVoteItemAvgArray[<span class="string">'success'</span>]){</span><br><span class="line"> $res[<span class="string">'success'</span>]=<span class="keyword">false</span>;</span><br><span class="line"> $res[<span class="string">'data'</span>]=<span class="string">'LessonVoteAnalysis/SGVoteItemAvgArray'</span>;</span><br><span class="line"> }</span><br><span class="line"> $res[<span class="string">'data'</span>]=array_merge($LessonVoteAnalysis[<span class="string">'data'</span>],$SGVoteItemAvgArray[<span class="string">'data'</span>]);</span><br><span class="line"> $res[<span class="string">'data'</span>][<span class="string">'proposal'</span>]=$proposal;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p><img src="//yoursite.com/2020/12/28/cc-develop-note-iv/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201216220627346.png" alt="image-20201216220627346"></p>
<p>请求后端 API 之前也需要给基础教师加入权限</p>
<p>或者在<code>backend/modules/api/v1/controllers/base/BaseController.php</code>中去掉注释</p>
<p><img src="//yoursite.com/2020/12/28/cc-develop-note-iv/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201218114223567.png" alt="image-20201218114223567"></p>
<p>需要注意 Divided by 0 的情况， <code>== 0</code>和<code>=== 0</code>的区别</p>
<h2 id="毕业设计模块"><a href="#毕业设计模块" class="headerlink" title="毕业设计模块"></a>毕业设计模块</h2><p>学生账户登录，本科毕业设计模块（需要管理员先修改“毕业设计“的进度），学生打印表格时保留文档样式，以word格式导出。</p>
<h3 id="解决方案"><a href="#解决方案" class="headerlink" title="解决方案"></a>解决方案</h3><ul>
<li><del>在后端生成 word 文件，传输到前端。src\views\graduateProject\ExportGraduateFile.vue</del></li>
<li><p>在前端拿到表格数据，生成 word。backend/modules/api/v1/controllers/GraduateProjectController.php 的 actionExportGraduateFile($fid) 函数</p>
<p><el<em>-</em>main> ：主要区域容器。\<el-footer> ：底栏容器。</el-footer></p>
</li>
</ul>
<p><strong>参考</strong>：<a href="https://www.shuzhiduo.com/A/gVdneg9N5W/" target="_blank" rel="noopener">https://www.shuzhiduo.com/A/gVdneg9N5W/</a></p>
<p><strong>需要的插件</strong>：docxtemplater、jszip-utils、jszip、FileSaver。</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 下载题目审批表</span></span><br><span class="line">downloadTopicApprovalForm(i) {</span><br><span class="line"> <span class="keyword">let</span> _this = <span class="keyword">this</span>;</span><br><span class="line"> <span class="comment">// 判断有无附加商品来选择word模版</span></span><br><span class="line"> <span class="comment">// 读取并获得模板文件的二进制内容</span></span><br><span class="line"> <span class="keyword">let</span> filePath=<span class="string">""</span>;</span><br><span class="line"> <span class="keyword">switch</span>(i){</span><br><span class="line"> <span class="keyword">case</span> <span class="number">1</span>:</span><br><span class="line"> filePath=<span class="string">"static/file/TopicApprovalForm.docx"</span>;</span><br><span class="line"> <span class="keyword">break</span>;</span><br><span class="line"> <span class="keyword">case</span> <span class="number">2</span>:</span><br><span class="line"> filePath=<span class="string">"static/file/MidtermCheckForm.docx"</span>;</span><br><span class="line"> <span class="keyword">break</span>;</span><br><span class="line"> <span class="keyword">case</span> <span class="number">3</span>:</span><br><span class="line"> filePath=<span class="string">"static/file/TeacherScoreForm.docx"</span>;</span><br><span class="line"> <span class="keyword">break</span>;</span><br><span class="line"> <span class="keyword">case</span> <span class="number">4</span>:</span><br><span class="line"> filePath=<span class="string">"static/file/ReplyRecordForm.docx"</span>;</span><br><span class="line"> <span class="keyword">break</span>;</span><br><span class="line"> }</span><br><span class="line"> JSZipUtils.getBinaryContent(filePath, <span class="function"><span class="keyword">function</span> (<span class="params">error, content</span>) </span>{</span><br><span class="line"> <span class="comment">// console.log("-----", content);</span></span><br><span class="line"> <span class="comment">// input.docx是模板。我们在导出的时候，会根据此模板来导出对应的数据</span></span><br><span class="line"> <span class="comment">// 抛出异常</span></span><br><span class="line"> <span class="keyword">if</span> (error) {</span><br><span class="line"> <span class="keyword">throw</span> error;</span><br><span class="line"> }</span><br><span class="line"> <span class="comment">// 创建一个JSZip实例，内容为模板的内容</span></span><br><span class="line"> <span class="keyword">let</span> zip = <span class="keyword">new</span> JSZip(content);</span><br><span class="line"> <span class="comment">// console.log("+++++", zip);</span></span><br><span class="line"> <span class="comment">// 创建并加载docxtemplater实例对象</span></span><br><span class="line"> <span class="keyword">let</span> doc = <span class="keyword">new</span> Docxtemplater();</span><br><span class="line"> <span class="comment">// console.log("/////", doc);</span></span><br><span class="line"> doc.loadZip(zip);</span><br><span class="line"> <span class="comment">// console.log("=====", doc);</span></span><br><span class="line"> <span class="comment">// 设置模板变量的值</span></span><br><span class="line"> doc.setData({</span><br><span class="line"> <span class="comment">// 导出价格表全部信息</span></span><br><span class="line"> ..._this.studentInfoForm,</span><br><span class="line"> <span class="comment">// 导出价格表商品信息</span></span><br><span class="line"> ..._this.infoForm,</span><br><span class="line"> });</span><br><span class="line"> <span class="keyword">try</span> {</span><br><span class="line"> <span class="comment">// 用模板变量的值替换所有模板变量</span></span><br><span class="line"> doc.render();</span><br><span class="line"> } <span class="keyword">catch</span> (error) {</span><br><span class="line"> <span class="comment">// 抛出异常</span></span><br><span class="line"> <span class="keyword">let</span> e = {</span><br><span class="line"> message: error.message,</span><br><span class="line"> name: error.name,</span><br><span class="line"> stack: error.stack,</span><br><span class="line"> properties: error.properties,</span><br><span class="line"> };</span><br><span class="line"> <span class="built_in">console</span>.log(<span class="built_in">JSON</span>.stringify({ <span class="attr">error</span>: e }));</span><br><span class="line"> <span class="keyword">throw</span> error;</span><br><span class="line"> }</span><br><span class="line"> <span class="comment">// 生成一个代表docxtemplater对象的zip文件（不是一个真实的文件，而是在内存中的表示）</span></span><br><span class="line"> <span class="keyword">let</span> out = doc.getZip().generate({</span><br><span class="line"> type: <span class="string">"blob"</span>,</span><br><span class="line"> mimeType:</span><br><span class="line"> <span class="string">"application/vnd.openxmlformats-officedocument.wordprocessingml.document"</span>,</span><br><span class="line"> });</span><br><span class="line"> <span class="comment">// 将目标文件对象保存为目标类型的文件，并命名</span></span><br><span class="line"> saveAs(out, _this.studentInfoForm.StudentName + <span class="string">"-题目审批表.docx"</span>);</span><br><span class="line"> });</span><br><span class="line">},</span><br></pre></td></tr></table></figure>
<p><code>undefined</code>问题在前端解决；</p>
<p>学院由后端传值时判断backend/modules/api/v1/models/graduateProject/GraduateProjectStudentModel.php getStudentInfo($id)</p>
<h2 id="实验室网站"><a href="#实验室网站" class="headerlink" title="实验室网站"></a>实验室网站</h2><p>mstsc /admin /v:192.168.190.191</p>
<p>dbis_atd_record表</p>
<p>backend\models\AttendanceRule\Attendance.php</p>
<p>backend\controllers\attendance\AtdOffdaysController.php</p>
<p>time-insert\time_insert.php</p>
<p>frontend\controllers\TimingInsertController.php</p>
<p>考勤管理导入节假日</p>
<p>新增页面导入数据库数据</p>
<p>脚本：\192.168.190.206\Personal\xulicheng\XLC-DELL-530\DBISWork\DBISWEB</p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2020/12/28/cc-develop-note-v/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2020/12/28/cc-develop-note-v/" class="post-title-link" itemprop="url">cc-develop-note-v</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2020-12-28 20:31:00 / 修改时间：20:31:01" itemprop="dateCreated datePublished" datetime="2020-12-28T20:31:00+08:00">2020-12-28</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<h2 id="新闻敏感词过滤"><a href="#新闻敏感词过滤" class="headerlink" title="新闻敏感词过滤"></a>新闻敏感词过滤</h2><p><img src="//yoursite.com/2020/12/28/cc-develop-note-v/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201227211648167.png" alt="image-20201227211648167"></p>
<p>新建数据库表 cc_news_sensitive，在后端依次匹配并替换敏感文本内容为“**”，在前端显示出来，没有敏感词后再上传保存。</p>
<p>注意附件上传时的id及对应关系，不要重复上传</p>
<p>feat-filter-news-sensitive-words 分支</p>
<p>（暂时只处理”新建“，不处理”新增“）</p>
<h3 id="后端"><a href="#后端" class="headerlink" title="后端"></a>后端</h3><p>model：backend/modules/api/v1/models/news/NewsSensitiveModel.php</p>
<h4 id="关键词匹配"><a href="#关键词匹配" class="headerlink" title="关键词匹配"></a>关键词匹配</h4><ul>
<li>遍历文本</li>
<li>正则表达式</li>
<li>DFA算法。前两种方法当需要匹配的关键词数量增大时，效率很低。Deterministic Finite automation，确定性的有穷状态自动机。从一个状态输入一个字符集合能到达下一个确定的状态。<a href="https://github.com/FireLustre/php-dfa-sensitive" target="_blank" rel="noopener">https://github.com/FireLustre/php-dfa-sensitive</a></li>
</ul>
<p>mb_substr() 函数返回字符串的一部分， substr() 函数只针对英文字符，如果要分割的中文文字则需要使用 mb_substr()。</p>
<p>backend/modules/api/v1/models/news/NewsModel.php:</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 过滤新闻中的敏感词</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">static</span> <span class="function"><span class="keyword">function</span> <span class="title">filterNews</span><span class="params">($data)</span></span></span><br><span class="line"><span class="function"></span>{</span><br><span class="line"> $db=Yii::$app->db;</span><br><span class="line"> $sql=<span class="string">"SELECT cc_news_sensitive.WordContent FROM cc_news_sensitive"</span>;</span><br><span class="line"> $words=$db->createCommand($sql)->queryAll();</span><br><span class="line"> $sensitiveArr=[];</span><br><span class="line"> <span class="keyword">foreach</span> ($words <span class="keyword">as</span> $word) {</span><br><span class="line"> $sensitiveArr[]=$word[<span class="string">'WordContent'</span>];</span><br><span class="line"> }</span><br><span class="line"> $res[<span class="string">'sensitive'</span>]=<span class="keyword">false</span>;</span><br><span class="line"> $content=$data[<span class="string">'Content'</span>];</span><br><span class="line"></span><br><span class="line"> <span class="comment">// 构建敏感词库树</span></span><br><span class="line"> $handle = SensitiveHelper::init()->setTree($sensitiveArr);</span><br><span class="line"> <span class="comment">// 检测是否含有敏感词</span></span><br><span class="line"> $isLegal = $handle->islegal($content);</span><br><span class="line"> <span class="keyword">if</span>($isLegal===<span class="keyword">true</span>)</span><br><span class="line"> {</span><br><span class="line"> <span class="comment">// 含有敏感词</span></span><br><span class="line"> $res[<span class="string">'sensitive'</span>]=<span class="keyword">true</span>;</span><br><span class="line"> <span class="comment">// 敏感词替换为**</span></span><br><span class="line"> $filterContent = $handle->replace($content, <span class="string">'**'</span>);</span><br><span class="line"> $res[<span class="string">'newContent'</span>]=$filterContent;</span><br><span class="line"> }</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>backend/modules/api/v1/controllers/NewsController.php：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//添加新闻</span></span><br><span class="line"><span class="keyword">public</span> <span class="function"><span class="keyword">function</span> <span class="title">actionPostNews</span><span class="params">()</span></span></span><br><span class="line"><span class="function"></span>{</span><br><span class="line"> $request = \Yii::$app->request;</span><br><span class="line"> <span class="keyword">if</span> ($request->getIsOptions()) {</span><br><span class="line"> <span class="keyword">return</span> <span class="keyword">$this</span>->ResponseOptions(<span class="keyword">$this</span>->verbs()[<span class="string">'post-news'</span>]);</span><br><span class="line"> }</span><br><span class="line"> $data = $request->getBodyParams();</span><br><span class="line"> $filterRes=NewsModel::filterNews($data);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"> <span class="keyword">if</span>($filterRes[<span class="string">'sensitive'</span>]===<span class="keyword">true</span>)</span><br><span class="line"> {</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">false</span>;</span><br><span class="line"> $res[<span class="string">'sensitive'</span>]=<span class="keyword">true</span>;</span><br><span class="line"> $res[<span class="string">'newContent'</span>]=$filterRes[<span class="string">'newContent'</span>];</span><br><span class="line"> $res[<span class="string">'data'</span>]=$data;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line"> }</span><br><span class="line"> date_default_timezone_set(<span class="string">'PRC'</span>);</span><br><span class="line"> $data[<span class="string">'PublishTime'</span>] = date(<span class="string">'Y-m-d H:i:s'</span>, time());</span><br><span class="line"> $data[<span class="string">'UpdateTime'</span>] = $data[<span class="string">'PublishTime'</span>];</span><br><span class="line"> $data[<span class="string">'DeleteStatus'</span>] = <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"> $res=NewsModel::postNews($data);</span><br><span class="line"> $res[<span class="string">'sensitive'</span>]=<span class="keyword">false</span>;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<h3 id="前端"><a href="#前端" class="headerlink" title="前端"></a>前端</h3><p>src\views\news\NewsCompose.vue 的 postNews()</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line">.then(<span class="function">(<span class="params">res</span>) =></span> {</span><br><span class="line"> <span class="keyword">if</span> (res.data.success === <span class="literal">true</span>) {</span><br><span class="line"> <span class="comment">// 添加新闻之后，服务器返回新闻ID，传给对应的上传组件</span></span><br><span class="line"> <span class="keyword">this</span>.NIDForm.NID = res.data.data.NID;</span><br><span class="line"> <span class="comment">// 上传附件操作</span></span><br><span class="line"> <span class="keyword">this</span>.$refs.upload.submit();</span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> type: <span class="string">"success"</span>,</span><br><span class="line"> message: <span class="string">"发布成功!"</span>,</span><br><span class="line"> });</span><br><span class="line"> <span class="keyword">this</span>.newsJumpTip = <span class="literal">false</span>;</span><br><span class="line"> <span class="keyword">this</span>.goToNewsList();</span><br><span class="line"> } <span class="keyword">else</span> {</span><br><span class="line"> <span class="keyword">if</span> (res.data.sensitive === <span class="literal">true</span>) {</span><br><span class="line"> <span class="comment">// 含有敏感词</span></span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> type: <span class="string">"info"</span>,</span><br><span class="line"> message: <span class="string">"已将敏感词替换为**，请检查后重新上传！"</span>,</span><br><span class="line"> });</span><br><span class="line"> <span class="comment">// 重新显示修改后的内容</span></span><br><span class="line"> <span class="keyword">this</span>.newsForm.newsContent = res.data.newContent;</span><br><span class="line"> <span class="keyword">this</span>.$refs.tinymce.setContent(<span class="keyword">this</span>.newsForm.newsContent);</span><br><span class="line"> } <span class="keyword">else</span> {</span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> type: <span class="string">"error"</span>,</span><br><span class="line"> message: <span class="string">"发布失败，请检查信息是否都已正确填写!"</span>,</span><br><span class="line"> });</span><br><span class="line"> }</span><br><span class="line"> }</span><br><span class="line">})</span><br></pre></td></tr></table></figure>
<p>数据修改后没有在视图中同时显示，因此使用 this.$refs 获取 tinymce 组件，调用 setContent() 方法手动更新。</p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2020/12/28/cc-develop-note-iii/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2020/12/28/cc-develop-note-iii/" class="post-title-link" itemprop="url">cc-develop-note-iii</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2020-12-28 20:30:57 / 修改时间：20:40:00" itemprop="dateCreated datePublished" datetime="2020-12-28T20:30:57+08:00">2020-12-28</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<p>导入新生，user存储成功，student、assignment存储失败</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">if</span> (!$stu->save()) {</span><br><span class="line"> $res[<span class="string">'errors'</span>] = $stu->errors;</span><br><span class="line"> <span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(implode($stu->errors));</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">errors: {SID: ["Sid must be a string."]}</span><br></pre></td></tr></table></figure>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">{"success":false,"errors":{"TutorID":["Tutor ID must be an integer."]},"data":"student 保存失败！"}</span><br></pre></td></tr></table></figure>
<p>==注意属性类型！==</p>
<p>PHP 转换函数: intval()、floatval()、strval() </p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//批量导入新生，记录日志，成功时返回新生SID集合</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">static</span> <span class="function"><span class="keyword">function</span> <span class="title">batchFreshmanStudentUpload</span><span class="params">($data)</span></span>{</span><br><span class="line"> date_default_timezone_set(<span class="string">'PRC'</span>);</span><br><span class="line"> $log = LogBatchModel::beforeBatch($data);</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">false</span>;</span><br><span class="line"> <span class="keyword">if</span>($log[<span class="string">'success'</span>]===<span class="keyword">false</span>)</span><br><span class="line"> {</span><br><span class="line"> <span class="comment">//创建批量导入的日志时出现错误</span></span><br><span class="line"> $res[<span class="string">'logBatch'</span>]=$log;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line"> }</span><br><span class="line"> $LBID=$log[<span class="string">'data'</span>][<span class="string">'LBID'</span>];</span><br><span class="line"> $filePath=$data[<span class="string">'FilePath'</span>];</span><br><span class="line"> <span class="keyword">try</span>{</span><br><span class="line"> <span class="comment">//获得新生集合</span></span><br><span class="line"> $StudentArray = <span class="keyword">self</span>::readFreshmanFromExcel($filePath);</span><br><span class="line"> }<span class="keyword">catch</span> (<span class="keyword">Exception</span> $e)</span><br><span class="line"> {</span><br><span class="line"> <span class="comment">//解析 Excel 出现错误</span></span><br><span class="line"> $res[<span class="string">'filePath'</span>]=$filePath;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line"> }</span><br><span class="line"> <span class="comment">// 存入数据库</span></span><br><span class="line"> $key=[<span class="string">'SID'</span>,<span class="string">'StudentName'</span>,<span class="string">'Grade'</span>,<span class="string">'Education'</span>,<span class="string">'Major'</span>,<span class="string">'IDCard'</span>,<span class="string">'Passwd'</span>,<span class="string">'Tutor'</span>];<span class="comment">// 与模型的属性名相对应</span></span><br><span class="line"></span><br><span class="line"> $transaction=Yii::$app->db->beginTransaction(<span class="string">'SERIALIZABLE'</span>);</span><br><span class="line"> <span class="keyword">try</span> {</span><br><span class="line"> <span class="keyword">for</span> ($i = <span class="number">0</span>; $i < count($StudentArray); $i++)</span><br><span class="line"> {</span><br><span class="line"> $row = $StudentArray[$i][<span class="number">0</span>];</span><br><span class="line"> $student=[];</span><br><span class="line"> <span class="keyword">for</span>($j=<span class="number">0</span>;$j<count($key);$j++)</span><br><span class="line"> {</span><br><span class="line"> $student[$key[$j]]=$row[$j];</span><br><span class="line"> }</span><br><span class="line"> <span class="keyword">if</span>($student[<span class="string">'SID'</span>]===<span class="keyword">null</span>)<span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">'学号不能为空！'</span>);</span><br><span class="line"> <span class="comment">// Sid must be a string.</span></span><br><span class="line"> $student[<span class="string">'SID'</span>]=strval($student[<span class="string">'SID'</span>]);</span><br><span class="line"> <span class="keyword">if</span>($student[<span class="string">'StudentName'</span>]===<span class="keyword">null</span>)<span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">'姓名不能为空！'</span>);</span><br><span class="line"> <span class="comment">// 查询 EducationLevelID</span></span><br><span class="line"> $EducationLevel=EducationLevelModel::findOne([<span class="string">'LevelName'</span> => $student[<span class="string">'Education'</span>]]);</span><br><span class="line"> <span class="keyword">if</span>($EducationLevel===<span class="keyword">null</span>)<span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">'层次不能为空！'</span>);</span><br><span class="line"> $EducationLevelID = $EducationLevel[<span class="string">'ELID'</span>];</span><br><span class="line"> <span class="comment">// 查询 MID</span></span><br><span class="line"> $Major=MajorModel::findOne([<span class="string">'EducationLevelID'</span>=>$EducationLevelID,<span class="string">'MajorName'</span>=>$student[<span class="string">'Major'</span>]]);</span><br><span class="line"> <span class="keyword">if</span>($Major===<span class="keyword">null</span>)<span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">'专业不能为空！'</span>);</span><br><span class="line"> $MID = $Major[<span class="string">'MID'</span>];</span><br><span class="line"> <span class="comment">// 创建 MajorEnrollyear 然后查询 MEYID</span></span><br><span class="line"> $majorEnrollYear=<span class="keyword">new</span> MajorEnrollyearModel();</span><br><span class="line"> $majorEnrollYear->Grade=$student[<span class="string">'Grade'</span>];</span><br><span class="line"> <span class="keyword">if</span>($majorEnrollYear===<span class="keyword">null</span>)<span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">'年级不能为空！'</span>);</span><br><span class="line"> $majorEnrollYear->MajorID=$MID;</span><br><span class="line"> $majorEnrollYear->save();</span><br><span class="line"> $MajorEnrollyear=MajorEnrollyearModel::findOne([<span class="string">'MajorID'</span>=>$MID,<span class="string">'Grade'</span>=>$student[<span class="string">'Grade'</span>]]);</span><br><span class="line"> <span class="keyword">if</span>($MajorEnrollyear===<span class="keyword">null</span>)<span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">'学号 '</span>.$student[<span class="string">'SID'</span>].<span class="string">' 的年级、专业、层次对应关系错误！'</span>);</span><br><span class="line"> $student[<span class="string">'MajorEnrollYearID'</span>]=$MajorEnrollyear[<span class="string">'MEYID'</span>];</span><br><span class="line"></span><br><span class="line"> <span class="comment">// 查询 TutorID</span></span><br><span class="line"> $Tutor=TeachersModel::findOne([<span class="string">'TeacherName'</span>=>$student[<span class="string">'Tutor'</span>]]);</span><br><span class="line"> <span class="keyword">if</span>($Tutor===<span class="keyword">null</span>)</span><br><span class="line"> $student[<span class="string">'TutorID'</span>]=<span class="keyword">null</span>;</span><br><span class="line"> <span class="keyword">else</span></span><br><span class="line"> $student[<span class="string">'TutorID'</span>]=intval($Tutor[<span class="string">'TID'</span>]);</span><br><span class="line"></span><br><span class="line"> <span class="comment">// 创建 user</span></span><br><span class="line"> <span class="keyword">if</span>($student[<span class="string">'Passwd'</span>]===<span class="keyword">null</span>)$student[<span class="string">'Passwd'</span>]=<span class="string">'111111'</span>;</span><br><span class="line"> $student[<span class="string">'Passwd'</span>]=hash_hmac(<span class="string">'SHA256'</span>, <span class="string">'cc-frontend-vue'</span>,$student[<span class="string">'Passwd'</span>]);</span><br><span class="line"> $user = <span class="keyword">new</span> User();</span><br><span class="line"> $user->username = $student[<span class="string">'StudentName'</span>];</span><br><span class="line"> $user->setPassword($student[<span class="string">'Passwd'</span>]);</span><br><span class="line"> $user->generateAuthKey();</span><br><span class="line"> <span class="keyword">if</span> (!$user->save()){</span><br><span class="line"> $res[<span class="string">'errors'</span>] = $user->errors;</span><br><span class="line"> <span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">"user 保存失败！"</span>);</span><br><span class="line"> }</span><br><span class="line"></span><br><span class="line"> <span class="comment">// 创建学生</span></span><br><span class="line"> $stu = <span class="keyword">new</span> StudentModel();</span><br><span class="line"> $stu->id = $user->id;</span><br><span class="line"> $stu->MajorEnrollYearID =$student[<span class="string">'MajorEnrollYearID'</span>];</span><br><span class="line"> $stu->SID = $student[<span class="string">'SID'</span>];</span><br><span class="line"> $stu->StudentName = $student[<span class="string">'StudentName'</span>];</span><br><span class="line"> $stu->TutorID=$student[<span class="string">'TutorID'</span>];</span><br><span class="line"> $stu->IDCard=$student[<span class="string">'IDCard'</span>];</span><br><span class="line"> <span class="keyword">if</span> (!$stu->save()) {</span><br><span class="line"> $res[<span class="string">'errors'</span>] = $stu->errors;</span><br><span class="line"> <span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">"student 保存失败！"</span>);</span><br><span class="line"> }</span><br><span class="line"></span><br><span class="line"> <span class="comment">// 创建 auth_assignment</span></span><br><span class="line"> $assignment = <span class="keyword">new</span> Assignment($user->id);</span><br><span class="line"> $res[<span class="string">'authRes'</span>]=$assignment->assign([<span class="string">'学生'</span>,<span class="string">'游客'</span>]);</span><br><span class="line"> <span class="keyword">if</span>($res[<span class="string">'authRes'</span>]!==<span class="number">2</span>)</span><br><span class="line"> {</span><br><span class="line"> <span class="keyword">throw</span> <span class="keyword">new</span> \<span class="keyword">Exception</span>(<span class="string">"auth 保存失败！"</span>);</span><br><span class="line"> }</span><br><span class="line"> }</span><br><span class="line"> $transaction->commit();</span><br><span class="line"> <span class="keyword">for</span> ($i = <span class="number">0</span>; $i < count($StudentArray); $i++)</span><br><span class="line"> $res[<span class="string">'data'</span>][] = $StudentArray[$i][<span class="number">0</span>][<span class="number">0</span>];</span><br><span class="line"> }<span class="keyword">catch</span> (\<span class="keyword">Exception</span> $exception) {</span><br><span class="line"> $transaction->rollBack();</span><br><span class="line"> LogBatchModel::afterBatch(<span class="keyword">false</span>,$LBID);</span><br><span class="line"> $res[<span class="string">'data'</span>] = $exception->getMessage();</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line"> }</span><br><span class="line"> LogBatchModel::afterBatch(<span class="keyword">true</span>,$LBID);</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">true</span>;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>新导入学生可以成功登陆。</p>
<p>表格上传失败时进行重置。</p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2020/12/28/cc-develop-note-i/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2020/12/28/cc-develop-note-i/" class="post-title-link" itemprop="url">cc-develop-note-i</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2020-12-28 20:30:52 / 修改时间：20:39:45" itemprop="dateCreated datePublished" datetime="2020-12-28T20:30:52+08:00">2020-12-28</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<ol>
<li>==vue 和 PHP 对 Excel、word 导入导出的支持==</li>
<li>==安装前后端项目==</li>
<li>==本地数据库==</li>
</ol>
<h5 id="Windows-系统安装指定版本的composer："><a href="#Windows-系统安装指定版本的composer：" class="headerlink" title="Windows 系统安装指定版本的composer："></a>Windows 系统安装指定版本的composer：</h5><p><a href="https://pkg.phpcomposer.com/#how-to-install-composer" target="_blank" rel="noopener">https://pkg.phpcomposer.com/#how-to-install-composer</a></p>
<ol>
<li>找到并进入 PHP 的安装目录（和你在命令行中执行的 <code>php</code> 指令应该是同一套 PHP）。</li>
<li>将 <code>composer.phar</code> 复制到 PHP 的安装目录下面，也就是和 <code>php.exe</code> 在同一级目录。</li>
<li>在 PHP 安装目录下新建一个 <code>composer.bat</code> 文件，并将下列代码保存到此文件中。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">@php <span class="string">"%~dp0composer.phar"</span> %*</span><br></pre></td></tr></table></figure>
<p>最后重新打开一个命令行窗口试一试执行 <code>composer --version</code> 看看是否正确输出版本号。</p>
<p>yii2 项目初始化命令：<code>.\init</code></p>
<h5 id="Windows-符号链接-mklink"><a href="#Windows-符号链接-mklink" class="headerlink" title="Windows 符号链接 mklink"></a>Windows 符号链接 mklink</h5><p>mklink 命令是将文件或目录建立双向连接, 该变任何一方都会发生变化, 其主要文件链接有三: 符号链接(软件接), 目录连接(软件接), 文本文件链接(硬连接), 可以这样理解, <strong>软连接, 是建立快捷方式, 硬连接, 是进行复制</strong></p>
<p>在 cmd 里面输入: <strong>mklink /?</strong> 来查看 mklink 命令和参数的使用</p>
<figure class="highlight powershell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mklink /D cc C:\Users\pcy\Documents\DBISWork\cc-computer-backend</span><br></pre></td></tr></table></figure>
<h5 id="使用表前缀"><a href="#使用表前缀" class="headerlink" title="使用表前缀"></a>使用表前缀</h5><p><a href="https://www.yiiframework.com/doc/guide/2.0/zh-cn/db-dao" target="_blank" rel="noopener">https://www.yiiframework.com/doc/guide/2.0/zh-cn/db-dao</a></p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">'components'</span> => [</span><br><span class="line"> <span class="comment">// ...</span></span><br><span class="line"> <span class="string">'db'</span> => [</span><br><span class="line"> <span class="comment">// ...</span></span><br><span class="line"> <span class="string">'tablePrefix'</span> => <span class="string">'tbl_'</span>,</span><br><span class="line"> ],</span><br><span class="line">],</span><br></pre></td></tr></table></figure>
<p>src\api\http.js</p>
<figure class="highlight javascript"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 如果出现问题，就直接 hard code 写死域名</span></span><br><span class="line">Instance.defaults.baseURL = <span class="string">'http://localhost:8081/cc-computer-homepage/backend/web/index.php/api/v1'</span></span><br></pre></td></tr></table></figure>
<p>当前：<em>PHP/5.6.40</em></p>
<h3 id="Yii2-对-word、Excel-上传下载的支持——PHPOffice"><a href="#Yii2-对-word、Excel-上传下载的支持——PHPOffice" class="headerlink" title="Yii2 对 word、Excel 上传下载的支持——PHPOffice"></a>Yii2 对 word、Excel 上传下载的支持——PHPOffice</h3><p><strong>PHPExcel</strong> was officially deprecated in 2017 and permanently archived in 2019. The project has not be maintained for years and must not be used anymore. All users must migrate to its direct successor <a href="https://github.com/PHPOffice/PhpSpreadsheet" target="_blank" rel="noopener">PhpSpreadsheet</a>, or another alternative.</p>
<h4 id="PhpSpreadsheet"><a href="#PhpSpreadsheet" class="headerlink" title="PhpSpreadsheet"></a>PhpSpreadsheet</h4><p>PHP 7.2+</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta"><?php</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">require</span> <span class="string">'vendor/autoload.php'</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">use</span> <span class="title">PhpOffice</span>\<span class="title">PhpSpreadsheet</span>\<span class="title">Spreadsheet</span>;</span><br><span class="line"><span class="keyword">use</span> <span class="title">PhpOffice</span>\<span class="title">PhpSpreadsheet</span>\<span class="title">Writer</span>\<span class="title">Xlsx</span>;</span><br><span class="line"></span><br><span class="line">$spreadsheet = <span class="keyword">new</span> Spreadsheet();</span><br><span class="line">$sheet = $spreadsheet->getActiveSheet();</span><br><span class="line">$sheet->setCellValue(<span class="string">'A1'</span>, <span class="string">'Hello World !'</span>);</span><br><span class="line"></span><br><span class="line">$writer = <span class="keyword">new</span> Xlsx($spreadsheet);</span><br><span class="line">$writer->save(<span class="string">'hello world.xlsx'</span>);</span><br></pre></td></tr></table></figure>
<h6 id="Architecture"><a href="#Architecture" class="headerlink" title="Architecture"></a>Architecture</h6><p><img src="https://phpspreadsheet.readthedocs.io/en/latest/topics/images/01-schematic.png" alt="01-schematic.png"></p>
<h6 id="Creating-a-spreadsheet"><a href="#Creating-a-spreadsheet" class="headerlink" title="Creating a spreadsheet"></a>Creating a spreadsheet</h6><p>Loading a Workbook from a file:</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">$inputFileName = <span class="string">'./sampleData/example1.xls'</span>;</span><br><span class="line"><span class="comment">/** Load $inputFileName to a Spreadsheet object **/</span></span><br><span class="line">$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);</span><br></pre></td></tr></table></figure>
<p>Creating a new workbook:</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/** Create a new Spreadsheet Object **/</span></span><br><span class="line">$spreadsheet = <span class="keyword">new</span> \PhpOffice\PhpSpreadsheet\Spreadsheet();</span><br></pre></td></tr></table></figure>
<h6 id="Accessing-cells"><a href="#Accessing-cells" class="headerlink" title="Accessing cells"></a>Accessing cells</h6><p>Setting a cell value by coordinate:</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// Set cell A1 with a string value</span></span><br><span class="line">$spreadsheet->getActiveSheet()->setCellValue(<span class="string">'A1'</span>, <span class="string">'PhpSpreadsheet'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">// Set cell A2 with a numeric value</span></span><br><span class="line">$spreadsheet->getActiveSheet()->setCellValue(<span class="string">'A2'</span>, <span class="number">12345.6789</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">// Set cell A3 with a boolean value</span></span><br><span class="line">$spreadsheet->getActiveSheet()->setCellValue(<span class="string">'A3'</span>, <span class="keyword">TRUE</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">// Set cell A4 with a formula</span></span><br><span class="line">$spreadsheet->getActiveSheet()->setCellValue(</span><br><span class="line"> <span class="string">'A4'</span>,</span><br><span class="line"> <span class="string">'=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">$spreadsheet->getActiveSheet()</span><br><span class="line"> ->getCell(<span class="string">'B8'</span>)</span><br><span class="line"> ->setValue(<span class="string">'Some value'</span>);</span><br></pre></td></tr></table></figure>
<p><a href="https://phpoffice.github.io/PhpSpreadsheet/namespaces/phpoffice-phpspreadsheet.html" target="_blank" rel="noopener">Documentation</a></p>
<h4 id="PHPWord"><a href="#PHPWord" class="headerlink" title="PHPWord"></a>PHPWord</h4><p>The current version of PHPWord supports Microsoft Office Open XML (OOXML or OpenXML), OASIS Open Document Format for Office Applications (OpenDocument or ODF), and Rich Text Format (RTF).</p>
<p>PHPWord requires the following:</p>
<ul>
<li>PHP 5.3.3+</li>
<li>XML Parser extension</li>
<li>Zend\Escaper component</li>
<li>Zend\Stdlib component</li>
</ul>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta"><?php</span></span><br><span class="line"><span class="keyword">require_once</span> <span class="string">'bootstrap.php'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">// Creating the new document...</span></span><br><span class="line">$phpWord = <span class="keyword">new</span> \PhpOffice\PhpWord\PhpWord();</span><br><span class="line"></span><br><span class="line"><span class="comment">/* <span class="doctag">Note:</span> any element you append to a document must reside inside of a Section. */</span></span><br><span class="line"></span><br><span class="line"><span class="comment">// Adding an empty Section to the document...</span></span><br><span class="line">$section = $phpWord->addSection();</span><br><span class="line"><span class="comment">// Adding Text element to the Section having font styled by default...</span></span><br><span class="line">$section->addText(</span><br><span class="line"> <span class="string">'"Learn from yesterday, live for today, hope for tomorrow. '</span></span><br><span class="line"> . <span class="string">'The important thing is not to stop questioning." '</span></span><br><span class="line"> . <span class="string">'(Albert Einstein)'</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="comment">// Saving the document as OOXML file...</span></span><br><span class="line">$objWriter = \PhpOffice\PhpWord\IOFactory::createWriter($phpWord, <span class="string">'Word2007'</span>);</span><br><span class="line">$objWriter->save(<span class="string">'helloWorld.docx'</span>);</span><br></pre></td></tr></table></figure>
<p>前端发起ajax请求，后端生成excel并下载，同时需要在header头中，带上token验证信息，实现如下：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">$filename = <span class="string">'demo.xlsx'</span>;</span><br><span class="line">$objWriter = \PHPExcel_IOFactory::createWriter($objectPHPExcel, <span class="string">'Excel2007'</span>);</span><br><span class="line">ob_start();</span><br><span class="line">$objWriter->save(<span class="string">"php://output"</span>);</span><br><span class="line">$xlsData = ob_get_contents();</span><br><span class="line">ob_end_clean();</span><br><span class="line"><span class="keyword">return</span> Api::success([<span class="string">'filename'</span> => $filename, <span class="string">'file'</span> => <span class="string">"data:application/vnd.ms-excel;base64,"</span> . base64_encode($xlsData)]);</span><br></pre></td></tr></table></figure>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">$(<span class="string">'.download'</span>).click(<span class="function"><span class="keyword">function</span>(<span class="params"></span>)</span>{</span><br><span class="line"><span class="keyword">var</span> url = <span class="string">"http://xxxx.com/group/bi/export"</span>;</span><br><span class="line"><span class="keyword">var</span> params = {</span><br><span class="line"> from_date: <span class="string">'2017-09-01'</span>,</span><br><span class="line"> to_date: <span class="string">'2017-09-08'</span>,</span><br><span class="line"> group_id: <span class="number">1</span></span><br><span class="line"> };</span><br><span class="line"> $.ajax({</span><br><span class="line"> type:<span class="string">'POST'</span>,</span><br><span class="line"> url: url,</span><br><span class="line"> data: params,</span><br><span class="line"> beforeSend: <span class="function"><span class="keyword">function</span>(<span class="params">request</span>) </span>{</span><br><span class="line"> request.setRequestHeader(<span class="string">"Authorization"</span>, <span class="string">"token信息，验证身份"</span>);</span><br><span class="line"> },</span><br><span class="line"> success: <span class="function"><span class="keyword">function</span>(<span class="params">redata</span>) </span>{</span><br><span class="line"> <span class="comment">// 创建a标签，设置属性，并触发点击下载</span></span><br><span class="line"> <span class="keyword">var</span> $a = $(<span class="string">"<a>"</span>);</span><br><span class="line"> $a.attr(<span class="string">"href"</span>, redata.data.file);</span><br><span class="line"> $a.attr(<span class="string">"download"</span>, redata.data.filename);</span><br><span class="line"> $(<span class="string">"body"</span>).append($a);</span><br><span class="line"> $a[<span class="number">0</span>].click();</span><br><span class="line"> $a.remove();</span><br><span class="line"> }</span><br><span class="line"> });</span><br><span class="line">});</span><br></pre></td></tr></table></figure>
<p><a href="https://www.jb51.net/special/339.htm" target="_blank" rel="noopener">PHP 操作 office 文档</a></p>
<h4 id="vue使用FormData上传excel文件，使用blob下载文件"><a href="#vue使用FormData上传excel文件，使用blob下载文件" class="headerlink" title="vue使用FormData上传excel文件，使用blob下载文件"></a>vue使用FormData上传excel文件，使用blob下载文件</h4><p> BLOB (binary large object) 二进制大对象</p>
<h5 id="导入excel文件"><a href="#导入excel文件" class="headerlink" title="导入excel文件"></a>导入excel文件</h5><figure class="highlight javascript"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line">inputCustomProduct(e){</span><br><span class="line"> <span class="comment">//声明一个FormDate对象</span></span><br><span class="line"> <span class="keyword">let</span> formData = <span class="keyword">new</span> FormData();</span><br><span class="line"> <span class="keyword">let</span> file = e.target.files[<span class="number">0</span>]</span><br><span class="line"> <span class="comment">//把文件信息放入对象中</span></span><br><span class="line"> formData.append( <span class="string">"excelFile"</span>, file);</span><br><span class="line"> <span class="comment">// let params = new URLSearchParams(formData) //当请求没有转换成formData时使用</span></span><br><span class="line"> <span class="keyword">if</span>(file.type == <span class="string">"application/vnd.ms-excel"</span> || file.type == <span class="string">"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"</span>){</span><br><span class="line"> <span class="keyword">this</span>.$ajax({</span><br><span class="line"> method: <span class="string">"post"</span>,</span><br><span class="line"> url: <span class="string">""</span>,</span><br><span class="line"> data: formData,</span><br><span class="line"> headers: {</span><br><span class="line"> <span class="string">'Content-Type'</span>: <span class="string">'multipart/form-data'</span></span><br><span class="line"> }</span><br><span class="line"> }).then( <span class="function"><span class="params">res</span> =></span> {</span><br><span class="line"> <span class="keyword">if</span>(res.code === <span class="number">200</span>){</span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> message: <span class="string">"导入成功"</span>,</span><br><span class="line"> type: <span class="string">"success"</span></span><br><span class="line"> })</span><br><span class="line"> }<span class="keyword">else</span> {</span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> message: <span class="string">"导入失败"</span>,</span><br><span class="line"> type: <span class="string">"error"</span></span><br><span class="line"> })</span><br><span class="line"> }</span><br><span class="line"> })</span><br><span class="line"> }<span class="keyword">else</span> {</span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> message: <span class="string">"请选择excel文件！"</span>,</span><br><span class="line"> type: <span class="string">"error"</span></span><br><span class="line"> })</span><br><span class="line"> }</span><br><span class="line"> <span class="comment">//此处必须将控制导入的input值进行置空，否则不会触发change事件，会导致同一个文件不能二次导入</span></span><br><span class="line"> <span class="built_in">document</span>.getElementById(<span class="string">'up'</span>).value = <span class="literal">null</span>;</span><br><span class="line"> },</span><br></pre></td></tr></table></figure>
<h5 id="导出Excel"><a href="#导出Excel" class="headerlink" title="导出Excel"></a>导出Excel</h5><figure class="highlight javascript"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">outputCustomProduct() {</span><br><span class="line"> <span class="keyword">this</span>.$ajax({</span><br><span class="line"> method: <span class="string">""</span>,</span><br><span class="line"> url: <span class="string">""</span>,</span><br><span class="line"> params: {</span><br><span class="line"> </span><br><span class="line"> },</span><br><span class="line"> responseType: <span class="string">'blob'</span></span><br><span class="line"> }).then(<span class="function"><span class="params">res</span> =></span> {</span><br><span class="line"> <span class="built_in">console</span>.log(res)</span><br><span class="line"> <span class="comment">//调用成功，在html中创建一个a元素</span></span><br><span class="line"> <span class="keyword">let</span> aTag = <span class="built_in">document</span>.createElement(<span class="string">'a'</span>);</span><br><span class="line"> <span class="comment">//创建一个blob对象</span></span><br><span class="line"> <span class="keyword">let</span> blob = <span class="keyword">new</span> Blob([res], {</span><br><span class="line"> <span class="comment">//Excel文件版本</span></span><br><span class="line"> type: <span class="string">"multipart/form-data;charset=utf-8"</span>, <span class="comment">//application/vnd.openxmlformats-officedocument.spreadsheetml.sheet</span></span><br><span class="line"> });<span class="comment">// 这个content是下载的文件内容，自己修改</span></span><br><span class="line"> aTag.download = <span class="string">'订单导入模板.xls'</span>;<span class="comment">// 下载的文件名</span></span><br><span class="line"> aTag.href = <span class="built_in">window</span>.URL.createObjectURL(blob); <span class="comment">//创建一个URL对象</span></span><br><span class="line"> aTag.click();</span><br><span class="line"> <span class="built_in">document</span>.body.removeChild(aTag)</span><br><span class="line"> <span class="built_in">window</span>.URL.revokeObjectURL(blob); <span class="comment">//释放URL对象</span></span><br><span class="line"> })</span><br><span class="line"> },</span><br></pre></td></tr></table></figure>
<h4 id="Vue-Element-UI-实现文件excel的上传及下载2种方式（文件流及a标签）"><a href="#Vue-Element-UI-实现文件excel的上传及下载2种方式（文件流及a标签）" class="headerlink" title="Vue Element UI 实现文件excel的上传及下载2种方式（文件流及a标签）"></a>Vue Element UI 实现文件excel的上传及下载2种方式（文件流及a标签）</h4><p><strong>上传</strong>使用Element ui 里的 el-upload</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><el-form-item label="上传"></span><br><span class="line"> <el-upload</span><br><span class="line"> class="upload-demo"</span><br><span class="line"> ref="upload"</span><br><span class="line"> action="#"</span><br><span class="line"> :limit="1"</span><br><span class="line"> :http-request="uploadOk"</span><br><span class="line"> :on-preview="handlePreview"</span><br><span class="line"> :before-upload="beforeAvatarUpload"</span><br><span class="line"> :on-remove="handleRemove"</span><br><span class="line"> :file-list="fileList"</span><br><span class="line"> :auto-upload="true"></span><br><span class="line"> <el-button slot="trigger" size="small" type="primary">选取文件</el-button></span><br><span class="line"> <div slot="tip" class="el-upload__tip">只能上传xls/xlsx文件</div></span><br><span class="line"> </el-upload></span><br><span class="line"> </el-form-item></span><br></pre></td></tr></table></figure>
<blockquote>
<p>action 必选参数，上传的地址<br>:limit 最大允许上传个数<br>:http-request 覆盖默认的上传行为，自定义上传<br>:on-preview 文件上传时的钩子<br>:before-upload 上传文件之前的钩子<br>:on-remove 文件列表移除文件时的钩子<br>:file-list 上传的文件列表<br>:auto-upload 是否在选取文件后立即进行上传</p>
</blockquote>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line"> <span class="comment">// 上传前验证</span></span><br><span class="line">beforeAvatarUpload(file) {</span><br><span class="line"> <span class="keyword">let</span> Xls = file.name.split(<span class="string">'.'</span>); </span><br><span class="line"> <span class="keyword">if</span>(Xls[<span class="number">1</span>] === <span class="string">'xls'</span>||Xls[<span class="number">1</span>] === <span class="string">'xlsx'</span>){</span><br><span class="line"> <span class="keyword">return</span> file</span><br><span class="line"> }<span class="keyword">else</span> {</span><br><span class="line"> <span class="keyword">this</span>.$message.error(<span class="string">'上传文件只能是 xls/xlsx 格式!'</span>)</span><br><span class="line"> <span class="keyword">return</span> <span class="literal">false</span></span><br><span class="line"> }</span><br><span class="line">},</span><br><span class="line">handleRemove(file, fileList) {</span><br><span class="line"> <span class="built_in">console</span>.log(file, fileList);</span><br><span class="line">},</span><br><span class="line">handlePreview(file) {</span><br><span class="line"> <span class="built_in">console</span>.log(file);</span><br><span class="line">},</span><br><span class="line"><span class="comment">// 上传成功</span></span><br><span class="line">uploadOk(val){</span><br><span class="line"> <span class="keyword">let</span> fd = <span class="keyword">new</span> FormData() <span class="comment">// 上传文件必须为FormData 所以这边New FormData() ，把上传文件的File填进去。</span></span><br><span class="line"> fd.append(<span class="string">'upload_file'</span>,val.file)</span><br><span class="line"> fd.append(<span class="string">'dir_name'</span>,<span class="string">'contract'</span>)</span><br><span class="line"> <span class="comment">// console.log(fd.upload_file) 错误</span></span><br><span class="line"> <span class="comment">// console.log(fd.get("upload_file") 正确</span></span><br><span class="line"> <span class="keyword">this</span>.$post(<span class="string">'/upload/uploadFile'</span>, fd).then(<span class="function">(<span class="params">result</span>) =></span> {</span><br><span class="line"> <span class="keyword">if</span> (result.code == <span class="number">200</span>) {</span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> message: <span class="string">'上传成功'</span>,</span><br><span class="line"> type: <span class="string">'success'</span></span><br><span class="line"> });</span><br><span class="line"> <span class="comment">// 接收excel文件地址</span></span><br><span class="line"> <span class="keyword">this</span>.addForm.addurl = result.data.file_url</span><br><span class="line"> }<span class="keyword">else</span>{</span><br><span class="line"> <span class="keyword">this</span>.message(result.msg)</span><br><span class="line"> }</span><br><span class="line"> })</span><br><span class="line">},</span><br></pre></td></tr></table></figure>
<h5 id="下载"><a href="#下载" class="headerlink" title="下载"></a>下载</h5><p>a标签方式：</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 下载源文件</span></span><br><span class="line"> handleDownload: <span class="function"><span class="keyword">function</span>(<span class="params">index, row</span>)</span>{</span><br><span class="line"> <span class="keyword">if</span>(row.contract_file_url == <span class="string">''</span>){</span><br><span class="line"> <span class="keyword">this</span>.$message({</span><br><span class="line"> message: <span class="string">'没有源文件'</span>,</span><br><span class="line"> type: <span class="string">'warning'</span></span><br><span class="line"> })</span><br><span class="line"> }<span class="keyword">else</span>{</span><br><span class="line"> <span class="comment">// 创建 a 标签 </span></span><br><span class="line"> <span class="keyword">let</span> link = <span class="built_in">document</span>.createElement(<span class="string">'a'</span>)</span><br><span class="line"> <span class="comment">// href 链接, row.contract_file_url 是从表格里拿到的下载文件地址</span></span><br><span class="line"> link.setAttribute(<span class="string">'href'</span>, row.contract_file_url)</span><br><span class="line"> <span class="comment">// 自执行点击事件</span></span><br><span class="line"> link.click()</span><br><span class="line"> } </span><br><span class="line"> },</span><br></pre></td></tr></table></figure>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2020/12/28/cc-develop-note-ii/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2020/12/28/cc-develop-note-ii/" class="post-title-link" itemprop="url">cc-develop-note-ii</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2020-12-28 20:30:48 / 修改时间：20:40:07" itemprop="dateCreated datePublished" datetime="2020-12-28T20:30:48+08:00">2020-12-28</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<p><img src="//yoursite.com/2020/12/28/cc-develop-note-ii/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201112194147771.png" alt="image-20201112194147771"></p>
<p>==管理员批量导入新生名单==</p>
<p>==整理会议论文列表==</p>
<p><em>不用添加老师；导入毕业生名单是另外的功能，只需要学号，把对应学号的学生的毕业状态置位即可</em></p>
<p>新账户密码：NKcc+身份证后六位，没有身份证的话，用NKcc+学号。后台处理一下 或者 设置密码一列从excel里读取，让使用者自己录入密码。</p>
<p>Excel需要的列：用户名、年级、层次（本、学硕、专硕、博）、身份证号、密码</p>
<h2 id="会议论文列表"><a href="#会议论文列表" class="headerlink" title="会议论文列表"></a>会议论文列表</h2><p><a href="https://m.ais.cn/goodMeet" target="_blank" rel="noopener">https://m.ais.cn/goodMeet</a></p>
<p>电子信息，计算机科学类，ei检索。偏系统，软件的。找下会议论文列表。有的是会议网站就有，没有的看看谷歌学术或者百度学术有没有搜索指定会议的。</p>
<p>搜索论文：<a href="https://www.engineeringvillage.com/search/quick.url" target="_blank" rel="noopener">https://www.engineeringvillage.com/search/quick.url</a></p>
<p>工程索引（The Engineering Index，简称EI）</p>
<h2 id="git储藏"><a href="#git储藏" class="headerlink" title="git储藏"></a>git储藏</h2><p><code>git stash</code> 将当前未提交到本地（和服务器）的代码推入到Git的栈中，操作后的工作区间和上一次提交的内容是完全一样的。</p>
<p><code>git checkout -b feat-import</code>创建分支并进行切换。git新功能用的前缀是 <code>feat</code></p>
<p><code>git stash pop</code> 恢复之前缓存的工作目录。将缓存堆栈中的第一个stash删除，并将对应修改应用到当前的工作目录下。</p>
<p><code>git stash apply</code>将以前的工作应用回来，但并不删除stash拷贝。</p>
<font color="grey">Windows徽标键+ V，打开剪贴板历史记录面板</font>
<h2 id="批量导入毕业生"><a href="#批量导入毕业生" class="headerlink" title="批量导入毕业生"></a>批量导入毕业生</h2><h3 id="下载Excel空表"><a href="#下载Excel空表" class="headerlink" title="下载Excel空表"></a>下载Excel空表</h3><p>使用 a 标签的 download 属性</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><a href="src/assets/graduate.xlsx">下载毕业生信息excel表格</a></span><br></pre></td></tr></table></figure>
<p><img src="//yoursite.com/2020/12/28/cc-develop-note-ii/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201114093539717.png" alt="image-20201114093539717"></p>
<h5 id="路径中-和-的区别"><a href="#路径中-和-的区别" class="headerlink" title="路径中 @ 和 . 的区别"></a>路径中 @ 和 . 的区别</h5><p>import store from ‘. /vuex/store’是相对路径,代表当前路径同级下vuex下的store<br>import store from ‘ @/vuex/store’也是相对路径，和上面意思差不多,但是具体代表什么路径,要看webpack里面对于@是如何配置的，比如:</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">alias: {</span><br><span class="line"> <span class="string">'vue$'</span>: <span class="string">'vue/dist/vue. esm.js'</span>,</span><br><span class="line"> <span class="string">'@'</span>: resolve(<span class="string">'src'</span>),</span><br><span class="line"> Axios<span class="string">':'</span> axios</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<h3 id="上传Excel"><a href="#上传Excel" class="headerlink" title="上传Excel"></a>上传Excel</h3><h4 id="el-upload组件"><a href="#el-upload组件" class="headerlink" title="el-upload组件"></a>el-upload组件</h4><p>action 必选参数，上传的地址</p>
<p>手动提交：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">this.$refs.upload.submit();</span><br></pre></td></tr></table></figure>
<h4 id="显示跨域问题"><a href="#显示跨域问题" class="headerlink" title="显示跨域问题"></a>显示跨域问题</h4><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Access to XMLHttpRequest at 'http://localhost:8081/cc-computer-homepage/backend/web/index.php/api/v1/students/batch' from origin 'http://localhost:8084' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.</span><br></pre></td></tr></table></figure>
<ul>
<li>后端没有提供API</li>
</ul>
<p>backend/config/main.php中配置方法路径和方法名。controller中补充 verbs。在controller中编写方法。</p>
<p>控制器 ID <code>user</code> 以复数形式出现在 <code>users</code> 末端。要获取请求参数，调用 <code>request</code> 组件的 <a href="https://www.yiiframework.com/doc/api/2.0/yii-web-request#get(" target="_blank" rel="noopener">get()</a>-detail) 方法和 <a href="https://www.yiiframework.com/doc/api/2.0/yii-web-request#post(" target="_blank" rel="noopener">post()</a>-detail) 方法。 他们分别返回 <code>$_GET</code> 和 <code>$_POST</code> 的值。先发一个 OPTION 请求，成功之后再发 POST 请求。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">// 设置上传的请求头部</span><br><span class="line">headers: { Authorization: "Bearer " + getCookie("access-token")},</span><br></pre></td></tr></table></figure>
<ul>
<li>路由管理，数据库中没有该权限</li>
</ul>
<h4 id="PHP调试"><a href="#PHP调试" class="headerlink" title="PHP调试"></a>PHP调试</h4><figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">return</span> [<span class="string">"success"</span>=> <span class="keyword">false</span>, <span class="string">"data"</span>=>$UploadTime];</span><br></pre></td></tr></table></figure>
<p>返回 json 格式的数据到输出流，在 Network –> Response 中可以查看</p>
<h4 id="文件保存"><a href="#文件保存" class="headerlink" title="文件保存"></a>文件保存</h4><p>调用 common/components/fileUpload.php 的 uploadFile() 函数，将文件保存到设置的路径。拿到该路径存入日志。</p>
<h5 id="获取当前用户-ID"><a href="#获取当前用户-ID" class="headerlink" title="获取当前用户 ID"></a>获取当前用户 ID</h5><p><del>localStorage 和 sessionStorage 属性允许在浏览器中存储 key/value 对的数据。localStorage 用于长久保存整个网站的数据，保存的数据没有过期时间，直到手动去删除。</del></p>
<p><del>UserID: getCookie(“realid”)</del></p>
<p>从前端获取是不安全的，直接从后端获取 UserID :</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$user_id = \Yii::$app->user->identity->getId();</span><br></pre></td></tr></table></figure>
<p>Yii::$app->user->identity 返回当前登录用户或当前用户未通过身份验证（表示访客）的身份类的实例。</p>
<h4 id="PHPExcel"><a href="#PHPExcel" class="headerlink" title="PHPExcel"></a>PHPExcel</h4><h5 id="composer-换源"><a href="#composer-换源" class="headerlink" title="composer 换源"></a>composer 换源</h5><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">composer config -g repo.packagist composer https://mirrors.aliyun.com/composer/</span><br></pre></td></tr></table></figure>
<h5 id="使用-phpExcel"><a href="#使用-phpExcel" class="headerlink" title="使用 phpExcel"></a>使用 phpExcel</h5><p>没找到PHPExcel的命名空间，暂时使用了简单粗暴的方式来引入第三方类库：</p>
<p>在web目录中index.php中：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">require</span>(<span class="keyword">__DIR__</span> . <span class="string">'/../../vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php'</span>);</span><br></pre></td></tr></table></figure>
<p>在 controller 中使用的时候 <code>new \IOFactory</code> 需要加一个反斜杠：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$inputFileType = \PHPExcel_IOFactory::identify($path);<span class="comment">//自动识别Excel版本</span></span><br></pre></td></tr></table></figure>
<h6 id="读取指定列的信息"><a href="#读取指定列的信息" class="headerlink" title="读取指定列的信息"></a>读取指定列的信息</h6><p>backend/modules/api/v1/models/student/StudentModel.php :</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//解析Excel表格，返回毕业生学号数组</span></span><br><span class="line"><span class="keyword">private</span> <span class="keyword">static</span> <span class="function"><span class="keyword">function</span> <span class="title">readGraduateFromExcel</span><span class="params">($path)</span></span></span><br><span class="line"><span class="function"></span>{</span><br><span class="line"> <span class="comment">//自动识别Excel版本</span></span><br><span class="line"> $inputFileType = \PHPExcel_IOFactory::identify($path);</span><br><span class="line"> $objReader = \PHPExcel_IOFactory::createReader($inputFileType);</span><br><span class="line"> $objPHPExcel = $objReader->load($path);</span><br><span class="line"> $sheet = $objPHPExcel->getSheet(<span class="number">0</span>);<span class="comment">//读取第一个sheet</span></span><br><span class="line"></span><br><span class="line"> $highestRow = $sheet->getHighestRow();<span class="comment">//取得总行数</span></span><br><span class="line"> $result = [];</span><br><span class="line"> <span class="comment">//过滤第一行表头，内容从Excel的第二行开始读</span></span><br><span class="line"> <span class="keyword">for</span> ($i = <span class="number">2</span>; $i <= $highestRow; $i++) {</span><br><span class="line"> <span class="comment">//列的索引是从0开始计数</span></span><br><span class="line"> $result[] = $sheet->getCellByColumnAndRow(<span class="number">0</span>, $i)->getValue();</span><br><span class="line"> }</span><br><span class="line"> <span class="keyword">return</span> $result;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<font color="red">没有考虑学号为空的情况，即：必须填写要添加的毕业生的学号</font>
<h6 id="创建自己的packagist-安装包"><a href="#创建自己的packagist-安装包" class="headerlink" title="创建自己的packagist 安装包"></a><del>创建自己的packagist 安装包</del></h6><ol>
<li><p>创建GitHub项目并clone到本地</p>
</li>
<li><p>在项目下打开cmd，输入命令<code>composer init</code>用来创建composer.json，接下来需要在控制台中设置项目名称、描述、作者等信息，方括号里面是默认的信息。其中，资源包的最低稳定版本，默认为 stable。 可选 -dev、-patch、-alpha、-beta 或 -RC；包类型默认选择 library ，License []: mit</p>
</li>
<li><p>在composer文件中添加以下代码，使安装自动加载</p>
<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">"autoload": {</span><br><span class="line"> "psr-4": {</span><br><span class="line"> "joyce\\myExcel\\": ""</span><br><span class="line"> }</span><br><span class="line">},</span><br></pre></td></tr></table></figure>
<p>就可以使用</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">use</span> <span class="title">joyce</span>\<span class="title">myExcel</span> <span class="title">xxx</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>进入 packagist 官网(<a href="https://packagist.org/" target="_blank" rel="noopener">https://packagist.org</a>)注册账户并在设置里面关联自己的GitHub账户，使用GitHub账户登录，点击submit 提交GitHub项目地址，点击check。</p>
</li>
<li><p>部署自动更新（ packgist 官网有教程）</p>
</li>
<li><p>发布版本 （改动之后需要发布版本 packagist 里面才会更新）</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">git <span class="built_in">log</span> --oneline</span><br><span class="line">git tag -a <span class="string">'1.0.1'</span> xxxxxx</span><br><span class="line">git push --tags</span><br></pre></td></tr></table></figure>
</li>
<li><p>遇到的问题：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">[UnexpectedValueException]</span><br><span class="line">Error while installing pcy/my-php-excel-packgist, composer-plugin packages should have a class defined in their extra key to be usable.</span><br></pre></td></tr></table></figure>
<p>是因为包类型选择了 composer-plugin </p>
</li>
</ol>
<h3 id="添加批量导入操作的日志"><a href="#添加批量导入操作的日志" class="headerlink" title="添加批量导入操作的日志"></a>添加批量导入操作的日志</h3><p>对于毕业生，excel 表格中只需要有学号信息。在数据库表 cc_student 中根据学号 SID 修改记录，将 IsGraduated 和 StudyStatus 两个字段对应改为 1 和 已毕业。</p>
<h5 id="数据库表-cc-log-batch"><a href="#数据库表-cc-log-batch" class="headerlink" title="数据库表 cc_log_batch"></a>数据库表 cc_log_batch</h5><p>用于记录在批量导入过程中，</p>
<p>参考批量创建活动 <code>backend/modules/api/v1/models/activity/ActivityParticipationModel.php</code></p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$transaction=Yii::$app->db->beginTransaction(<span class="string">'SERIALIZABLE'</span>);</span><br></pre></td></tr></table></figure>
<p>MySQL数据库提供的四种隔离级别：</p>
<p>① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。</p>
<p>② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。</p>
<p>③ Read committed (读已提交)：可避免脏读的发生。</p>
<p>④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。</p>
<h5 id="LogBatchModel-php"><a href="#LogBatchModel-php" class="headerlink" title="LogBatchModel.php"></a>LogBatchModel.php</h5><p>参照<code>backend/modules/api/v1/models/log/LogApiModel.php</code>编写 LogBatchModel .</p>
<p>核心验证器 <code>safe</code>：该验证器并不进行数据验证。</p>
<ul>
<li>“message”:”Setting unknown property: backend\modules\api\v1\models\log\LogBatchModel::ResStatus”</li>
</ul>
<p>原因：afterBatch() 函数中 copy-paste 错误。</p>
<p><img src="//yoursite.com/2020/12/28/cc-develop-note-ii/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201127215353617.png" alt="image-20201127215353617"></p>
<h6 id="backend-modules-api-v1-models-student-StudentModel-php"><a href="#backend-modules-api-v1-models-student-StudentModel-php" class="headerlink" title="backend/modules/api/v1/models/student/StudentModel.php:"></a>backend/modules/api/v1/models/student/StudentModel.php:</h6><figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//批量导入毕业生，记录日志，成功时返回毕业生SID集合</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">static</span> <span class="function"><span class="keyword">function</span> <span class="title">batchGraduateStudentUpload</span><span class="params">($data)</span></span>{</span><br><span class="line"> date_default_timezone_set(<span class="string">'PRC'</span>);</span><br><span class="line"> $log = LogBatchModel::beforeBatch($data);</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">false</span>;</span><br><span class="line"> <span class="keyword">if</span>($log[<span class="string">'success'</span>]===<span class="keyword">false</span>)</span><br><span class="line"> {</span><br><span class="line"> <span class="comment">//创建批量导入的日志时出现错误</span></span><br><span class="line"> $res[<span class="string">'logBatch'</span>]=$log;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line"> }</span><br><span class="line"> $LBID=$log[<span class="string">'data'</span>][<span class="string">'LBID'</span>];</span><br><span class="line"> $filePath=$data[<span class="string">'FilePath'</span>];</span><br><span class="line"> <span class="keyword">try</span>{</span><br><span class="line"> <span class="comment">//获得毕业生的SID集合</span></span><br><span class="line"> $SIDArray = <span class="keyword">self</span>::readGraduateFromExcel($filePath);</span><br><span class="line"> }<span class="keyword">catch</span> (<span class="keyword">Exception</span> $e)</span><br><span class="line"> {</span><br><span class="line"> <span class="comment">//解析 Excel 出现错误</span></span><br><span class="line"> $res[<span class="string">'filePath'</span>]=$filePath;</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line"> }</span><br><span class="line"></span><br><span class="line"> $transaction=Yii::$app->db->beginTransaction(<span class="string">'SERIALIZABLE'</span>);</span><br><span class="line"> <span class="keyword">try</span> {</span><br><span class="line"> <span class="keyword">for</span> ($i = <span class="number">0</span>; $i < count($SIDArray); $i++)</span><br><span class="line"> {</span><br><span class="line"> $SID = $SIDArray[$i];</span><br><span class="line"> $newStatus[<span class="string">'IsGraduated'</span>]=<span class="number">1</span>;</span><br><span class="line"> $newStatus[<span class="string">'StudyStatus'</span>]=<span class="string">'已毕业'</span>;</span><br><span class="line"> <span class="comment">//将 IsGraduated 和 StudyStatus 两个字段对应改为 1 和 已毕业</span></span><br><span class="line"> <span class="keyword">self</span>::updateStudent($SID,$newStatus);</span><br><span class="line"> }</span><br><span class="line"> $transaction->commit();</span><br><span class="line"> $res[<span class="string">'data'</span>] = $SIDArray;</span><br><span class="line"> } <span class="keyword">catch</span> (\<span class="keyword">Exception</span> $exception) {</span><br><span class="line"> $transaction->rollBack();</span><br><span class="line"> LogBatchModel::afterBatch(<span class="keyword">false</span>,$LBID);</span><br><span class="line"> $res[<span class="string">'data'</span>] = $exception->getMessage();</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line"> }</span><br><span class="line"> $res[<span class="string">'success'</span>] = <span class="keyword">true</span>;</span><br><span class="line"> LogBatchModel::afterBatch(<span class="keyword">true</span>,$LBID);</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>测试 batchGraduateStudentUpload() ，batch-graduate 接口 Network -> Response：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">{"success":true,"data":[1713650,1713429,1713032]}</span><br></pre></td></tr></table></figure>
<p>==UpdateStudent() 接口或许有点问题==，在batchGraduateStudentUpload() 中重写了修改学生毕业状态的语句。</p>
<p>在前端给出提示，显示已修改为毕业生的学号或给出错误提示：</p>
<p><img src="//yoursite.com/2020/12/28/cc-develop-note-ii/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201129162233663.png" alt="image-20201129162233663"></p>
<p>src\views\student\StudentInfoManage.vue:</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 文件上传成功处理</span></span><br><span class="line">handleFileSuccess(response, file, fileList) {</span><br><span class="line"><span class="keyword">this</span>.upload.open = <span class="literal">false</span>;</span><br><span class="line"><span class="keyword">this</span>.upload.isUploading = <span class="literal">false</span>;</span><br><span class="line"><span class="keyword">this</span>.$refs.upload.clearFiles();</span><br><span class="line"><span class="keyword">if</span>(response.success===<span class="literal">true</span>){</span><br><span class="line"><span class="keyword">this</span>.$alert(<span class="string">"以下学号的学生已修改为毕业生：\n"</span>+response.data, <span class="string">"导入结果"</span>, { <span class="attr">dangerouslyUseHTMLString</span>: <span class="literal">true</span> });</span><br><span class="line">}</span><br><span class="line"><span class="keyword">else</span>{</span><br><span class="line"><span class="keyword">this</span>.$alert(<span class="string">"导入失败，请检查Excel数据格式"</span>, <span class="string">"导入结果"</span>, { <span class="attr">dangerouslyUseHTMLString</span>: <span class="literal">true</span> });</span><br><span class="line">}</span><br><span class="line"><span class="comment">// 重新获取列表</span></span><br><span class="line"><span class="keyword">this</span>.searchStudentInfoManage();</span><br><span class="line">},</span><br></pre></td></tr></table></figure>
<h2 id="批量导入新生"><a href="#批量导入新生" class="headerlink" title="批量导入新生"></a>批量导入新生</h2><p>在批量导入毕业生的基础上，按照相同逻辑实现导入新生的功能。尽量整合两个模块。</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 导入新生</span></span><br><span class="line">handleImportFreshman() {</span><br><span class="line"> <span class="keyword">this</span>.upload.filePath=<span class="string">"src/assets/freshman.xlsx"</span>;</span><br><span class="line"> <span class="keyword">this</span>.upload.title = <span class="string">"导入新生"</span>;</span><br><span class="line"> <span class="keyword">this</span>.upload.url=<span class="keyword">this</span>.DownBaseUrL + <span class="string">"/students/batch-freshman"</span>;</span><br><span class="line"> <span class="keyword">this</span>.attachmentInfo.ModuleName=<span class="string">"batchFreshmanStudentUpload"</span>;</span><br><span class="line"> <span class="comment">// console.log("clicked 导入新生 button");</span></span><br><span class="line"> <span class="keyword">this</span>.upload.open = <span class="literal">true</span>;</span><br><span class="line">},</span><br></pre></td></tr></table></figure>
<h3 id="设计新生excel"><a href="#设计新生excel" class="headerlink" title="设计新生excel"></a>设计新生excel</h3><p>学号、姓名、年级、层次、专业、身份证号、密码、导师。</p>
<p>层次和专业设置下拉菜单。将模板 freshman.xlsx 放入 cc-frontend\src\assets 文件夹下。</p>
<ul>
<li>修改Excel表格，修改可选项、必填项，增加导师列 (数据 –> 数据验证)</li>
</ul>
<h3 id="数据库表"><a href="#数据库表" class="headerlink" title="数据库表"></a>数据库表</h3><p>需要进行插入的表：cc_major_enrollyear、cc_student、cc_user、cc_auth_assignment（如果是导入学生的话需要插两条：学生、游客）</p>
<p><del>关于“层次”属性需要查表 cc_education_level ，考虑到条目不多，可以直接在后端做映射而不去数据库查表。</del>为了避免数据库有变更，还是查表吧。</p>
<p>添加 user、student 参照 backend/modules/api/v1/controllers/UserController.php 的 actionSignup()。cc_student 的 EnrollDate 设置为上半年/下半年</p>
<p>添加 assignment 参照 vendor/clement/yii-rest-rbac2.0/controllers/AssignmentController.php 的 actionAssign($id)</p>
<p>backend/config/main.php：<code>"class" => "clement\\rest\Module",</code></p>
<p>可能会存储<strong>失败</strong>的情况：学号为空、找不到导师、找不到 MajorName & EducationLevelID 对应的 MID（cc_major表）、找不到 年级 & MajorID 对应的 MEYID（cc_major_enrollyear表）</p>
<p>密码明文默认111111</p>
<h4 id="password加密传输"><a href="#password加密传输" class="headerlink" title="password加密传输"></a>password加密传输</h4><p>常用的签名算法：MD5 和 HMAC-SHA256 </p>
<ul>
<li>验证 yii2 和 vue 对密码的 hash 算法结果是否一致，用户登录或新建单个用户时输入密码后由前端进行 HMAC-SHA256 加密存入数据库（src\components\admin\UserInfo.vue），但批量导入数据库时是由后端来加密密码存入数据库，需要保持加密结果一致。</li>
</ul>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">import</span> {sha256} <span class="keyword">from</span> <span class="string">'js-sha256'</span>;</span><br><span class="line"><span class="built_in">console</span>.log(sha256.hmac(<span class="string">'111111'</span>, <span class="string">'cc-frontend-vue'</span>));</span><br></pre></td></tr></table></figure>
<p><img src="//yoursite.com/2020/12/28/cc-develop-note-ii/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201130221713885.png" alt="image-20201130221713885"></p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">(property) Hash.hmac: Hmac</span><br><span class="line">(secretKey: string, <span class="attr">message</span>: Message) => string</span><br></pre></td></tr></table></figure>
<p>注意与 PHP 参数的顺序不同：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">function</span> <span class="title">hash_hmac</span> <span class="params">($algo, $data, $key, $raw_output = false)</span> </span>{}</span><br></pre></td></tr></table></figure>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta"><?php</span></span><br><span class="line">$hash = hash_hmac(<span class="string">'SHA256'</span>, <span class="string">'cc-frontend-vue'</span>,<span class="string">'111111'</span>);</span><br><span class="line"><span class="keyword">echo</span> <span class="string">"result = "</span> . $hash;</span><br><span class="line"><span class="meta">?></span></span><br></pre></td></tr></table></figure>
<p>xampp/htdocs/dashboard/phpinfo.php，访问<a href="http://localhost:8081/dashboard/phpinfo.php" target="_blank" rel="noopener">http://localhost:8081/dashboard/phpinfo.php</a></p>
<h3 id="后端-batch-freshman-接口"><a href="#后端-batch-freshman-接口" class="headerlink" title="后端 batch-freshman 接口"></a>后端 batch-freshman 接口</h3><p>backend/config/main.php: (‘controller’ => ‘api/v1/student’)</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">'POST,OPTIONS batch-freshman'</span> => <span class="string">'batch-freshman-student-upload'</span>,</span><br></pre></td></tr></table></figure>
<p>backend/modules/api/v1/controllers/StudentController.php: </p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//批量导入新生</span></span><br><span class="line"><span class="keyword">public</span> <span class="function"><span class="keyword">function</span> <span class="title">actionBatchFreshmanStudentUpload</span><span class="params">()</span></span></span><br><span class="line"><span class="function"></span>{</span><br><span class="line"> $request = \Yii::$app->request;</span><br><span class="line"> <span class="keyword">if</span> ($request->getIsOptions()) {</span><br><span class="line"> <span class="keyword">return</span> <span class="keyword">$this</span>->ResponseOptions(<span class="keyword">$this</span>->verbs()[<span class="string">'batch-freshman-student-upload'</span>]);</span><br><span class="line"> }</span><br><span class="line"> $upload = <span class="keyword">new</span> fileUpload(<span class="keyword">false</span>, <span class="number">2</span>);</span><br><span class="line"> $res = $upload->uploadFile();</span><br><span class="line"> <span class="keyword">if</span> ($res->{<span class="string">'success'</span>} == <span class="keyword">false</span>) {</span><br><span class="line"> <span class="keyword">return</span> [<span class="string">'success'</span> => <span class="keyword">false</span>, <span class="string">'data'</span> => $res->{<span class="string">'error'</span>}];</span><br><span class="line"> }</span><br><span class="line"> $temp[<span class="string">'FilePath'</span>]=$res->{<span class="string">'data'</span>}[<span class="string">'Destination'</span>];</span><br><span class="line"> $temp[<span class="string">'UploadTime'</span>]=$request->post(<span class="string">'UploadTime'</span>);</span><br><span class="line"> $temp[<span class="string">'UserID'</span>] = \Yii::$app->user->identity->getId();</span><br><span class="line"> $temp[<span class="string">'ModuleName'</span>] = $request->post(<span class="string">'ModuleName'</span>);</span><br><span class="line"> $temp[<span class="string">'IsImported'</span>]=<span class="number">0</span>;</span><br><span class="line"> <span class="keyword">return</span> StudentModel::batchFreshmanStudentUpload($temp);</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<h4 id="解析新生Excel表格"><a href="#解析新生Excel表格" class="headerlink" title="解析新生Excel表格"></a>解析新生Excel表格</h4><p>backend/modules/api/v1/models/student/StudentModel.php ：</p>
<ul>
<li>如果某一格为空？</li>
</ul>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="function"><span class="keyword">function</span> <span class="title">rangeToArray</span><span class="params">($pRange = <span class="string">'A1'</span>, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)</span></span></span><br></pre></td></tr></table></figure>
<ul>
<li>由于在后面的列中编写了下拉列表，因此不能用函数来获取总行数总列数？</li>
</ul>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">$highestRow = $sheet->getHighestRow();<span class="comment">//取得总行数</span></span><br><span class="line">$highestCol = $sheet->getHighestColumn();<span class="comment">//取得总行数</span></span><br></pre></td></tr></table></figure>
<p>解决：将下拉列表的选项放在新的 sheet 中</p>
<blockquote>
<p>{“success”:true,</p>
<p>“data”:[[[2011430,”张一”,2020,”本科”,null,”123654166623564612”,111111,”温延龙”]],[[2011431,”张二”,2017,”博士”,”信息安全”,”369852232365894626”,111111,”宋春瑶”]],[[2011432,”张三”,2018,”硕士”,”软件工程”,null,111111,”王刚强”]],[[null,”张四”,2019,”专业硕士”,”运筹学与控制论”,null,null,”温延龙”]],[[2011434,”张五”,2020,”本科”,”计算机科学与技术”,null,111111,”袁晓洁”]]],</p>
<p>“logBatch”:{“success”:true,”data”:{“FilePath”:”/storager/2020_year/12_month/02_day/1c7b9bac371e8038e26b3081fee650e3/1c7b9bac371e8038e26b3081fee650e3.xlsx”,”UploadTime”:”2020-12-02 10:56:00”,”UserID”:64041,”ModuleName”:”batchFreshmanStudentUpload”,”IsImported”:0,”LBID”:18}}}</p>
</blockquote>
<p>batchFreshmanStudentUpload($data) 得到 excel 解析内容后，先对异常数据进行预处理。</p>
<h4 id="新生数组数据处理"><a href="#新生数组数据处理" class="headerlink" title="新生数组数据处理"></a>新生数组数据处理</h4><p>哪些不能为空？MajorEnrollYearID –> 专业、年级、层次都不能为空；StudentName；SID。</p>
<p>创建 user 时仿照 backend/modules/api/v1/controllers/UserController.php 的 actionSignup()</p>
<p>测试 batchFreshmanStudentUpload($data) 报错：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">{"success":false,"data":"Undefined offset: 1"}</span><br></pre></td></tr></table></figure>
<p><img src="//yoursite.com/2020/12/28/cc-develop-note-ii/Users\pcy\AppData\Roaming\Typora\typora-user-images\image-20201203120812297.png" alt="image-20201203120812297"></p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">$debugOffset=<span class="keyword">true</span>;</span><br><span class="line"><span class="keyword">if</span>($debugOffset===<span class="keyword">true</span>)</span><br><span class="line">{</span><br><span class="line"> <span class="keyword">for</span> ($i = <span class="number">0</span>; $i < count($StudentArray); $i++)</span><br><span class="line"> {</span><br><span class="line"> $row = $StudentArray[$i][<span class="number">0</span>];</span><br><span class="line"> $student=[];</span><br><span class="line"> <span class="keyword">for</span>($j=<span class="number">0</span>;$j<count($key);$j++)</span><br><span class="line"> {</span><br><span class="line"> $student[$key[$j]]=$row[$j];</span><br><span class="line"> }</span><br><span class="line"> $res[<span class="string">'data'</span>][]=$student;</span><br><span class="line"> }</span><br><span class="line"> <span class="keyword">for</span> ($i = <span class="number">0</span>; $i < count($StudentArray); $i++)</span><br><span class="line"> $res[<span class="string">'SID'</span>][] = $StudentArray[$i][<span class="number">0</span>][<span class="number">0</span>];</span><br><span class="line"> <span class="keyword">return</span> $res;</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>测试：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">$res[<span class="string">'StudentArray'</span>]=$StudentArray;</span><br><span class="line">$res[<span class="string">'Student'</span>]=$StudentArray[<span class="number">1</span>];</span><br><span class="line">$res[<span class="string">'SID'</span>]=$StudentArray[<span class="number">1</span>][<span class="number">0</span>][<span class="number">0</span>];</span><br></pre></td></tr></table></figure>
<p>结果：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">{</span><br><span class="line">"success":false,</span><br><span class="line">"StudentArray":</span><br><span class="line">[[[2011430,"张一",2017,"本科","自动化","123654166623564612",111111,null]],[[2011431,"张二",2017,"本科","信息安全","369852232365894626",111111,null]],[[2011432,"张三",2018,"博士","软件工程",null,111111,"王刚（计）"]],[[2011431,"张四",2019,"硕士","计算机应用技术",null,123456,"温延龙"]],[[2011434,"张五",2017,"本科","计算机科学与技术",null,111111,null]]],</span><br><span class="line">"Student":[[2011431,"张二",2017,"本科","信息安全","369852232365894626",111111,null]],</span><br><span class="line">"SID":2011431</span><br><span class="line">}</span><br></pre></td></tr></table></figure>
<p>是因为每行多了一层数组。</p>
<h2 id="提交"><a href="#提交" class="headerlink" title="提交"></a>提交</h2><p>前端不需要提交 package-lock.json、dev.env.js、http.js、util.js</p>
<p>后端：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">git add .</span><br><span class="line">git commit -m <span class="string">"feat: 批量导入毕业生名单；批量导入新生名单"</span></span><br><span class="line">git push origin feat-import:feat-import</span><br></pre></td></tr></table></figure>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2020/11/28/BT-interview-note-iii/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2020/11/28/BT-interview-note-iii/" class="post-title-link" itemprop="url">BT-interview-note-iii</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2020-11-28 13:17:39" itemprop="dateCreated datePublished" datetime="2020-11-28T13:17:39+08:00">2020-11-28</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar-check"></i>
</span>
<span class="post-meta-item-text">更新于</span>
<time title="修改时间：2020-12-22 14:34:37" itemprop="dateModified" datetime="2020-12-22T14:34:37+08:00">2020-12-22</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<p>redis：<a href="https://cloudpai.gitee.io/2018/04/18/2018-04-18-3/" target="_blank" rel="noopener">https://cloudpai.gitee.io/2018/04/18/2018-04-18-3/</a></p>
<p>java 垃圾回收机制</p>
<p>tcp 和 udp 的区别</p>
<p>项目的框架，数据库表和模型、控制器</p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope="" itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="http://yoursite.com/2020/11/28/BT-interview-note-ii/">
<span hidden itemprop="author" itemscope="" itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/logo.jpg">
<meta itemprop="name" content="Joyce">
<meta itemprop="description" content="Growing Path">
</span>
<span hidden itemprop="publisher" itemscope="" itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Joyce' Blog">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2020/11/28/BT-interview-note-ii/" class="post-title-link" itemprop="url">BT-interview-note-ii</a>