UpgradeCommunityTerm.php 9.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228
  1. <?php
  2. namespace App\Console\Commands;
  3. use App\Models\Channel;
  4. use App\Models\DhammaTerm;
  5. use App\Models\Sentence;
  6. use App\Models\UserOperationDaily;
  7. use App\Services\ChannelService;
  8. use App\Services\V3\GlossaryService;
  9. use App\Tools\Tools;
  10. use Illuminate\Console\Command;
  11. use Illuminate\Support\Str;
  12. class UpgradeCommunityTerm extends Command
  13. {
  14. /**
  15. * The name and signature of the console command.
  16. *
  17. * @var string
  18. */
  19. protected $signature = 'upgrade:community.term {lang} {word?}';
  20. /**
  21. * The console command description.
  22. *
  23. * @var string
  24. */
  25. protected $description = '由各用户的术语生成社区术语表(_community_term_{lang}_):每个 word + tag 一条,取最常见的译名与得分最高的注释';
  26. /**
  27. * Execute the console command.
  28. *
  29. * @return int
  30. */
  31. public function handle(ChannelService $channels, GlossaryService $glossaries)
  32. {
  33. if (Tools::isStop()) {
  34. return 0;
  35. }
  36. $lang = strtolower($this->argument('lang'));
  37. $langFamily = explode('-', $lang)[0];
  38. $localTerm = $channels->systemUid("_community_term_{$lang}_");
  39. if (! $localTerm) {
  40. return 1;
  41. }
  42. $channelId = $channels->systemUid('_System_Pali_VRI_');
  43. if ($channelId === false) {
  44. $this->error('no channel');
  45. return 1;
  46. }
  47. $langs = [$this->argument('lang'), $lang, $langFamily];
  48. $wordArg = $this->argument('word');
  49. // 来源:该语言的全部术语,但不含社区 channel 自己——
  50. // 否则社区里的每一行都会作为来源把自己续命,坏行永远清不掉。
  51. $sources = fn () => DhammaTerm::query()
  52. ->whereIn('language', $langs)
  53. ->where(fn ($q) => $q->whereNull('channal')->orWhere('channal', '<>', $localTerm));
  54. // 要生成的 (word, tag)。tag 的 NULL、''、纯空白都算「无 tag」,归成一组。
  55. $pairs = $sources()
  56. ->select(['word', 'tag'])
  57. ->whereNotNull('word')
  58. ->where('word', '<>', '')
  59. ->distinct()
  60. ->when($wordArg, fn ($q) => $q->where('word', $wordArg))
  61. ->get()
  62. ->map(fn ($row) => (object) ['word' => $row->word, 'tag' => self::normalizeTag($row->tag)])
  63. ->unique(fn ($row) => self::pairKey($row->word, $row->tag))
  64. ->values();
  65. if ($pairs->isEmpty()) {
  66. // 来源查空了多半是出了问题,不要据此清空整个社区 channel
  67. $this->error('no source terms');
  68. return 1;
  69. }
  70. // 社区 channel 里现有的行,按同样的口径分组;处理一组就取走一组,最后剩下的都是该删的
  71. $existing = DhammaTerm::where('channal', $localTerm)
  72. ->when($wordArg, fn ($q) => $q->where('word', $wordArg))
  73. ->orderBy('create_time')
  74. ->orderBy('guid')
  75. ->get()
  76. ->groupBy(fn ($row) => self::pairKey($row->word, self::normalizeTag($row->tag)));
  77. $aiChannels = Channel::where('source_type', 'ai')->pluck('uid')->flip();
  78. $total = $pairs->count();
  79. foreach ($pairs as $index => $word) {
  80. /**
  81. * 最优算法
  82. * 1. 找到最常见的意思
  83. * 2. 找到分数最高的
  84. */
  85. $bestNote = '';
  86. $allTerm = $sources()
  87. ->where('word', $word->word)
  88. ->get()
  89. ->filter(fn ($term) => self::normalizeTag($term->tag) === $word->tag)
  90. ->values();
  91. $score = [];
  92. // $term_exp = [];
  93. foreach ($allTerm as $key => $term) {
  94. // AI 生成的 channel(source_type=ai)里的注释不参与评选;译名统计仍算它们
  95. if ($term->channal !== null && isset($aiChannels[$term->channal])) {
  96. continue;
  97. }
  98. // 获取经验值
  99. $exp = UserOperationDaily::where('user_id', $term->editor_id)
  100. ->where('date_int', '<=', date_timestamp_get(date_create($term->updated_at)) * 1000)
  101. ->sum('duration');
  102. $iExp = (int) ($exp / 1000);
  103. $noteStrLen = $term->note ? mb_strlen($term->note, 'UTF-8') : 0;
  104. $paliStrLen = 0;
  105. $tranStrLen = 0;
  106. $noteWithoutPali = '';
  107. if ($term->note && ! empty(trim($term->note))) {
  108. // 计算note得分
  109. // 查找句子模版
  110. $pattern = "/\{\{[0-9].+?\}\}/";
  111. // 获取去掉句子模版的剩余部分
  112. $noteWithoutPali = preg_replace($pattern, '', $term->note);
  113. $sentences = [];
  114. $iSent = preg_match_all($pattern, $term->note, $sentences);
  115. if ($iSent > 0) {
  116. foreach ($sentences[0] as $sentence) {
  117. $sentId = explode('-', trim($sentence, '{}'));
  118. if (count($sentId) === 4) {
  119. $hasTran = Sentence::where('book_id', $sentId[0])
  120. ->where('paragraph', $sentId[1])
  121. ->where('word_start', $sentId[2])
  122. ->where('word_end', $sentId[3])
  123. ->exists();
  124. $sentLen = Sentence::where('book_id', $sentId[0])
  125. ->where('paragraph', $sentId[1])
  126. ->where('word_start', $sentId[2])
  127. ->where('word_end', $sentId[3])
  128. ->where('channel_uid', $channelId)
  129. ->value('strlen');
  130. if ($sentLen) {
  131. $paliStrLen += $sentLen;
  132. if ($hasTran) {
  133. $tranStrLen += $sentLen;
  134. }
  135. }
  136. }
  137. }
  138. }
  139. }
  140. // 计算该术语note的总得分
  141. $score["{$key}"] = $iExp * $noteStrLen;
  142. // $term_exp["{$key}"] = $iExp;
  143. // $updated_time["{$key}"] = $term->updated_at 先提取,具体如何使用待定
  144. }
  145. // 需要过滤掉system_term的数量,把count(*)替换为经验值加合作为基础得分,(基础经验得分之和转化为标准活动周,再加上最近的更新时间,为最终得分)
  146. // FIXME: 只按 word 统计、不看 tag,同词不同义项会被写成同一个译名
  147. $hotMeaning = $sources()
  148. ->selectRaw('meaning,count(*) as co')
  149. ->where('word', $word->word)
  150. ->groupBy('meaning')
  151. ->orderBy('co', 'desc')
  152. ->first();
  153. if ($hotMeaning) {
  154. $bestNote = '';
  155. if (count($score) > 0) {
  156. arsort($score);
  157. $bestNote = $allTerm[(int) key($score)]->note;
  158. }
  159. // 同一组若有多行(历史上 NULL / '' / 空白 tag 各建了一行),留最早建的那行,其余删掉
  160. $rows = $existing->pull(self::pairKey($word->word, $word->tag)) ?? collect();
  161. $term = $rows->shift() ?? new DhammaTerm([
  162. 'id' => app('snowflake')->id(),
  163. 'guid' => Str::uuid(),
  164. 'word' => $word->word,
  165. 'word_en' => Tools::getWordEn($word->word),
  166. 'meaning' => '',
  167. 'channal' => $localTerm,
  168. 'language' => $this->argument('lang'),
  169. 'owner' => config('mint.admin.root_uuid'),
  170. 'editor_id' => 0,
  171. 'create_time' => time() * 1000,
  172. ]);
  173. $rows->each->delete();
  174. $term->tag = $word->tag;
  175. $term->meaning = $hotMeaning->meaning;
  176. $term->note = $bestNote;
  177. $term->modify_time = time() * 1000;
  178. $term->save();
  179. }
  180. $done = $index + 1;
  181. if ($done % 100 === 0 || $done === $total) {
  182. $this->info("{$done}/{$total}");
  183. }
  184. }
  185. // 没有来源支撑的行(来源术语已删,或历史重复行):这个 channel 只由本任务维护,一并删除(软删)
  186. $stale = $existing->flatten();
  187. $stale->each->delete();
  188. $this->info("done: {$total} terms, {$stale->count()} stale rows removed");
  189. // 表可能变了:换 ETag,客户端(/v3/glossaries/community)下次请求拿新数据
  190. $glossaries->rotateEtag($localTerm);
  191. return 0;
  192. }
  193. /** tag 的 NULL、''、纯空白都当作「无 tag」(null) */
  194. private static function normalizeTag(?string $tag): ?string
  195. {
  196. $tag = trim((string) $tag);
  197. return $tag === '' ? null : $tag;
  198. }
  199. private static function pairKey(?string $word, ?string $tag): string
  200. {
  201. return ($word ?? '')."\0".($tag ?? '');
  202. }
  203. }