-- 资源表
select t1.cmtsresourceid, -- 资源ID GUID
t1.createtime, -- 创建时间
t1.fileext, -- 扩展名
t1.filename, -- 文件名
t1.filesize, -- 文件大小，整数
t1.ismultifile, -- 是不是复合文件
t1.title, -- 标题
t1.categoryid, -- 应用类型
t1.fileext, -- 扩展名
t1.new_mediatype_id, -- 媒体类型
t1.new_mediatype_name,-- 媒体类型
t1.new_stage_id, -- 目标库的学段
t1.new_subject_id, -- 目的库的学科
t1.versionid, -- 版本
t1.nodeid, -- 结点
t1.new_user_id , -- 用户ID
t1.new_user_name, -- 用户姓名
t1.new_path -- 新资源路径
from t_cmts_cmtsresource t1 where isdelete=0 limit 100;
-- ----------------------------------------------------------------
use zyk_ts;
-- ======================================================================================================
-- 媒体类型
-- 修正一下实现完整对应
update t_stype_mediatype set mediatype_name='图片' where mediatype_id='002';
update t_stype_mediatype set mediatype_name='其他' where mediatype_id='007';
alter table t_cmts_cmtsresource add column new_mediatype_id varchar(36);
alter table t_cmts_cmtsresource add column new_mediatype_name varchar(36);
-- 删除270个没有检查到的
delete from t_cmts_cmtsresource where fileext not in (select extension_name from dsideal_db_tskp.t_resource_extension);
-- 添加新表中对应的媒体类型ID
update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_resource_extension t2 on t1.fileext=t2.extension_name set t1.new_mediatype_id=t2.extension_id;
-- 添加新表中对应的媒体类型名称
update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_resource_extension t2 on t1.fileext=t2.extension_name set t1.new_mediatype_name=t2.mediatype_name;
-- ======================================================================================================
-- 学段名称
alter table t_cmts_cmtsresource add column stage_name varchar(30);
update t_cmts_cmtsresource t1 inner join t_stype_stage t2 on t1.stageid=t2.stage_id set t1.stage_name=t2.stage_name ;
-- 科目名称
alter table t_cmts_cmtsresource add column subject_name varchar(30);
update t_cmts_cmtsresource t1 inner join t_stype_subject t2 on t1.subjectid=t2.subject_id set t1.subject_name=t2.subject_name ;
-- 在新的表中的学段ID
alter table t_cmts_cmtsresource add column new_stage_id varchar(30);
-- 目标的学段ID
update t_cmts_cmtsresource t1 set t1.new_stage_id=4 where stageid='0001';
update t_cmts_cmtsresource t1 set t1.new_stage_id=5 where stageid='0002';
update t_cmts_cmtsresource t1 set t1.new_stage_id=6 where stageid='0003';
-- 在新的表中的科目ID
alter table t_cmts_cmtsresource add column new_subject_id varchar(30);
-- 目标的科目ID
update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_dm_subject t2 on t1.subject_name=t2.subject_name and t1.new_stage_id=t2.stage_id
set t1.new_subject_id=t2.subject_id;
-- ======================================================================================================
-- 新的版本ID
alter table t_cmts_cmtsresource add column new_scheme_id_char varchar(36);
alter table t_cmts_cmtsresource add column new_scheme_id_int int;
alter table t_cmts_cmtsresource add column new_structure_code varchar(36);
alter table t_cmts_cmtsresource add column new_structure_id_char varchar(36);
alter table t_cmts_cmtsresource add column new_structure_id int;
-- 目标的版本ID
-- update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_resource_scheme t2 on t1.versionid=t2.old_version_id set t1.new_scheme_id=t2.scheme_id;
-- 新的版本名称
select versionid from t_cmts_cmtsresource where versionid not in (select old_version_id from dsideal_db_tskp.t_resource_scheme);
select *,node_id,node_name,node_code from t_stype_node;
select old_structure_id from dsideal_db_tskp.t_resource_structure where OLD_STRUCTURE_ID is not null;
select * from t_stype_node where node_id not in (select node_id from dsideal_db_tskp.t_resource_structure_new where node_id is not null)
-- ======================================================================================================
-- 新的完整的路径
alter table t_cmts_cmtsresource add column new_path varchar(128);
update t_cmts_cmtsresource set mediatypeid='002';
-- 旧资源
update t_cmts_cmtsresource t1 set t1.new_path=CONCAT('D:/dsideal_cmts/Tomcat_7.0.34/webapps/cmts/uploadfiles/resources',t1.filepath) where t1.resfrom is null;
-- 新资源
update t_cmts_cmtsresource t1 inner join t_cmts_disk t2 on t1.stageid=t2.stage_id and t1.subjectid=t2.subject_id and t1.mediatypeid=t2.mediatype_id
set t1.new_path=concat(t2.path_content,t1.filepath) where t1.resfrom is not null;
-- ======================================================================================================
-- 用户名称
alter table t_cmts_cmtsresource add column new_user_id varchar(128);
alter table t_cmts_cmtsresource add column new_user_name varchar(128);
update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_sys_loginperson t2 on t1.userid=t2.old_user_uuid set t1.new_user_id=t2.person_id ;
update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_sys_loginperson t2 on t1.userid=t2.old_user_uuid set t1.new_user_name=t2.person_name ;
-- ======================================================================================================
-- 应用类型: app_type_id:2 默认是素材
-- res_type :2
-- 媒体类型:resource_type_name,resource_type
-- meterial_type:和媒体类型一样
-- bk_type,bk_type_name
-- release_status:1
-- parent_structure_name:-1
-- reource_size_int :-1
-- 备课类型: 001 课件
-- 002 教案 1
-- 003 学案 2
-- 006 微课堂 3
-- 007 视频课堂 4
-- 007 视频课堂 5
alter table t_cmts_cmtsresource add column new_thumb_status varchar(128);
alter table t_cmts_cmtsresource add column new_preview_status varchar(128);
alter table t_cmts_cmtsresource add column new_thumb_id varchar(128);
update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_resource_extension t2 on t1.fileext=t2.extension_name set
t1.new_thumb_status=t2.thumb_status , t1.new_preview_status=t2.preview_status , t1.new_thumb_id=t2.thumb_id ;
-- ======================================================================================================
-- 标识为来自开平
alter table dsideal_db_tskp.t_resource_base add column from_Kp int;
alter table dsideal_db_tskp.t_resource_info add column from_Kp int;
-- 记录原始路径
alter table dsideal_db_tskp.t_resource_base add column old_file_path varchar(128);
-- ======================================================================================================
-- 要求前端人员小蒙：将备课的类型修改为：
-- 002 教案 1
-- 003 学案 2
-- 006 微课堂 3
-- 007 视频课堂 4
-- 007 视频课堂 5
-- ======================================================================================================
-- 宽城的版权信息等需要修改。
-- ======================================================================================================
select resource_id_int,resource_id_char,resource_title,resource_size,resource_size_int,resource_type,
resource_type_name,resource_category,resource_page,create_time,person_name,create_person,b_use,update_logo,ts,
source_id,extension,file_id,file_md5,file_sha1,thumb_id,thumb_md5,thumb_sha1,pinyin,product_id,scheme_id_char,
scheme_id,structure_code,structure_id_char,structure_id,material_type,is_single,is_old,preview_status,down_count,
check_status,check_message,thumb_status,old_file_path,is_multifile,chuli,is_3_2,parent_name,width,height,
for_urlencoder_url,for_iso_url,release_status,res_type,bk_type,bk_type_name,m3u8_status,m3u8_url,stage_id,subject_id ,from_Kp
from dsideal_db_tskp.t_resource_base limit 1;
select id,resource_id_int,resource_id_char,resource_title,resource_type_name,
resource_format,resource_page,resource_size,resource_size_int,create_time,
down_count,file_id,thumb_id,resource_type,structure_id,person_id,person_name,identity_id,
group_id,preview_status,scheme_id_int,ts,thumb_status,update_ts,for_urlencoder_url,for_iso_url,
width,height,parent_structure_name,release_status,res_type,bk_type,bk_type_name,material_type,m3u8_status,
m3u8_url,app_type_id,stage_id,subject_id,from_Kp
from dsideal_db_tskp.t_resource_info;
-- ======================================================================================================
