GZ/nwyj/.svn/pristine/e7/e7dc1a0eeb473fd227c75796945b3879d78779fc.svn-base

package com.sinosoft.lz.system.logon.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.log4j.Logger;

import com.persistence.DbConnection;
import com.sinosoft.lz.system.logon.vo.LoginInfo;

public class LoginDao{

	private final Logger	log	= Logger.getLogger(getClass());
	
	
	public LoginInfo checkLoginPw(String userName, String password) {

		// 构造查询用户的sql语句
		StringBuffer sb = new StringBuffer();

		// 登录认证开始
		sb.append("select * from sys_user_info where login_name = ? and password = ? ");

		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		LoginInfo loginInfo = new LoginInfo();
		try {
			
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sb.toString());
			//转化为MD5
			String md5psw= password.trim();
			pstm.setString(1, userName);
			pstm.setString(2, md5psw);
			rs = pstm.executeQuery();

			boolean flag = rs.next();
			if(flag){
					//还没超出限制，登录成功 设置st=0
					loginInfo.setSt("0");
					loginInfo.setFd_objectid(rs.getString("FD_OBJECTID"));
					loginInfo.setUser_id(rs.getString("USER_ID"));
					loginInfo.setLogin_name(rs.getString("LOGIN_NAME"));
					loginInfo.setPassword(rs.getString("PASSWORD"));
					loginInfo.setEmploy_no(rs.getString("EMPLOY_NO"));
					loginInfo.setReal_name(rs.getString("REAL_NAME"));
					loginInfo.setContingency_type(rs.getString("CONTINGENCY_TYPE"));
					loginInfo.setDept_id(rs.getString("DEPT_ID"));
					loginInfo.setOffice_phone(rs.getString("OFFICE_PHONE"));
					loginInfo.setMobile(rs.getString("MOBILE"));
					loginInfo.setEmail(rs.getString("EMAIL"));
					loginInfo.setCompany_id(rs.getString("COMPANY_ID"));
					loginInfo.setOrg_id(rs.getString("ORG_ID"));
					loginInfo.setPosition(rs.getString("POSITION"));
					loginInfo.setLogin_date(rs.getString("LOGIN_DATE"));
					loginInfo.setLogin_time(rs.getString("LOGIN_TIMES"));
					loginInfo.setSaphruserid(rs.getString("SAPHRUSERID"));
					log.info(loginInfo.toString());

					return loginInfo;
				
			}else{
				//找不到用户，设置3 密码错误
				loginInfo.setSt("1");
				return loginInfo;
				
			}
			
		} catch (Exception e) {
			log.error("根据用户密码查询用户信息错误:" + e.getMessage());
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("关闭数据库连接错误:" + e.getMessage());
			}

		}
		return loginInfo;
	}
	
	
	public LoginInfo checkLogin(String userName) {

		// 构造查询用户的sql语句
		StringBuffer sb = new StringBuffer();

		// 登录认证开始
		sb.append("select * from sys_user_info where login_name = ? ");

		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		LoginInfo loginInfo = new LoginInfo();
		try {
			
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sb.toString());
			//转化为MD5
//			String md5psw= password.trim();
			pstm.setString(1, userName);
//			pstm.setString(2, md5psw);
			rs = pstm.executeQuery();

			boolean flag = rs.next();
			if(flag){
					//还没超出限制，登录成功 设置st=0
					loginInfo.setSt("0");
					loginInfo.setFd_objectid(rs.getString("FD_OBJECTID"));
					loginInfo.setUser_id(rs.getString("USER_ID"));
					loginInfo.setLogin_name(rs.getString("LOGIN_NAME"));
					loginInfo.setPassword(rs.getString("PASSWORD"));
					loginInfo.setEmploy_no(rs.getString("EMPLOY_NO"));
					loginInfo.setReal_name(rs.getString("REAL_NAME"));
					loginInfo.setContingency_type(rs.getString("CONTINGENCY_TYPE"));
					loginInfo.setDept_id(rs.getString("DEPT_ID"));
					loginInfo.setOffice_phone(rs.getString("OFFICE_PHONE"));
					loginInfo.setMobile(rs.getString("MOBILE"));
					loginInfo.setEmail(rs.getString("EMAIL"));
					loginInfo.setCompany_id(rs.getString("COMPANY_ID"));
					loginInfo.setOrg_id(rs.getString("ORG_ID"));
					loginInfo.setPosition(rs.getString("POSITION"));
					loginInfo.setLogin_date(rs.getString("LOGIN_DATE"));
					loginInfo.setLogin_time(rs.getString("LOGIN_TIMES"));
					loginInfo.setSaphruserid(rs.getString("SAPHRUSERID"));
					log.info(loginInfo.toString());

					return loginInfo;
				
			}else{
				//找不到用户，设置3 密码错误
				loginInfo.setSt("3");
				return loginInfo;
				
			}
			
		} catch (Exception e) {
			log.error("根据用户密码查询用户信息错误:" + e.getMessage());
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("关闭数据库连接错误:" + e.getMessage());
			}

		}
		return loginInfo;
	}
	
	public void setLoginDate(String login_name ){
				//String sql = "update sys_user_info set LOGIN_DATE = ? , LOGIN_TIMES = ? where login_name = ? ";
				String sql = "update sys_user_info set LOGIN_DATE = ?  where login_name = ? ";
				DbConnection dbConn = new DbConnection();
				Connection conn = null;
				PreparedStatement pstm = null;
				
					try {
						conn = dbConn.getConnection();
						pstm = conn.prepareStatement(sql);
						//获取时间日期
						/*Date date = new Date();
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						String[] dateStr = sdf.format(date).split(" ");
						String dateStr1 = dateStr[0];
						String dateStr2 = dateStr[1];*/
						Date date = new Date();
						SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						//SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm:ss");
						String dateStr1 = sdf1.format(date);
						//String dateStr2 = sdf2.format(date);
						
						pstm.setString(1, dateStr1);
						//pstm.setString(2, dateStr2);
						pstm.setString(2, login_name);
						int result  = pstm.executeUpdate();
						if(result == 1){
							log.info("保存时间成功");
						}
						
					} catch (ClassNotFoundException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally{
						try {
							pstm.close();
							conn.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							log.error("关闭数据库连接错误:"+e.getMessage());
						}
					}
	}
	
	
	/*public LoginInfo checkLogin(String userName, String password) {

		// 构造查询用户的sql语句
		StringBuffer sb = new StringBuffer();

		// 登录认证开始
		sb.append("select  decode(a.login_times-4,'1','1','0') as TIMESLIMIT,");
		sb.append("decode(sign(getDate()-(to_date(login_date)+NUMTODSINTERVAL(0.5, 'hour'))),1,'0','1') as TIMELIMIT,");
		sb.append("DECODE(A.PASSWORD,?,'1','0') AS PWD,");
		sb.append("FD_OBJECTID,USER_ID,LOGIN_NAME,PASSWORD,EMPLOY_NO,REAL_NAME,CONTINGENCY_TYPE,DEPT_ID,OFFICE_PHONE,MOBILE,EMAIL,COMPANY_ID,ORG_ID,POSITION,IS_DEL,MOD_DATE,LOGIN_DATE,"
				+ "30-ROUND((getDate()-to_Date(LOGIN_DATE))*24*60) AS LASTLOGINDATE ,NVL(LOGIN_TIMES,'0') LOGIN_TIMES "
				+ " from sys_user_info a where login_name = ? and is_del ='0'");
		//sb.append("and ((login_times <5) or (getDate()>to_date(login_date)+NUMTODSINTERVAL(0.5,'hour') and login_times>5))");
		//登陆后处理登录次数和登录时间
		StringBuffer sb2 = new StringBuffer();
		//sb2.append("update sys_user_info set login_times = ? , login_date=getDate() where login_name = ?");
		
		
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm2 = null;
		ResultSet rs = null;
		LoginInfo loginInfo = new LoginInfo();
		try {
			
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sb.toString());
			//转化为MD5
			String md5psw= password.trim();
			pstm.setString(2, userName);
			pstm.setString(1, md5psw);
			rs = pstm.executeQuery();
			*//**
			 * rs存在那么表示用户名密码没问题  
			 * 
			 *//*
			boolean flag = rs.next();
			if(flag){
				//用户名密码正确
				boolean is_times_limit = "1".equals(rs.getString("TIMESLIMIT"));
				boolean is_time_limit = "1".equals(rs.getString("TIMELIMIT"));
				boolean is_try_many = is_times_limit && is_time_limit;
				boolean error_pwd = "0".equals(rs.getString("PWD"));
				//最后一次登录的时间
				String loginDate = rs.getString("LASTLOGINDATE");
				//剩下尝试次数
				String loginTimesLimit=(4-Integer.parseInt(rs.getString("LOGIN_TIMES")))+"";
				
				 if(is_try_many){
					//设置st=1 表示半小时后再来尝试
					loginInfo.setSt("1");
					loginInfo.setMod_date(loginDate);
					return loginInfo;
				};
				if(error_pwd){
					//密码错误（如果记录登录时间大于30分钟 则清零）
					sb2.append("update sys_user_info set login_times = decode(sign(getDate()-(to_date(login_date)+NUMTODSINTERVAL(0.5, 'hour'))),1,'0',login_times+1) , login_date=getDate() where login_name = ?");
					pstm2 = conn.prepareStatement(sb2.toString());
					//记录登录次数

					pstm2.setString(1, userName);
					pstm2.executeUpdate();
					loginInfo.setSt("2");
					loginInfo.setMod_date(loginTimesLimit);
					return loginInfo;
				}else{
					//还没超出限制，登录成功 设置st=0
					loginInfo.setSt("0");
					loginInfo.setFd_objectid(rs.getString("FD_OBJECTID"));
					loginInfo.setUser_id(rs.getString("USER_ID"));
					loginInfo.setLogin_name(rs.getString("LOGIN_NAME"));
					loginInfo.setPassword(rs.getString("PASSWORD"));
					loginInfo.setEmploy_no(rs.getString("EMPLOY_NO"));
					loginInfo.setReal_name(rs.getString("REAL_NAME"));
					loginInfo.setContingency_type(rs.getString("CONTINGENCY_TYPE"));
					loginInfo.setDept_id(rs.getString("DEPT_ID"));
					loginInfo.setOffice_phone(rs.getString("OFFICE_PHONE"));
					loginInfo.setMobile(rs.getString("MOBILE"));
					loginInfo.setEmail(rs.getString("EMAIL"));
					loginInfo.setCompany_id(rs.getString("COMPANY_ID"));
					loginInfo.setOrg_id(rs.getString("ORG_ID"));
					loginInfo.setPosition(rs.getString("POSITION"));
					
					log.info(loginInfo.toString());
					
					sb2.append("update sys_user_info set login_times = '0' , login_date=getDate() where login_name = ?");
					pstm2 = conn.prepareStatement(sb2.toString());
					//清空登录次数

					pstm2.setString(1, userName);
					pstm2.executeUpdate();
					
					return loginInfo;
					
				}
				
			}else{
				//找不到用户，设置3 密码错误
				loginInfo.setSt("3");
				return loginInfo;
				
			}
			
		} catch (Exception e) {
			log.error("根据用户密码查询用户信息错误:" + e.getMessage());
		} finally {
			try {
				rs.close();
				pstm.close();
				if(pstm2!=null){
					pstm2.close();
				}
				
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("关闭数据库连接错误:" + e.getMessage());
			}

		}
		return loginInfo;
	}*/

	public String[] getRoleIdsByUserId(String userId) {
		StringBuffer sb = new StringBuffer();
		sb.append("select distinct r.role_id,r.role_name from sys_role r, sys_user_ROLE_REL ur where ur.role_id = r.role_id and ur.user_id = ? and ur.is_del='0'");
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String roleids = "";
		String rolenames = "";
		try {
			conn = dbConn.getConnection();
			log.info(sb.toString());
			pstm = conn.prepareStatement(sb.toString());
			pstm.setString(1, userId);
			rs = pstm.executeQuery();
			while (rs.next()) {
				roleids += rs.getString("role_id") + ",";
				rolenames += rs.getString("role_name") + ",";
				log.info(roleids);
			}
			if(roleids!=null && !"".equals(roleids)){
				roleids = roleids.substring(0,roleids.lastIndexOf(","));
			}
			if(rolenames!=null && !"".equals(rolenames)){
				rolenames = rolenames.substring(0,rolenames.lastIndexOf(","));
			}
		} catch (Exception e) {
			log.error("获取roleids错误:" + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("关闭数据库连接错误:" + e.getMessage());
			}
		}
		return new String[]{roleids,rolenames};
	}

	public String[] getDeptsByUserId(String userId) {
		StringBuffer sb = new StringBuffer();
//		sb.append("select d.dept_id,d.dept_name ,d.parent_id ,d.dept_type,d.corp_name,d.corp_id from SYS_DEPARTMENT d ,SYS_DEPT_USER du where du.dept_id = d.dept_id and du.user_id = ?  order by d.dept_id ");
		sb.append(" select distinct ");
		sb.append("         d.dept_id  , ");
		sb.append("         d.dept_name, ");
		sb.append("         d.parent_id, ");
		sb.append("         d.dept_type, ");
		sb.append("         d.corp_name, ");
		sb.append("         d.corp_id, ");
		sb.append("         pd.corp_id as pcorp_id, ");
		sb.append("         pd.corp_name as pcorp_name ");
		sb.append(" from ");
		sb.append("          SYS_USER_INFO du ");
		sb.append("         left join SYS_DEPARTMENT d on du.dept_id = d.dept_id ");
		sb.append("         left join SYS_DEPARTMENT cd on cd.dept_id = d.corp_id ");
		sb.append("         left join SYS_DEPARTMENT pd on pd.dept_id = cd.parent_id ");
		sb.append(" where ");
		sb.append("         du.user_id = ? ");
		sb.append(" order by ");
		sb.append("         d.dept_id ");
		
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String deptids = "";
		String deptnames = "";
		String parent_ids = "";
		String dept_types = "";
		String corp_ids = "";
		String corp_names = "";
		String pcorp_ids = "";
		String pcorp_names = "";
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sb.toString());
			pstm.setString(1, userId);
			rs = pstm.executeQuery();
			while (rs.next()) {
				if(!"".equals(rs.getString("dept_id"))&&rs.getString("dept_id")!=null&&!"null".equals(rs.getString("dept_id"))){
					
					deptids += rs.getString("dept_id") + ",";
					deptnames += rs.getString("dept_name") + ",";
					parent_ids += rs.getString("parent_id") + ",";
					dept_types += rs.getString("dept_type") + ",";
					corp_ids += rs.getString("corp_id") + ",";
					corp_names += rs.getString("corp_name") + ",";
					pcorp_ids += rs.getString("pcorp_id") + ",";
					pcorp_names += rs.getString("pcorp_name") + ",";
					log.info(deptids);
				}
			}
			if(deptids!=null&&!"".equals(deptids)){
				deptids = deptids.substring(0, deptids.lastIndexOf(","));
			}
			if(deptnames!=null&&!"".equals(deptnames)){
				deptnames = deptnames.substring(0, deptnames.lastIndexOf(","));
			}
			if(parent_ids!=null&&!"".equals(parent_ids)){
				parent_ids = parent_ids.substring(0,parent_ids.lastIndexOf(","));
			}
			if(dept_types!=null&&!"".equals(dept_types)){
				dept_types = dept_types.substring(0,dept_types.lastIndexOf(","));
			}
			if(corp_ids!=null&&!"".equals(corp_ids)){
				corp_ids = corp_ids.substring(0,corp_ids.lastIndexOf(","));
			}
			if(corp_names!=null&&!"".equals(corp_names)){
				corp_names = corp_names.substring(0,corp_names.lastIndexOf(","));
			}
			if(pcorp_ids!=null&&!"".equals(pcorp_ids)){
				pcorp_ids = pcorp_ids.substring(0,pcorp_ids.lastIndexOf(","));
			}
			if(pcorp_names!=null&&!"".equals(pcorp_names)){
				pcorp_names = pcorp_names.substring(0,pcorp_names.lastIndexOf(","));
			}
		} catch (Exception e) {
			log.error("获取deptids错误:" + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("关闭数据库连接错误:" + e.getMessage());
			}
		}
		return new String[] { deptids, deptnames ,parent_ids,dept_types,corp_ids,corp_names,pcorp_ids,pcorp_names};
	}
}