def addRolePrivilege(oracle, **args):
select_colums = ['role_id']
where_dic = {}
where_dic['role_id'] = args['role_id']
sql = 'from role'
role_info = select(oracle, select_colums, sql, where_dic)
if (role_info == None):
raise Warning_, '角色role_id = %s不存在' % (args['role_id'])
select_colums = ['role_id', 'privilege_id']
where_dic = {}
where_dic['role_id'] = args['role_id']
where_dic['privilege_id'] = args['privilege_id']
sql = 'from role_privilege'
role_info = select(oracle, select_colums, sql, where_dic)
if (role_info != None):
raise Warning_, '角色权限role_id = %s privilege_id = %s已经存在' % (
role_info[0]['role_id'], role_info[0]['privilege_id'])
insert_value = args.copy()
del insert_value['oper_staff_id']
insert(oracle, 'role_privilege', insert_value)
where_dic = {}
where_dic['role_id'] = args['role_id']
where_dic['privilege_id'] = args['privilege_id']
add_infos = {'oper': 'add'}
add_infos['oper_staff_id'] = args['oper_staff_id']
add_infos['oper_date'] = '/sysdate'
insertLog(oracle, 'role_privilege', where_dic, add_infos)
def loginStaff(oracle, **args):
'''登录工号 staff_id, passwd'''
for k, v in args.items():
exec "%s = '%s'"%(k,v)
select_colums = ['passwd', 'staff_desc', 'staff_id', 'state']
sql = 'from staff where staff_id = %s'%staff_id
result = select(oracle, select_colums, sql)
if(result == None):
raise Warning_,'工号%s不存在'%staff_id
staff_info = result[0]
#工号是否有效
if(staff_info['state'] == '0'):
raise Warning_,'这个工号已经被管理员删除了,有疑问请联系管理员!'
#加密
m = hashlib.md5()
m.update(passwd)
in_passwd = m.hexdigest()
if(staff_info['passwd'] == None):
raise Warning_,'这个工号竟然没设密码!'
if(staff_info['passwd'].upper()!= in_passwd.upper()):
raise Warning_,'密码错误!'
select_colums = ['r.name role_name', 'r.role_id']
sql = '''from staff_role sr,role r
where sr.staff_id = %s
and sr.role_id = r.role_id
'''%staff_id
result = select(oracle, select_colums, sql)
if(result == None):
#raise Warning_,'工号%s没有角色或者机构'%staff_id
result = [staff_info]
else:
result[0].update(staff_info)
select_colums = ['b.name bss_org_name', 'b.bss_org_id']
sql = '''
from staff s, bss_org b
where s.bss_org_id = b.bss_org_id
and s.staff_id = %s
'''%staff_id
bss_info = select(oracle, select_colums, sql)
if(bss_info == None):
raise Warning_,'工号%s没有机构'%staff_id
else:
result[0].update(bss_info[0])
return result
def getRetailInfo(oracle, dic):
''' retail_id '''
for k, v in dic.items():
exec "%s = '%s'" % (k, v)
return select(oracle, ['flex_id', 'name', 'score', 'value', 'num'],
'from retail_detail', {'id': retail_id})
def getStaffId(oracle, select_colums, where_dic):
''' 取得当前持有这个发票资源的工号 '''
sql = ' from distri '
select_result = select(oracle, select_colums, sql, where_dic)
distri_id = where_dic['distri_id']
if (select_result == None):
raise Exception('根据distri_id = %s 找不到发票持有者' % distri_id)
return select_result
def getRealStaffID(oracle, staff_id, sys_type):
''' 从绑定表中取电子发票的 staff_id '''
where_dic = {'out_staff_id':staff_id, 'sys_type':sys_type, 'state':'1'}
result = select(oracle, ['staff_id'], 'from staff_map', where_dic)
if(result == None):
raise Warning_,'工号 %s 不存在绑定关系'%staff_id
else:
return result[0]['staff_id']
def test_select_mutated(self):
a = []
class F(object):
def fileno(self):
del a[-1]
return sys.stdout.fileno()
a[:] = [F()] * 10
self.assertEqual(select.select([], a, []), ([], a[:5], []))
def getNeedUpdate(oracle, table_name, partition):
'''获取,有多少个需要更新'''
select_colums = ['count(*)']
if(partition == None):
sql = 'from invoice.crm_update_acct_id c, acct.%s b where c.acct_id = b.acct_id'%table_name
else:
sql = 'from invoice.crm_update_acct_id c, acct.%s partition(%s) b where c.acct_id = b.acct_id'%(table_name, partition)
select_result = select(oracle, select_colums, sql)
return select_result[0]['count(*)']
def checkIsInstance(oracle, tax_code, tax_nbr, and_the):
'''检查发票是否都上架了'''
select_colums = ['pool_id', 'state', 'sys_type']
sql = "from pool where tax_code = '%s' and tax_nbr = '%s' %s" % (
tax_code, tax_nbr, and_the)
result = select(oracle, select_colums, sql)
if (result == None):
raise Warning_('待调整发票 tax_code = %s, tax_nbr = %s 未能找到,\
请确定是否已上架.' % (tax_code, tax_nbr))
return result[0]
def getTableColumnNames(oracle, table_name):
'''查询表有哪些字段'''
select_colums = ['column_name']
sql = 'from user_tab_columns'
where_dic = {}
where_dic['table_name'] = table_name.upper()
column_names = select(oracle, select_colums, sql, where_dic)
if (column_names == None):
raise Exception, '表%s不存在' % (table_name)
return column_names
def getBssOrgID(oracle):
'''给出最小的一个还未用的bss_org_id'''
select_colums = ['bss_org_id']
sql = ''' from (SELECT min(bss_org_id), max(bss_org_id)+1 bss_org_id
FROM (SELECT bss_org_id,
TO_NUMBER(bss_org_id) -
(ROW_NUMBER() OVER(ORDER BY bss_org_id)) DIF
FROM bss_org)
GROUP BY DIF)
where rownum < 2'''
return select(oracle, select_colums, sql)
def getOrgID(oracle):
'''给出最小的一个还未用的org_id'''
select_colums = ['id']
sql = ''' from (SELECT min(id), max(id)+1 id
FROM (SELECT id,
TO_NUMBER(id) -
(ROW_NUMBER() OVER(ORDER BY id)) DIF
FROM org)
GROUP BY DIF)
where rownum < 2'''
return select(oracle, select_colums, sql)
def select(self, select_colums, sql, where_args=None):
'''通用查询'''
try:
oracle = Oracle()
return defer.succeed(select(oracle, select_colums, sql, where_args))\
.addErrback(error, oracle)\
.addCallback(right, oracle)
except Warning_:
raise Warning_(getWarningInfo())
except Exception:
raise Exception(getExcInfo())
def getStaffID(oracle):
'''给出最小的一个还未用的staff_id'''
select_colums = ['staff_id']
sql = ''' from (SELECT min(staff_id), max(staff_id)+1 staff_id
FROM (SELECT staff_id,
TO_NUMBER(staff_id) -
(ROW_NUMBER() OVER(ORDER BY staff_id)) DIF
FROM staff)
GROUP BY DIF)
where rownum < 2'''
return select(oracle, select_colums, sql)
def bondStaff(oracle, **args):
'''工号绑定 staff_id out_staff_id sys_type passwd sys_type'''
for k, v in args.items():
exec "%s = '%s'"%(k,v)
if(sys_type!= 'C' and sys_type!= 'B'):
raise Warning_,'绑定工号时传入的系统类型为%s,不正确'
result = select(oracle, ['out_staff_id','sys_type'], 'from staff_map', {'staff_id':staff_id, 'state':'1','sys_type':sys_type})
if(result!= None):
raise Warning_,'工号%s已经绑定到系统%s的工号%s,不能重复绑定'%(staff_id, result[0]['sys_type'], result[0]['out_staff_id'])
result = select(oracle, ['staff_id'], 'from staff_map', {'out_staff_id':out_staff_id, 'state':'1', 'sys_type':sys_type})
if(result!= None):
raise Exception,'系统%s工号%s已经绑定到了工号%s上,不能重复绑定'%(sys_type, out_staff_id, result[0]['staff_id'])
if(sys_type == 'C'):
result = oracle.execute('select passwd,staff_id from v_staff_crm where upper(staff_id) = :staff_id',{'staff_id':out_staff_id.upper()})
elif(sys_type == 'B'):
result = oracle.execute('select passwd,staff_id from acct.staff where upper(staff_code) = :staff_id',{'staff_id':out_staff_id.upper()})
if(len(result) == 0):
raise Warning_,'外部系统%s 工号%s不存在'%(sys_type,out_staff_id)
crm_passwd = result[0][0]
out_staff_id = result[0][1]
if(sys_type == 'C'):
m = hashlib.md5()
m.update(passwd)
passwd = m.hexdigest().upper()
if(passwd!= crm_passwd):
raise Warning_,'crm工号%s在密码不正确'%out_staff_id
insert_dic = {'staff_id':staff_id, 'out_staff_id':out_staff_id, 'state':1, 'sys_type':sys_type, 'created_date':'/sysdate', 'state_date':'/sysdate'}
insert(oracle, 'staff_map', insert_dic)
def getTax(oracle, staff_id):
where = {'staff_id': staff_id, 'state': 'instance'}
from_sql = '''
from
(select *
from pool
where staff_id = :staff_id
and state = :state
order by to_number(tax_code), to_number(tax_nbr)
)
where rownum<2
'''
result = select(oracle, ['tax_code', 'tax_nbr'], from_sql, bind_dic=where)
if (result == None):
return result
else:
return result[0]
def addStaffRole(oracle, **args):
'''增加工号角色 dic: role_id, staff_id, oper_staff_id'''
insert_value = args.copy()
del insert_value['oper_staff_id']
where_dic = insert_value
select_colums = ['role_id','staff_id']
sql = 'from staff_role'
role_info = select(oracle, select_colums, sql, where_dic)
print role_info
if(role_info!= None):
raise Warning_, '角色%s己经挂到工号%s上了'%(role_info[0]['role_id'], role_info[0]['staff_id'] )
insert(oracle, 'staff_role', insert_value)
add_infos = {'oper':'add'}
add_infos['oper_staff_id'] = args['oper_staff_id']
add_infos['oper_date'] = '/sysdate'
insertLog(oracle, 'staff_role', where_dic, add_infos)
def delStaffRole(oracle, **args):
'''删除工号角色 dic: role_id, staff_id, oper_staff_id'''
insert_value = args.copy()
del insert_value['oper_staff_id']
where_dic = insert_value
select_colums = ['role_id','staff_id']
sql = 'from staff_role'
role_info = select(oracle, select_colums, sql, where_dic)
print role_info
if(role_info == None):
raise Warning_, '角色%s工号%s己经不存在,无需删除'%(role_info[0]['role_id'], role_info[0]['staff_id'] )
add_infos = {'oper':'del'}
add_infos['oper_staff_id'] = args['oper_staff_id']
add_infos['oper_date'] = '/sysdate'
insertLog(oracle, 'staff_role', where_dic, add_infos)
delete(oracle = oracle, table_name = 'staff_role', where_dic = where_dic)
def getPoolId(oracle, dic):
'''取得已经上架的发票pool_id'''
select_colums = ['pool_id']
where_dic = {}
where_dic['tax_code'] = dic['tax_code']
where_dic['tax_nbr'] = dic['tax_nbr']
#where_dic['state'] = 'instance'
sql = 'from pool'
and_the = "and state in('instance','pre-use')"
select_value = select(oracle, select_colums, sql, where_dic, and_the)
#select_value = selectPool(oracle, select_colums, where_dic, and_the)
if (select_value == None):
raise Exception, '上架/预打记录数有%s条,tax_code = %s,tax_nbr = %s' % (
0, where_dic['tax_code'], where_dic['tax_nbr'])
if (len(select_value) != 1):
raise Exception, '上架/预打记录数有%s条,tax_code = %s,tax_nbr = %s' % (
len(select_value), where_dic['tax_code'], where_dic['tax_nbr'])
pool_id = select_value[0]['pool_id']
return pool_id
def test_select(self):
cmd = 'for i in 0 1 2 3 4 5 6 7 8 9; do echo testing...; sleep 1; done'
p = os.popen(cmd, 'r')
for tout in (0, 1, 2, 4, 8, 16) + (None,)*10:
if test_support.verbose:
print 'timeout =', tout
rfd, wfd, xfd = select.select([p], [], [], tout)
if (rfd, wfd, xfd) == ([], [], []):
continue
if (rfd, wfd, xfd) == ([p], [], []):
line = p.readline()
if test_support.verbose:
print repr(line)
if not line:
if test_support.verbose:
print 'EOF'
break
continue
self.fail('Unexpected return values from select():', rfd, wfd, xfd)
p.close()
def addOrg(oracle, dic):
'''depend on flex'''
for k, v in dic.items():
exec "%s = '%s'" % (k, v)
print dic.__doc__
if ('id' in dic):
id = dic['id']
else:
select_result = select(oracle, ['org_seq.nextval'], 'from dual')
id = select_result[0]['nextval']
dic['id'] = id
del dic['oper_staff_id']
insert(oracle, 'org', dic)
add_infos = {
'oper': 'add',
'oper_staff_id': oper_staff_id,
'oper_date': '/sysdate'
}
insertLog(oracle, 'org', {'id': id}, add_infos)
def delRole(oracle, **args):
'''删除角色 role_id oper_staff_id'''
where_dic = {}
where_dic['role_id'] = args['role_id']
#是否有工号是这个角色的
select_colums = ['staff_id']
sql = 'from staff_role'
staff_role_info = select(oracle, select_colums, sql, where_dic)
if (staff_role_info != None):
raise Warning_, '角色role_id = %s下还有工号,请先删除工号角色关系后再删除该工号' % (
args['role_id'])
add_infos = {'oper': 'del'}
add_infos['oper_staff_id'] = args['oper_staff_id']
add_infos['oper_date'] = '/sysdate'
insertLog(oracle, 'role', where_dic, add_infos)
sql = 'delete from role where role_id = %s' % args['role_id']
row_count = oracle.execute(sql)
if (row_count == 0):
raise Exception, '未能正确删除role表;role_id = %s' % args['role_id']
def addRole(oracle, **args):
'''增加角色 dic: role_id'''
select_colums = ['role_id', 'name']
where_dic = {}
where_dic['role_id'] = args['role_id']
sql = 'from role'
role_info = select(oracle, select_colums, sql, where_dic)
if (role_info != None):
raise Exception, '角色%s(%s)已经存在' % (role_info[0]['role_id'],
role_info[0]['name'])
insert_value = args.copy()
del insert_value['oper_staff_id']
insert_value['state_date'] = '/sysdate'
insert(oracle, 'role', insert_value)
where_dic = {}
where_dic['role_id'] = args['role_id']
add_infos = {'oper': 'add'}
add_infos['oper_staff_id'] = args['oper_staff_id']
add_infos['oper_date'] = '/sysdate'
insertLog(oracle, 'role', where_dic, add_infos)
def checkIfExist(oracle, **args):
'''检查号段是否重叠'''
select_colums = [
'distri_id', 'tax_code', 'tax_begin_nbr', 'tax_end_nbr', 'staff_id'
]
sql = '''
from distri
where 1 = 1
and tax_code in (:tax_code)
and ((to_number(tax_begin_nbr) = to_number(:tax_begin_nbr) or
to_number(tax_end_nbr) = to_number(:tax_begin_nbr) or
to_number(tax_end_nbr) = to_number(:tax_end_nbr) or
to_number(tax_begin_nbr) = to_number(:tax_end_nbr)) or
(to_number(tax_begin_nbr) > to_number(:tax_begin_nbr) and
to_number(tax_end_nbr) < to_number(:tax_end_nbr)) or
(to_number(tax_begin_nbr) < to_number(:tax_begin_nbr) and
to_number(tax_end_nbr) < to_number(:tax_end_nbr) and
to_number(tax_end_nbr) > to_number(:tax_begin_nbr)) or
(to_number(tax_begin_nbr) > to_number(:tax_begin_nbr) and
to_number(tax_end_nbr) > to_number(:tax_end_nbr) and
to_number(tax_begin_nbr) < to_number(:tax_end_nbr)) or
(to_number(tax_begin_nbr) < to_number(:tax_begin_nbr) and
to_number(tax_end_nbr) > to_number(:tax_end_nbr)))
and state in ('instance','hold','in','inFromCRM')
'''
select_info = select(oracle, select_colums, sql, bind_dic=args)
if (select_info != None):
select_info = select_info[0]
return '号段有重叠,已存在发票代码:%s 开始号码:%s,结束号码:%s,属于工号%s' % (
select_info['tax_code'], select_info['tax_begin_nbr'],
select_info['tax_end_nbr'], select_info['staff_id'])
else:
return "0"
def getNeedUpdateInfo(oracle):
'''查询需要update的信息'''
select_colums = ['id', 'table_name', 'partition', 'need_update', 'update_count']
select_result = select(oracle, select_colums,"from invoice.update_acct_id where need_update>0 and update_count is null")
return select_result
def hasChrild(oracle, id):
return select(oracle, ['id'], 'from org', where_dic={'parent_id': id})
def getUpdateInfo(oracle):
'''查询信息'''
select_colums = ['id', 'table_name', 'partition', 'need_update', 'update_count']
select_result = select(oracle, select_colums,"from invoice.update_acct_id")
return select_result
def getSeq(oracle, seq_name):
'''get seq use seq_name'''
select_colums = ['%s.nextval id' % seq_name]
sql = ' from dual'
return select(oracle, select_colums, sql)[0]['id']
def getStaff(oracle, staff_id):
'''查询工号'''
select_colums = ['staff_id', 'staff_desc', 'bss_org_id']
sql = 'from staff '
where_dic = {'staff_id':staff_id}
return select(oracle, select_colums, sql, where_dic)
def test_returned_list_identity(self):
# See issue #8329
r, w, x = select.select([], [], [], 1)
self.assertIsNot(r, w)
self.assertIsNot(r, x)
self.assertIsNot(w, x)
def selectPool(oracle, select_colums, where_dic):
'''查询pool表'''
sql = "from pool "
return select(oracle, select_colums, sql, where_dic)
