辽阳事情处理

/*
一、 创建到管理软件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 ;
*/

/*=========================================================================================================================================*/
-- 管理软件有多少个学校
SELECT id,RTRIM(LTRIM(school_name)) AS school_name FROM [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_school WHERE  delete_flag=0
except
SELECT 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  org_type=2 and b_use=1');

-- 云平台有多少个学校
SELECT org_id,org_name,old_org_org_id FROM OPENQUERY(CloudMysql,'SELECT org_id,org_name,old_org_uuid,old_org_org_id from t_base_organization where  org_type=2 and b_use=1');
/*=========================================================================================================================================*/
-- 管理软件有多少个除了学校外的单位
SELECT org_id,RTRIM(LTRIM(org_name )) AS  org_name FROM [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_orgtree WHERE org_type=0
EXCEPT
SELECT old_org_org_id,org_name FROM OPENQUERY(CloudMysql,'select * from t_base_organization where org_id=bureau_id and org_type=1 and old_org_org_id is not null and b_use=1')

SELECT * FROM OPENQUERY(CloudMysql,'select * from t_base_organization where org_id=bureau_id and org_type=1 and old_org_org_id is not null and b_use=1')
WHERE old_org_org_id='44B5EC42-ADBD-407B-88A6-A61F945F6A76'
/*=========================================================================================================================================*/
-- 云平台有哪些员工(刘全峰理解有误，需要按差集进行标识)
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 and t2.identity_id=5 and t2.b_use=1')
EXCEPT
-- 管理软件有哪些员工
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
/*=========================================================================================================================================*/
-- 管理软件有哪些部门
with CTE
as
(
select *,1 AS [Level] from [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_orgtree  where id = '0A7A5AA6-9FB7-446F-9822-CBFE190E4408'
union all
select a.*, [Level] + 1 AS [Level] from [BaseServer].[system_manager_ssh_liaoyang_20171127].[dbo].t_base_orgtree  a
inner join CTE b
on a.parent_id = b.id
)
select id,org_id,org_name from CTE WHERE id<>'0A7A5AA6-9FB7-446F-9822-CBFE190E4408' AND org_type=2 AND delete_flag=0 AND use_flag=1

EXCEPT
-- 云平台有哪些部门
SELECT old_org_uuid AS id,old_org_org_id AS 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 in (1,2)') 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 in (1,2)')
/*=========================================================================================================================================*/