yunkai-export/file/流程.txt

#必要步骤
（修改本地化：dbName和账号、试卷前缀）
1、将云开数据库 导入 本地

2、建要处理的科目临时表
CREATE TABLE `temp_subject` (
  `ec_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `yun_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
学校提供待导入的科目，将Excel填入该表

3、检查是否有包含公式的题
select q.id,q.question_type,q.topic,q.answer,an.answer_text
					from wq_question_bank_subject t left join wq_subject sub on t.subject_id=sub.id
					left join wq_question_bank b on t.question_bank_id=b.id
					left join wq_question_question_bank f on t.question_bank_id=f.question_bank_id
					left join wq_question q on f.question_id=q.id 
					left join wq_question_answer_item an on q.id=an.question_id
where 1=1
and sub.subject_code in (select yun_code from temp_subject)
and b.rent_id=811 and b.is_deleted=0 and q.is_deleted=0 and q.rent_id=811
and (q.topic like '%$$%' or q.answer like '%$$%' or an.answer_text like '%$$%');
若存在 公式题 则继续执行下面继续处理步骤；若无，则忽略公式处理。
{
    处理公式的临时表
    CREATE TABLE `temp_gs` (
      `id` bigint NOT NULL,
      `content1` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
      `content2` text COLLATE utf8mb4_bin,
      `content3` text COLLATE utf8mb4_bin,
      `content4` text COLLATE utf8mb4_bin,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

    查询 存在公式关键字的字段，并将结果集导出至Excel，使用公式处理工具“formula-analysis 1.0.1.exe”
    将Excel的内容执行处理后，复制至临时表“temp_gs”，然后执行update语句。
    查询
    select id,topic from wq_question where topic like '%$$%';
    修改
    update wq_question INNER JOIN temp_gs on wq_question.id=temp_gs.id
    set wq_question.topic= CONCAT_WS('',temp_gs.content1,temp_gs.content2,temp_gs.content3,temp_gs.content4);
    执行完update后，清空“temp_gs”。

    同上
    查询
    select id,answer from wq_question where answer like '%$$%';
    修改
    update wq_question INNER JOIN temp_gs on wq_question.id=temp_gs.id
    set wq_question.answer= CONCAT_WS('',temp_gs.content1,temp_gs.content2,temp_gs.content3,temp_gs.content4);

    同上
    查询
    select id,answer_text from wq_question_answer_item where answer_text like '%$$%';
     修改
    update wq_question_answer_item INNER JOIN temp_gs on wq_question_answer_item.id=temp_gs.id
    set wq_question_answer_item.answer_text= CONCAT_WS('',temp_gs.content1,temp_gs.content2,temp_gs.content3,temp_gs.content4);
}

4、云开数据库中 本地化的表“wq_knowledge_system”需要新加的字段root_id，
再在yunkai-export代码“FillRootId.java”中更新属性树


5、检查是否存在 非标准格式的 套题，若存在，直接当作 单独的题 处理，执行以下分析脚本。
insert into wq_question_question_bank (question_bank_id,question_id)
select f.question_bank_id,t.id 
from wq_question t left join wq_question_question_bank f on t.question_id=f.question_id
where t.question_id!=-1 and t.rent_id=811 
and f.question_bank_id is not null;

6、执行yunkai-export代码“ExportProperty.java”
学校存放的文件目录 提前置入 相关Excel文件 ，然后才可执行！！！


7、执行yunkai-export代码“ExportPaperByCourseCode.java”
MyConsumer.java内的paperDir需要修改正确 ，然后才可执行！！！

8、执行yunkai-import代码“ExportPaperByCourseCode.java”
{
a、云平台管理端导入课程
b、导入课程属性，执行ImportPropByCourse（修改sourceDir、host、rootOrgId、key、token）
c、导入试卷，执行ImportPaperByCourse（修改sourceDir、host、rootOrgId、key、token）
}

9、执行去重 examcloud-oe-tool

End
===============================================================================


辅助分析脚本：
#查询wq_question表中question_id!=-1的数据，将有效的question补入wq_question_question_bank。将套题作为单独的题处理
#insert into wq_question_question_bank (question_bank_id,question_id)
#select question_bank_id,question_id from temp_childer;



选项就一个的查询
var total=0;
db.getCollection('question').find({
"orgId":"17351",
"questionType":{"$in":["SINGLE_ANSWER_QUESTION","MULTIPLE_ANSWER_QUESTION"]},
	"$or":[
		{"quesOptions":{"$eq":null}},
		{"quesOptions":{"$size":0}},
		{"quesOptions":{"$size":1}}
	]
}).forEach(function(ques){
	total++;
	//print(ques._id+" "+ques.questionType+" "+ques.course.code);

	db.getCollection('paperDetailUnit').find({
	"question.$id":ques._id,
	"paperType":"IMPORT"
	}).forEach(function(unit){
            print(unit._id+","+ques._id+","+ques.questionType+","+ques.course.code);
	});
});
print("total:" + total);


选项就一个的查询,数量
db.getCollection('question').count({
"orgId":"17351",
"questionType":{"$in":["SINGLE_ANSWER_QUESTION","MULTIPLE_ANSWER_QUESTION"]},

//"quesOptions":{"$ne":null,"$not":{"$size":0}}
//"quesOptions":{"$ne":null,"$size":1}
//"quesOptions":{"$eq":[]}

	"$or":[
		{"quesOptions":{"$eq":null}},
		{"quesOptions":{"$size":0}},
		{"quesOptions":{"$size":1}}
	]
});




select k.* from wq_knowledge_system k where k.root_id in(select DISTINCT ss.root_id
from wq_question_bank_subject t
left join wq_question_bank b on t.question_bank_id=b.id
left join wq_question_question_bank f on t.question_bank_id=f.question_bank_id
left join wq_question q on f.question_id=q.id
left join wq_question_knowledge_system s on f.question_id=s.question_id
left join wq_knowledge_system ss on s.knowledge_system_id=ss.id
where t.subject_id=622 and b.rent_id=811 and b.is_deleted=0
and q.is_deleted=0 and q.rent_id=811)


select DISTINCT f.id,f.subject_name,f.subject_code
from temp_subject tem
INNER JOIN wq_subject f on tem.subject_code=f.subject_code
INNER JOIN wq_question_bank_subject t on f.id=t.subject_id
where f.is_deleted=0 and f.rent_id=811
order by f.id

update wq_question INNER JOIN temp_old_data on wq_question.id=temp_old_data.id
set wq_question.topic= temp_old_data.content;

update wq_question_answer_item INNER JOIN temp_old_data on wq_question_answer_item.id=temp_old_data.id
set wq_question_answer_item.answer_text= temp_old_data.content;