def area_user_handle(v_areaname, v_area, levelnum):
dbconn = MysqlOper()
userhandlenum = dbconn.dbonequery(
'select count(0) from idm_user_handle where userareaid = %s', v_area)
if userhandlenum[0] == 0:
user_basic = dbconn.dbmanyquery(
'select userid, username, userdeptno, userorg, userupdate, '
'usercreate, useremptype, userstatus, userareaid '
'from idm_user_data where userareaid = %s', v_area)
else:
user_incdate = dbconn.dbonequery(
'select max(userupdate) from idm_user_handle '
'where userareaid = %s', v_area)
user_basic = dbconn.dbmanyquery(
'select userid, username, userdeptno, userorg, userupdate, '
'usercreate, useremptype, userstatus, userareaid from idm_user_data '
'where userupdate > %s and userareaid = %s', user_incdate[0],
v_area)
if len(user_basic) > 0:
l_userinfo = []
for userinfo in user_basic:
l_userorgou = []
userid = userinfo[0]
username = userinfo[1]
userdeptno = userinfo[2]
userupdate = userinfo[4]
usercreate = userinfo[5]
useremptype = userinfo[6]
userstatus = userinfo[7]
userareaid = userinfo[8]
l_userorg = userinfo[3].split('_')
v_userorglevel = len(l_userorg)
if v_userorglevel >= levelnum:
userorglevel = levelnum
else:
userorglevel = v_userorglevel
for index in range(userorglevel):
if l_userorg[index] == '融创中国':
l_userorg[index] = '融创集团'
l_userorgou.append('OU=' + l_userorg[index])
else:
l_userorgou.append('OU=' + l_userorg[index])
l_userorgou.reverse()
userorg = (','.join(l_userorgou) + ',DC=SUNAC,DC=local')
# 判断条件不够，可以加上 用户如果没有变化 就不需要
if userstatus == 'Active' and useremptype == 'Full-Time':
if userhandlenum[0] == 0:
userhandled = 11
else:
res_userhandle = dbconn.dbonequery(
'select userid, userorg, userhandled from idm_user_handle '
'where userid = %s', userid)
if res_userhandle is not None and res_userhandle[
1] == userorg and res_userhandle[2] in (1, 2):
userhandled = 7
else:
userhandled = 11
else:
userhandled = 0
t_userobj = (userid, username, userdeptno, userorg, userupdate,
usercreate, useremptype, userstatus, userareaid,
userhandled)
l_userinfo.append(t_userobj)
res_insert = dbconn.dbmanyinsert(
'replace into idm_user_handle(userid,username,userdeptno,userorg,'
'userupdate,usercreate,useremptype,userstatus,userareaid,userhandled) '
'values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', l_userinfo)
if res_insert:
infolog_user('IDM信息--区域"%s" 已处理用户原始数据信息：%s条' %
(v_areaname, len(user_basic)))
else:
infolog_user('IDM信息--区域"%s" 没有新的用户需要做处理' % v_areaname)
dbconn.dbclose()
def area_org_exec(v_areaname, v_area):
# 绑定Mysql数据库
dbconn = MysqlOper()
adconn = AdOper()
t_maxorglevel = dbconn.dbonequery('select max(organlevel) from idm_org_handle where organhandled > 9 '
'and areaid=%s', v_area)
t_minorglevel = dbconn.dbonequery('select min(organlevel) from idm_org_handle where organhandled > 9 '
'and areaid=%s', v_area)
maxorglevel = t_maxorglevel[0]
minorglevel = t_minorglevel[0]
try:
if maxorglevel is not None:
while minorglevel <= maxorglevel:
t_resorg = dbconn.dbmanyquery(
'select organnumber, organname, organparentno, organdep, pre_orgdep, organhandled '
'from idm_org_handle where organlevel = %s and organhandled > 9 and areaid = %s',
minorglevel, v_area)
if len(t_resorg) > 0:
for orgobject in t_resorg:
adfilter = '(distinguishedName=' + orgobject[3] + ')'
res_adquery = adconn.adquery('ou=融创集团,dc=SUNAC,dc=local', adfilter)
if res_adquery:
dbconn.dbonemod('update idm_org_handle set organhandled = 2 '
'where organnumber = %s ', (orgobject[0]))
infolog_org('AD信息--组织已存在：%s' % orgobject[3])
else:
if orgobject[5] in [10, 11]:
res_ouadd = adconn.adadd(orgobject[3], 'organizationalUnit')
if res_ouadd:
dbconn.dbonemod('update idm_org_handle set organhandled = 1 '
'where organnumber = %s ', (orgobject[0]))
infolog_org('AD信息--创建成功的OU：%s' % (orgobject[3]))
else:
errlog_org('AD信息--创建失败的OU：%s' % (orgobject[3]))
dbconn.dbonemod('update idm_org_handle set organhandled = 10 '
'where organnumber = %s ', (orgobject[0]))
elif orgobject[5] == 12:
res_ourename = adconn.adrename(orgobject[4], 'OU=' + orgobject[1])
if res_ourename:
dbconn.dbonemod('update idm_org_handle set organhandled = 1 '
'where organnumber = %s ', (orgobject[0]))
infolog_org('AD信息--重命名成功的OU：%s' % (orgobject[3]))
else:
errlog_org('AD信息--重命名失败的OU：%s' % (orgobject[3]))
dbconn.dbonemod('update idm_org_handle set organhandled = 10 '
'where organnumber = %s ', (orgobject[0]))
elif orgobject[5] == 13:
orgparent = dbconn.dbonequery('select organdep from idm_org_handle '
'where organnumber = %s ', (orgobject[2]))
res_oumove = adconn.admove(orgobject[4], 'OU=' + orgobject[1], orgparent[0])
if res_oumove:
dbconn.dbonemod('update idm_org_handle set organhandled = 1 '
'where organnumber = %s ', (orgobject[0]))
infolog_org('AD信息--移动成功的OU：%s' % (orgobject[3]))
else:
errlog_org('AD信息--移动失败的OU：%s' % (orgobject[3]))
dbconn.dbonemod('update idm_org_handle set organhandled = 10 '
'where organnumber = %s ', (orgobject[0]))
else:
infolog_org('AD信息--无预定义动作')
dbconn.dbonemod('update dic_idm_org set orghandled = 3 '
'where organnumber = %s ', (orgobject[0]))
else:
infolog_org('AD信息--区域"%s" 没有需要处理的OU' % v_areaname)
minorglevel += 1
else:
infolog_org('AD信息--区域"%s" 没有新增需要处理的OU' % v_areaname)
except Exception as e:
errlog_org(repr(e))
finally:
adconn.adclose()
dbconn.dbclose()
def area_org_handle(v_areaname, v_area, levelnum):
dbconn = MysqlOper()
orghandlenum = dbconn.dbonequery('select count(0) from idm_org_handle where areaid = %s', v_area)
if orghandlenum[0] == 0:
org_basic = dbconn.dbmanyquery('select organnumber,organname,organparentno,organupdate,organcreate,'
'organdep,organstatus,areaid from idm_org_data where areaid = %s', v_area)
else:
org_incdate = dbconn.dbonequery('select max(organupdate) from idm_org_handle '
'where areaid = %s', v_area)
org_basic = dbconn.dbmanyquery('select organnumber,organname,organparentno,organupdate,organcreate,'
'organdep,organstatus,areaid from idm_org_data '
'where organupdate > %s and areaid = %s', org_incdate[0], v_area)
if len(org_basic) > 0:
l_orginfo = []
for objbasicorg in org_basic:
l_orglongnamestr = []
organnumber = objbasicorg[0]
organname = objbasicorg[1]
organparentno = objbasicorg[2]
organupdate = objbasicorg[3]
organcreate = objbasicorg[4]
organstatus = objbasicorg[6]
areaid = objbasicorg[7]
organdisplayname = objbasicorg[5]
if organdisplayname is not None:
l_orglongname = organdisplayname.split('_')
organlevel = len(l_orglongname)
for index in range(organlevel):
if l_orglongname[index] == '融创中国':
l_orglongname[index] = '融创集团'
l_orglongnamestr.append('OU=' + l_orglongname[index])
else:
l_orglongnamestr.append('OU=' + l_orglongname[index])
l_orglongnamestr.reverse()
organdep = (','.join(l_orglongnamestr) + ',DC=SUNAC,DC=local')
else:
organdep = 'no idm organization'
organlevel = 0
if organdisplayname is not None and organdisplayname.startswith('融创中国') \
and organstatus == 'Active':
obj_idmorg = dbconn.dbonequery(
'select organnumber, organname, organparentno, organdep, organhandled from idm_org_handle '
'where organnumber = %s ', organnumber)
if organlevel > levelnum:
pre_orgparentno = "not required pre org num"
pre_orgdep = "not required pre org"
organhandled = 6
elif obj_idmorg is None:
pre_orgparentno = "no pre Organization Num"
pre_orgdep = "no pre Organization"
organhandled = 11
else:
pre_orgparentno = obj_idmorg[2]
pre_orgdep = obj_idmorg[3]
if obj_idmorg[4] == 11:
organhandled = 11
elif organname != obj_idmorg[1] and organparentno == pre_orgparentno:
organhandled = 12
elif organname == obj_idmorg[1] and organparentno != pre_orgparentno:
organhandled = 13
elif organname == obj_idmorg[1] and organparentno == pre_orgparentno \
and organdep != pre_orgdep:
organhandled = 5
else:
organhandled = 3
else:
organhandled = 0
pre_orgparentno = "Error pre Organization Num"
pre_orgdep = "Error pre Organization"
orgobject = (organnumber, organname, organparentno, organupdate, organcreate,
organdep, organlevel, organstatus, pre_orgparentno, pre_orgdep, organhandled, areaid)
l_orginfo.append(orgobject)
res_insert = dbconn.dbmanyinsert('replace into idm_org_handle(organnumber,organname,organparentno,organupdate,'
'organcreate,organdep,organlevel,organstatus,pre_orgparentno,pre_orgdep,'
'organhandled,areaid) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', l_orginfo)
if res_insert:
infolog_org('IDM信息--区域"%s" 已处理组织原始数据信息：%s条' % (v_areaname, len(org_basic)))
else:
infolog_org('IDM信息--区域"%s" 没有新的组织需要做处理' % v_areaname)
dbconn.dbclose()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from libs.connect import MysqlOper, excelidm
EXCELORGFILE = \
r"C:\Users\zxcvb\Documents\CloudStation\My Python\SunacProject\AD and IDM\ADUser Adjust\files\tmp001-org205.xlsx"
EXCELUSRFILE = \
r"C:\Users\zxcvb\Documents\CloudStation\My Python\SunacProject\AD and IDM\ADUser Adjust\files\tmp001-user205.xlsx"
# 插入IDM组织原始数据到数据库
dbconn = MysqlOper()
# 判断【idm_org_init】是否为空
resorgunm = dbconn.dbonequery('select count(0) from idm_org_data')
if resorgunm[0] == 0:
orgvalue = excelidm(EXCELORGFILE)
orgsql = 'insert into idm_org_data(organnumber,organname,organparentno,organupdate,' \
'organcreate,organdep,organstatus) values (%s,%s,%s,%s,%s,%s,%s)'
orgres_insert = dbconn.dbmanyinsert(orgsql, orgvalue)
# 指定组织增量查询的起始时间
if orgres_insert:
maxorgdate = dbconn.dbonequery(
'select max(organupdate) from idm_org_data')
dbconn.dbonemod('replace into idm_inc_flag values ("org", %s)',
maxorgdate[0])
# 为IDM组织添加区域ID
dbconn.dbonemod('UPDATE idm_org_data SET areaid = CASE '
'WHEN organdep like "融创中国_集团本部%%" THEN "000101" '
'WHEN organdep like "融创中国_华北区域%%" THEN "000102" '
'WHEN organdep like "融创中国_北京区域%%" THEN "000104" '
'WHEN organdep like "融创中国_西南区域%%" THEN "000107" '
'WHEN organdep like "融创中国_上海区域%%" THEN "000120" '
def area_user_inc():
CURRENT_DATE = datetime.today()
timestr = datetime.today().strftime('%Y%m%d%H%M%S%f')[:-3]
idmid = 'AD_SUNAC_300_' + timestr
WSDL_URL = "http://esb.sunac.com.cn:8002/WP_SUNAC/APP_PUBLIC_SERVICES" \
"/Proxy_Services/TA_IDM/PUBLIC_SUNAC_300_queryIdmUserData_PS?wsdl"
# WSDL_URL = "http://esbqas.sunac.com.cn:8001/WP_SUNAC/APP_PUBLIC_SERVICES" \
# "/Proxy_Services/TA_IDM/PUBLIC_SUNAC_300_queryIdmUserData_PS?wsdl"
# SYSTEMID = 'Sunac_IDMUser'
SYSTEMID = 'Sunac_ADQZ_USR'
dbconn = MysqlOper()
# IDM一天的增量查询，确定开始和结束时间。
inc_begindate = dbconn.dbonequery(
'select inc_datetime from idm_inc_flag where inc_type="user" ')
begintime = datetime.strftime(inc_begindate[0], '%Y-%m-%d %H:%M:%S.%f')
inc_enddate = inc_begindate[0] + timedelta(days=2)
endtime = datetime.strftime(inc_enddate, '%Y-%m-%d %H:%M:%S.%f')
try:
NUM = 1
NUM_LOOP = 'YES'
# 查询到的增量 循环支持的最大页，每页100条数据，如果查询当前页小于100条 停止页面循环，如果等于100条，继续查询下一页
while NUM <= 999 and NUM_LOOP == 'YES':
residmuser = idmquery(WSDL_URL, begintime, endtime, NUM, SYSTEMID,
idmid, 'idmuser')
if residmuser['body']['HEADER']['RESULT'] == '0':
# XML中不允许有&,否则会解析失败
user_list_old = residmuser['body']['LIST']
user_list = user_list_old.replace("&", "及")
xml_tree = Et.fromstring(user_list)
if len(xml_tree) > 0:
l_userinfo = []
for userinfo in xml_tree.iter('USER'):
userid = userinfo.find('UserLogin').text
username = userinfo.find('Username').text
userdeptno = userinfo.find('UserDeptNo').text
olduserorg = userinfo.find('UserOrgDisplayName').text
userupdate = userinfo.find('UserUpdate').text
usercreate = userinfo.find('UserCreate').text
useremptype = userinfo.find('UserEmpType').text
userstatus = userinfo.find('UserStatus').text
t_userobj = (userid, username, userdeptno, olduserorg,
userupdate, usercreate, useremptype,
userstatus)
l_userinfo.append(t_userobj)
res_insert = dbconn.dbmanyinsert(
'replace into idm_user_data(userid,username,userdeptno,olduserorg,'
'userupdate, usercreate,useremptype,userstatus) '
'values (%s,%s,%s,%s,%s,%s,%s,%s)', l_userinfo)
if res_insert:
infolog_user(
'IDM信息--查询时间：%s 至 %s，结果集当前分页：%s，新增用户数：%s' %
(begintime, endtime, NUM, len(xml_tree)))
if len(xml_tree) == 100:
NUM += 1
else:
infolog_user('IDM信息--没有更多的翻页数据')
NUM_LOOP = 'NO'
# 为增量IDM用户数据添加区域ID
dbconn.dbonemod(
'update idm_user_data t1 left join idm_org_data t2 '
'on t1.userdeptno = t2.organnumber '
'set t1.userorg = t2.organdep,t1.userareaid = t2.areaid '
'where userupdate >= %s', begintime)
# 判断增量的日期，插入增量标志作为下次增量查询的开始时间
incdate(inc_enddate, CURRENT_DATE, 'user')
else:
errlog_user('IDM信息--组织插入失败')
else:
infolog_user('IDM信息--查询时间：%s 至 %s，没有查询到增量用户' %
(begintime, endtime))
NUM_LOOP = 'NO'
# 判断增量的日期，插入增量标志作为下次增量查询的开始时间
incdate(inc_enddate, CURRENT_DATE, 'user')
else:
errlog_user('IDM信息--用户查询接口出现错误')
NUM_LOOP = 'NO'
except Exception as e:
errlog_user(e)
finally:
# 关闭数据库连接
dbconn.dbclose()
def area_comp_exec(v_areaname, v_area):
adconn = AdOper()
dbconn = MysqlOper()
t_areainfo = dbconn.dbmanyquery(
'select areadn from sum_inf_area '
'where status = 1 and parentid =%s order by parentid', v_area)
# 创建区域定义的计算机OU
for obj_area in t_areainfo:
adfilter = '(distinguishedName=' + obj_area[0] + ')'
querycompou = adconn.adquery('OU=融创集团,DC=SUNAC,DC=local', adfilter)
if querycompou:
pass
else:
resadou = adconn.adadd(obj_area[0], 'organizationalUnit')
if resadou:
infolog_comp(f'AD信息--区域计算机OU创建成功，{obj_area[0]}')
# 查询computers OU下的以当前区域代码为前缀的计算机
adcompprefix = dbconn.dbonequery(
'select compprefix, areaname from sum_inf_area where areaid = %s',
v_area)
adconn.adattrquery('CN=Computers,DC=SUNAC,DC=local',
f"(&(objectClass=computer)(name={adcompprefix[0]}*))",
'name')
t_adcomp = dbconn.dbmanyquery(
'select compprefix, areadn, areacode, areaname from sum_inf_area '
'where parentid = %s and status = 1 order by compprefix desc', v_area)
resadconn = adconn.adconn.response
if len(resadconn) > 0:
# 循环当前区域的主机，归类到对应的OU下
for_compmove(v_areaname, resadconn, t_adcomp)
else:
pass
# 将单个区域下computer OU下的计算机账号归类
adconn.adattrquery(
f"OU=computers,OU={adcompprefix[1]},OU=融创集团,DC=SUNAC,DC=local",
f"(&(objectClass=computer)(name={adcompprefix[0]}*))", 'name')
t_adcomp1 = dbconn.dbmanyquery(
'select compprefix, areadn, areacode, areaname from sum_inf_area '
'where parentid = %s and status = 1 order by compprefix desc', v_area)
resadconn1 = adconn.adconn.response
if len(resadconn1) > 0:
for_compmove(v_areaname, resadconn1, t_adcomp1)
else:
pass
# 查询单个区域下的计算机OU组是否有变更计算机账号，并做相应归类
t_areaou = dbconn.dbmanyquery(
'select areacode,compprefix,areadn from sum_inf_area '
'where parentid = %s and status = 1', v_area)
p_areaou = dbconn.dbonequery(
'select areacode from sum_inf_area where areaid = %s', v_area)
t_adcomp2 = dbconn.dbmanyquery(
'select compprefix, areadn, areacode, areaname from sum_inf_area '
'where status = 1 and parentid<>0 order by compprefix desc')
for areaou in t_areaou:
adconn.adattrquery(areaou[2],
f"(&(objectClass=computer)(!(name={areaou[1]}*)))",
'name')
resadconn2 = adconn.adconn.response
if len(resadconn2) > 0:
for_compmove(v_areaname, resadconn2, t_adcomp2)
else:
pass
adconn.adclose()
dbconn.dbclose()
def area_org_inc():
CURRENT_DATE = datetime.today()
timestr = datetime.today().strftime('%Y%m%d%H%M%S%f')[:-3]
idmid = 'AD_SUNAC_301_' + timestr
WSDL_URL = "http://esb.sunac.com.cn:8002/WP_SUNAC/APP_PUBLIC_SERVICES" \
"/Proxy_Services/TA_IDM/PUBLIC_SUNAC_301_queryIdmOrgData_PS?wsdl"
# WSDL_URL = "http://esbqas.sunac.com.cn:8001/WP_SUNAC/APP_PUBLIC_SERVICES" \
# "/Proxy_Services/TA_IDM/PUBLIC_SUNAC_301_queryIdmOrgData_PS?wsdl"
# SYSTEMID = 'Sunac_IDMOrg'
SYSTEMID = 'Sunac_AD_ORG'
dbconn = MysqlOper()
# IDM一天的增量查询，确定开始和结束时间。
inc_begindate = dbconn.dbonequery(
'select inc_datetime from idm_inc_flag where inc_type="org" ')
begintime = datetime.strftime(inc_begindate[0], '%Y-%m-%d %H:%M:%S.%f')
inc_enddate = inc_begindate[0] + timedelta(days=2)
endtime = datetime.strftime(inc_enddate, '%Y-%m-%d %H:%M:%S.%f')
# 查询IDM增量数据
try:
NUM = 1
NUM_LOOP = 'YES'
# 查询增量 循环支持的最大页，每页100条数据，如果查询到的当前页小于100条 停止页面循环，如果等于100条，查询下一页
while NUM <= 999 and NUM_LOOP == 'YES':
residmorg = idmquery(WSDL_URL, begintime, endtime, NUM, SYSTEMID,
idmid, 'idmorg')
if residmorg['body']['HEADER']['RESULT'] == '0':
org_list = residmorg['body']['LIST']
# 解析获取的soap信息
xml_tree = Et.fromstring(org_list)
# 如果当天有新增数据，停止增加天数
if len(xml_tree) > 0:
l_orginfo = []
for orginfo in xml_tree.iter('ORG'):
organnumber = orginfo.find('OrganNumber').text
organname = orginfo.find('OrganName').text
organparentno = orginfo.find('OrganParentNo').text
organupdate = orginfo.find('OrganUpdate').text
organcreate = orginfo.find('OrganCreate').text
organdep = orginfo.find('OrganDisplayName').text
if organdep is not None and organdep.startswith(
'融创中国_集团本部'):
areaid = '000101'
elif organdep is not None and organdep.startswith(
'融创中国_华北区域'):
areaid = '000102'
elif organdep is not None and organdep.startswith(
'融创中国_北京区域'):
areaid = '000104'
elif organdep is not None and organdep.startswith(
'融创中国_西南区域'):
areaid = '000107'
elif organdep is not None and organdep.startswith(
'融创中国_上海区域'):
areaid = '000120'
elif organdep is not None and organdep.startswith(
'融创中国_东南区域'):
areaid = '000121'
elif organdep is not None and organdep.startswith(
'融创中国_华中区域'):
areaid = '000122'
elif organdep is not None and organdep.startswith(
'融创中国_华南区域'):
areaid = '000123'
elif organdep is not None and organdep.startswith(
'融创中国_服务集团'):
areaid = '0004'
elif organdep is not None and organdep.startswith(
'融创中国_文旅集团'):
areaid = '0.04'
elif organdep is not None and organdep.startswith(
'融创中国_文化集团'):
areaid = '0.01'
else:
areaid = '000'
organstatus = orginfo.find('OrganStatus').text
orgobject = (organnumber, organname, organparentno,
organupdate, organcreate, organdep,
organstatus, areaid)
l_orginfo.append(orgobject)
res_insert = dbconn.dbmanyinsert(
'replace into idm_org_data(organnumber,organname,organparentno,'
'organupdate,organcreate,organdep,organstatus,areaid) '
'values (%s,%s,%s,%s,%s,%s,%s,%s)', l_orginfo)
# 判断组织插入是否成功，如果成功查询翻页记录
if res_insert:
infolog_org('IDM信息--查询时间：%s 至 %s，结果集当前分页：%s，变更组织数：%s' %
(begintime, endtime, NUM, len(xml_tree)))
if len(xml_tree) == 100:
NUM += 1
else:
infolog_org('IDM信息--没有更多的翻页数据')
NUM_LOOP = 'NO'
# 判断增量的日期，插入增量标志作为下次增量查询的开始时间
incdate(inc_enddate, CURRENT_DATE, 'org')
else:
errlog_org('IDM信息--组织插入失败')
else:
infolog_org('IDM信息--查询时间：%s 至 %s，没有查询到增量组织' %
(begintime, endtime))
NUM_LOOP = 'NO'
# 判断增量的日期，插入增量标志作为下次增量查询的开始时间
incdate(inc_enddate, CURRENT_DATE, 'org')
else:
errlog_org('IDM信息--组织查询接口出现错误')
NUM_LOOP = 'NO'
except Exception as e:
errlog_org(e)
# 关闭数据库连接
finally:
dbconn.dbclose()