def find4and(cls, table, conditions, order=dict(id=1), page=None,field='*'):
"""
and精确查找
:param table:str 表名称
:param conditions: dict 参数 default None
:param order:dict 排序字段{field:1/-1} 1正序（默认id正序） -1倒序
:param page:dict 分页dict(pageindex=1,pagesize=20)
:return:future
"""
# 参照以下模型
# SELECT * FROM test INNER JOIN(SELECT id FROM test WHERE CompanyCode='5057' ORDER BY id DESC LIMIT 0,100) AS t1 ON t1.id=test.id
sql =None
cond=''
orders=cls.__dict2order(order)
if conditions:
cond=' where %s ' % cls.dict2str_split(conditions, 'and')
if page:
pageindex = page.get('pageindex', 1) if page.get('pageindex', 1) > 0 else 1
pagesize = page.get('pagesize', 20) if page.get('pagesize', 20) > 0 else 1
offset = (int(pageindex)-1)* int(pagesize)
sql='SELECT %(field)s FROM %(table)s AS a INNER JOIN(SELECT id FROM %(table)s %(cond)s ORDER BY %(orders)s LIMIT %(offset)s,%(pagesize)s) AS _ ON _.id=a.id'
sql=sql%dict(field=field,table=table,cond=cond,orders=orders,offset=offset,pagesize=pagesize)
else:
sql='SELECT %(field)s FROM %(table)s %(cond)s ORDER BY %(orders)s'%dict(field=field,table=table,cond=cond,orders=orders)
return db.fetchall(sql, conditions)
def get(self):
pid = self.args.get('pid', None) if self.args else '001'
ret = None
ret = self.redis.get('gis_allregion')
if ret:
ret = json.loads(ret)
else:
sql = 'SELECT r_id,r_pid,r_name FROM gis_region'
ret = yield db.fetchall(sql)
self.redis.set('gis_allregion', json.dumps(ret))
self.write(dict(errcode=RET.OK, errmsg=RMS.OK, data=ret))
def get(self):
try:
sql = 'SELECT t_name,t_tel,t_carid,t_tzcode,a.t_createtime,b.t_createtime AS t_activetime,t_mul,t_from FROM busi_tzcord AS a INNER JOIN busi_tzflow AS b ON a.t_uid=b.t_codeid AND t_state=1'
ret = yield db.fetchall(sql)
except Exception as e:
self.write(
dict(errcode=RET.SERVERERR, errmsg=RMS.SERVERERR, data=str(e)))
else:
self.write(
dict(errcode=RET.OK,
errmsg=RMS.OK,
data=json.loads(json.dumps(ret, cls=MyEncoder))))
def get(self):
c1 = self.args if self.args else {}
if c1.get('bc_treeid'):
sql = 'SELECT bc_id,bc_treeid,bc_name,bc_remark,bc_createtime,\
(SELECT cu_username FROM comp_sysuer WHERE a.bc_createuid = cu_userid) AS bc_createmen\
FROM base_code AS a WHERE bc_treeid=%(bc_treeid)s'
ret = yield db.fetchall(sql, c1)
self.write(
dict(errcode=RET.OK,
errmsg=RMS.OK,
data=json.loads(json.dumps(ret, cls=MyEncoder))))
else:
self.write(dict(errcode=RET.PARAMERR, errmsg=RMS.PARAMERR))
def get(self):
c1 = copy.deepcopy(self.args) if self.args else {}
pagesize = c1.pop('pagesize', 20)
pageindex = c1.pop('pageindex', 1)
dbc = c1.pop('dbc', None)
key1 = c1.pop('key1', '')
ret = yield db.callproc('api_tz_list', (key1, pageindex, pagesize))
result = dict(errcode=RET.OK,
errmsg=RMS.OK,
data=json.loads(json.dumps(ret, cls=MyEncoder)))
if dbc == 'yes':
sql = 'select count(id) as _count from busi_tzcord'
if key1 != '':
sql += ' where t_tel like %(key)s or t_tzcode like %(key)s'
count = yield db.fetchall(sql, dict(key=key1))
result.setdefault('datacount', count[0].get('_count'))
self.write(result)
def __sltcmd_like(cls, table, conditions, order, page, symbol,field):
sql =None
cond=''
orders=cls.__dict2order(order)
c1 = None
if conditions:
c1 = copy.deepcopy(conditions)
del conditions
for k, v in c1.items():
c1[k] = '%'+v+'%'
cond=' where %s ' % cls.dict2like(c1, symbol)
if page:
pageindex = page.get('pageindex', 1) if page.get('pageindex', 1) > 0 else 1
pagesize = page.get('pagesize', 20) if page.get('pagesize', 20) > 0 else 1
offset = (int(pageindex)-1)* int(pagesize)
sql='SELECT %(field)s FROM %(table)s AS a INNER JOIN(SELECT id FROM %(table)s %(cond)s ORDER BY %(orders)s LIMIT %(offset)s,%(pagesize)s) AS _ ON _.id=a.id'
sql=sql%dict(field=field,table=table,cond=cond,orders=orders,offset=offset,pagesize=pagesize)
else:
sql='SELECT %(field)s FROM %(table)s %(cond)s ORDER BY %(orders)s'%dict(field=field,table=table,cond=cond,orders=orders)
return db.fetchall(sql, c1)
def wrapper(RequestHandler, *args, **kwargs):
# 首选缓存中获取用户信息，没有的按照游客角色id“000000”处理
roleid = '000000'
session_data = Session(RequestHandler).data
if session_data:
roleid = session_data.get('roleid') if session_data.get(
'roleid') else '000000'
# 获取角色的接口
roleapi = RequestHandler.redis.hget('roleapi_cache', roleid) #开发阶段屏蔽
# roleapi = None
if not roleapi:
sql = 'SELECT (SELECT sa_uri FROM sys_api WHERE a.ra_apiid = sa_id) AS ra_uri, ra_get,ra_post,ra_put,ra_delete FROM sys_roleapi AS a WHERE ra_roleid=%s'
ret = yield db.fetchall(sql, args=(roleid,))
ret = json.dumps(ret)
RequestHandler.redis.hset('roleapi_cache', roleid, ret)
roleapi = ret
# 接口权限
roleapi = json.loads(roleapi)
request_uri = RequestHandler.request.uri.split('?')[0]
accordapi = None
for api in roleapi:
if re.match(r'^%s$' % api.get('ra_uri'), request_uri):
accordapi = api
break
# 接口请求方式权限
if accordapi:
request_method = 'ra_%s' % RequestHandler.request.method.lower()
rm_power = accordapi.get(request_method, 0)
if str(rm_power) == '1':
fun(RequestHandler, *args, **kwargs)
else:
RequestHandler.send_error(
403, msg='服务器拒绝了你，原因：%s无权访问或登录过期' % (RequestHandler.request.method))
raise gen.Return()
else:
RequestHandler.send_error(403, msg='服务器拒绝了你，原因：无权访问或登录过期')
raise gen.Return()
def test():
aa = u'测试菜单'
print "select * from sys_menu where sm_caption='%s'" % aa
return db.fetchall("select * from sys_menu where sm_caption='%s'" %
aa)