def incdate(incdate, curdate, inctype):
try:
dbconn = MysqlOper()
if incdate < curdate:
inc_flag = incdate
else:
inc_flag = curdate
dbconn.dbonemod('update idm_inc_flag set inc_datetime=%s where inc_type=%s', inc_flag, inctype)
except Exception as e:
errlog_org(e)
finally:
dbconn.dbclose()
def area_user_exec(v_areaname, v_area):
dbconn = MysqlOper()
adconn = AdOper()
processed_user = dbconn.dbmanyquery('select userid,userorg from idm_user_handle '
'where userhandled > 9 and userareaid = %s', v_area)
for userobj in processed_user:
adfilterstr = f'(&(objectClass=person)(sAMAccountName={userobj[0].lower()})(!(userAccountControl=514)))'
adquery = adconn.adquery('ou=融创集团,dc=SUNAC,dc=local', adfilterstr)
if adquery:
for aduserobj in adconn.adconn.response:
pre_userorg = aduserobj['dn']
l_userorg = pre_userorg.split(',')
userobjcn = l_userorg[0]
userobjou = ','.join(l_userorg[1:])
if userobjou != userobj[1]:
res_admodify = adconn.admove(pre_userorg, userobjcn, userobj[1])
if res_admodify:
dbconn.dbonemod('update idm_user_handle set pre_userorg=%s,userhandled=1 '
'where userid = %s', pre_userorg, userobj[0])
infolog_user('AD信息--区域"%s" 已移动到新OU的用户：%s' % (v_areaname, userobj[0]))
else:
dbconn.dbonemod('update idm_user_handle set pre_userorg=%s,userhandled=10 '
'where userid=%s', pre_userorg, userobj[0])
errlog_user('AD信息--区域"%s" 移动OU失败的用户：%s' % (v_areaname, userobj[0]))
else:
dbconn.dbonemod('update idm_user_handle set pre_userorg=%s,userhandled=2 '
'where userid = %s', pre_userorg, userobj[0])
infolog_user('AD信息--区域"%s" 已存在的用户：%s' % (v_areaname, userobj[0]))
else:
pre_userorg = 'no ad user'
dbconn.dbonemod('update idm_user_handle set pre_userorg=%s,userhandled=0 '
'where userid = %s', pre_userorg, userobj[0])
errlog_user('AD信息--区域"%s" 没有创建的无效用户：%s' % (v_areaname, userobj[0]))
adconn.adclose()
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()
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" '
'WHEN organdep like "融创中国_东南区域%%" THEN "000121" '
'WHEN organdep like "融创中国_华中区域%%" THEN "000122" '
'WHEN organdep like "融创中国_华南区域%%" THEN "000123" '
'WHEN organdep like "融创中国_服务集团%%" THEN "0004" '
'WHEN organdep like "融创中国_文旅集团%%" THEN "0.04" '
'WHEN organdep like "融创中国_文化集团%%" THEN "0.01" '
'ELSE "000" END ')
# 判断【idm_user_init】是否为空
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()