package com.sogou.qadev.service.cynthia.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.sogou.qadev.service.cynthia.bean.UserInfo;
import com.sogou.qadev.service.cynthia.service.DbPoolConnection;
/**
* @description:TODO
* @author:backright db processor
* @mail:liming@sogou-inc.com
* @date:2014-5-6 下午5:14:18
* @version:v1.0
*/
public class BackRightAccessSessionMySQL {
/**
* @description:get all back right users
* @date:2014-5-6 下午5:14:39
* @version:v1.0
* @return
*/
public List<UserInfo> getBackRightUsers() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<UserInfo> allUsers = new ArrayList<UserInfo>();
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
String sql = "SELECT A.user_name, B.id,B.nick_name from event_user as A JOIN user_info as B on A.user_name = B.user_name order by A.user_name asc";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
UserInfo ui = new UserInfo();
ui.setId(rs.getInt("id"));
ui.setUserName(rs.getString("user_name"));
ui.setNickName(rs.getString("nick_name"));
allUsers.add(ui);
}
}catch(Exception e)
{
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
return allUsers;
}
/**
* @description:add back right user
* @date:2014-5-6 下午5:14:55
* @version:v1.0
* @param userMail
* @return
*/
public boolean addBackRightUser(String userMail) {
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "insert ignore into event_user(user_name,event_id) values(?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userMail);
pstmt.setInt(2, 2);
boolean isSuccess = pstmt.executeUpdate() >0;
return isSuccess;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:delete back right user
* @date:2014-5-6 下午5:15:14
* @version:v1.0
* @param userMail
* @return
*/
public boolean delBackRightUser(String userMail) {
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "delete from event_user where user_name = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userMail);
return pstmt.executeUpdate() > 0;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:get template right users
* @date:2014-5-6 下午5:15:26
* @version:v1.0
* @param templateId
* @return
*/
public List<UserInfo> getTemplateRightUser(String templateId) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<UserInfo> allUsers = new ArrayList<UserInfo>();
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
String sql = "SELECT A.admin_user, B.id,B.nick_name from template_admin_user as A left JOIN user_info as B on A.admin_user = B.user_name where A.template_id = ? order by A.admin_user asc";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, templateId);
rs = pstmt.executeQuery();
while(rs.next()) {
UserInfo ui = new UserInfo();
if (rs.getString("admin_user") != null && rs.getString("admin_user").equals("*")) {
ui.setUserName("*");
ui.setNickName("所有人");
}else {
ui.setId(rs.getInt("id"));
ui.setUserName(rs.getString("admin_user"));
ui.setNickName(rs.getString("nick_name"));
}
allUsers.add(ui);
}
}catch(Exception e)
{
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
return allUsers;
}
/**
* @description:delete user template right
* @date:2014-5-6 下午5:15:37
* @version:v1.0
* @param templateId
* @param userMail
* @return
*/
public boolean delUserTemplateRight(String templateId, String userMail) {
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "delete from template_admin_user where template_id = ? and admin_user=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, templateId);
pstmt.setString(2, userMail);
return pstmt.executeUpdate() > 0;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:add user template right
* @date:2014-5-6 下午5:15:55
* @version:v1.0
* @param templateIds
* @param userMail
* @return
*/
public boolean addUserTemplateRight(String[] templateIds, String userMail) {
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
conn.setAutoCommit(false);
String sql = "insert ignore into template_admin_user(template_id,admin_user) values(?,?)";
pstmt = conn.prepareStatement(sql);
for (String templateId : templateIds) {
pstmt.setString(1, templateId);
pstmt.setString(2, userMail);
pstmt.addBatch();
}
pstmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);//提交完成后回复现场将Auto commit,还原为true,
return true;
}catch(Exception e)
{
e.printStackTrace();
try {
if (!conn.isClosed()) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:query all template rights of user
* @date:2014-5-6 下午5:16:09
* @version:v1.0
* @param userMail
* @return:template id name map
*/
public Map<String, String> queryUserTemplateRights(String userMail) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Map<String, String> templateRightMap = new HashMap<String, String>();
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
String sql = "SELECT A.admin_user, B.id,B.name from template_admin_user as A left JOIN template as B on A.template_id = B.id where A.admin_user = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userMail);
rs = pstmt.executeQuery();
while(rs.next()) {
templateRightMap.put(rs.getString("id"), rs.getString("name"));
}
}catch(Exception e)
{
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
return templateRightMap;
}
/**
* @description:set system setting(json)
* @date:2014-5-6 下午5:16:33
* @version:v1.0
* @param systemJson
* @return
*/
public boolean setSystemOption(String systemJson) {
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "update system_set set value=? where set_name='system'";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, systemJson);
return pstmt.executeUpdate() > 0;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:get system setting
* @date:2014-5-6 下午5:16:46
* @version:v1.0
* @param userMail
* @return
*/
public String getSystemOption(String userMail) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String setJson = "";
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "select value from system_set where set_name = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userMail);
rs = pstmt.executeQuery();
if (rs.next()) {
setJson = rs.getString("value");
}
}catch(Exception e)
{
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeAll(rs, pstmt, conn);
}
return setJson;
}
/**
* @description:get template user right
* @date:2014-5-6 下午5:16:56
* @version:v1.0
* @param templateId
* @param userMail
* @return
*/
public boolean deltemplateUserRight(String templateId, String userMail) {
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = DbPoolConnection.getInstance().getConnection();
String sql = "delete from template_admin_user where template_id = ? and admin_user=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, templateId);
pstmt.setString(2, userMail);
return pstmt.executeUpdate() > 0;
}catch(Exception e)
{
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:add template right user
* @date:2014-5-6 下午5:17:08
* @version:v1.0
* @param templateId
* @param users
* @return
*/
public boolean addtemplateUserRight(String templateId, String[] users) {
Connection conn = null;
PreparedStatement pstmt = null;
if(users == null || users.length == 0)
return false;
try
{
conn = DbPoolConnection.getInstance().getConnection();
conn.setAutoCommit(false);
String sql = "insert ignore into template_admin_user(template_id,admin_user) values(?,?)";
pstmt = conn.prepareStatement(sql);
for (String user : users) {
pstmt.setString(1, templateId);
pstmt.setString(2, user);
pstmt.addBatch();
}
pstmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);//提交完成后回复现场将Auto commit,还原为true,
return true;
}catch(Exception e)
{
e.printStackTrace();
try {
if (!conn.isClosed()) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(pstmt);
DbPoolConnection.getInstance().closeConn(conn);
}
}
}