管理软件向云平台同步基础数据的检查步骤

/*
一、 创建到管理软件SQL SERVER的链接数据库

1、 创建链接服务器到管理软件的SQL SERVER
EXEC  sp_addlinkedserver @server='BaseServer', @srvproduct='',@provider='SQLOLEDB',@datasrc='10.10.14.76';
EXEC sp_addlinkedsrvlogin 'BaseServer','false', NULL,'ypt','ypt' ;

-- 删除链接服务器
-- Exec sp_droplinkedsrvlogin BaseServer,NULL;
-- Exec sp_dropserver BaseServer ;

-- 测试一下：
SELECT * FROM [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_dept;
*/

/*
二、创建到云平台的MYSQL的链接数据库
/*
 删除链接服务器
 Exec sp_droplinkedsrvlogin CloudMysql,NULL;
 Exec sp_dropserver CloudMysql ;
*/
EXEC master.dbo.sp_addlinkedserver @server = N'CloudMysql', @srvproduct=N'CloudMysql', @provider=N'MSDASQL', @datasrc=N'MySql_Link';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CloudMysql',@useself=N'False',@locallogin=NULL,@rmtuser=N'root',@rmtpassword='DsideaL147258369';

-- 测试一下：
SELECT * FROM OPENQUERY(CloudMysql,' select ORG_ID,ORG_NAME,PARENT_ID,SORT_ID,CREATE_TIME,BUREAU_ID,DISTRICT_ID,CITY_ID,PROVINCE_ID,LEVEL,B_GROUP,EDU_TYPE,AREA_ID,ORG_TYPE,B_USE,school_type,org_code,
JP,school_level,last_updated_time,old_org_uuid,old_org_pid,old_org_org_id from t_base_organization') AS A ;
*/

/*=========================================================================================================================================*/
-- 管理软件通过名称反查学校ID
SELECT id,org_id,org_name FROM [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_orgtree WHERE org_name ='辽化第二高级中学' AND delete_flag=0

-- 云平台通过名称反查学校ID
SELECT org_id,old_org_uuid FROM OPENQUERY(CloudMysql,'SELECT org_id,ORG_NAME,old_org_uuid from t_base_organization where ORG_NAME=''辽化第二高级中学''')

-- 云平台ID：200125172
-- 管理软件ID：0A7A5AA6-9FB7-446F-9822-CBFE190E4408
-- 管理软件ORG_ID:C24CC62E-76EC-4D53-AB79-5935D1BA1B85
/*=========================================================================================================================================*/
-- 管理软件有哪些员工
SELECT t1.id AS old_user_uuid,t1.login_name,t1.user_realname AS person_name FROM [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].[T_BASE_USER] AS t1
INNER JOIN [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].[T_BASE_USER_IDENTITY] AS t2 ON t1.id=t2.user_id
WHERE t1.org_id='C24CC62E-76EC-4D53-AB79-5935D1BA1B85' AND t2.identity_id ='8a8a87a13c42898b013c428a7c860000' AND t1.delete_flag=0 AND t1.use_flag=1

-- 云平台有哪些员工
SELECT old_user_uuid,login_name,person_name FROM OPENQUERY(CloudMysql,'select t1.person_name,t1.old_user_uuid,t2.login_name from t_base_person as t1 inner join t_sys_loginperson as t2 on t1.person_id=t2.person_id
where t1.BUREAU_ID=200125172 and t1.b_use=1')

/*=========================================================================================================================================*/
-- 管理软件有哪些部门(这里面是一级部门还可以，多级部门就是一个坑)
SELECT id,org_id,org_name FROM [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_orgtree WHERE parent_id='0A7A5AA6-9FB7-446F-9822-CBFE190E4408'
AND delete_flag=0 AND use_flag=1

-- 云平台有哪些部门
SELECT old_org_uuid,old_org_org_id,ORG_NAME FROM OPENQUERY(CloudMysql,'select ORG_ID,ORG_NAME,old_org_uuid,old_org_org_id from t_base_organization where BUREAU_ID=200125172 and b_use=1 and PARENT_ID=200125172')
/*=========================================================================================================================================*/
-- 管理软件有哪些班级
SELECT id FROM [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_class WHERE school_id='C24CC62E-76EC-4D53-AB79-5935D1BA1B85'
AND delete_flag=0  ORDER BY id;

-- 云平台有哪些班级
SELECT old_id FROM OPENQUERY(CloudMysql,'select class_id,class_name,old_id from t_base_class where BUREAU_ID=200125172 and b_use=1') ORDER BY old_id;
/*=========================================================================================================================================*/
-- 管理软件有哪些学生
SELECT t1.id,t1.login_name,t1.user_realname FROM [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_user AS t1 INNER JOIN
[BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].[T_BASE_USER_IDENTITY] AS t2 ON t1.id=t2.user_id
 WHERE t1.org_id='C24CC62E-76EC-4D53-AB79-5935D1BA1B85' AND t2.identity_id ='8a8a87a13c42898b013c428aa6c40001'

-- 云平台有哪些学生
SELECT * FROM OPENQUERY(CloudMysql,'SELECT student_id,student_name,class_id from t_base_student where bureau_id=200125172 and b_use=1')
/*=========================================================================================================================================*/