///////////////////
   PROJECT
   CourseEgov
///////////////////


//////////////////////////////////////////////
//////// PACKAGE courseEgovPack 
//////////////////////////////////////////////

package courseEgovPack;

import javax.jws.WebMethod;
import javax.jws.WebService;

@WebService // compiler directive to turn this into a web service
public class NumberService {
	
	// sumUpProcedure 
	// String mInput: value to sum up 1..mInput (typed web services not possible)
	// return value as String (typed web services not possible)
	
	@WebMethod // compiler directive to turn this into a web method
        public String sumUpProcedure (String mInput){ // return sum of 1..mInput
	    int m=Integer.valueOf(mInput);
		int x=0; // accumulator variable	
		for (int i=1;i<=m;i++){
	       x=x+i;
		}
		return String.valueOf(x);
	}

}

//////////////////////////////////////////////
//////// PACKAGE coursePlainJavaPack
//////////////////////////////////////////////

package coursePlainJavaPack;

public class SumOfNumbers {
	
    public static void main (String[] args) {
		
		//int n=Integer.valueOf(args[0]); // get the number from the Console
		int n = 10; // program puts out Sum of numbers 1...n
		
		int x=0; // accumulator variable for the result value
		for (int i=1; i<=n; i++){
		  x = x + i;
		}
		
		System.out.println("Our calculation:" + x);
		System.out.println("Gauss calculation:" + ((n*(n+1))/2));
		
	}

}

///////////////////////////////////////////////////////////////////////////

package coursePlainJavaPack;

public class NumberAgent {

    // OBJECT-ORIENTED PROGRAMMING
	
    // attributes of NumberAgent objects
	
	private int n=0; 
	
    // methods of NumberAgent objects
	
	NumberAgent(){ // Constructor
	}
	
	public void setValue(int x){
		n=x;
	}
	
	public int getValue(){
		return n;
	}
	
	public void sumUp(){ // computes and stores sum of 1..n
        int x=0; // accumulator variable	
		for (int i=1;i<=n;i++){
			x=x+i;
		}
		n=x;
	}
	
	public void factorial(){ // returns sum of 1..n
        int x=1; // accumulator variable	
		for (int i=1;i<=n;i++){
			x=x*i;
		}
		n=x;
	}
	
	// END: OBJECT-ORIENTED PROGRAMMING
	
	// a main method to exploit NumberAgent objects
	
	public static void main (String[] args){
		
		NumberAgent numberAgent= new NumberAgent();
		
		numberAgent.setValue(10);
		numberAgent.sumUp(); 
		System.out.println("Sum 1...10 :" + numberAgent.getValue());
		
		numberAgent.setValue(4);
		numberAgent.factorial(); 
		System.out.println("Fac of 4   :" + numberAgent.getValue());
		
		System.out.println("Sum 1...10 :" + sumUpProcedure(10));
		
		System.out.println("Fac of 4   :" + factorialProcedure(4));
		
		System.out.println("Sum 1...10 :" + sumUpFunction(10));
		
		System.out.println("Fac of 4   :" + factorialFunction(4));
		
	}
	
	// STRUCTURED PROGRAMMING (a.k.a PROCEDURAL PROGRAMMING)
	
	private static int sumUpProcedure (int m){ // return sum of 1..m
	   int x=0; // accumulator variable	
	   for (int i=1;i<=m;i++){
		 x=x+i;
	    }	
	   return x;
	}
	
	private static int factorialProcedure (int m){ // return factorial of m
	   int x=1; // accumulator variable	
	   for (int i=1;i<=m;i++){
	     x=x*i;
	   }	
	   return x;
    }
	
	// FUNCTIONAL PROGRAMMING 
	
	private static int sumUpFunction (int m){ // return sum of 1..m
	  if (m>1){
		  return m + sumUpFunction(m-1);
	  } else {
	    return 1;
	  }
	}
	
	private static int factorialFunction (int m){ // return factorial  m
		  if (m>1){
			  return m * factorialFunction(m-1);
		  } else {
		    return 1;
		  }
		}
	
}

///////////////////////////////////////////////////////////////////////////

