def delManageCompany(id: int = None, ManageCompanyName: str = None):
comStr = ''
paramers = None
if id == None and ManageCompanyName != None:
comStr = 'delete from zf_manage_company where mc_name = :arg'
paramers = (ManageCompanyName, )
elif id != None and ManageCompanyName == None:
comStr = 'delete from zf_manage_company where mc_id = :arg'
paramers = (id, )
elif id != None and ManageCompanyName != None:
comStr = 'delete from zf_manage_company where mc_id = :id and mc_name = :name'
paramers = {'id': id, 'name': ManageCompanyName}
else:
logging.info('未指定查询语句')
try:
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute(comStr, paramers) # 带参数的执行语句
connection.commit() # 通过连接对象提交
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.deleteDataError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def insertUkeyRecord(recordObj):
comStr = "insert into zf_ukey_record (ur_company_num,ur_role_num,ur_ukey_num,ur_action_num,ur_proposer,ur_application_content,ur_create_time) values (:companyNum,:roleNum,:ukeyNum,:actionNum,:propName,:applicontent,:createTime)" # 带参数的插入语句
paramers = {
'companyNum': recordObj.companyNum,
'roleNum': recordObj.roleNum,
'ukeyNum': recordObj.ukeyNum,
'actionNum': recordObj.actionNum,
'propName': recordObj.proposerName,
'applicontent': recordObj.applicationContent,
'createTime': recordObj.createTime
}
try:
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute(comStr, paramers) # 带参数的执行语句
connection.commit() # 通过连接对象提交
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.insertDataError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def delRoles(id: int = None, roleName: str = None):
comStr = ''
paramers = None
if id == None and roleName != None:
comStr = 'delete from zf_role where r_name = :arg'
paramers = (roleName, )
elif id != None and roleName == None:
comStr = 'delete from zf_role where r_id = :arg'
paramers = (id, )
elif id != None and roleName != None:
comStr = 'delete from zf_role where r_id = :id and r_name = :roleName'
paramers = (id, roleName)
else:
logging.info('未指定查询语句')
try:
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute(comStr, paramers) # 带参数的执行语句
connection.commit() # 通过连接对象提交
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.deleteDataError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def insertManageCompany(McName):
comStr = 'insert into zf_manage_company (mc_name) values (:name)' # 带参数的插入语句
try:
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute(comStr, {'name': McName}) # 带参数的执行语句
connection.commit() # 通过连接对象提交
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.insertDataError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def insertUkey(ukeyObj):
# Ukey插入sql语句
insertUkeyComStr = 'insert into zf_Ukey u (u.ukey_id,u.ownername,u.ownermobile,u.ownercarnum,u.ownercompanynum,u.role_id,u.usetime,u.isuse) values (:ukeyID,:ownName,:ownMobile,:ownCarNum,:ownCompanyNum,:roleId,:useTime,:isUse)'
# 参数化
ukeyParamers = {
'ukeyID': ukeyObj.ukeyId,
'ownName': ukeyObj.name,
'ownMobile': ukeyObj.mobile,
'ownCarNum': ukeyObj.carNum,
'ownCompanyNum': ukeyObj.companyId,
'roleId': ukeyObj.roleId,
'useTime': ukeyObj.useTime,
'isUse': ukeyObj.isUse
}
# 添加记录语句
inertUkeyRcordComStr = 'insert into zf_ukey_record r (r.ur_company_num,r.ur_role_num,r.ur_ukey_num,r.ur_action_num,r.ur_proposer,r.ur_application_content,r.ur_create_time) values (:companyId,:roleId,:ukeyId,:actionId,:proposer,:appContent,:creatTime)'
# 参数化
recordParamers = {
'companyId': ukeyObj.companyId,
'roleId': ukeyObj.roleId,
'ukeyId': ukeyObj.ukeyId,
'actionId': ukeyObj.actionId,
'proposer': ukeyObj.name,
'appContent': ukeyObj.appContent,
'creatTime': ukeyObj.useTime
}
try:
connection = pool.acquire()
cursor = connection.cursor()
connection.begin() # 事务开始
cursor.execute(insertUkeyComStr, ukeyParamers) # 执行uekey插入语句
cursor.execute(inertUkeyRcordComStr, recordParamers) # 执行添加注册Ukey记录语句
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
connection.rollback() # 报错回滚
return StatuCode.insertDataError.value
except Exception as e:
logging.info(e)
connection.rollback() # 报错回滚
return StatuCode.unknowError.value
finally:
connection.commit() # 通过连接对象提交
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def getCompanys():
comStr = 'select * from zf_companys'
try:
connection = pool.acquire() # 对oracle连接对象池中获取连接
cursor = connection.cursor() # 获取游标
cursor.execute(comStr) # 执行SQL语句
content = cursor.fetchall() # 获取行数据,返回一个元组类型的list列表
cloumns = cursor.description # 获取当前表查询的列名
return ToolsHelp.formateData(content,cloumns) # 调用formateData()工具方法格式化数据
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.selectTabelError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def delUkey(id: int):
comStr = 'delete from zf_ukey where ukey_id = :id'
paramers = (id, )
try:
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute(comStr, paramers) # 带参数的执行语句
connection.commit() # 通过连接对象提交
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.deleteDataError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def getHasRoleIdLis(companyId: int):
comStr = 'select role_id from zf_ukey where ownercompanynum = :companyId' # 查询字符串 select ukey_id from zf_ukey order by ukey_id
try:
connection = pool.acquire() # 对oracle连接对象池中获取连接
cursor = connection.cursor() # 获取游标
cursor.execute(comStr, (companyId, )) # 执行SQL语句
content = cursor.fetchall() # 获取行数据,返回一个元组类型的list列表
cloumns = cursor.description # 获取当前表查询的列名
return ToolsHelp.formateUkeyIds(content,
cloumns) # 调用formateUkeyIds()工具方法格式化数据
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.selectTabelError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def updateManageCompany(id, newMcName):
comStr = 'update zf_manage_company t set t.mc_name = :name where t.mc_id = :id' # 更新语句
try:
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute(comStr, {
'name': newMcName,
'id': id
}) # 参数为dict的执行语句,通过key匹配值
connection.commit() # 通过连接对象提交
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.insertDataError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def updateUkey(id: int,
phoneNum: str = None,
companyId: int = None,
roleId: int = None,
useTime: str = None,
unuseTime: str = None,
isuse: int = None,
isdestroy: int = None):
comStr = '' # 更新语句
paramers = None
if phoneNum != None:
comStr = 'update zf_ukey set ownermobile = :phoneNum where ukey_id = :id'
paramers = {'phoneNum': phoneNum, 'id': id}
elif companyId != None and roleId != None:
comStr = 'update zf_ukey set ownercompanynum = :companyId,role_id = :roleId where ukey_id = :id'
paramers = {'companyId': companyId, 'roleId': roleId, 'id': id}
elif useTime != None and isuse != None:
comStr = "update zf_ukey set usetime = :useTime,isuse= :isuse where ukey_id = :id"
paramers = {'usetime': useTime, 'isuse': isuse, 'id': id}
elif unuseTime != None and isdestroy != None:
comStr = "update zf_ukey set unusetime = :unuseTime,isdestroy= :isdestroy where ukey_id = :id"
paramers = {'unusetime': unuseTime, 'isdestroy': isdestroy, 'id': id}
else:
logging.info('zf_ukey update comStr is error')
try:
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute(comStr, paramers) # 参数为dict的执行语句,通过key匹配值
connection.commit() # 通过连接对象提交
return StatuCode.successCode.value
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.insertDataError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
def getUkeysBycompanyId(companyId: int = None): # 正在使用的Ukey
if companyId != None:
comStr = 'select u.ukey_id,u.ownername,u.ownermobile,u.ownercarnum,u.usetime,u.unusetime,u.isuse,u.isdestroy,r.r_name,c.compname from zf_companys c,zf_role r, zf_ukey u where u.ownercompanynum = :companyId and u.ownercompanynum = c.compid and u.role_id = r.r_id and u.isuse = 1 and u.isdestroy =0 order by u.ukey_id'
paramers = {'companyId': companyId}
else:
comStr = 'select u.ukey_id,u.ownername,u.ownermobile,u.ownercarnum,u.usetime,u.unusetime,u.isuse,u.isdestroy,r.r_name,c.compname from zf_companys c,zf_role r, zf_ukey u where u.ownercompanynum = c.compid and u.role_id = r.r_id and u.isuse = 1 and u.isdestroy =0 order by u.ukey_id' # 查询字符串
paramers = {}
try:
connection = pool.acquire() # 对oracle连接对象池中获取连接
cursor = connection.cursor() # 获取游标
cursor.execute(comStr, paramers) # 执行SQL语句
content = cursor.fetchall() # 获取行数据,返回一个元组类型的list列表
cloumns = cursor.description # 获取当前表查询的列名
return ToolsHelp.formateData(content,
cloumns) # 调用formateData()工具方法格式化数据
except oracle.DatabaseError as msg:
logging.info(msg)
return StatuCode.selectTabelError.value
except Exception as e:
logging.info(e)
return StatuCode.unknowError.value
finally:
cursor.close() # 关闭游标
pool.release(connection) # 释放连接对象回连接池
