def qq_oauth_register_associate(self, access_token, qq_oauth_openid,
user_id):
sql_exec(
"delete from dr_authenticate where user_id=%s and qq_oauth_openid=%s",
(user_id, qq_oauth_openid))
sql_exec(
"insert into dr_authenticate (user_id, qq_oauth_openid, qq_oauth_access_token) values(%s, %s, %s)",
(user_id, qq_oauth_openid, access_token))
trace(
"associate user id=%s to auth qq_oauth_openid=%s access_token=%s" %
(user_id, qq_oauth_openid, access_token))
def GET(self, group="", query_all="false", r=None):
enable_crossdomain();
if query_all == True or query_all == "true" or str(query_all) == "1":
query_all = True
else:
query_all = False
# if not null, must be a digit.
if group != "" and str(group) != "-1" and not str(group).isdigit():
error("group must be digit, actual is %s"%(group));
raise cherrypy.HTTPError(400, "group must be digit");
records = [];
if query_all:
if group == "" or str(group) == "-1":
records = sql_exec("select user_id,user_name from dr_user");
else:
records = sql_exec("select u.user_id,u.user_name "
"from dr_user u,dr_group g,dr_rs_group_user rs "
"where rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s", (group));
else:
if group == "" or str(group) == "-1":
records = sql_exec("select user_id,user_name from dr_user where enabled=true");
else:
records = sql_exec("select u.user_id,u.user_name "
"from dr_user u,dr_group g,dr_rs_group_user rs "
"where u.enabled=true "
"and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s", (group));
user_id = None;
auth = _config["auth"];
if auth["on"]:
# QQ-OAuth not enabled.
if auth["strategy"] == "qq_oauth":
# check QQ-OAuth session.
user_id = cherrypy.session.get(SESSION_KEY);
# the user cannot authorize by specified user.
exception_users = authorize_get_exception_user_id(user_id);
trace("get users while group=%s for user_id=%s exception_users=%s"%(group, user_id, exception_users));
ret = [];
for record in records:
returned_user_id = record["user_id"];
if returned_user_id in exception_users:
continue;
ret.append({
"id":returned_user_id, "value":record["user_name"]
});
return json.dumps({"code":ErrorCode.Success, "auth":user_id, "users":ret});
def GET(self, group="", query_all="false", r=None):
enable_crossdomain();
if query_all == True or query_all == "true" or str(query_all) == "1":
query_all = True
else:
query_all = False
# if not null, must be a digit.
if group != "" and str(group) != "-1" and not str(group).isdigit():
error("group must be digit, actual is %s"%(group));
raise cherrypy.HTTPError(400, "group must be digit");
records = [];
if query_all:
if group == "" or str(group) == "-1":
records = sql_exec("select user_id,user_name from dr_user");
else:
records = sql_exec("select u.user_id,u.user_name "
"from dr_user u,dr_group g,dr_rs_group_user rs "
"where rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s", (group));
else:
if group == "" or str(group) == "-1":
records = sql_exec("select user_id,user_name from dr_user where enabled=true");
else:
records = sql_exec("select u.user_id,u.user_name "
"from dr_user u,dr_group g,dr_rs_group_user rs "
"where u.enabled=true "
"and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s", (group));
user_id = None;
auth = _config["auth"];
if auth["on"]:
# QQ-OAuth not enabled.
if auth["strategy"] == "qq_oauth":
# check QQ-OAuth session.
user_id = cherrypy.session.get(SESSION_KEY);
# the user cannot authorize by specified user.
exception_users = authorize_get_exception_user_id(user_id);
trace("get users while group=%s for user_id=%s exception_users=%s"%(group, user_id, exception_users));
ret = [];
for record in records:
returned_user_id = record["user_id"];
if returned_user_id in exception_users:
continue;
ret.append({
"id":returned_user_id, "value":record["user_name"]
});
return json.dumps({"code":ErrorCode.Success, "auth":user_id, "users":ret});
def GET(self, r=None):
enable_crossdomain();
records = sql_exec("select type_id,type_name from dr_type");
ret = [];
for record in records:
ret.append({"id":record["type_id"], "value":record["type_name"]});
return json.dumps({"code":ErrorCode.Success, "data":ret});
def query_detail_group(self, group, start_time="", end_time="", user_id="", product_id="", type_id="", query_all=False):
if query_all:
sql = "select %s from %s where %s"%(
"report_id,product_id,u.user_id,type_id,bug_id,work_hours,report_content,work_date,insert_date,modify_date,priority",
"dr_report,dr_user u,dr_group g,dr_rs_group_user rs",
"dr_report.user_id = rs.user_id and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s"%(group));
else:
sql = "select %s from %s where %s"%(
"report_id,product_id,u.user_id,type_id,bug_id,work_hours,report_content,work_date,insert_date,modify_date,priority",
"dr_report,dr_user u,dr_group g,dr_rs_group_user rs",
"u.enabled = true and dr_report.user_id = rs.user_id and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s"%(group));
(names, params) = self.build_sql_conditions(start_time, end_time, user_id, product_id, type_id);
sql = "%s %s %s"%(sql, names, "order by dr_report.report_id asc");
records = sql_exec(sql, params);
ret = [];
for record in records:
ret.append({
"report_id":record["report_id"], "product_id":record["product_id"], "user_id":record["user_id"],
"type_id":record["type_id"], "bug_id":record["bug_id"], "work_hours":record["work_hours"],
"report_content":record["report_content"], "work_date":str(record["work_date"]),
"insert_date":str(record["insert_date"]), "modify_date":str(record["modify_date"]),
"priority":record["priority"]
});
return json.dumps({"code":ErrorCode.Success, "data":ret});
def qq_oauth_query_available_associate_user(self, access_token,
qq_oauth_openid):
# query all un-associated users.
users = []
records = sql_exec(
"select user_id,user_name from dr_user where "
"user_id not in (select user_id from dr_authenticate) "
"and enabled=true")
for record in records:
users.append({
"id": record["user_id"],
"value": record["user_name"]
})
return json.dumps({
"code":
ErrorCode.NotAssociated,
"error":
ErrorCode.NotAssociated,
"access_token":
access_token,
"qq_oauth_openid":
qq_oauth_openid,
"users":
users,
"error_description":
"user not found, please associate one"
})
def email_for_time(self, mail_time, mail_times):
(hour, minute, second) = mail_time.split(":");
now = datetime.datetime.now();
if now.hour != int(hour):
return True;
if now.minute != int(minute):
return True;
if now.second != int(second):
return True;
mail = _config["mail"];
# log
date = now.strftime("%Y-%m-%d");
trace("email from %s when time is %s, date is %s"%(mail["username"], mail_times, date));
time.sleep(1);
# check email strategy
if not self.email_strategy_check(date):
return False;
# query email to user list.
records = sql_exec("select user_id,user_name,email from dr_user where enabled=true and user_id not in "
"(select distinct u.user_id from dr_user u, dr_report r where u.user_id = r.user_id and r.work_date=%s)"%(date));
if len(records) == 0:
trace("all user reported, donot email");
return False;
# generate to user list.
to_user = [];
for record in records:
to_user.append(record["user_name"]);
trace("email to %s."%(to_user));
for record in records:
if not self.do_email_to(record["user_id"], record["user_name"], record["email"], date):
return False;
trace("email to %s cc=%s success."%(to_user, mail["cc_user"]));
return True;
def email_for_time(self, mail_time, mail_times):
(hour, minute, second) = mail_time.split(":");
now = datetime.datetime.now();
if now.hour != int(hour):
return True;
if now.minute != int(minute):
return True;
if now.second != int(second):
return True;
mail = _config["mail"];
# log
date = now.strftime("%Y-%m-%d");
trace("email from %s when time is %s, date is %s"%(mail["username"], mail_times, date));
time.sleep(1);
# check email strategy
if not self.email_strategy_check(date):
return False;
# query email to user list.
records = sql_exec("select user_id,user_name,email from dr_user where enabled=true and user_id not in "
"(select distinct u.user_id from dr_user u, dr_report r where u.user_id = r.user_id and r.work_date=%s)"%(date));
if len(records) == 0:
trace("all user reported, donot email");
return False;
# generate to user list.
to_user = [];
for record in records:
to_user.append(record["user_name"]);
trace("email to %s."%(to_user));
for record in records:
if not self.do_email_to(record["user_id"], record["user_name"], record["email"], date):
return False;
trace("email to %s cc=%s success."%(to_user, mail["cc_user"]));
return True;
def query_detail_group(self, group, start_time="", end_time="", user_id="", product_id="", type_id="", query_all=False):
if query_all:
sql = "select %s from %s where %s"%(
"report_id,product_id,u.user_id,type_id,bug_id,work_hours,report_content,work_date,insert_date,modify_date,priority",
"dr_report,dr_user u,dr_group g,dr_rs_group_user rs",
"dr_report.user_id = rs.user_id and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s"%(group));
else:
sql = "select %s from %s where %s"%(
"report_id,product_id,u.user_id,type_id,bug_id,work_hours,report_content,work_date,insert_date,modify_date,priority",
"dr_report,dr_user u,dr_group g,dr_rs_group_user rs",
"u.enabled = true and dr_report.user_id = rs.user_id and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s"%(group));
(names, params) = self.build_sql_conditions(start_time, end_time, user_id, product_id, type_id);
sql = "%s %s %s"%(sql, names, "order by dr_report.report_id asc");
records = sql_exec(sql, params);
ret = [];
for record in records:
ret.append({
"report_id":record["report_id"], "product_id":record["product_id"], "user_id":record["user_id"],
"type_id":record["type_id"], "bug_id":record["bug_id"], "work_hours":record["work_hours"],
"report_content":record["report_content"], "work_date":str(record["work_date"]),
"insert_date":str(record["insert_date"]), "modify_date":str(record["modify_date"]),
"priority":record["priority"]
});
return json.dumps({"code":ErrorCode.Success, "data":ret});
def query_summary(self,
start_time="",
end_time="",
user_id="",
product_id="",
type_id="",
query_all=False):
if query_all:
sql = "select %s from %s where true" % (
"sum(work_hours) as work_hours", "dr_report")
else:
sql = "select %s from %s where %s" % (
"sum(work_hours) as work_hours", "dr_report,dr_user u",
"u.enabled = true and dr_report.user_id = u.user_id")
(names, params) = self.build_sql_conditions(start_time, end_time,
user_id, product_id,
type_id)
sql = "%s %s" % (sql, names)
records = sql_exec(sql, params)
ret = {
"code": ErrorCode.Success,
"data": {
"user_id": user_id,
"product_id": product_id,
"type_id": type_id,
"work_hours": records[0]["work_hours"]
}
}
return json.dumps(ret)
def GET(self, r=None):
enable_crossdomain();
records = sql_exec("select type_id,type_name from dr_type");
ret = [];
for record in records:
ret.append({"id":record["type_id"], "value":record["type_name"]});
return json.dumps({"code":ErrorCode.Success, "data":ret});
def email_strategy_check(self, date):
mail = _config["mail"];
# check only when someone has submitted report.
if mail["strategy_check_only_someone_submited"]:
records = sql_exec("select user_id,email from dr_user where enabled=true and user_id in "
"(select distinct u.user_id from dr_user u, dr_report r where u.user_id = r.user_id and r.work_date=%s)"%(date));
if len(records) < mail["strategy_check_only_someone_submited_count"]:
trace("strategy_check_only_someone_submited is checked, "
"bug only %s submited(<%s), ignore and donot email."%(len(records), mail["strategy_check_only_someone_submited_count"]));
return False;
return True;
def email_strategy_check(self, date):
mail = _config["mail"];
# check only when someone has submitted report.
if mail["strategy_check_only_someone_submited"]:
records = sql_exec("select user_id,email from dr_user where enabled=true and user_id in "
"(select distinct u.user_id from dr_user u, dr_report r where u.user_id = r.user_id and r.work_date=%s)"%(date));
if len(records) < mail["strategy_check_only_someone_submited_count"]:
trace("strategy_check_only_someone_submited is checked, "
"bug only %s submited(<%s), ignore and donot email."%(len(records), mail["strategy_check_only_someone_submited_count"]));
return False;
return True;
def authorize_get_exception_user_id(user_id):
if user_id is None:
return [];
# check admin role, if admin, access all users.
records = sql_exec("select user_id from dr_authorize_admin where user_id='%s'"%(user_id));
if len(records) > 0:
return [];
# check manager role, if manager, access himself and all users managed by him.
records = sql_exec("select user_id from dr_user "
"where user_id!='%s' "
"and user_id not in(select user_id from dr_authorize_manager where manager_id='%s')"
%(user_id, user_id));
ret = [];
for record in records:
ret.append(record["user_id"]);
return ret;
def authorize_get_exception_user_id(user_id):
if user_id is None:
return [];
# check admin role, if admin, access all users.
records = sql_exec("select user_id from dr_authorize_admin where user_id='%s'"%(user_id));
if len(records) > 0:
return [];
# check manager role, if manager, access himself and all users managed by him.
records = sql_exec("select user_id from dr_user "
"where user_id!='%s' "
"and user_id not in(select user_id from dr_authorize_manager where manager_id='%s')"
%(user_id, user_id));
ret = [];
for record in records:
ret.append(record["user_id"]);
return ret;
def qq_oauth_query_available_associate_user(self, access_token, qq_oauth_openid):
# query all un-associated users.
users = [];
records = sql_exec(
"select user_id,user_name from dr_user where "
"user_id not in (select user_id from dr_authenticate) "
"and enabled=true");
for record in records:
users.append({"id":record["user_id"], "value":record["user_name"]});
return json.dumps({"code":ErrorCode.NotAssociated, "error":ErrorCode.NotAssociated,
"access_token":access_token, "qq_oauth_openid":qq_oauth_openid, "users":users,
"error_description":"user not found, please associate one"});
def GET(self, r=None):
enable_crossdomain()
records = sql_exec("select product_id,product_name from dr_product")
ret = []
for record in records:
ret.append({
"id": record["product_id"],
"value": record["product_name"]
})
return json.dumps({
"code": ErrorCode.Success,
"data": ret
})
def query_summary(self, start_time="", end_time="", user_id="", product_id="", type_id="", query_all=False):
if query_all:
sql = "select %s from %s where true"%("sum(work_hours) as work_hours", "dr_report");
else:
sql = "select %s from %s where %s"%("sum(work_hours) as work_hours",
"dr_report,dr_user u",
"u.enabled = true and dr_report.user_id = u.user_id");
(names, params) = self.build_sql_conditions(start_time, end_time, user_id, product_id, type_id);
sql = "%s %s"%(sql, names);
records = sql_exec(sql, params);
ret = {"code":ErrorCode.Success, "data":{
"user_id":user_id, "product_id":product_id, "type_id":type_id, "work_hours":records[0]["work_hours"]
}};
return json.dumps(ret);
def check_auth(*args, **kwargs):
# auth not enabled in config.
auth = _config["auth"];
if not auth["on"]:
return;
# method donot require check.
conditions = cherrypy.request.config.get('auth.require', None)
if conditions is None:
return;
# QQ-OAuth not enabled.
if auth["strategy"] == "qq_oauth":
trace("check session, session_id=%s"%(cherrypy.session.id));
# check QQ-OAuth session.
user_id = cherrypy.session.get(SESSION_KEY);
if user_id is None:
error("session invalid, check auth failed.");
enable_crossdomain();
raise cherrypy.HTTPError(401, "You are not authorized, login please.");
return;
# check user enabled.
if True:
user_id = cherrypy.session.get(SESSION_KEY);
trace("check whether user enabled. id=%s"%(user_id));
records = sql_exec("select user_id from dr_user where user_id='%s' and enabled=true",(user_id));
if len(records) <= 0:
error("user disabled, id=%s."%(user_id));
enable_crossdomain();
raise cherrypy.HTTPError(401, "You are disabled");
return;
# check condition.
for condition in conditions:
if not condition():
error("codition check invalid, check auth failed.");
enable_crossdomain();
raise cherrypy.HTTPError(401, "You are not authorized for specified condition");
return;
trace("check auth success. user_id=%s"%(user_id));
def require_admin():
user_id = cherrypy.session.get(SESSION_KEY);
records = sql_exec("select user_id from dr_authorize_admin where user_id='%s'"%(user_id));
return len(records) > 0;
class RESTAuth(object):
exposed = True
def qq_oauth_query_available_associate_user(self, access_token,
qq_oauth_openid):
# query all un-associated users.
users = []
records = sql_exec(
"select user_id,user_name from dr_user where "
"user_id not in (select user_id from dr_authenticate) "
"and enabled=true")
for record in records:
users.append({
"id": record["user_id"],
"value": record["user_name"]
})
return json.dumps({
"code":
ErrorCode.NotAssociated,
"error":
ErrorCode.NotAssociated,
"access_token":
access_token,
"qq_oauth_openid":
qq_oauth_openid,
"users":
users,
"error_description":
"user not found, please associate one"
})
def qq_oauth_get_associated(self, qq_oauth_openid):
return sql_exec(
"select u.user_id,u.user_name from dr_user u, dr_authenticate a "
"where u.enabled=true and u.user_id=a.user_id and a.qq_oauth_openid=%s",
(qq_oauth_openid))
def qq_oauth_auto_register(self, access_token, qq_oauth_openid):
auth = _config["auth"]
# https://graph.qq.com/user/get_user_info?access_token=71871H1H3187I31EQJK3197J3JWQ8Q0D&appid=8373636744&openid=87JDD73KH32W3983JIUDS92198DS5B32
# get user nickname as user_name, email empty
api = "%s?access_token=%s&appid=%s&openid=%s" % (
auth["qq_oauth_api_get_user_info"], access_token,
auth["qq_oauth_api_app_id"], qq_oauth_openid)
trace("auto register get user_info from %s" % (api))
# query qq_oauth_openid
url = urllib.urlopen(api)
data = url.read()
url.close()
json_data = data.strip().strip("callback").strip("(").strip(";").strip(
")").strip()
trace("trim get_user_info data to %s" % (json_data))
try:
res_json = json.loads(json_data)
except Exception, e:
error("ex=%s, info=%s" % (e, traceback.format_exc()))
return json.dumps({
"code": ErrorCode.Failed,
"error": ErrorCode.Failed,
"error_description": "userinfo to json error"
})
# check userinfo
if "error" in res_json:
return json.dumps({
"code":
ErrorCode.Failed,
"error":
ErrorCode.Failed,
"error_description":
"request userinfo error, response=%s" % (data)
})
if "nickname" not in res_json:
return json.dumps({
"code":
ErrorCode.Failed,
"error":
ErrorCode.Failed,
"error_description":
"request nickname invalid, response=%s" % (data)
})
nickname = res_json["nickname"]
trace("nickname=%s access_token=%s qq_oauth_openid=%s" %
(nickname, access_token, qq_oauth_openid))
# check exists.
user_name = nickname
records = sql_exec(
"select user_id from dr_user where user_name=%s and enabled=true",
(user_name))
# exists, change nickname with random postfix.
if len(records) != 0:
user_name = "%s%s" % (nickname, int(random.random() * 1000000))
# register user
sql_exec("insert into dr_user(user_name) values(%s)", (user_name))
records = sql_exec("select user_id from dr_user where user_name=%s",
(user_name))
user_id = records[0]["user_id"]
trace(
"auto insert user, access_token=%s, qq_oauth_openid=%s, user_id=%s"
% (access_token, qq_oauth_openid, user_id))
self.qq_oauth_register_associate(access_token, qq_oauth_openid,
user_id)
class RESTAdmin(object):
exposed = True
@require_auth(require_admin)
def POST(self):
enable_crossdomain()
(code, ret) = (ErrorCode.Success, [])
req_str = cherrypy.request.body.read()
try:
req = json.loads(req_str)
except Exception, e:
error(sys.exc_info)
return json.dumps({
"code": ErrorCode.Failed,
"error": ErrorCode.Failed,
"error_description": "to json error"
})
if req["action"] == "get_users":
ret = sql_exec("select * from dr_user")
elif req["action"] == "set_user":
ret = sql_exec(
"update dr_user set user_name=%s,email=%s,enabled=%s where user_id=%s",
(req["name"], req["email"], req["enabled"], req["id"]))
elif req["action"] == "create_user":
ret = sql_exec(
"insert into dr_user (user_name,email,enabled) values(%s,%s,%s)",
(req["name"], req["email"], req["enabled"]), True)
elif req["action"] == "get_user":
ret = sql_exec("select * from dr_user where user_id=%s",
(req["id"], ))
ret = ret[0]
elif req["action"] == "get_admins":
ret = sql_exec("select * from dr_authorize_admin")
elif req["action"] == "set_admin":
if req["admin"]:
ret = sql_exec(
"select * from dr_authorize_admin where user_id=%s",
(req["user_id"], ))
if len(ret) <= 0:
ret = sql_exec(
"insert into dr_authorize_admin (user_id) values(%s)",
(req["user_id"], ))
else:
ret = sql_exec(
"delete from dr_authorize_admin where user_id=%s",
(req["user_id"], ))
elif req["action"] == "get_user_group":
ret = sql_exec(
"select g.* from dr_group g, dr_rs_group_user r where g.group_id = r.group_id and r.user_id=%s",
(req["id"], ))
elif req["action"] == "set_user_group":
if req["in"]:
ret = sql_exec(
"select * from dr_rs_group_user where group_id=%s and user_id=%s",
(req["group_id"], req["user_id"]))
if len(ret) <= 0:
ret = sql_exec(
"insert into dr_rs_group_user (group_id,user_id) values(%s,%s)",
(req["group_id"], req["user_id"]))
else:
ret = sql_exec(
"delete from dr_rs_group_user where group_id=%s and user_id=%s",
(req["group_id"], req["user_id"]))
elif req["action"] == "get_groups":
ret = sql_exec("select * from dr_group")
elif req["action"] == "create_group":
ret = sql_exec("insert into dr_group(group_name) values(%s)",
(req["name"], ), True)
elif req["action"] == "set_group":
ret = sql_exec(
"update dr_group set group_name=%s where group_id=%s",
(req["name"], req["id"]))
elif req["action"] == "get_group":
ret = sql_exec("select * from dr_group where group_id=%s",
(req["group_id"], ))[0]
elif req["action"] == "get_group_user":
ret = sql_exec(
"select u.* from dr_user u, dr_rs_group_user r where u.user_id = r.user_id and r.group_id=%s",
(req["group_id"], ))
elif req["action"] == "get_products":
ret = sql_exec("select * from dr_product")
elif req["action"] == "create_product":
ret = sql_exec("insert into dr_product(product_name) values(%s)",
(req["name"], ), True)
elif req["action"] == "set_product":
ret = sql_exec(
"update dr_product set product_name=%s where product_id=%s",
(req["name"], req["id"]))
elif req["action"] == "get_types":
ret = sql_exec("select * from dr_type")
elif req["action"] == "create_type":
ret = sql_exec("insert into dr_type(type_name) values(%s)",
(req["name"], ), True)
elif req["action"] == "set_type":
ret = sql_exec("update dr_type set type_name=%s where type_id=%s",
(req["name"], req["id"]))
else:
error("invalid action for admin: %s, req=%s" %
(req["action"], req_str))
code = ErrorCode.Failed
return json.dumps({
"code": code,
"data": ret
})
def qq_oauth_get_associated(self, qq_oauth_openid):
return sql_exec("select u.user_id,u.user_name from dr_user u, dr_authenticate a "
"where u.enabled=true and u.user_id=a.user_id and a.qq_oauth_openid=%s", (qq_oauth_openid));
class RESTDailyReport(object):
exposed = True
'''
build the sql query conditions.
@return the builded sql.
'''
def build_sql_conditions(self, start_time, end_time, user_id, product_id,
type_id):
(names, params) = ("", [])
if start_time != "":
names += " and dr_report.work_date>=%s"
params.append(start_time)
if end_time != "":
names += " and dr_report.work_date<=%s"
params.append(end_time)
if product_id != "":
names += " and dr_report.product_id=%s"
params.append(product_id)
if type_id != "":
names += " and dr_report.type_id=%s"
params.append(type_id)
if user_id != "":
names += " and dr_report.user_id=%s"
params.append(user_id)
if len(params) == 0:
return (names, None)
return (names, tuple(params))
'''
query summary work hours, all users without group
'''
def query_summary(self,
start_time="",
end_time="",
user_id="",
product_id="",
type_id="",
query_all=False):
if query_all:
sql = "select %s from %s where true" % (
"sum(work_hours) as work_hours", "dr_report")
else:
sql = "select %s from %s where %s" % (
"sum(work_hours) as work_hours", "dr_report,dr_user u",
"u.enabled = true and dr_report.user_id = u.user_id")
(names, params) = self.build_sql_conditions(start_time, end_time,
user_id, product_id,
type_id)
sql = "%s %s" % (sql, names)
records = sql_exec(sql, params)
ret = {
"code": ErrorCode.Success,
"data": {
"user_id": user_id,
"product_id": product_id,
"type_id": type_id,
"work_hours": records[0]["work_hours"]
}
}
return json.dumps(ret)
'''
query detail info, all users without group
'''
def query_detail(self,
start_time="",
end_time="",
user_id="",
product_id="",
type_id="",
query_all=False):
if query_all:
sql = "select %s from %s where true" % (
"report_id,product_id,user_id,type_id,bug_id,work_hours,report_content,work_date,insert_date,modify_date,priority",
"dr_report")
else:
sql = "select %s from %s where %s" % (
"report_id,product_id,u.user_id,type_id,bug_id,work_hours,report_content,work_date,insert_date,modify_date,priority",
"dr_report,dr_user u",
"u.enabled = true and dr_report.user_id = u.user_id")
(names, params) = self.build_sql_conditions(start_time, end_time,
user_id, product_id,
type_id)
sql = "%s %s %s" % (sql, names, "order by dr_report.report_id asc")
records = sql_exec(sql, params)
ret = []
for record in records:
ret.append({
"report_id": record["report_id"],
"product_id": record["product_id"],
"user_id": record["user_id"],
"type_id": record["type_id"],
"bug_id": record["bug_id"],
"work_hours": record["work_hours"],
"report_content": record["report_content"],
"work_date": str(record["work_date"]),
"insert_date": str(record["insert_date"]),
"modify_date": str(record["modify_date"]),
"priority": record["priority"]
})
return json.dumps({
"code": ErrorCode.Success,
"data": ret
})
'''
query summary hours of specified group
'''
def query_summary_group(self,
group,
start_time="",
end_time="",
user_id="",
product_id="",
type_id="",
query_all=False):
if query_all:
sql = "select %s from %s where %s" % (
"sum(work_hours) as work_hours",
"dr_report,dr_user u,dr_group g,dr_rs_group_user rs",
"dr_report.user_id = rs.user_id and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s"
% (group))
else:
sql = "select %s from %s where %s" % (
"sum(work_hours) as work_hours",
"dr_report,dr_user u,dr_group g,dr_rs_group_user rs",
"u.enabled = true and dr_report.user_id = rs.user_id and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s"
% (group))
(names, params) = self.build_sql_conditions(start_time, end_time,
user_id, product_id,
type_id)
sql = "%s %s" % (sql, names)
records = sql_exec(sql, params)
ret = {
"code": ErrorCode.Success,
"data": {
"user_id": user_id,
"product_id": product_id,
"type_id": type_id,
"work_hours": records[0]["work_hours"]
}
}
return json.dumps(ret)
'''
query detail info of specified group
'''
def query_detail_group(self,
group,
start_time="",
end_time="",
user_id="",
product_id="",
type_id="",
query_all=False):
if query_all:
sql = "select %s from %s where %s" % (
"report_id,product_id,u.user_id,type_id,bug_id,work_hours,report_content,work_date,insert_date,modify_date,priority",
"dr_report,dr_user u,dr_group g,dr_rs_group_user rs",
"dr_report.user_id = rs.user_id and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s"
% (group))
else:
sql = "select %s from %s where %s" % (
"report_id,product_id,u.user_id,type_id,bug_id,work_hours,report_content,work_date,insert_date,modify_date,priority",
"dr_report,dr_user u,dr_group g,dr_rs_group_user rs",
"u.enabled = true and dr_report.user_id = rs.user_id and rs.user_id = u.user_id and g.group_id = rs.group_id and g.group_id = %s"
% (group))
(names, params) = self.build_sql_conditions(start_time, end_time,
user_id, product_id,
type_id)
sql = "%s %s %s" % (sql, names, "order by dr_report.report_id asc")
records = sql_exec(sql, params)
ret = []
for record in records:
ret.append({
"report_id": record["report_id"],
"product_id": record["product_id"],
"user_id": record["user_id"],
"type_id": record["type_id"],
"bug_id": record["bug_id"],
"work_hours": record["work_hours"],
"report_content": record["report_content"],
"work_date": str(record["work_date"]),
"insert_date": str(record["insert_date"]),
"modify_date": str(record["modify_date"]),
"priority": record["priority"]
})
return json.dumps({
"code": ErrorCode.Success,
"data": ret
})
@require_auth()
def GET(self,
group="",
start_time="",
end_time="",
summary="",
user_id="",
product_id="",
type_id="",
query_all="false",
r=None):
enable_crossdomain()
if query_all == True or query_all == "true" or str(query_all) == "1":
query_all = True
else:
query_all = False
# if not null, must be a digit.
if group != "" and str(group) != "-1" and not str(group).isdigit():
error("group must be digit, actual is %s" % (group))
raise cherrypy.HTTPError(400, "group must be digit")
trace(
'group=%s, start_time=%s, end_time=%s, summary=%s, user_id=%s, product_id=%s, type_id=%s, query_all=%s'
% (group, start_time, end_time, summary, user_id, product_id,
type_id, query_all))
if user_id != "":
authorize_user(user_id)
if group == "" or str(group) == "-1":
if summary == "1":
return self.query_summary(start_time, end_time, user_id,
product_id, type_id, query_all)
else:
return self.query_detail(start_time, end_time, user_id,
product_id, type_id, query_all)
else:
if summary == "1":
return self.query_summary_group(group, start_time, end_time,
user_id, product_id, type_id,
query_all)
else:
return self.query_detail_group(group, start_time, end_time,
user_id, product_id, type_id,
query_all)
@require_auth()
def POST(self):
enable_crossdomain()
req_json_str = cherrypy.request.body.read()
try:
req_json = json.loads(req_json_str)
except Exception, e:
error(sys.exc_info)
return json.dumps({
"code": ErrorCode.Failed,
"error": ErrorCode.Failed,
"error_description": "to json error"
})
user_id = req_json["user"]
work_date = req_json["date"]
# check authorize.
authorize_user(user_id)
# remove the removed reports
exists_reports = []
for item in req_json["items"]:
report_id = item["report_id"]
if report_id != "" and report_id != 0:
exists_reports.append(str(report_id))
if len(exists_reports) > 0:
sql_exec(
"delete from dr_report where user_id=%s and work_date=%s and report_id not in ("
+ ",".join(exists_reports) + ")", (user_id, work_date))
else:
sql_exec("delete from dr_report where user_id=%s and work_date=%s",
(user_id, work_date))
# update or insert new
for item in req_json["items"]:
report_id = item["report_id"]
product_id = item["product_id"]
type_id = item["type_id"]
bug_id = item["bug_id"]
report_content = item["report_content"]
work_hours = item["work_hours"]
priority = item["priority"]
if report_id != "" and report_id != 0:
ret = sql_exec(
"update dr_report set product_id=%s, user_id=%s, type_id=%s, bug_id=%s, work_hours=%s, priority=%s, report_content=%s, work_date=%s, modify_date=now() "
"where report_id=%s and (product_id!=%s or user_id!=%s or type_id!=%s or bug_id!=%s or work_hours!=%s or priority!=%s or report_content!=%s or work_date!=%s)",
(product_id, user_id, type_id, bug_id, work_hours,
priority, report_content, work_date, report_id,
product_id, user_id, type_id, bug_id, work_hours,
priority, report_content, work_date))
else:
ret = sql_exec(
"insert into dr_report (product_id, user_id, type_id, bug_id, work_hours, priority, report_content, work_date, insert_date, modify_date) values(%s, %s, %s, %s, %s, %s, %s, %s, now(), now())",
(product_id, user_id, type_id, bug_id, work_hours,
priority, report_content, work_date))
return json.dumps({
"code": ErrorCode.Success,
"error": ErrorCode.Success,
"desc": "success"
})
def qq_oauth_register_associate(self, access_token, qq_oauth_openid, user_id):
sql_exec("delete from dr_authenticate where user_id=%s and qq_oauth_openid=%s", (user_id, qq_oauth_openid));
sql_exec("insert into dr_authenticate (user_id, qq_oauth_openid, qq_oauth_access_token) values(%s, %s, %s)", (user_id, qq_oauth_openid, access_token));
trace("associate user id=%s to auth qq_oauth_openid=%s access_token=%s"%(user_id, qq_oauth_openid, access_token));
def qq_oauth_get_associated(self, qq_oauth_openid):
return sql_exec(
"select u.user_id,u.user_name from dr_user u, dr_authenticate a "
"where u.enabled=true and u.user_id=a.user_id and a.qq_oauth_openid=%s",
(qq_oauth_openid))
