def save_role(p_role):
result = {}
val = check_role(p_role)
if val['code'] == '-1':
return val
try:
db = get_connection()
cr = db.cursor()
role_id = get_roleid()
role_name = p_role['name']
status = p_role['status']
privs = p_role['privs']
func_privs = p_role['func_privs']
print('privs=',privs)
sql="""insert into t_role(id,name,status,creation_date,creator,last_update_date,updator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')
""".format(role_id,role_name,status,current_rq(),'DBA',current_rq(),'DBA');
print(sql)
cr.execute(sql)
save_role_privs(role_id,privs)
save_role_func_privs(role_id,func_privs)
cr.close()
db.commit()
result={}
result['code']='0'
result['message']='保存成功！'
return result
except:
result['code'] = '-1'
result['message'] = '保存失败！'
return result
def save_func(p_func):
result = {}
val = check_func(p_func)
if val['code'] == '-1':
return val
try:
db = get_connection()
cr = db.cursor()
sql = """insert into t_func(func_name,func_url,priv_id,status,creation_date,creator,last_update_date,updator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')
""".format(p_func['func_name'],
p_func['func_url'], p_func['priv_id'], p_func['status'],
current_rq(), 'DBA', current_rq(), 'DBA')
cr.execute(sql)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '保存成功！'
return result
except:
print(traceback.format_exc())
result['code'] = '-1'
result['message'] = '保存失败！'
return result
def save_menu(p_menu):
result = {}
try:
db = get_connection()
cr = db.cursor()
name = p_menu['name']
url = p_menu['url']
status = p_menu['status']
parent_id = p_menu['parent_id']
menu_id = get_menuid(parent_id)
print("menu_id=" + menu_id)
sql = """insert into t_xtqx(id,name,url,status,parent_id,creation_date,creator,last_update_date,updator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')
""".format(menu_id, name, url, status, parent_id, current_rq(),
'DBA', current_rq(), 'DBA')
cr.execute(sql)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '保存成功！'
return result
except:
print(traceback.format_exc())
result['code'] = '-1'
result['message'] = '保存失败！'
return result
def save_user(p_user):
result = {}
val = check_user(p_user)
if val['code'] == '-1':
return val
try:
db = get_connection()
cr = db.cursor()
userid = get_userid()
loginname = p_user['login']
wkno = p_user['wkno']
username = p_user['user']
password = aes_encrypt(p_user['pass'], loginname)
gender = p_user['gender']
email = p_user['email']
phone = p_user['phone']
proj_group = p_user['proj_group']
dept = p_user['dept']
expire_date = p_user['expire_date']
status = p_user['status']
privs = p_user['privs']
file_path = p_user['file_path']
file_name = p_user['file_name']
if file_path == '':
file_path = '/static/assets/images/users'
if file_name == '':
if gender == '1':
file_name = 'boy.png'
else:
file_name = 'girl.png'
print(username, wkno, password, gender, email, phone, proj_group, dept,
expire_date, file_path, file_name)
sql = """insert into t_user(id,login_name,wkno,name,password,gender,email,phone,project_group,dept,expire_date,status,file_path,file_name,creation_date,creator,last_update_date,updator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')
""".format(userid, loginname, wkno, username, password, gender,
email, phone, proj_group, dept, expire_date,
status, file_path, file_name, current_rq(), 'DBA',
current_rq(), 'DBA')
print(sql)
cr.execute(sql)
save_user_role(userid, privs)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '保存成功！'
return result
except Exception as e:
print(e)
result['code'] = '-1'
result['message'] = '保存失败！'
return result
def save_ds(p_ds):
result = {}
val = check_ds(p_ds, 'add')
if val['code'] == '-1':
return val
try:
db = get_connection()
cr = db.cursor()
ds_id = get_dsid()
ds_market_id = p_ds['market_id']
ds_inst_type = p_ds['inst_type']
ds_db_type = p_ds['db_type']
ds_db_env = p_ds['db_env']
ds_db_desc = p_ds['db_desc']
ds_ip = format_sql(p_ds['ip'])
ds_port = p_ds['port']
ds_service = p_ds['service']
ds_user = p_ds['user']
ds_proxy_status = p_ds['proxy_status']
ds_proxy_server = p_ds['proxy_server']
if p_ds['pass'] != '':
ds_pass = aes_encrypt(p_ds['pass'], ds_user)
else:
ds_pass = p_ds['pass']
status = p_ds['status']
sql = """insert into t_db_source
(id,db_type,db_env,db_desc,ip,port,service,user,password,status,creation_date,creator,last_update_date,updator,market_id,inst_type,proxy_status,proxy_server)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')
""".format(ds_id, ds_db_type, ds_db_env, ds_db_desc, ds_ip,
ds_port, ds_service, ds_user, ds_pass, status,
current_rq(), 'DBA', current_rq(), 'DBA', ds_market_id,
ds_inst_type, ds_proxy_status, ds_proxy_server)
print(sql)
cr.execute(sql)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '保存成功！'
return result
except:
e_str = exception_info()
print(e_str)
result['code'] = '-1'
result['message'] = '保存失败！'
return result
def upd_password(p_user):
result = {}
try:
db = get_connection()
cr = db.cursor()
userid = p_user['userid']
loginname = p_user['loginname']
password = aes_encrypt(p_user['password'], loginname)
sql = """update t_user
set password ='******',
last_update_date ='{1}' ,
updator='{2}'
where id='{3}'""".format(password, current_rq(), 'DBA', userid)
print("upd_password=", sql)
cr.execute(sql)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '修改成功！'
except:
exception_info()
result['code'] = '-1'
result['message'] = '修改失败！'
return result
def upd_role(p_role):
result = {}
try:
db = get_connection()
cr = db.cursor()
roleid = p_role['roleid']
rolename = p_role['name']
status = p_role['status']
privs = p_role['privs']
func_privs= p_role['func_privs']
print("upd_role=",roleid,rolename,status)
print("privs=", privs)
print("func_privs=", func_privs)
sql="""update t_role
set name ='{0}',
status ='{1}' ,
last_update_date ='{2}' ,
updator='{3}'
where id='{4}'""".format(rolename,status,current_rq(),'DBA',roleid)
print(sql)
cr.execute(sql)
upd_role_privs(roleid,privs)
upd_role_func_privs(roleid,func_privs)
cr.close()
db.commit()
result={}
result['code']='0'
result['message']='更新成功！'
except :
result['code'] = '-1'
result['message'] = '更新失败！'
return result
def upd_func(p_func):
result = {}
try:
db = get_connection()
cr = db.cursor()
funcid = p_func['funcid']
func_name = p_func['func_name']
func_url = p_func['func_url']
priv_id = p_func['priv_id']
status = p_func['status']
sql = """update t_func
set func_name ='{0}',
func_url ='{1}' ,
priv_id ='{2}' ,
status ='{3}' ,
last_update_date ='{4}' ,
updator='{5}'
where id='{6}'""".format(func_name, func_url, priv_id, status,
current_rq(), 'DBA', funcid)
cr.execute(sql)
cr.close()
db.commit()
result['code'] = '0'
result['message'] = '更新成功！'
except:
result['code'] = '-1'
result['message'] = '更新失败！'
return result
def upd_menu(p_menu):
result = {}
try:
db = get_connection()
cr = db.cursor()
menuid = p_menu['menuid']
name = p_menu['name']
status = p_menu['status']
url = p_menu['url']
parent_id = p_menu['parent_id']
sql = """update t_xtqx
set name ='{0}',
status ='{1}' ,
url ='{2}' ,
parent_id ='{3}',
last_update_date ='{4}' ,
updator='{5}'
where id='{6}'""".format(name, status, url, parent_id,
current_rq(), 'DBA', menuid)
cr.execute(sql)
cr.close()
db.commit()
result['code'] = '0'
result['message'] = '更新成功！'
except:
result['code'] = '-1'
result['message'] = '更新失败！'
return result
def save_role_func_privs(p_role_id, p_privs):
result = {}
try:
db = get_connection()
cr = db.cursor()
print(p_privs)
for id in p_privs:
sql = """insert into t_role_func_privs(role_id,func_id,creation_date,creator,last_update_date,updator)
values({0},'{1}','{2}','{3}','{4}','{5}')
""".format(p_role_id, id, current_rq(), 'DBA', current_rq(),
'DBA')
print(sql)
cr.execute(sql)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '保存成功！'
return result
except:
result['code'] = '-1'
result['message'] = '保存失败！'
return result
def exp_port(static_path):
db = get_connection()
cr1 = db.cursor()
cr2 = db.cursor()
cr3 = db.cursor()
row_data = 0
workbook = xlwt.Workbook(encoding='utf8')
worksheet = workbook.add_sheet('port')
header_styles = set_header_styles(30,1)
os.system('cd {0}'.format(static_path + '/downloads/port'))
file_name = static_path + '/downloads/port/exp_port_{0}.xls'.format(current_rq())
file_name_s = 'exp_port_{0}.xls'.format(current_rq())
sql_market = "SELECT distinct a.market_id FROM t_port a ORDER BY a.market_id"
sql_header = """
SELECT
a.market_id AS "项目编码",
b.dmmc AS "项目名称",
app_desc AS "项目描述",
local_ip AS "本地IP",
local_port AS "本地PORT" ,
mapping_port AS "映射PORT",
mapping_domain AS "映射域名"
FROM t_port a,t_dmmx b,t_user c
WHERE a.market_id=b.dmm
and a.creater=c.login_name
and b.dm='05' limit 1"""
sql_content = """
SELECT
a.market_id AS "项目编码",
b.dmmc AS "项目名称",
app_desc AS "项目描述",
local_ip AS "本地IP",
local_port AS "本地PORT" ,
mapping_port AS "映射PORT",
mapping_domain AS "映射域名"
FROM t_port a,t_dmmx b,t_user c
WHERE a.market_id=b.dmm
and a.creater=c.login_name
and b.dm='05'
and a.market_id='{}'
ORDER BY a.market_id
"""
print(sql_market)
# 写表头
cr1.execute(sql_header)
desc = cr1.description
for k in range(len(desc)):
worksheet.write(row_data, k, desc[k][0], header_styles)
if k == len(desc) - 1:
worksheet.col(k).width = 8000
else:
worksheet.col(k).width = 4000
#循环项目写单元格
row_data = row_data + 1
cr2.execute(sql_market)
rs2 = cr2.fetchall()
for m in range(len(rs2)):
print('sql_content=',sql_content.format(rs2[m][0]))
cr3.execute(sql_content.format(rs2[m][0]))
rs3 = cr3.fetchall()
for i in rs3:
for j in range(len(i)):
if i[j] is None:
worksheet.write(row_data, j, '', cell_styles)
else:
cell_styles = set_row_styles(30, (m+3)*2-1)
worksheet.write(row_data, j, str(i[j]), cell_styles)
row_data = row_data + 1
workbook.save(file_name)
db.commit()
cr1.close()
cr2.close()
cr3.close()
print("{0} export complete!".format(file_name))
#生成zip压缩文件
zip_file = static_path + '/downloads/port/exp_port_{0}.zip'.format(current_rq())
rzip_file = '/static/downloads/port/exp_port_{0}.zip'.format(current_rq())
#若文件存在则删除
if os.path.exists(zip_file):
os.system('rm -f {0}'.format(zip_file))
z = zipfile.ZipFile(zip_file, 'w', zipfile.ZIP_DEFLATED, allowZip64=True)
z.write(file_name, arcname=file_name_s)
z.close()
print('zip_file=', zip_file)
# 删除json文件
os.system('rm -f {0}'.format(file_name))
return rzip_file
def upd_user(p_user):
result = {}
try:
db = get_connection()
cr = db.cursor()
userid = p_user['userid']
loginname = p_user['loginname']
wkno = p_user['wkno']
username = p_user['username']
password = aes_encrypt(p_user['password'], loginname)
gender = p_user['gender']
email = p_user['email']
phone = p_user['phone']
proj_group = p_user['proj_group']
dept = p_user['dept']
expire_date = p_user['expire_date']
status = p_user['status']
roles = p_user['roles']
file_path = p_user['file_path']
file_name = p_user['file_name']
if file_path == '':
file_path = '/static/assets/images/users'
if file_name == '':
if gender == '1':
file_name = 'boy.png'
else:
file_name = 'girl.png'
sql = """update t_user
set name ='{0}',
login_name='{1}',
password ='******',
gender ='{3}',
email ='{4}',
phone ='{5}',
dept ='{6}',
expire_date ='{7}' ,
status ='{8}' ,
last_update_date ='{9}' ,
updator ='{10}',
file_path ='{11}',
file_name = '{12}',
project_group = '{13}',
wkno = '{14}'
where id='{15}'""".format(username, loginname, password,
gender, email, phone,
dept, expire_date, status,
current_rq(), 'DBA', file_path,
file_name, proj_group, wkno, userid)
print("upd_user=", sql)
cr.execute(sql)
upd_user_role(userid, roles)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '更新成功！'
except:
exception_info()
result['code'] = '-1'
result['message'] = '更新失败！'
return result
def upd_ds(p_ds):
result = {}
val = check_ds(p_ds, 'upd')
if val['code'] == '-1':
return val
try:
db = get_connection()
cr = db.cursor()
ds_id = p_ds['dsid']
ds_market_id = p_ds['market_id']
ds_inst_type = p_ds['inst_type']
ds_db_type = p_ds['db_type']
ds_db_env = p_ds['db_env']
ds_db_desc = p_ds['db_desc']
ds_ip = format_sql(p_ds['ip'])
ds_port = p_ds['port']
ds_service = p_ds['service']
ds_user = p_ds['user']
ds_proxy_status = p_ds['proxy_status']
ds_proxy_server = p_ds['proxy_server']
print('upd_ds...p_ds=', p_ds)
if p_ds['pass'] != '':
ds_pass = aes_encrypt(p_ds['pass'], ds_user)
else:
ds_pass = p_ds['pass']
status = p_ds['status']
sql = """update t_db_source
set db_type ='{0}',
db_env ='{1}',
db_desc ='{2}' ,
ip ='{3}',
port ='{4}' ,
service ='{5}' ,
user ='******' ,
password ='******' ,
status ='{8}' ,
last_update_date ='{9}' ,
updator ='{10}',
market_id ='{11}',
inst_type ='{12}',
proxy_status ='{13}',
proxy_server ='{14}'
where id='{15}'""".format(ds_db_type, ds_db_env, ds_db_desc,
ds_ip, ds_port,
ds_service, ds_user, ds_pass, status,
current_rq(), 'DBA', ds_market_id,
ds_inst_type, ds_proxy_status,
ds_proxy_server, ds_id)
print(sql)
cr.execute(sql)
cr.close()
db.commit()
result = {}
result['code'] = '0'
result['message'] = '更新成功！'
except:
result['code'] = '-1'
result['message'] = '更新失败！'
return result
