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)