package WSRemco;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;



// spul starten (via servlet service (in tree)) als Run on Server en dan in de browser intikken:

//               http://localhost:8080/WimDeOG/PHPOpdracht/InchToFeet/24

//    of         http://localhost:8080/WimDeOG/PHPOpdracht/DeleteBestuurder/2

//    of         http://localhost:8080/WimDeOG/PHPOpdracht/Bestuurders



import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;



@Path("PHPOpdracht")

public class Bedrijfsleven {



 Connection conn = null;

 @GET
	@Path("/InchToFeet/{i}")
	@Produces(MediaType.TEXT_XML)
	public String convertInchToFeet(@PathParam("i") int i) {

		int inch = i;
		double feet = 0;
		feet = (double) inch / 12;

		return "<InchToFeetService>" + "<Inch>" + inch + "</Inch>" + "<Feet>" + feet + "</Feet>"
				+ "</InchToFeetService>";
	}


 @Path("/Werknemers")

 @GET

 @Produces(MediaType.TEXT_XML)

 public String getWerknemersService() {

  String serviceResult = "";
  connectDB();
  serviceResult = getWerknemers();
  disconnectDB();
  return serviceResult;
 }


 @Path("/WerknemersAdd/{i}/{d}")

 @GET

 @Produces(MediaType.TEXT_XML)

 public String AddWerknemersService(@PathParam("i") int Id, @PathParam("d") String Naam) {

  int serviceResult = 0;
  connectDB();
  serviceResult = updateWerknemer(Id, Naam);
  disconnectDB();
  return "" + serviceResult;
 }



 @Path("/WerknemersUpdate/{i}/{d}")

 @GET

 @Produces(MediaType.TEXT_XML)

 public String UpdateWerknemersService(@PathParam("i") int i, @PathParam("d") String Naam) {

  int serviceResult = 0;
  connectDB();
  serviceResult = updateWerknemer(i, Naam);
  disconnectDB();
  return "" + serviceResult;
 }



 @Path("/WerknemersDelete/{i}")

 @GET

 @Produces(MediaType.TEXT_PLAIN)

 public String DeleteWerknemersService(@PathParam("i") int i) {

  int serviceResult = 0;



  connectDB();

  serviceResult = deleteWerknemers(i);

  disconnectDB();



  return "" + serviceResult;

 }



 public void connectDB() {

  String url = "jdbc:mysql://localhost:3306/";

  String dbName = "myPHPOpdracht";

  String driver = "com.mysql.jdbc.Driver";

  String userName = "root";

  String password = "";



  try {

   Class.forName(driver).newInstance();
   conn = DriverManager.getConnection(url + dbName, userName, password);

  } catch (Exception e) {
   e.printStackTrace();
  }

 }



 public void disconnectDB() {
  try {

   if (conn != null) {

    conn.close();
   }

  } catch (SQLException e) {

   // TODO Auto-generated catch block

   e.printStackTrace();

  }

 }



 public String getWerknemers() {


  String resultString = "";
  Statement stmt = null;
  try {

   System.out.println("Creating statement...");

   stmt = conn.createStatement();
   String sql = "select id, Naam, Achternaam, Afdeling from werknemers";
   ResultSet rs = stmt.executeQuery(sql);
   resultString = "<Werknemers>";

   while (rs.next()) {
    resultString = resultString +

     "<Werknemers>" +

     "<id>" + rs.getInt("id") + "</id>" +

     "<Naam>" + rs.getString("Naam") + "</Naam>" +

     "<Achternaam>" + rs.getString("Achternaam") + "</Achternaam>" +

     "<Afdeling>" + rs.getString("Afdeling") + "</Afdeling>" +

     "</Werknemers>";

   }
   resultString = resultString + "</Werknemers>";

   System.out.print(resultString);

   rs.close();



  } catch (Exception e) {

   e.printStackTrace();

  }



  return resultString;

 }



 public int deleteWerknemers(int id) {



  int result = 0;



  try {
   System.out.println("Creating prepare statement...");

   String sql = "delete from werknemers where id = ?";


   PreparedStatement prepStmt = conn.prepareStatement(sql);



   prepStmt.setInt(1, id);

   result = prepStmt.executeUpdate();



   System.out.print(result);



  } catch (Exception e) {

   e.printStackTrace();

  }



  return result;

 }



 public int updateWerknemer(int id, String Naam) {



  int result = 0;



  Statement stmt = null;



  try {

   System.out.println("Creating statement...");

   stmt = conn.createStatement();



   String sql = "UPDATE werknemers SET Naam = ? WHERE id = ?";

   PreparedStatement prepStmt = conn.prepareStatement(sql);

   prepStmt.setInt(1, id);
   prepStmt.setString(2, Naam);

   result = prepStmt.executeUpdate();



   System.out.print(result);



  } catch (Exception e) {

   e.printStackTrace();

  }



  return result;

 }



 public int insertBestuurder(String Naam, String Achternaam, String Afdeling) {



  int result = 0;



  Statement stmt = null;



  try {

   System.out.println("Creating statement...");

   stmt = conn.createStatement();

   String sql = "INSERT INTO werknemers (Naam,Achternaam,Afdeling) VALUES (?, ?, ?)";

   PreparedStatement prepStmt = conn.prepareStatement(sql);

   prepStmt.setString(1, Naam);
   prepStmt.setString(2, Achternaam);
   prepStmt.setString(3, Afdeling);

   result = prepStmt.executeUpdate();



   System.out.print(result);



  } catch (Exception e) {

   e.printStackTrace();

  }



  return result;

 }



}