package Oad;
import java.sql.*;
//import javax.servlet.*;
//import javax.servlet.http.*;

public class baasiUba{
	String DBuser="if21";
	String DBpass="ke+88lla";
	
	private boolean checkUname(String uName){
		Connection cn=null;
		Statement st=null;
		ResultSet rs;
		String lause="SELECT * FROM users WHERE uName='"+uName+"';";
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();   
    		cn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/if21", DBuser, DBpass);
    		st=cn.createStatement();
    		rs=st.executeQuery(lause);
    		if(rs.next()==false){return false;}
    		cn.close();
		}catch(Exception e){e.printStackTrace();return true;}
		return true;
	}
	
	public String addUser(String uName, String gName, String fName, String pass, String foto,String cv){
		Connection cn=null;
		Statement st=null;
		String lause="INSERT INTO users (uName, fName, gName, pass, rights_ID, foto, cv) VALUES ('"+uName+"','"+fName+"','"+gName+"','"+pass+"',0,'"+foto+"','"+cv+"');";
		if(checkUname(uName)==true){return "Kasutajanimi "+uName+" on juba hõivatud!<br/> Palun valige teine kasutajanimi.";}
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();   
    		cn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/if21", DBuser, DBpass);
    		st=cn.createStatement();
    		st.executeUpdate(lause);
    		cn.close();
		}catch(Exception e){return "Tekkis viga: "+e+"<br/> kasutaja registreerimine ebaõnnestus!";}
		return "Kasutaja "+uName +" registreerimine õnnestus!";
	}
	
	public String showUsers(String sid, String IP){
		String[] nimed={"ID:", "Kasutajanimi:","Eesnimi:","Perenimi:","Salas6na:","6igused:","Foto:","Endast:"};
		String[] rights={"Tavakasutaja", "UserAdmin","NewsAdmin","UserAdmin & NewsAdmin","FileAdmin","FileAdmin & UserAdmin","FileAdmin & NewsAdmin","FileAdmin & UserAdmin & NewsAdmin","CommentAdmin","CommentAdmin & UserAdmin","CommentAdmin & NewsAdmin","CommentAdmin & NewsAdmin & UserAdmin","CommentAdmin & FileAdmin","CommentAdmin & FileAdmin & UserAdmin","CommentAdmin & FileAdmin & NewsAdmin","CommentAdmin & FileAdmin & UserAdmin & NewsAdmin"};
		Connection cn=null;
		Statement st=null;
		ResultSet rs=null;
		ResultSetMetaData rsmd=null;
		String tagasi="";
		String lause="SELECT * FROM users;";
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();   
    		cn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/if21", "if21", "ke+88lla");
    		st=cn.createStatement();
    		rs=st.executeQuery(lause);
    		rsmd=rs.getMetaData();
    		while(rs.next()){
    			tagasi=tagasi.concat("<table>");
    			tagasi=tagasi.concat("<tr><td><b>KASUTAJA:</b></td><td><i>"+rs.getString(2)+"</i></td></tr>");
    			for(int i=2; i<=rsmd.getColumnCount(); i++){
    				if(i!=5 && i!=6 && i!=7){
    					tagasi=tagasi.concat("<tr><td><b>"+nimed[i-1]+"</b></td><td>"+rs.getString(rsmd.getColumnName(i))+"</td></tr>");
    				}
    				if(i==5 && Auth.sessOK(sid, IP) && Auth.doAuth(sid, IP, Auth.USER)!=null){
    					tagasi=tagasi.concat("<tr><td><b>"+nimed[i-1]+"</b></td><td>"+rs.getString(rsmd.getColumnName(i))+"</td></tr>");
    				}
    				if(i==6){
    					tagasi=tagasi.concat("<tr><td><b>"+nimed[i-1]+"</b></td><td>"+rights[rs.getInt(rsmd.getColumnName(i))]+"</td></tr>");
    				}
    				if(i==7){
    					tagasi=tagasi.concat("<tr><td><b>"+nimed[i-1]+"</b></td><td><a href='"+rs.getString(rsmd.getColumnName(i))+"'>link</a></td></tr>");
    				}
    			}
    			if(Auth.sessOK(sid, IP) && Auth.doAuth(sid, IP, Auth.USER)!=null ){
    					tagasi=tagasi.concat("<tr><td><a href='cngUser.jsp?ID="+rs.getInt("ID")+"&sid="+sid+"&uName="+rs.getString("uName")+"&gName="+rs.getString("gName")+"&fName="+rs.getString("fName")+"&pass="+rs.getString("pass")+"&rights="+rs.getString("rights_ID")+"&foto="+rs.getString("foto")+"&cv="+rs.getString("cv")+"'>MUUDA</a></td></tr>");
    			}
    			tagasi=tagasi.concat("</table><br/>");
    		}
    		cn.close();
		}catch(Exception e){return "Tekkis viga: "+e;}

		return tagasi;
	}
	
	public String cngUser(String ID, String uName, String gName, String fName, String pass, int rights, String foto, String cv, String del){
		Connection cn=null;
		Statement st=null;
		ResultSet rs=null;
		String tagasi="";
		String lause="";
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();   
    		cn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/if21", "if21", "ke+88lla");
    		st=cn.createStatement();
  	}catch(Exception e){return "Tekkis viga: "+e;}
  	if(del!=null){
  		lause="DELETE FROM users WHERE ID='"+ID+"';";
  		try{
  			st.executeUpdate(lause);
  		}catch(Exception e){return "Tekkis viga: "+e;}
  	}else{
  		lause="UPDATE users SET uName='"+uName+"', gName='"+gName+"', fName='"+fName+"', pass='"+pass+"',rights_ID="+rights+",foto='"+foto+"',cv='"+cv+"' WHERE ID='"+ID+"';";
  		try{
  			st.executeUpdate(lause);
  		}catch(Exception e){return "Tekkis viga: "+e;}
  	}
  	try{
  		cn.close();
  	}catch(Exception e){return "Tekkis viga: "+e;}
  	return "Andmed edukalt muudetud! ";
	}
}