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