itrequestcontroller.java

provide a room booking system

/*
 * ITRequestController.java
 *
 * Created on January 19, 2007, 9:04 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package Wearnes;

/**
 *
 * @author dongliang.guo
 */
import java.util.*;
import java.sql.*;
public class ITRequestController {
    
    /** Creates a new instance of ITRequestController */
    public ITRequest request;
  

    private WearnesDB wearnesDB;
  
    
    
    public ITRequestController() 
    {
        wearnesDB=WearnesDB.getInstance((new CommonUtility()).getConnectionURL());    
       //wearnesDB=new WearnesDB("jdbc:mysql://wpstem:3306/wearnes?user=guo&password=password");
    }
    public int createITRequest(ITRequest req)
    {
        int reqID=0;
        String sql="insert into request(user_name,req_date,expected_completion_date,company,department,category,item,description,impact,process_status) values('"
                +req.user_name()+"','"+req.req_date()+"','"+req.expected_completion_date()+"','"+req.company()+"','"+req.department()+"','"+req.category()+"','"+req.item()+"','"+req.description()+"','"+req.impact()+"','"+req.process_status()+"')";
        try
        {
           boolean r=wearnesDB.insert(sql);
         
           if(r)
           {
             String selectSql="select req_id from request where user_name='"+req.user_name()+"'order by req_id desc";
	
             ResultSet rs=wearnesDB.select(selectSql);
             if(rs.next())
             {
                    reqID=rs.getInt("req_id");
                    
             }
           }
           else
           {
            reqID=-2;
           }
	}catch(SQLException e)
	{
		return -1;
	}
       
        return reqID;
    }
    public ITRequest getRequest(int req_id)
    {
        ITRequest request=null;
         try
            {
         
             String id=(new Integer(req_id)).toString().trim();
             String selectSql="select * from request where req_id="+id;
             String selectObserver="select * from observerofrequest where requestID="+id;
	
             ResultSet rs=wearnesDB.select(selectSql);
             if(rs.next())
             {
                   request=new ITRequest(
                           rs.getString("user_name"),
                           rs.getString("req_date"),
                           rs.getString("company"),
                           rs.getString("department"),
                           rs.getString("category"),
                           rs.getString("item"),
                           rs.getString("description"),
                           rs.getString("impact"),
                           rs.getString("process_status"),
                           rs.getString("expected_completion_date")
                           );
                   request.setCost(rs.getInt("cost"));
                   request.setCompleted_by(rs.getString("completed_by"));
                   

            
            
             
                 ResultSet rsObserver=wearnesDB.select(selectObserver);
                 ObserverProfile profile=null;
                
                 while (rsObserver.next())
                 {                 
                    profile =new ObserverProfile(req_id,rsObserver.getString("process_status"),rsObserver.getString("observerName"),rsObserver.getString("observerDate"),rsObserver.getString("observerApprovement"),rsObserver.getString("observerComment"));
                    request.attachObserversProfile(profile);
                 }
             }
           
	}catch(SQLException e)
	{
		return null;
	}
        return request;
    }
   public ArrayList getObserversOfRequest(int req_id)
    {
        ArrayList arr=new ArrayList();
         try{            {
     
             String id=(new Integer(req_id)).toString().trim();             
             String selectObserver="select * from observerofrequest where requestID="+id;
	     ResultSet rsObserver=wearnesDB.select(selectObserver);
             ObserverProfile profile=null;
                
                 while (rsObserver.next())
                 {                 
                    profile =new ObserverProfile(req_id,rsObserver.getString("process_status"),rsObserver.getString("observerName"),rsObserver.getString("observerDate"),rsObserver.getString("observerApprovement"),rsObserver.getString("observerComment"));
                    arr.add(profile);
                 }
             }
           
	}catch(SQLException e)
	{
		return null;
	}
        return arr;
    }
  public ArrayList getAllRequestToBeApproved(String name)
  {
       ArrayList array=new ArrayList(); 
         try
            {
         
             
             String selectSql="select * from request where process_status<'6' and user_name = '"+name.trim()+"'";
          
	
             ResultSet rs=wearnesDB.select(selectSql);
             while (rs.next())
             {
                   request=new ITRequest(
                           rs.getString("user_name"),
                           rs.getString("req_date"),
                           rs.getString("company"),
                           rs.getString("department"),
                           rs.getString("category"),
                           rs.getString("item"),
                           rs.getString("description"),
                           rs.getString("impact"),
                           rs.getString("process_status"),
                           rs.getString("expected_completion_date")
                           );
                   request.setReq_id(rs.getInt("req_id"));
                array.add(request);    
             }
           
	}catch(SQLException e)
	{
		//return null;
	}
        return array;
    }
    public ArrayList getAllRequestToBeApproved(String name,String status)
  {
       ArrayList array=new ArrayList(); 
         try
            {
         
             
             String selectSql="select * from request where process_status='"+status.trim()+"' and user_name = '"+name.trim()+"'";
          
	
             ResultSet rs=wearnesDB.select(selectSql);
             while (rs.next())
             {
                   request=new ITRequest(
                           rs.getString("user_name"),
                           rs.getString("req_date"),
                           rs.getString("company"),
                           rs.getString("department"),
                           rs.getString("category"),
                           rs.getString("item"),
                           rs.getString("description"),
                           rs.getString("impact"),
                           rs.getString("process_status"),
                           rs.getString("expected_completion_date")
                           );
                   request.setReq_id(rs.getInt("req_id"));
                array.add(request);    
             }
           
	}catch(SQLException e)
	{
		//return null;
	}
        return array;
    }
    public ArrayList getRequestByName(String name)
    {
        ArrayList array=new ArrayList(); 
         try
            {
         
             
             String selectSql="select * from request where item like '%"+name+"%'";
          
	
             ResultSet rs=wearnesDB.select(selectSql);
             while (rs.next())
             {
                   request=new ITRequest(
                           rs.getString("user_name"),
                           rs.getString("req_date"),
                           rs.getString("company"),
                           rs.getString("department"),
                           rs.getString("category"),
                           rs.getString("item"),
                           rs.getString("description"),
                           rs.getString("impact"),
                           rs.getString("process_status"),
                           rs.getString("expected_completion_date")
                           );
                   request.setReq_id(rs.getInt("req_id"));
                array.add(request);