package collabsoft.backlog_reports.c4;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
//import collabsoft.backlog_reports.c4.Report;
public class Report {
private Connection con;
public Report(){
connectUsingJDBC();
}
public static void main(String args[]){
Report dc = new Report();
dc.reviewMeeting(6, 8, 10);
dc.createReport("dede",100);
//dc.viewReport(100);
// dc.custRent(3344,123,22,11-11-2009);
}
/**
the following method is used to connect to the database
**/
public void connectUsingJDBC() {
// This is the name of the ODBC data source
String dataSourceName = "Simple_DB";
try {
// loading the driver in the memory
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// This is the connection URL
String dbURL = "jdbc:odbc:" + dataSourceName;
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Collabsoft","root","");
// This line is used to print the name of the driver and it would throw an exception if a problem occured
System.out.println("User connected using driver: " + con.getMetaData().getDriverName());
//Addcustomer(con,1111,"aaa","aaa","aa","aam","111","2222","111");
//rentedMovies(con);
//executePreparedStatement(con);
//executeCallableStatement(con);
//executeBatch(con);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*this code is to link the SQL code with the java for the task
*as an admin I should be able to create a report of a review meeting including notes, tasks and users
*i will take the task id and user id and note id that will be needed to be added in the review
*meeting report and i will display the information related to these ida
**/
public void reviewMeeting(int taskID, int userID, int noteID)// law el proc bt return table
{
try{
CallableStatement callableStatement = con.prepareCall("{CALL report_review_meeting(?,?,?)}");
callableStatement.setInt(1,taskID);
callableStatement.setInt(2,userID);
callableStatement.setInt(3,noteID);
ResultSet resultSet = callableStatement.executeQuery(); // or executeupdate() or updateQuery
ResultSetMetaData rsm = resultSet.getMetaData();
int numOfColumns = rsm.getColumnCount();
System.out.println("lojayna");
while (resultSet.next())
{
System.out.println("New Row:");
for (int i = 1; i <= numOfColumns; i++)
System.out.print(rsm.getColumnName(i) + ": " + resultSet.getObject(i) + " ");
System.out.println();
}
}
catch(Exception e)
{
System.out.println("E");
}
}
//////////////////////////////////
/////////////////////////////////
public void allproject(int projID)// law el proc bt return table
{
try{
CallableStatement callableStatement = con.prepareCall("{CALL all_project(?)}");
callableStatement.setInt(1,projID);
//callableStatement.setInt(2,userID);
//callableStatement.setInt(3,noteID);
ResultSet resultSet = callableStatement.executeQuery(); // or executeupdate() or updateQuery
ResultSetMetaData rsm = resultSet.getMetaData();
int numOfColumns = rsm.getColumnCount();
System.out.println("lojayna");
while (resultSet.next())
{
System.out.println("New Row:");
for (int i = 1; i <= numOfColumns; i++)
System.out.print(rsm.getColumnName(i) + ": " + resultSet.getObject(i) + " ");
System.out.println();
}
}
catch(Exception e)
{
System.out.println("E");
}
}
///////////////////////////////
/**
* here i take the event id and i take a string report and then
* i relate the report with the event
**/
public void createReport(String report,int E_ID )// law el proc bt return table
{
try{
Statement st = con.createStatement();
st.executeUpdate("UPDATE e_vent SET e_vent.report=report WHERE e_vent.E_ID= E_ID;");
/* CallableStatement callableStatement = con.prepareCall("{CALL Create_report(?,?)}");
callableStatement.setString(1,report);
callableStatement.setInt(2,E_ID);
ResultSet resultSet = callableStatement.executeQuery(); // or executeupdate() or updateQuery
ResultSetMetaData rsm = resultSet.getMetaData();
int numOfColumns = rsm.getColumnCount();
System.out.println("lojayna");
while (resultSet.next())
{
System.out.println("New Row:");
for (int i = 1; i <= numOfColumns; i++)
System.out.print(rsm.getColumnName(i) + ": " + resultSet.getObject(i) + " ");
System.out.println();
}*/
}
catch(Exception e)
{
System.out.println("E");
System.out.println(e);
}
}
/**
*in the following method i view the report of the event having the ID eventID
**/
public void viewReport(int eventID)// law el proc bt return table
{
try{
CallableStatement callableStatement = con.prepareCall("{CALL view_report(?)}");
callableStatement.setInt(1,eventID);
ResultSet resultSet = callableStatement.executeQuery(); // or executeupdate() or updateQuery
ResultSetMetaData rsm = resultSet.getMetaData();
int numOfColumns = rsm.getColumnCount();
System.out.println("lojayna");
while (resultSet.next())
{
System.out.println("New Row:");
for (int i = 1; i <= numOfColumns; i++)
System.out.print(rsm.getColumnName(i) + ": " + resultSet.getObject(i) + " ");
System.out.println();
}
}
catch(Exception e)
{
System.out.println("E");
}
}
}
// the result of these methods is being showed on the console , i am using WIcket and i want it 2 be showed on the web how is that done ?!