package DataCenterPackage;

import RoomPackage.*;
import OrderPack.*;
import AccountsPack.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DataCenter
{
   private static DataCenter _INSTANCE;
   private String _DBAddr;
   private String _DBProtocol;
   private String _DBUser;
   private String _DSPwd;
   private String _DBName;
   private DataCache _dataCache;
   private Connection con = null;
   private String url = null;
   private Statement stmt;

   private DataCenter()
   {
       _DBAddr = DataConfig.getDBAddr();
       _DBProtocol= DataConfig.getDBProtocol();
       _DBUser = DataConfig.getDBUser();
       _DSPwd = DataConfig.getDSPwd();
       _DBName = DataConfig.getDBName();
       Init();
   }

   public static DataCenter get_INSTANCE()
   {
      if(_INSTANCE==null)
        _INSTANCE = new DataCenter();
      return _INSTANCE;
   }

   public String get_DBAddr() 
   {
      return _DBAddr;
   }

   public void set_DBAddr(String a_DBAddr) 
   {
      _DBAddr = a_DBAddr;
   }

   public String get_DBProtocol() 
   {
      return _DBProtocol;
   }

   public void set_DBProtocol(String a_DBProtocol) 
   {
      _DBProtocol = a_DBProtocol;
   }

   public String get_DBUser() 
   {
      return _DBUser;
   }

   public void set_DBUser(String a_DBUser) 
   {
      _DBUser = a_DBUser;
   }

   public String get_DSPwd() 
   {
      return _DSPwd;
   }

   public void set_DSPwd(String a_DSPwd) 
   {
      _DSPwd = a_DSPwd;
   }

   public DataCache get_dataCache() 
   {
      return _dataCache;
   }

   public void set_dataCache(DataCache a_dataCache) 
   {
      _dataCache = a_dataCache;
   }

   public void Init() 
   {
       _dataCache = DataCache.GetDataCache();
       Connect();
   }

   public boolean Disconnect() 
   {
       try {
           con.close();
           return true;
       } catch (SQLException e) {
           e.printStackTrace();
           return false;
       }
   }

   public boolean Connect() 
   {
       try {
            if(con!=null && con.isClosed())
                con.close();
            url = _DBProtocol + _DBAddr + "/" + _DBName +"?useUnicode=true&characterEncoding=gb2312";
            Class.forName("org.gjt.mm.mysql.Driver");
            con = DriverManager.getConnection(url, _DBUser, _DSPwd);
            stmt = con.createStatement(
                 ResultSet.TYPE_SCROLL_INSENSITIVE,
                 ResultSet.CONCUR_READ_ONLY);
       }catch (ClassNotFoundException e) {
            System.err.println("class not found:" + e.getMessage());
       } catch (SQLException e) {
            System.err.println("SQLException:" + e.getMessage());
       }
       return false;
   }

   public int AddOrder(Order order)
   {
       if(order.get_startTime() == null ||
               order.get_endTime() == null ||
               order.get_orderTime() == null||
               order.get_roomIDs()==null)
           return 0;
       StringBuffer roomIDs =new StringBuffer();
       int [] ids = order.get_roomIDs();
       for(int i =0;i< ids.length-1;i++){
           roomIDs.append(ids[i]);
           roomIDs.append(":");
       }
       roomIDs.append(ids[ids.length-1]);

       String query = "SELECT orderID FROM hotel_order";
       System.out.println(query);
       int maxid =0;
       try {
           ResultSet rs = stmt.executeQuery(query);
           maxid =0;
           while(rs.next()){
               int id = rs.getInt(1);
               if(id>maxid)
                   maxid = id;
           }
           maxid ++;
       } catch (SQLException e) {
           e.printStackTrace();
           return 0;
       }

       String insert = "INSERT INTO hotel_order VALUES(" +
            "'"+maxid +"',"+
            "'"+order.get_roomType() +"',"+
            "'"+order.get_startTime().getTime()+"',"+
            "'"+order.get_endTime().getTime() +"',"+
            "'"+order.get_orderTime().getTime() +"',"+
            "'"+order.get_roomCount() +"',"+
            "'"+0+"',"+ //userID
            "'"+order.get_userName() +"',"+
            "'"+(int)order.get_needPay() +"',"+
            "'"+(int)order.get_nowPay() +"',"+
            "'"+order.get_status() +"',"+
            "'"+roomIDs.toString() +"'"+
            ")";
       System.out.println(insert);
       try {
           stmt.executeUpdate(insert);
           return maxid;
       } catch (SQLException e) {
           e.printStackTrace();
           return 0;
       }
   }

   public int ModifyOrder(Order order)
   {
       if(order.get_startTime() == null ||
               order.get_endTime() == null ||
               order.get_orderTime() == null ||
               order.get_id()<=0)
           return 0;
       StringBuffer roomIDs =new StringBuffer();
       int [] ids = order.get_roomIDs();
       for(int i =0;i< ids.length-1;i++){
           roomIDs.append(ids[i]);
           roomIDs.append(":");
       }
       roomIDs.append(ids[ids.length-1]);

       String update = "UPDATE hotel_order SET " +
               "orderID =" +"'"+order.get_id() +"',"+
               "roomType =" +"'"+order.get_roomType() +"',"+
            "startTime =" +"'"+order.get_startTime().getTime()+"',"+
            "endTime =" +"'"+order.get_endTime().getTime() +"',"+
            "orderTime =" +"'"+order.get_orderTime().getTime() +"',"+
            "roomCount =" +"'"+order.get_roomCount() +"',"+
            "userID =" +"'"+0+"',"+ //userID
            "userName =" +"'"+order.get_userName() +"',"+
            "needPay =" +"'"+(int)order.get_needPay() +"',"+
            "nowPay =" +"'"+(int)order.get_nowPay() +"',"+
            "status =" +"'"+order.get_status() +"',"+
            "roomIDs ="+"'"+roomIDs.toString() +"'"+
            " WHERE orderID =" +"'"+order.get_id() +"'";
       System.out.println(update);
       try {
           return stmt.executeUpdate(update);
       } catch (SQLException e) {
           e.printStackTrace();
           return 0;
       }
   }

   public int DeleteOrder(Order order) 
   {
       if(order.get_id()<=0)
           return 0;
       return DeleteOrder(order.get_id());
   }

   public int DeleteOrder(int orderID) 
   {
       String delete = "DELETE FROM hotel_order WHERE orderID =" +"'"+orderID +"'";
       System.out.println(delete);
       try {
           return stmt.executeUpdate(delete);
       } catch (SQLException e) {
           e.printStackTrace();
           return 0;
       }
   }

   public Order [] SearchOrders(Condition []conditions){
       return SearchOrders(TranslateCondition(conditions));
   }

   public Order [] SearchOrders(String condition)
   {
       String query = "SELECT * FROM hotel_order "+ condition;
       System.out.println(query);
       try {
           ResultSet rs = stmt.executeQuery(query);
           List list = new ArrayList();
           while(rs.next()){
               Order order = new Order();
               order.set_id(rs.getInt(1));
               order.set_roomType(rs.getInt(2));
               order.set_startTime(new Time(rs.getLong(3)));
               order.set_endTime(new Time(rs.getLong(4)));
               order.set_orderTime(new Time(rs.getLong(5)));
               order.set_roomCount(rs.getInt(6));
               order.set_userName(rs.getString(8));
               order.set_needPay(rs.getInt(9));
               order.set_nowPay(rs.getInt(10));
               order.set_status(rs.getInt(11));
               String rooms = rs.getString(12);
               rooms.trim();
                String []results = rooms.split(":");
                int [] roomIDs = new int[results.length];
                for(int i =0;i<roomIDs.length;i++){
                    try{
                        roomIDs[i] = Integer.valueOf(results[i]).intValue();
                    }catch(Exception e){
                        roomIDs[i]= -1;
                    }
               }
               order.set_roomIDs(roomIDs);
               list.add(order);
           }
           Order [] orders = new Order[list.size()];
           list.toArray(orders);
           return orders;
       } catch (SQLException e) {
           e.printStackTrace();
           return null;
       }
   }

   private String TranslateCondition(Condition []conditions){
       if(conditions == null || conditions.length ==0)
        return "";
       StringBuffer str = new StringBuffer("WHERE ");
       for(int i =0;i< conditions.length-1 ;i++){
           str.append(conditions[i].toString());
           str.append(" and ");
       }
       str.append(conditions[conditions.length-1]);
       return str.toString();
   }

   public int [] SearchOrderIDs(Condition []conditions){
       return SearchOrderIDs(TranslateCondition(conditions));
   }

   public int [] SearchOrderIDs(String condition)
   {
       String query = "SELECT * FROM hotel_order "+ condition;
       System.out.println(query);
       try {
           ResultSet rs = stmt.executeQuery(query);
           List list = new ArrayList();
           while(rs.next()){
               list.add(new Integer(rs.getInt(1)));
           }
           Integer [] os = new Integer[list.size()];
           list.toArray(os);
           int [] orders = new int[os.length];
           for(int i =0;i<os.length;i++)
            orders[i] = os[i].intValue();
           return orders;
       } catch (SQLException e) {
           e.printStackTrace();
           return null;
       }
   }

   public Order QueryOrder(int orderID) 
   {
       String query = "SELECT * FROM hotel_order WHERE orderID = '"+ orderID +"'";
       try {
           ResultSet rs = stmt.executeQuery(query);
           while(rs.next()){
               Order order = new Order();
               order.set_id(rs.getInt(1));
               order.set_roomType(rs.getInt(2));
               order.set_startTime(new Time(rs.getLong(3)));
               order.set_endTime(new Time(rs.getLong(4)));
               order.set_orderTime(new Time(rs.getLong(5)));
               order.set_roomCount(rs.getInt(6));
               order.set_userName(rs.getString(8));
               order.set_needPay(rs.getInt(9));
               order.set_nowPay(rs.getInt(10));
               order.set_status(rs.getInt(11));
               String rooms = rs.getString(12);
               rooms.trim();
                String []results = rooms.split(":");
                int [] roomIDs = new int[results.length];
                for(int i =0;i<roomIDs.length;i++){
                    try{
                        roomIDs[i] = Integer.valueOf(results[i]).intValue();
                    }catch(Exception e){
                        roomIDs[i]= -1;
                    }
               }
               order.set_roomIDs(roomIDs);
               return order;
           }
           return null;
       } catch (SQLException e) {
           e.printStackTrace();
           return null;
       }
   }

   public int AddUser(User user)
   {
       String query = "SELECT * FROM hotel_user WHERE userName = '"+ user.get_userName() +"'";
        try {
           ResultSet rs = stmt.executeQuery(query);
           while(rs.next()){
               return 0;
           }
       } catch (SQLException e) {
           e.printStackTrace();
           return 0;
       }
       if((user.get_identity()!= User.GRP_OPERATOR) && (user.get_identity() != User.GRP_CUSTOMER) && (user.get_identity() != User.GRP_ADMINISTRATOR))
            return 0;
       String insert = "INSERT INTO hotel_user VALUES( NULL," +
            "'"+ user.get_userName() +"',"+
            "'"+ user.get_password() +"',"+
            "'"+ user.get_identity() +"',"+
            "'"+ ((user.get_identity()!= User.GRP_CUSTOMER)?((Operator)user).get_id():-1)+"',"+
            "'"+ user.get_type() +"',"+
            "'"+ user.get_point() +"',"+
            "'"+ ((user.get_vip())?"1":"0") +"',"+
            "'"+ user.get_lastLoginIP()+"',"+
            "'"+ System.currentTimeMillis()+"',"+   // to be modify to last login time
            "'"+ user.get_address()+"',"+
            "'"+ user.get_fromCity()+"',"+
            "'China',"+ // to be modify to country name
            "'"+ user.get_email()+"',"+
            "'"+ user.get_mobile()+"',"+
            "'"+ user.get_phone()+"',"+
            "'"+ user.get_name()+"',"+
            "'"+ System.currentTimeMillis()+"',"+
            "'"+ (user.get_sex()?0:1)+"',"+
            "'"+ user.get_description()+"',"+
            "'"+ user.get_habit()+"',"+
            "'"+ user.get_ICQ()+"',"+
            "'"+ user.get_fax()+"'"+
            ")";
       System.out.println(insert);
       try {
           return stmt.executeUpdate(insert);
       } catch (SQLException e) {
           e.printStackTrace();