def post(self):
"""Create business for ec user.
"""
fields = DotDict(ecname="",
ecmobile="",
password="",
linkman="",
address="",
email="",
bizcode="",
type="")
for key in fields.iterkeys():
fields[key] = self.get_argument(key, '').strip()
self.db.execute("INSERT INTO T_CORP(cid, name, mobile, password,"
" linkman, address, email, timestamp, bizcode, type)"
" VALUES(%s, %s, %s, password(%s), %s, %s, %s, %s, %s, %s)",
fields.ecmobile, fields.ecname, fields.ecmobile,
fields.password, fields.linkman,
fields.address, fields.email,
int(time.time()), fields.bizcode, fields.type)
self.db.execute("INSERT INTO T_GROUP(corp_id, name, type)"
" VALUES(%s, default, default)",
fields.ecmobile)
self.redirect("/ecbusiness/list/%s" % fields.ecmobile)
def get(self):
""" """
status = ErrorCode.SUCCESS
try:
page_number = int(self.get_argument('pagenum'))
page_count = int(self.get_argument('pagecnt'))
#reserved API
fields = DotDict(name="name LIKE '%%%%%s%%%%'")
for key in fields.iterkeys():
v = self.get_argument(key, None)
if v:
if not check_sql_injection(v):
status = ErrorCode.SELECT_CONDITION_ILLEGAL
self.write_ret(status)
return
fields[key] = fields[key] % (v,)
else:
fields[key] = None
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
logging.exception("[UWEB] cid: %s get line data format illegal. Exception: %s",
self.current_user.cid, e.args)
self.write_ret(status)
return
try:
where_clause = ' AND '.join([v for v in fields.itervalues()
if v is not None])
page_size = UWEB.LIMIT.PAGE_SIZE
if where_clause:
where_clause = ' AND ' + where_clause
if page_count == -1:
sql = "SELECT count(id) as count FROM T_LINE" + \
" WHERE 1=1 " + where_clause
sql += " AND cid = %s" % (self.current_user.cid,)
res = self.db.get(sql)
count = res.count
d, m = divmod(count, page_size)
page_count = (d + 1) if m else d
sql = "SELECT id AS line_id, name AS line_name FROM T_LINE" +\
" WHERE 1=1 " + where_clause
sql += " AND cid = %s LIMIT %s, %s" % (self.current_user.cid, page_number * page_size, page_size)
lines = self.db.query(sql)
for line in lines:
stations = self.db.query("SELECT name, latitude, longitude, seq "
" FROM T_STATION "
" WHERE line_id = %s",
line.line_id)
line["stations"] = stations
self.write_ret(status,
dict_=DotDict(lines=lines,
pagecnt=page_count))
except Exception as e:
logging.exception("[UWEB] cid: %s get line failed. Exception: %s",
self.current_user.cid, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
def get(self):
""" """
status = ErrorCode.SUCCESS
try:
page_number = int(self.get_argument('pagenum'))
page_count = int(self.get_argument('pagecnt'))
#reserved API
fields = DotDict(name="name LIKE '%%%%%s%%%%'",
mobile="mobile LIKE '%%%%%s%%%%'")
for key in fields.iterkeys():
v = self.get_argument(key, None)
if v:
fields[key] = fields[key] % (v,)
else:
fields[key] = None
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
logging.exception("[UWEB] cid: %s Send message to ios push server data format illegal. Exception: %s",
self.current_user.cid, e.args)
self.write_ret(status)
return
try:
where_clause = ' AND '.join([v for v in fields.itervalues()
if v is not None])
page_size = 20
if where_clause:
where_clause = ' AND ' + where_clause
if page_count == -1:
sql = "SELECT count(id) as count FROM T_PASSENGER" + \
" WHERE 1=1 " + where_clause
sql += " AND cid = %s" % (self.current_user.cid,)
res = self.db.get(sql)
count = res.count
d, m = divmod(count, page_size)
page_count = (d + 1) if m else d
sql = "SELECT id, pid, name, mobile FROM T_PASSENGER" +\
" WHERE 1=1 " + where_clause
sql += " AND cid = %s LIMIT %s, %s" % (self.current_user.cid, page_number * page_size, page_size)
passengers = self.db.query(sql)
for passenger in passengers:
for key in passenger.keys():
passenger[key] = passenger[key] if passenger[key] else ''
self.write_ret(status,
dict_=DotDict(passengers=passengers,
pagecnt=page_count))
except Exception as e:
logging.exception("[UWEB] cid: %s get passenger failed. Exception: %s",
self.current_user.cid, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
def post(self):
"""Retrieve the log of delegation.
"""
# check administrator_id
start_time = int(self.get_argument("start_time"))
end_time = int(self.get_argument("end_time"))
select_clause = (
"SELECT T_ADMINISTRATOR.name as administrator, T_ADMINISTRATOR.login,"
+ " T_DELEGATION_LOG.timestamp, T_TERMINAL_INFO.mobile as tmobile,"
+ " T_USER.name as user_name "
)
from_table_clause = " FROM T_DELEGATION_LOG, T_ADMINISTRATOR, T_TERMINAL_INFO, T_USER "
where_clause = (
" WHERE T_DELEGATION_LOG.timestamp BETWEEN %s AND %s"
+ " AND T_DELEGATION_LOG.administrator_id = T_ADMINISTRATOR.id"
+ " AND T_DELEGATION_LOG.uid = T_USER.uid"
+ " AND T_DELEGATION_LOG.tid = T_TERMINAL_INFO.tid"
)
where_clause = where_clause % (start_time, end_time)
fields = DotDict(
administrator="T_ADMINISTRATOR.name LIKE '%%%%%s%%%%'",
login="******",
user_name="T_USER.name LIKE '%%%%%s%%%%'",
mobile="T_USER.mobile LIKE '%%%%%s%%%%'",
tmobile="T_TERMINAL_INFO.mobile LIKE '%%%%%s%%%%'",
)
for key in fields.iterkeys():
v = self.get_argument(key, None)
if v:
if not check_sql_injection(v):
self.get()
return
fields[key] = fields[key] % (v,)
else:
fields[key] = None
terms = [where_clause] + [v for v in fields.itervalues() if v]
where_clause = " AND ".join(terms)
sql = select_clause + from_table_clause + where_clause
sql += " ORDER BY T_DELEGATION_LOG.timestamp DESC"
logs = self.db.query(sql)
for i, log in enumerate(logs):
log["id"] = i + 1
self.render("delegation/log.html", logs=logs, interval=[start_time, end_time])
def post(self):
"""Retrieve the administrators according to the given parameters.
"""
# TODO: this is ugly!
fields = DotDict(corporation="corporation LIKE '%%%%%s%%%%'",
name="name LIKE '%%%%%s%%%%'",
mobile="mobile LIKE '%%%%%s%%%%'",
phone="phone LIKE '%%%%%s%%%%'",
login="******",
valid="valid = %s",
source_id="source_id = %s")
for key in fields.iterkeys():
v = self.get_argument(key, None)
if v:
# if not check_sql_injection(v):
# self.get()
# return
fields[key] = fields[key] % (v,)
else:
fields[key] = None
where_clause = ' AND '.join([v for v in fields.itervalues()
if v is not None])
if where_clause:
sql = ("SELECT id, corporation, name, mobile,"
" phone, login, valid, type"
" FROM T_ADMINISTRATOR"
" WHERE ") + where_clause
administrators = self.db.query(sql)
else:
administrators = []
for i, administrator in enumerate(administrators):
administrator['seq'] = i + 1
for key in administrator:
if administrator[key] is None:
administrator[key] = ''
self.render("administrator/search.html",
sources=self.sources,
administrators=administrators)
def post(self, tmobile):
"""Modify a business."""
fields = DotDict(cnum="",
ctype="",
ccolor="",
cbrand="",
uname="",
umobile="",
tmobile="",
service_status="",
begintime="",
endtime="",
address="",
email="")
for key in fields.iterkeys():
fields[key] = self.get_argument(key, "")
if fields[key]:
if not check_sql_injection(fields[key]):
logging.error("Edit business condition contain SQL inject. %s : %s",
key, fields[key])
self.render('errors/error.html',
message=ErrorCode.ERROR_MESSAGE[ErrorCode.EDIT_CONDITION_ILLEGAL])
return
try:
self.db.execute("UPDATE T_USER"
" SET name = %s,"
" address = %s,"
" email = %s "
" WHERE mobile = %s",
fields.uname, fields.address,
fields.email, fields.umobile)
self.db.execute("UPDATE T_TERMINAL_INFO"
" SET begintime = %s,"
" endtime = %s,"
" service_status = %s "
" WHERE mobile = %s",
fields.begintime, fields.endtime,
fields.service_status, fields.tmobile)
terminal = self.db.get("SELECT tid "
" FROM T_TERMINAL_INFO"
" WHERE mobile = %s",
fields.tmobile)
self.db.execute("UPDATE T_CAR"
" SET cnum = %s,"
" type = %s,"
" color = %s,"
" brand = %s "
" WHERE tid = %s",
fields.cnum, fields.ctype,
fields.ccolor, fields.cbrand, terminal.tid)
terminal_info_key = get_terminal_info_key(terminal.tid)
terminal_info = self.redis.getvalue(terminal_info_key)
if terminal_info:
terminal_info['alias'] = fields.cnum if fields.cnum else fields.tmobile
self.redis.setvalue(terminal_info_key, terminal_info)
fields.sms_status = self.get_sms_status(fields.tmobile)
self.render('business/list.html',
business=fields,
status=ErrorCode.SUCCESS,
message='')
except Exception as e:
logging.exception("Edit business failed.mobile: %s, Exception: %s",
tmobile, e.args)
self.render('errors/error.html',
message=ErrorCode.ERROR_MESSAGE[ErrorCode.EDIT_USER_FAILURE])
def post(self):
"""Query businesses according to the given params.
"""
corplist = self.db.query("SELECT id, cid, name FROM T_CORP")
corps = self.get_argument('corps', None)
begintime = int(self.get_argument('begintime',0))
endtime = int(self.get_argument('endtime',0))
interval=[begintime, endtime]
if not corps:
corps = self.db.query("SELECT cid FROM T_CORP")
corps = [str(corp.cid) for corp in corps]
sql = "SELECT id FROM T_GROUP WHERE corp_id IN %s" % (tuple(corps + DUMMY_IDS),)
groups = self.db.query(sql)
groups = [str(group.id) for group in groups] + [-1,]
else:
groups = self.db.query("SELECT id FROM T_GROUP WHERE corp_id = %s", corps)
groups = [str(group.id) for group in groups]
fields = DotDict(umobile="tu.mobile LIKE '%%%%%s%%%%'",
tmobile="tt.mobile LIKE '%%%%%s%%%%'",
begintime="tt.begintime >= %s",
endtime="tt.begintime <= %s",
login="******")
for key in fields.iterkeys():
#BIG NOTE: if online is to be got, "tt.login != 0" is good
if key == 'login' and self.get_argument(key, None) == '1':
fields[key] = "tt.login != 0"
continue
v = self.get_argument(key, None)
if v:
if not check_sql_injection(v):
logging.error("Search business condition contain SQL inject. %s : %s", key, v)
self.render('errors/error.html',
message=ErrorCode.ERROR_MESSAGE[ErrorCode.SELECT_CONDITION_ILLEGAL])
return
else:
fields[key] = fields[key] % (v,)
else:
fields[key] = None
where_clause = ' AND '.join([v for v in fields.itervalues()
if v is not None])
try:
sql = ("SELECT tt.tid, tt.login, tu.name as uname, tu.mobile as umobile, tt.mobile as tmobile,"
" tt.softversion, tt.begintime, tt.endtime, tt.move_val, tt.static_val,"
" tt.service_status, tt.bt_name, tt.bt_mac, tt.biz_type,"
" tc.cnum, tcorp.name as ecname, tcorp.mobile as cmobile"
" FROM T_TERMINAL_INFO as tt LEFT JOIN T_CAR as tc ON tt.tid = tc.tid"
" LEFT JOIN T_USER as tu ON tt.owner_mobile = tu.mobile"
" LEFT JOIN T_GROUP as tg ON tt.group_id = tg.id"
" LEFT JOIN T_CORP as tcorp ON tg.corp_id = tcorp.cid"
" WHERE tt.service_status=1 AND tt.group_id IN %s ") % (tuple(groups + DUMMY_IDS),)
if where_clause:
sql += ' AND ' + where_clause
businesses = self.db.query(sql)
for i, business in enumerate(businesses):
business['seq'] = i + 1
#business['sms_status'] = self.get_sms_status(business['tmobile'])
business['corp_name'] = ''
#NOTE: if login !=0(offline), set login as 1(online)
business['login'] = business['login'] if business['login']==0 else 1
#biz = QueryHelper.get_biz_by_mobile(business['tmobile'], self.db)
#business['biz_type'] = biz['biz_type'] if biz else 1
terminal = QueryHelper.get_terminal_info(business['tid'], self.db, self.redis)
business['pbat'] = terminal['pbat'] if terminal.get('pbat', None) is not None else 0
business['alias'] = business['cnum'] if business['cnum'] else business['tmobile']
for key in business:
if business[key] is None:
business[key] = ''
# keep data in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.get_memcache_key(hash_)
self.redis.setvalue(mem_key, businesses,
time=self.MEMCACHE_EXPIRY)
self.render('business/search.html',
status=ErrorCode.SUCCESS,
message='',
interval=interval,
businesses=businesses,
corplist=corplist,
hash_=hash_)
except Exception as e:
logging.exception("Search business failed. Exception: %s.",
e.args)
self.render('errors/error.html',
message=ErrorCode.ERROR_MESSAGE[ErrorCode.SEARCH_BUSINESS_FAILURE])
def post(self):
"""Create business for a couple of users.
"""
fields = DotDict(group_id="",
cnum="",
ctype="",
ccolor="",
cbrand="",
tmobile="",
begintime="",
endtime="",
uname="",
umobile="",
password="",
address="",
email="",
ecmobile="")
for key in fields.iterkeys():
fields[key] = self.get_argument(key,'')
#if not check_sql_injection(fields[key]):
# logging.error("Create business condition contain SQL inject. %s : %s", key, fields[key])
# self.render('errors/error.html',
# message=ErrorCode.ERROR_MESSAGE[ErrorCode.CREATE_CONDITION_ILLEGAL])
# return
white_list = check_zs_phone(fields.tmobile, self.db)
if not white_list:
logging.error("Create business error, %s is not whitelist", fields.tmobile)
self.render('errors/error.html',
message=ErrorCode.ERROR_MESSAGE[ErrorCode.MOBILE_NOT_ORDERED])
return
try:
# 1: add user
user_info = dict(umobile=fields.umobile,
password=fields.password,
uname=fields.uname,
address=fields.address,
email=fields.email)
add_user(user_info, self.db, self.redis)
# record the add action
bind_info = dict(tid=fields.tmobile,
tmobile=fields.tmobile,
umobile=fields.umobile,
group_id=-1,
cid=fields.ecmobile,
add_time=int(time.time()))
record_add_action(bind_info, self.db)
# 2: add terminal
if not fields.umobile:
user_mobile = fields.ecmobile
else:
user_mobile = fields.umobile
terminal_info = dict(tmobile=fields.tmobile,
owner_mobile=user_mobile,
begintime=fields.begintime,
offline_time=fields.begintime,
endtime=4733481600, # 2120.1.1
cnum=fields.cnum,
ctype=fields.ctype,
ccolor=fields.ccolor,
cbrand=fields.cbrand)
add_terminal(terminal_info, self.db, self.redis)
# 4: send message to terminal
register_sms = SMSCode.SMS_REGISTER % (fields.umobile, fields.tmobile)
ret = SMSHelper.send_to_terminal(fields.tmobile, register_sms)
ret = DotDict(json_decode(ret))
sms_status = 0
if ret.status == ErrorCode.SUCCESS:
self.db.execute("UPDATE T_TERMINAL_INFO"
" SET msgid = %s"
" WHERE mobile = %s",
ret['msgid'], fields.tmobile)
#convert front desk need format
sms_status = 1
else:
sms_status = 0
logging.error("Create business sms send failure. terminal mobile: %s, owner mobile: %s", fields.tmobile, fields.mobile)
fields.sms_status = sms_status
fields.service_status = 1
self.render('business/list.html',
business=fields,
status=ErrorCode.SUCCESS,
message='')
except Exception as e:
logging.exception("Create business failed. Exception: %s.",
e.args)
self.render('errors/error.html',
message=ErrorCode.ERROR_MESSAGE[ErrorCode.CREATE_USER_FAILURE])
def post(self):
"""Create a administrator.
"""
# create
fields = DotDict(corporation="",
name="",
login="",
password="",
mobile="",
phone="",
email="",
valid="",
source_id="")
list_inject = ['name', 'password', 'mobile', 'phone']
for key in list_inject:
v = self.get_argument(key, '')
# if not check_sql_injection(v):
# self.get()
# return
for key in fields.iterkeys():
fields[key] = self.get_argument(key, '')
fields.source_id = fields.source_id if fields.source_id else 5
administrator_id = self.db.execute("INSERT INTO T_ADMINISTRATOR (login, password, "
" name, mobile, phone, email, "
" corporation, source_id, valid, type)"
" VALUES (%s, password(%s), %s, %s, "
" %s, %s, %s, %s, %s, %s)",
fields.login, fields.password,
fields.name, fields.mobile,
fields.phone, fields.email,
fields.corporation,
fields.source_id, fields.valid, 2)
# insert privilege
privileges = map(int, self.get_arguments('privileges'))
if privileges:
self.db.executemany("INSERT INTO T_PRIVILEGE"
" VALUES (%s, %s)",
[(administrator_id, id)
for id in privileges])
cities = map(int, str_to_list(self.get_argument('cities', '')))
key = self.get_area_memcache_key(administrator_id)
if len(cities) == 1 and cities[0] == 0:
self.db.execute("INSERT INTO T_AREA_PRIVILEGE"
" VALUES(NULL, %s, %s, %s)",
administrator_id, AREA.CATEGORY.PROVINCE,
AREA.PROVINCE.LIAONING)
cities = self.db.query("SELECT city_id, city_name FROM T_HLR_CITY"
" WHERE province_id = %s",
AREA.PROVINCE.LIAONING)
self.redis.setvalue(key, cities)
else:
# put privilege_areas into memcached
areas = self.get_area(cities)
self.redis.setvalue(key, areas)
cities = self.db.query("SELECT region_code FROM T_HLR_CITY"
" WHERE city_id IN %s",
tuple(cities + DUMMY_IDS))
cids = [c.region_code for c in cities]
for cid in cids:
self.db.execute("INSERT INTO T_AREA_PRIVILEGE"
" VALUES (NULL, %s, %s, %s)",
administrator_id, AREA.CATEGORY.CITY, cid)
self.redirect("/administrator/list/%s" % administrator_id)
def post(self, administrator_id):
"""Edit the administrator.
"""
is_self = (administrator_id == self.current_user.id)
# update basic info
fields = DotDict(corporation="corporation = %s",
name="name = %s",
mobile="mobile = %s",
phone="phone = %s",
email="email = %s",
valid="valid = %s",
source_id="source_id = %s"
)
list_inject = ['corporation', 'name', 'mobile', 'phone']
for key in list_inject:
v = self.get_argument(key, '')
# if not check_sql_injection(v):
# self.get(administrator_id)
# return
if is_self:
del fields['valid']
del fields['source_id']
self.bookkeep(dict(id=self.current_user.id,
session_id=self.current_user.session_id),
quote(safe_utf8(self.get_argument('name', u""))))
data = [self.get_argument(key, '')
for key in fields.iterkeys()] + [administrator_id]
set_clause = ','.join([v for v in fields.itervalues()])
self.db.execute("UPDATE T_ADMINISTRATOR"
" SET " + set_clause +
" WHERE id = %s",
*data)
if not is_self:
# update privilege
privileges = map(int, self.get_arguments('privileges'))
if privileges:
rs = self.db.query("SELECT privilege_group_id FROM T_PRIVILEGE"
" WHERE administrator_id = %s",
administrator_id)
ids = [r.privilege_group_id for r in rs]
new_ids = list(set(privileges) - set(ids))
old_ids = list(set(ids) - set(privileges))
# clean up old ids
self.db.execute("DELETE FROM T_PRIVILEGE"
" WHERE administrator_id = %s"
" AND privilege_group_id in %s",
administrator_id, tuple(old_ids + DUMMY_IDS))
# insert new ids
self.db.executemany("INSERT INTO T_PRIVILEGE"
" VALUES (%s, %s)",
[(administrator_id, priv)
for priv in new_ids])
key = self.get_area_memcache_key(administrator_id)
cities = [int(i)
for i in str_to_list(self.get_argument('cities', ''))]
if len(cities) == 1 and int(cities[0]) == 0:
self.db.execute("DELETE FROM T_AREA_PRIVILEGE"
" WHERE administrator_id = %s",
administrator_id)
self.db.execute("INSERT INTO T_AREA_PRIVILEGE"
" VALUES(NULL, %s, %s, %s)",
administrator_id, AREA.CATEGORY.PROVINCE,
AREA.PROVINCE.LIAONING)
cities = self.db.query("SELECT city_id, city_name FROM T_HLR_CITY"
" WHERE province_id = %s",
AREA.PROVINCE.LIAONING)
self.redis.setvalue(key, cities)
else:
if cities:
areas = self.get_area(cities)
self.redis.setvalue(key, areas)
cities = self.db.query("SELECT region_code FROM T_HLR_CITY"
" WHERE city_id IN %s",
tuple(cities + DUMMY_IDS))
cids = [c.region_code for c in cities]
rs = self.db.query("SELECT area_id FROM T_AREA_PRIVILEGE"
" WHERE category = %s"
" AND administrator_id = %s",
AREA.CATEGORY.CITY, administrator_id)
ids = [r.area_id for r in rs]
new_ids = list(set(cids) - set(ids))
old_ids = list(set(ids) - set(cids))
# clean up old ids
self.db.execute("DELETE FROM T_AREA_PRIVILEGE"
" WHERE administrator_id = %s"
" AND category = %s"
" AND area_id in %s",
administrator_id, AREA.CATEGORY.CITY,
tuple(old_ids + DUMMY_IDS))
self.db.execute("DELETE FROM T_AREA_PRIVILEGE"
" WHERE administrator_id = %s"
" AND category = %s",
administrator_id, AREA.CATEGORY.PROVINCE)
# insert new ids
self.db.executemany("INSERT INTO T_AREA_PRIVILEGE"
" VALUES (NULL, %s, %s, %s)",
[(administrator_id, AREA.CATEGORY.CITY, id)
for id in new_ids])
else:
self.db.execute("DELETE FROM T_AREA_PRIVILEGE"
" WHERE administrator_id = %s",
administrator_id)
self.redis.delete(key)
self.redirect("/administrator/list/%s" % administrator_id)
def prepare_data(self, hash_):
mem_key = self.get_memcache_key(hash_)
data = self.redis.getvalue(mem_key)
if data:
return data
# defaulty, we consider parents to be selected
category = str(self.get_argument('category', XXT.USER_TYPE.PARENT))
city = int(self.get_argument('cities', 0))
terms = []
users = []
if int(city) == 0:
cities = [city.city_id for city in self.cities]
else:
cities = [city,]
select_clause = "SELECT T_XXT_USER.name AS pname,"\
+ " T_XXT_USER.optype AS poptype,"\
+ " T_XXT_USER.status AS jxq_status,"\
+ " T_XXT_USER.plan_id AS pplan,"\
+ " T_XXT_USER.timestamp AS ptimestamp,"\
+ " T_XXT_TARGET.mobile AS tmobile, T_XXT_TARGET.lbmp_status,"\
+ " T_XXT_TARGET.optype AS toptype,"\
+ " T_XXT_TARGET.name AS tname,"\
+ " T_XXT_TARGET.plan_id AS tplan,"\
+ " T_XXT_TARGET.timestamp AS ttimestamp,"\
+ " T_HLR_CITY.city_name AS city,"\
+ " T_XXT_GROUP.name AS group_name,"
USER_BIND_TARGET = " FROM T_XXT_USER LEFT JOIN T_XXT_TARGET"\
" ON (T_XXT_USER.mobile = T_XXT_TARGET.parent_mobile"\
" AND T_XXT_USER.service_id = T_XXT_TARGET.service_id),"
TARGET_BIND_USER = "******"\
" ON (T_XXT_USER.mobile = T_XXT_TARGET.parent_mobile"\
" AND T_XXT_USER.service_id = T_XXT_TARGET.service_id),"
from_table_clause = "T_HLR_CITY, T_XXT_GROUP"
where_clause = " WHERE T_XXT_GROUP.xxt_id in %s "
if int(self.type) == 1:
groups = self.db.query("SELECT txg.xxt_id as id"
" FROM T_XXT_GROUP AS txg,"
" T_ADMINISTRATOR AS ta"
" WHERE ta.login = txg.phonenum"
" AND ta.id = %s",
self.current_user.id)
else:
groups = self.db.query("SELECT DISTINCT xxt_id as id"
" FROM T_XXT_GROUP AS txg,"
" T_HLR_CITY AS thc"
" WHERE thc.city_id IN %s"
" AND txg.city_id = thc.region_code",
tuple(cities + DUMMY_IDS))
group_ids = [int(group.id) for group in groups]
groups = tuple(group_ids + DUMMY_IDS)
terms.append(where_clause)
if category == XXT.USER_TYPE.PARENT:
fields = DotDict(name="T_XXT_USER.name LIKE '%%%%%s%%%%' ",
mobile=" T_XXT_USER.mobile LIKE '%%%%%s%%%%' ")
for item in fields.iterkeys():
v = self.get_argument(item, None)
if v:
if not check_sql_injection(v):
return []
fields[item] = fields[item] % (v, )
else:
fields[item] = None
user_terms = [v for v in fields.itervalues() if v]
terms += user_terms
else:
fields = DotDict(name="T_XXT_TARGET.name LIKE '%%%%%s%%%%' ",
mobile=" T_XXT_TARGET.mobile LIKE '%%%%%s%%%%' ",
plan_id="T_XXT_TARGET.plan_id = %s ")
for item in fields.iterkeys():
v = self.get_argument(item, None)
if v:
if not check_sql_injection(v):
return []
fields[item] = fields[item] % (v,)
else:
fields[item] = None
child_terms = [v for v in fields.itervalues() if v]
terms += child_terms
USER_BIND_GROUP = " T_XXT_USER.group_id = T_XXT_GROUP.xxt_id "
TARGET_BIND_GROUP = " T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id "
GROUP_BIND_CITY = " T_XXT_GROUP.city_id = T_HLR_CITY.region_code"
terms.append(GROUP_BIND_CITY)
terms.append(USER_BIND_GROUP)
select_clause1 = select_clause + " T_XXT_USER.mobile AS pmobile"
sql1 = ''.join((select_clause1, USER_BIND_TARGET, from_table_clause, ' AND '.join(terms)))
terms.remove(USER_BIND_GROUP)
terms.append(TARGET_BIND_GROUP)
select_clause2 = select_clause + " T_XXT_TARGET.parent_mobile AS pmobile"
sql2 = ''.join((select_clause2, TARGET_BIND_USER, from_table_clause, ' AND '.join(terms)))
sql = sql1 + ' union ' + sql2
users = self.db.query(sql, groups, groups)
plans = self.db.query("SELECT xxt_id, name FROM T_XXT_PLAN")
d_plan = dict()
for plan in plans:
d_plan[plan.xxt_id] = plan.name
for i, user in enumerate(users):
user['id'] = i + 1
for key in user:
if key in ('pplan', 'tplan'):
user[key] = d_plan[user[key]] if user[key] else ''
else:
user[key] = user[key] if user[key] else ''
self.redis.setvalue(mem_key, users, time=self.MEMCACHE_EXPIRY)
return users
def post(self):
"""Create business for a couple of users.
"""
fields = DotDict(ecid="",
cnum="",
ctype="",
ccolor="",
cbrand="",
tmobile="",
begintime="",
endtime="",
uname="",
umobile="",
password="",
address="",
email="",
ecmobile="",
biz_type="")
for key in fields.iterkeys():
fields[key] = self.get_argument(key, '')
# if not check_sql_injection(fields[key]):
# logging.error("Create business condition contain SQL inject. %s : %s", key, fields[key])
# self.render('errors/error.html',
# message=ErrorCode.ERROR_MESSAGE[ErrorCode.CREATE_CONDITION_ILLEGAL])
# return
white_list = check_zs_phone(fields.tmobile, self.db)
if not white_list:
logging.error(
"Create business error, %s is not whitelist", fields.tmobile)
self.render('errors/error.html',
message=ErrorCode.ERROR_MESSAGE[ErrorCode.MOBILE_NOT_ORDERED])
return
try:
# 1: add user
user = self.db.get(
"SELECT id FROM T_USER WHERE mobile = %s", fields.umobile)
if not user:
self.db.execute("INSERT INTO T_USER(id, uid, password, name, mobile, address, email, remark)"
" VALUES(NULL, %s, password(%s), %s, %s, %s, %s, NULL)",
fields.umobile, '111111',
fields.uname, fields.umobile,
fields.address, fields.email)
self.db.execute("INSERT INTO T_SMS_OPTION(uid)"
" VALUES(%s)",
fields.umobile)
# 2: add terminal
group = self.db.get("SELECT id FROM T_GROUP"
" WHERE corp_id = %s AND type = 0 LIMIT 1",
fields.ecid)
if not group:
gid = self.db.execute("INSERT INTO T_GROUP(corp_id, name, type)"
" VALUES(%s, default, default)",
fields.ecid)
else:
gid = group.id
# record the add action, enterprise
bind_info = dict(tid=fields.tmobile,
tmobile=fields.tmobile,
umobile=fields.umobile,
group_id=gid,
cid=fields.ecmobile,
add_time=int(time.time()))
record_add_action(bind_info, self.db)
if not fields.umobile:
user_mobile = fields.ecmobile
else:
user_mobile = fields.umobile
# 3: send message to terminal
biz_type = int(fields.biz_type)
if biz_type == UWEB.BIZ_TYPE.YDWS:
self.db.execute("INSERT INTO T_TERMINAL_INFO(tid, group_id, mobile, owner_mobile,"
" begintime, endtime, offline_time, login_permit)"
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
fields.tmobile, gid,
fields.tmobile, user_mobile,
fields.begintime, 4733481600, fields.begintime, 0)
register_sms = SMSCode.SMS_REGISTER % (
fields.umobile, fields.tmobile)
ret = SMSHelper.send_to_terminal(fields.tmobile, register_sms)
self.db.execute("INSERT INTO T_CAR(tid, cnum, type, color, brand)"
" VALUES(%s, %s, %s, %s, %s)",
fields.tmobile, fields.cnum,
fields.ctype, fields.ccolor, fields.cbrand)
else:
tid = get_tid_from_mobile_ydwq(fields.tmobile)
activation_code = QueryHelper.get_activation_code(self.db)
self.db.execute("INSERT INTO T_TERMINAL_INFO(tid, group_id, mobile, owner_mobile,"
" begintime, endtime, offline_time, login_permit,"
" biz_type, activation_code, service_status)"
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
tid, gid,
fields.tmobile, user_mobile,
fields.begintime, 4733481600,
fields.begintime, 0, biz_type,
activation_code, UWEB.SERVICE_STATUS.TO_BE_ACTIVATED)
register_sms = SMSCode.SMS_REGISTER_YDWQ % (
ConfHelper.UWEB_CONF.url_out, activation_code)
ret = SMSHelper.send(fields.tmobile, register_sms)
self.db.execute("INSERT INTO T_CAR(tid, cnum, type, color, brand)"
" VALUES(%s, %s, %s, %s, %s)",
tid, fields.cnum,
fields.ctype, fields.ccolor, fields.cbrand)
ret = DotDict(json_decode(ret))
sms_status = 0
if ret.status == ErrorCode.SUCCESS:
self.db.execute("UPDATE T_TERMINAL_INFO"
" SET msgid = %s"
" WHERE mobile = %s",
ret['msgid'], fields.tmobile)
# convert front desk need format
sms_status = 1
else:
sms_status = 0
logging.error("[ADMIN] Create business sms send failure."
" terminal mobile: %s, owner mobile: %s",
fields.tmobile, fields.umobile)
fields.sms_status = sms_status
fields.service_status = 1
self.render('business/list.html',
business=fields,
status=ErrorCode.SUCCESS,
message='')
except Exception as e:
logging.exception("Add terminal failed. Exception: %s.",
e.args)
self.render('errors/error.html',
message=ErrorCode.ERROR_MESSAGE[ErrorCode.CREATE_USER_FAILURE])
def prepare_data(self, hash_):
"""Prepare search results for post.
"""
mem_key = self.get_memcache_key(hash_)
data = self.redis.getvalue(mem_key)
if data:
if data[0]:
return data
corp = self.get_argument('corps', None)
begintime = self.get_argument('begintime', 0)
if not begintime:
begintime = 0
else:
begintime = int(begintime)
endtime = self.get_argument('endtime', 0)
if not endtime:
endtime = 0
else:
endtime = int(endtime)
#begintime = int(self.get_argument('begintime',0))
#endtime = int(self.get_argument('endtime',0))
interval = [begintime, endtime]
if not corp:
corps = self.db.query("SELECT id, cid FROM T_CORP")
corps = [str(c.cid) for c in corps]
else:
corps = [str(corp), ]
fields = DotDict(ecmobile="mobile LIKE '%%%%%s%%%%'")
#begintime="timestamp >= %s",
# endtime="timestamp <= %s")
for key in fields.iterkeys():
v = self.get_argument(key, None)
if v:
# if not check_sql_injection(v):
# self.get()
# return
fields[key] = fields[key] % (v,)
else:
fields[key] = None
where_clause = ' AND '.join([v for v in fields.itervalues()
if v is not None])
sql = ("SELECT cid, name as ecname, mobile as ecmobile, address, email"
" FROM T_CORP"
" WHERE cid IN %s ") % (tuple(corps + DUMMY_IDS),)
if where_clause:
sql += " AND " + where_clause
businesses = self.db.query(sql)
for i, business in enumerate(businesses):
business['seq'] = i + 1
groups = self.db.query(
"SELECT id FROM T_GROUP WHERE corp_id = %s", business.cid)
groups = [g.id for g in groups]
terminals = self.db.query("SELECT id FROM T_TERMINAL_INFO"
" WHERE group_id IN %s"
" AND service_status = %s",
tuple(groups + DUMMY_IDS),
UWEB.SERVICE_STATUS.ON)
business['total_terminals'] = len(terminals)
for key in business:
if business[key] is None:
business[key] = ''
# NOTE: here, in order to get the latest data, data is not kept in redis
# self.redis.setvalue(mem_key,(businesses,interval),
# time=self.MEMCACHE_EXPIRY)
return businesses, interval