content: package com.alimama.mdrill.ui.service;
  
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.text.SimpleDateFormat;
  import java.util.HashMap;
  import java.util.Map;
  import java.util.Map.Entry;
  
  import org.apache.log4j.Logger;
  
  import backtype.storm.utils.Utils;
  
  import com.alimama.mdrill.adhoc.MySqlConn;
  import com.alimama.mdrill.json.JSONArray;
  import com.alimama.mdrill.json.JSONException;
  import com.alimama.mdrill.json.JSONObject;
  
  public class UserJson {
  	private static Logger LOG = Logger.getLogger(UserJson.class);
  
  	private static MySqlConn getConn()
  	{
  		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);
  		
  		return m_fpsql;
  	}
  	
  	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() throws JSONException, SQLException
  	{
  		return getJson(null,false);
  	}
  	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 void addjson(String json,String user) throws Exception
  	{
  		JSONObject jsonObj = new JSONObject(json);
  		String userid=jsonObj.getString("userid");
  		HashMap<String,String> val=new HashMap<String, String>();
  		val.put("userid", jsonObj.getString("userid"));
  		val.put("email", jsonObj.getString("email"));
  		val.put("cname", jsonObj.getString("cname"));
  		val.put("role", String.valueOf(jsonObj.getInt("role")));
  		val.put("permission", jsonObj.getJSONArray("permission").toString());
  		add(userid, val,user);
  	}
  
  	
  	
  	public static String add(String userid,Map<String,String> val,String user) throws Exception
  	{
  		del(userid,user);
  		return create(userid, val,user);
  	}
  	
  	public static String del(String userid,String user) throws Exception
  	{
  		JSONObject jsonObj = new JSONObject();
  		jsonObj.put("code", "0");
  		MySqlConn m_fpsql = getConn();
  
  		
  		Connection conn = m_fpsql.getConn();
  		String strSql = "delete from users_json  where userid=? ";
  		PreparedStatement m_fps = conn.prepareStatement(strSql);
  		try {
  			int index=1;
  			m_fps.setString(index++, userid);
  			m_fps.executeUpdate();
  			jsonObj.put("code", "1");
  		} catch (Exception e) {
  			jsonObj.put("message", e.toString());
  			jsonObj.put("code", "0");
  		}finally{
  			m_fps.close();
  			m_fpsql.close();
  		}
  		
  		return jsonObj.toString();
  	}
  	
  	
  	public static String create(String userid,Map<String,String> val,String user) throws Exception
  	{
  		MySqlConn m_fpsql = getConn();
  		val.put("opuser", String.valueOf(user));
  		val.put("optime", getNowTime());
  		JSONObject jsonObj = new JSONObject();
  
  		StringBuffer sqlbuffer=new StringBuffer();
  		StringBuffer sqlbuffer2=new StringBuffer();
  
  		String joinchar="";
  		String[] indexval=new String[val.size()];
  		int index=0;
  		for(Entry<String, String> e:val.entrySet())
  		{
  			sqlbuffer.append(joinchar).append(e.getKey());
  			sqlbuffer2.append(joinchar).append("?");
  
  			joinchar=",";
  			indexval[index]=e.getValue();
  			index++;
  		}
  		
  		Connection conn = m_fpsql.getConn();
  		String strSql = "insert into users_json " +
  				"("+sqlbuffer+")" +
  				"values" +
  				"("+sqlbuffer2+")";
  		PreparedStatement m_fps = conn.prepareStatement(strSql);
  		try {
  			for(int i=0;i<indexval.length;i++)
  			{
  				m_fps.setString(i+1, indexval[i]);
  
  			}
  			m_fps.executeUpdate();
  			jsonObj.put("code", "1");
  
  			jsonObj.put("____debug", m_fps.toString());
  		} catch (Exception e) {
  			jsonObj.put("____debug2", m_fps.toString());
  			jsonObj.put("____debugerror", e.toString());
  			jsonObj.put("code", "0");
  		}finally{
  			m_fps.close();
  			m_fpsql.close();
  		}
  		return jsonObj.toString();
  	}
  
  	private static String getNowTime()
  	{
  		SimpleDateFormat fmt=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  		
  		return fmt.format(new java.util.Date());
  
  	}
  	public String update(String userid,Map<String,String> val,String user) throws SQLException
  	{
  		MySqlConn m_fpsql = getConn();
  		val.put("opuser", String.valueOf(user));
  		val.put("optime", getNowTime());
  		StringBuffer sqlbuffer=new StringBuffer();
  		String joinchar="";
  		String[] indexval=new String[val.size()];
  		int index=0;
  		for(Entry<String, String> e:val.entrySet())
  		{
  			sqlbuffer.append(joinchar).append(e.getKey()+"=?");
  			joinchar=",";
  			indexval[index]=e.getValue();
  			index++;
  		}
  		
  		Connection conn = m_fpsql.getConn();
  		String strSql = "update users_json set " +
  				" "+sqlbuffer+" where userid=? ";
  		System.out.println(strSql);
  		PreparedStatement m_fps = conn.prepareStatement(strSql);
  		try {
  			for(int i=0;i<indexval.length;i++)
  			{
  				m_fps.setString(i+1, indexval[i]);
  
  			}
  			m_fps.setString(indexval.length+1, userid);
  
  			m_fps.executeUpdate();
  			String fullstrSql=m_fps.toString();
  			return fullstrSql;
  		} catch (Exception e) {
  			LOG.error("updatePercent"+m_fps.toString(),e);
  		}finally{
  			m_fps.close();
  			m_fpsql.close();
  		}
  		return "";
  	}
  }
  