From 83c25ed7ede6410d3cc11e2fc9158c0847bfa83e Mon Sep 17 00:00:00 2001
From: Tobias Spitzer
Date: Fri, 27 Jun 2014 11:38:34 +0200
Subject: Ersten zwei Funktionen

---
 bwLehrpool_ServerMaintenance/src/Main.java    |  6 ++++-
 bwLehrpool_ServerMaintenance/src/sql/sql.java | 34 ++++++++++++++++++++++++---
 2 files changed, 36 insertions(+), 4 deletions(-)

diff --git a/bwLehrpool_ServerMaintenance/src/Main.java b/bwLehrpool_ServerMaintenance/src/Main.java
index 42b3dfcd..bd6eddd4 100644
--- a/bwLehrpool_ServerMaintenance/src/Main.java
+++ b/bwLehrpool_ServerMaintenance/src/Main.java
@@ -20,7 +20,7 @@ public class Main {
 		System.out.println(actDate);
 		
 		try {
-			Wini ini = new Wini(new File("C:\\Users\\tspitzer\\Desktop\\Maintenance_Config.ini"));
+			Wini ini = new Wini(new File("Maintenance_Config.ini"));
 			String delete_ftp=ini.get("ftp", "day");
 			System.out.println(delete_ftp);
 			String inactive_lecture=ini.get("lecturetoinactive", "day");
@@ -31,6 +31,10 @@ public class Main {
 			System.out.println(image_without_lecture);
 			sql s=new sql();
 			s.deleteFTPUsers();
+			System.out.println("FTP User clean successful");
+			s.setLectureInactive();
+			System.out.println("Set Lectures Inactive Successful");
+			
 		} catch (InvalidFileFormatException e) {
 			// TODO Auto-generated catch block
 			e.printStackTrace();
diff --git a/bwLehrpool_ServerMaintenance/src/sql/sql.java b/bwLehrpool_ServerMaintenance/src/sql/sql.java
index 61b95172..dd8f9a39 100644
--- a/bwLehrpool_ServerMaintenance/src/sql/sql.java
+++ b/bwLehrpool_ServerMaintenance/src/sql/sql.java
@@ -1,5 +1,7 @@
 package sql;
 
+import java.io.File;
+import java.io.IOException;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
@@ -7,6 +9,9 @@ import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Date;
 
+import org.ini4j.InvalidFileFormatException;
+import org.ini4j.Wini;
+
 public class sql {
 	
 	public Connection getConnection() {
@@ -18,12 +23,13 @@ public class sql {
 			e.printStackTrace();
 		}
 		try {
+			Wini ini = new Wini(new File("Maintenance_Config.ini"));
 			Connection con = DriverManager
-					.getConnection("jdbc:mysql://localhost/bwlehrpool?user=bwlp_user&password=!20bwlehrpool14!");
+					.getConnection("jdbc:mysql://"+ini.get("sql", "connection")+"?user="+ini.get("sql", "user")+"&password="+ini.get("sql", "pass")+"");
 			con.setAutoCommit(false);
 			
 			return con;
-		} catch (SQLException e) {
+		} catch (SQLException | IOException e) {
 			// TODO Auto-generated catch block
 	
 			e.printStackTrace();
@@ -37,7 +43,29 @@ public class sql {
 			Connection con=getConnection();
 			Statement stm = con.createStatement();
 
-			stm.executeUpdate("Delete from bwLehrpool.FtpUsers where CreationDate > date_sub(now(),interval 1 day);");
+			stm.executeUpdate("Delete from bwLehrpool.FtpUsers where CreationDate < date_sub(now(),interval 1 day);");
+			con.commit();
+			con.close();
+			
+			
+		} catch (SQLException e) {
+			// TODO Auto-generated catch block
+			
+			e.printStackTrace();
+		}
+	}
+	
+	public void setLectureInactive(){
+		try {
+			Connection con=getConnection();
+			Statement stm_query = con.createStatement();
+			Statement stm_update = con.createStatement();
+			ResultSet rs=stm_query.executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where lastUsed < date_sub(now(),interval 1 day);");
+			while(rs.next()){
+				System.out.println(rs.getString("lectureID"));
+				stm_update.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `isActive`='0' WHERE `lectureID`='"+rs.getString("lectureID")+"';");
+			}
+			con.commit();
 			con.close();
 			
 			
-- 
cgit v1.2.3-55-g7522