public static void addJsonList(String json, String user) throws Exception {
JSONObject jsonObj = new JSONObject(json);
JSONArray list = jsonObj.getJSONObject("data").getJSONArray("users");
for (int i = 0; i < list.length(); i++) {
JSONObject obj = list.getJSONObject(i);
addjson(obj.toString(), user);
}
}
public static String getJson(String daystart, boolean extamsg)
throws JSONException, SQLException {
JSONObject jsonObj = new JSONObject();
jsonObj.put("code", "1");
MySqlConn m_fpsql = getConn();
String strSql =
"select userid,email,cname,role,permission,'-' as queryday,opuser,optime from users_json order by role desc,userid";
if (daystart != null && !daystart.isEmpty()) {
strSql =
" select userid,email,cname,role,permission,opuser,optime from users_json where (cname not in (select nick from query_analyser.day_user_pv where queryday>'"
+ daystart
+ "')) order by role desc,userid";
}
if (extamsg) {
strSql =
" select a.userid as userid,a.email as email ,a.cname as cname,a.role as role,a.permission as permission,a.opuser as opuser,a.optime as optime, b.queryday as queryday "
+ "from ( "
+ ""
+ strSql
+ ") "
+ " a "
+ " left join (select nick,max(queryday) as queryday from query_analyser.day_user_pv group by nick ) b"
+ " on a.cname=b.nick order by role desc,queryday desc";
}
jsonObj.put("_exehql", strSql);
Connection conn = m_fpsql.getConn();
Statement stmt = conn.createStatement();
try {
ResultSet res = stmt.executeQuery(strSql);
JSONArray userlist = new JSONArray();
while (res.next()) {
JSONObject item = new JSONObject();
item.put("userid", String.valueOf(res.getString("userid")));
item.put("email", String.valueOf(res.getString("email")));
item.put("queryday", String.valueOf(res.getString("queryday")));
item.put("cname", String.valueOf(res.getString("cname")));
item.put("role", Integer.parseInt(res.getString("role")));
item.put("permission", new JSONArray(res.getString("permission")));
item.put("opuser", String.valueOf(res.getString("opuser")));
item.put("optime", String.valueOf(res.getString("optime")));
userlist.put(item);
}
m_fpsql.close();
jsonObj.put("code", "1");
jsonObj.put("message", "success");
JSONObject dddd = new JSONObject();
dddd.put("users", userlist);
jsonObj.put("data", dddd);
} catch (Exception e) {
jsonObj.put("message", e.toString());
jsonObj.put("code", "0");
} finally {
m_fpsql.close();
}
return jsonObj.toString();
}
public static String getUserTables(
String username, int start, int rows, int type, String callback)
throws SQLException, JSONException {
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
Map stormconf = Utils.readStormConfig();
String connstr = (String) stormconf.get("higo.download.offline.conn");
String uname = (String) stormconf.get("higo.download.offline.username");
String passwd = (String) stormconf.get("higo.download.offline.passwd");
MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);
Connection conn = m_fpsql.getConn();
Statement stmt = conn.createStatement();
String strsqlJoin =
"select '1' as source "
+ ",tableShowName as tableShowName"
+ ",tableName as tableName"
+ ",colsShowName as colsShowName"
+ ",colsName as colsName"
+ ",colsType as colsType"
+ ",splitString as splitString"
+ ",txtStorePath as txtStorePath"
+ ",indexStorePath as indexStorePath"
+ ",'0' as extval"
+ ",'0' as isfinish"
+ ",status as status"
+ ",username as username"
+ ",createtime as createtime"
+ ",lastuptime as lastuptime"
+ ",joins as joins"
+ ",'2' as stage"
+ ",percent as percent"
+ ",resultkb as resultkb"
+ " from adhoc_joins where username='******'", "")
+ "' and status<>'DEL' ";
StringBuffer bufferSql = new StringBuffer();
if (type == 0) // 个人中心列表
{
String strsqlDownload =
"select '2' as source "
+ ",jobname as tableShowName"
+ ",uuid as tableName"
+ ",'empty' as colsShowName"
+ ",'empty' as colsName"
+ ",'empty' as colsType"
+ ",'empty' as splitString"
+ ",storedir as txtStorePath"
+ ",'empty' as indexStorePath"
+ ",extval as extval"
+ ",isfinish as isfinish"
+ ",'INDEX' as status"
+ ",username as username"
+ ",starttime as createtime"
+ ",endtime as lastuptime"
+ ",'' as joins"
+ ",stage as stage"
+ ",percent as percent"
+ ",resultkb as resultkb"
+ " from adhoc_download where username='******'", "")
+ "' and status<>'DEL' ";
bufferSql.append(
"select source,tableShowName,tableName,colsShowName"
+ ",colsName,colsType,splitString,txtStorePath,indexStorePath,extval,status,username,"
+ "createtime,lastuptime,joins,stage,percent,resultkb");
bufferSql.append(
" from ("
+ strsqlJoin
+ " union "
+ strsqlDownload
+ ") tmp order by tmp.createtime desc limit "
+ start
+ ","
+ rows
+ " ");
}
if (type == 1) // for join
{
bufferSql.append(
strsqlJoin
+ " and status='INDEX' order by createtime desc limit "
+ start
+ ","
+ rows
+ " ");
}
String sql = bufferSql.toString();
TableJoin.LOG.info("getUserTables:" + sql);
ResultSet res = stmt.executeQuery(sql);
JSONObject jsonObj = new JSONObject();
jsonObj.put("code", "1");
jsonObj.put("_exehql", sql);
JSONArray jsonArray = new JSONArray();
while (res.next()) {
JSONObject item = new JSONObject();
item.put("source", res.getString("source"));
item.put("tableShowName", res.getString("tableShowName")); // 展示名称
item.put("tableName", res.getString("tableName")); // uuid
item.put("colsShowName", res.getString("colsShowName"));
item.put("colsName", res.getString("colsName"));
item.put("colsType", res.getString("colsType"));
item.put("splitString", res.getString("splitString"));
item.put("txtStorePath", res.getString("txtStorePath"));
item.put("indexStorePath", res.getString("indexStorePath"));
item.put("extval", res.getString("extval"));
item.put("status", res.getString("status"));
item.put("username", res.getString("username"));
item.put("createtime", res.getString("createtime"));
item.put("lastuptime", res.getString("lastuptime"));
item.put("joins", res.getString("joins"));
item.put("stage", res.getString("stage"));
item.put("percent", res.getString("percent"));
item.put("resultkb", res.getString("resultkb"));
boolean issuccess =
res.getString("status").equals("INDEX") && res.getString("extval").equals("0");
item.put(
"proccess", parsePercent(res.getString("stage"), res.getString("percent"), issuccess));
item.put("isActive", String.valueOf(issuccess));
jsonArray.put(item);
}
HashMap<String, String> cnt = getUserTablesCount(username, type);
JSONObject data = new JSONObject();
data.put("list", jsonArray);
data.put("total", cnt.get("cnt"));
jsonObj.put("data", data);
jsonObj.put("total_debug", new JSONObject(cnt));
m_fpsql.close();
if (callback != null && callback.length() > 0) {
return callback + "(" + jsonObj.toString() + ")";
} else {
return jsonObj.toString();
}
}