package coursePlainJavaPack;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class BasicDBAccessLocal {
	
	public static void main(String args[]){
			
		Connection c = null;
			
		try {
	        	       	
			String url = "jdbc:postgresql://localhost:5432/postgres";
	        String username = "postgres";
	        String password = "ICT637postgresql";
	        Class.forName("org.postgresql.Driver");

	        c = DriverManager.getConnection(url, username, password);
	        c.setAutoCommit(false);
	        
	        Statement selectStatement = c.createStatement(
        			ResultSet.TYPE_SCROLL_SENSITIVE,
        			ResultSet.CONCUR_READ_ONLY
            	);
	        
	        // READ DATA
	        
	        ResultSet resultSet = selectStatement.executeQuery(
	    		   		"Select * from company;"
	    		   );
	            resultSet.first();
	            
	            System.out.println(resultSet.getString(2));
	            
	            while (resultSet.next()){
	              System.out.println(resultSet.getString(2));
	            }
	                    
		        // WRITE DATA
		       
	            resultSet.last();
	            System.out.println(
	            		"Number of rows: "
	            	  + String.valueOf(resultSet.getRow())
	            	);
		        selectStatement.executeUpdate(
		            "INSERT INTO company (id,name,age,address,salary)"
		        +   "VALUES ("
		        +      String.valueOf(resultSet.getRow()+1) + ","
		        +      "'bob', 30, 'testaddress', 1500"
		        +   ");"
		        );
		       
	            selectStatement.close();
	            c.commit();
	            c.close();

		} catch (Exception e) {
			e.printStackTrace();
	        System.err.println(e.getClass().getName() +": " + e.getMessage());
	        System.exit(0);
		}
	}
}

///////////////////////////////////////////////////////////////////////////

package coursePlainJavaPack;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class BasicDBAccessCloud {
	
	public static void main(String args[]){
			
		Connection c = null;
			
		try {
	        	       	
			String url = "jdbc:postgresql://babar.elephantsql.com:5432/qqwtblyv"; //Todo:
			String username = "qqwtblyv";
			String password = "ALo8tyV9bxRKkXm6691WELtjdYbX8L4p";
			Class.forName("org.postgresql.Driver");

	        c = DriverManager.getConnection(url, username, password);
	        c.setAutoCommit(false);
	        
	        Statement selectStatement = c.createStatement(
        			ResultSet.TYPE_SCROLL_SENSITIVE,
        			ResultSet.CONCUR_READ_ONLY
            	);
	        
	        // READ DATA
	        
	        ResultSet resultSet = selectStatement.executeQuery(
	    		   		"Select * from 	testdata;"
	    		   );
	            resultSet.first();
	            
	            System.out.println(resultSet.getString(2));
	            
	            while (resultSet.next()){
	              System.out.println(resultSet.getString(2));
	            }
		       
	            selectStatement.close();
	            c.commit();
	            c.close();

		} catch (Exception e) {
			e.printStackTrace();
	        System.err.println(e.getClass().getName() +": " + e.getMessage());
	        System.exit(0);
		}
	}
}

///////////////////////////////////////////////////////////////////////////

package coursePlainJavaPack;

import javax.xml.soap.MessageFactory;
import javax.xml.soap.MimeHeaders;
import javax.xml.soap.SOAPBody;
import javax.xml.soap.SOAPConnection;
import javax.xml.soap.SOAPConnectionFactory;
import javax.xml.soap.SOAPElement;
import javax.xml.soap.SOAPEnvelope;
import javax.xml.soap.SOAPMessage;
import javax.xml.soap.SOAPPart;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.NodeList;

public class BasicSOAPAccess {

  public static void main(String args[]) {
	
	String n = "9"; // value of n, for which 1..n should be calculated  
	  
	try {
	  
	  SOAPConnectionFactory soapConnectionFactory = SOAPConnectionFactory.newInstance();
	  SOAPConnection soapConnection = soapConnectionFactory.createConnection();

	  MessageFactory messageFactory = MessageFactory.newInstance();
	  SOAPMessage soapMessage = messageFactory.createMessage();
	  SOAPPart soapPart = soapMessage.getSOAPPart();
	  SOAPEnvelope envelope = soapPart.getEnvelope();

	  SOAPBody soapBody = envelope.getBody();
	  
	  // XML element for method name
	  SOAPElement soapBodyElemLevel1 = 
			  soapBody.addChildElement("sumUpProcedure");
	  
	  // XML element for paramater name
	  SOAPElement soapBodyElemLevel2 = 
			  soapBodyElemLevel1.addChildElement("mInput");
	  
	  // setting value for paramater 
	  soapBodyElemLevel2.addTextNode(n);

	  MimeHeaders headers = soapMessage.getMimeHeaders();
	  headers.addHeader("SOAPAction","");

	  soapMessage.saveChanges();
    
	  // http://localhost:8080/'project name'/services/'web service name'
	  String url = "http://localhost:8080/CourseEgov/services/NumberService";
    	  
	  SOAPMessage soapResponse = soapConnection.call(soapMessage, url);

	  SOAPBody body = soapResponse.getSOAPBody();

	  // ns1 : default namespace 
	  NodeList nodeList = body.getElementsByTagName("ns1:sumUpProcedureReturn");
	  
	  System.out.print("Sum of 1.." + n + " :");
	  System.out.println(nodeList.item(0).getTextContent());
	  
	  //System.out.println(soapBody);
	  //System.out.println(soapMessage);
	  //soapMessage.writeTo(System.out);
      //printSOAPResponse(soapResponse);

	soapConnection.close();

  } catch (Exception e) {
	  System.err.println("Error");
	  e.printStackTrace();
  }
}

}


