UpgradeCommunityTerm.php 9.0 KB

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