//////////////////////////////////////////////
//////// Java Sever Pages :: RequestPage.jsp
//////////////////////////////////////////////

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>  
  <% 
  if (    request.getParameter("firstname")==null 
	   || request.getParameter("firstname")==""
	   || request.getParameter("lastname")==null 
	   || request.getParameter("lastname")=="" 
  ) {%> 
   
  <b>Please input data!</b> 
   
  <form action="http://localhost:8080/CourseEgov/RequestPage.jsp" method="get">
    <table>
      <tr>
        <td>
          <INPUT TYPE="radio" name="gender" value="male" checked="checked">Mr.<br>
          <INPUT TYPE="radio" name="gender" value="female">Mrs.
        </td>
        <td></td>
      <tr>
      <tr>
        <td>   
        <%if (request.getParameter("firstname")=="") {
          %> <font color=ff0000><b>!</b></font> 
        <%}%>      
          First name:   
        </td>
        <td><input type="text" name="firstname"></td>
      </tr>
      <tr>
        <td>
        <%if (request.getParameter("lastname")=="") {
          %> <font color=ff0000><b>!</b></font> 
        <%}%>   
          Last name:
        </td>
        <td><input type="text" name="lastname"></td>
      </tr>
    </table>
   <br>
   <input type="submit" value="Submit">
  </form> 
  
  <%} else {%>
  
    Hello  
    <%String str=request.getParameter("gender");
      if (str.equals("male")) {
    %> Mr. 
    <%} else {%> 
       Mrs. 
    <%}%>    
 
    <i>
    <%= request.getParameter("lastname") %>
    !</i>
    <br>
    <br>
    <a href="http://localhost:8080/CourseEgov/RequestPage.jsp">Next Round</a>
  
  <%}%>
  
</body>
</html>

////////////////////////////////////////////////
////
//// SQL 
////
////////////////////////////////////////////////


CREATE TABLE course (
   id serial PRIMARY KEY,
   courseTitle VARCHAR(255),
   lecturer VARCHAR(255),
   startDate DATE 
);

INSERT INTO course (courseTitle,startDate,lecturer) VALUES(
   'Introduction to IT and eGov',
   '2017-09-14',
   'Dirk Draheim'
);

INSERT INTO course (lecturer,startDate,courseTitle) VALUES(
   'Christian Stevens',
   '2056-10-06',
   'Cloud Computing Tools and Devops' 
);

SELECT * FROM course;
  
DROP TABLE course;	

//////////////////////////////

SELECT * FROM person;
DROP TABLE person;
DELETE FROM person;

SELECT * FROM course;
DROP TABLE course;
DELETE FROM course;

CREATE TABLE person (
   id serial PRIMARY KEY,
   firstname VARCHAR(255),
   lastname VARCHAR(255),
   street VARCHAR(255),
   zipcode INTEGER
);

CREATE TABLE course (
   id serial PRIMARY KEY,
   courseTitle VARCHAR(255),
   lecturer serial REFERENCES person (id),
   startDate DATE 
);

SELECT * FROM person WHERE lastname='Paparoddi';

INSERT INTO course (courseTitle,lecturer,startDate) VALUES('Development of Data Exchange Layers',4,'2017-03-01');
INSERT INTO course (courseTitle,lecturer,startDate) VALUES('eGove Law',8,'2017-02-01');
INSERT INTO course (courseTitle,lecturer,startDate) VALUES('RealTime SE',4,'2017-10-01');
INSERT INTO course (courseTitle,lecturer,startDate) VALUES('Business English',10,'2017-10-01');

DELETE FROM person WHERE id=4;

SELECT * FROM course,person;

SELECT (course.courseTitle,person.firstname,person.lastname) FROM course,person;

SELECT (course.courseTitle,person.firstname,person.lastname) 
FROM course,person 
WHERE course.lecturer=person.id;

INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Bob','Meyer','Boston rd. 23',123934);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Charles','Miller','London Plaza 17',12123);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Anika','Henson','Sunset Blvd. 89',46571);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Joseph','King','Caprese Str. 23',12934);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Paul','Meyer','Boston rd. 23',123934);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Karin','Moeller','London Plaza 17',12123);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Peter','Milne','Sunset Blvd. 89',46571);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Giorgi','Moeller','Caprese Str. 23',12934);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Peter','Moeller','Caprese Str. 23',12934);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('John','Moeller','Caprese Str. 23',12934);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Julian','Webber','Caprese Str. 23',12934);
INSERT INTO person (firstname,lastname,street,zipcode)  VALUES('Jannis','Webber','Kalime R. 19',5564);