subscribermanagementsystembean.java

Sun公司Dream项目

            s.pin = rs.getString(3);
            s.firstname = rs.getString(4);
            s.lastname = rs.getString(5);
            s.SimCardNum = rs.getString(11);
        } else {
            // check if user is a sub subscriber
            rs.close();
            stmt.close();
            stmt = conn.prepareStatement("SELECT * FROM  SUBSUBSCRIBERS WHERE SUBSUBSCRIBERS.SIMCARDNUM = ?");
            stmt.setQueryTimeout(QUERYTIMEOUT);
            stmt.setString(1, SIMNumber);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                s.masterid = rs.getInt(1);
                s.id = rs.getLong(2);
                s.login = rs.getString(3);
                s.pin = rs.getString(4);
                s.firstname = rs.getString(5);
                s.lastname = rs.getString(6);
                s.SimCardNum = rs.getString(7);
            } else {
                s = null;
            }
        }
        rs.close();
        stmt.close();
        conn.close();
        return s;
    }
    
    
    
    /**
     *updates the database with the rights information
     */
    public void updateFixedRightsInfo(RightsInfo userRightsInfo) throws java.rmi.RemoteException, SQLException{
        
        System.out.println("Checking inputs for sms.updateFixedRightsInfo");
        printRightsInfo(userRightsInfo);
        System.out.println("End of Checking inputs for sms.updateFixedRightsInfo");
        int remainingRights = userRightsInfo.remainingRights;
        String userid = userRightsInfo.userID;
        String contentid = userRightsInfo.movieName;
        
        Connection conn = dataSource.getConnection();
        
        //check if the user+contentid combination exists
        PreparedStatement stmt = conn.prepareStatement("SELECT RIGHTSKEY FROM OPERA_LICENSE_INFO WHERE (OPERA_LICENSE_INFO.CONTENTID = ?) AND (OPERA_LICENSE_INFO.USERID = ?) ");
        stmt.setQueryTimeout(QUERYTIMEOUT);
        stmt.setString(1, contentid);
        stmt.setString(2, userid);
        ResultSet rs = stmt.executeQuery();
        if(rs.next()) {
            System.out.println("Existing License for contentid "+contentid+" and userid "+userid);
            String rightskey = rs.getString(1);
            stmt = conn.prepareStatement("UPDATE  OPERA_LICENSE_INFO SET  OPERA_LICENSE_INFO.REMAINING = ? WHERE (OPERA_LICENSE_INFO.CONTENTID = ?) AND (OPERA_LICENSE_INFO.USERID = ?) ");
            stmt.setQueryTimeout(QUERYTIMEOUT);
            stmt.setInt(1, remainingRights);
            stmt.setString(2, contentid);
            stmt.setString(3, userid);
            stmt.executeUpdate();
            if (userRightsInfo.verbElements != null) {
                for (int i=0; i< userRightsInfo.verbElements.length; i++) {
                    PreparedStatement rightsSelectStmt = conn.prepareStatement("SELECT * FROM RIGHTS_INFO WHERE (VERBNAME = ?) AND (VERBARGNAME=?) AND (RIGHTSKEY = ?) ");
                    rightsSelectStmt.setQueryTimeout(QUERYTIMEOUT);
                    rightsSelectStmt.setString(1, userRightsInfo.verbElements[i].verbName);
                    rightsSelectStmt.setString(2, userRightsInfo.verbElements[i].verbArgName);
                    rightsSelectStmt.setString(3, rightskey);
                    ResultSet rightsResultSet = rightsSelectStmt.executeQuery();
                    if (rightsResultSet.next()){
                        System.out.println("Existing VerbElement for "+userRightsInfo.verbElements[i].verbName);
                        PreparedStatement rightsUpdateStmt = conn.prepareStatement("UPDATE RIGHTS_INFO SET VERBARGVAL = ? WHERE (VERBNAME = ?) AND (VERBARGNAME=?) AND (RIGHTSKEY = ?) ");
                        rightsUpdateStmt.setString(1, userRightsInfo.verbElements[i].verbArgValue);
                        rightsUpdateStmt.setString(2, userRightsInfo.verbElements[i].verbName);
                        rightsUpdateStmt.setString(3, userRightsInfo.verbElements[i].verbArgName);
                        rightsUpdateStmt.setString(4, rightskey);
                        rightsUpdateStmt.executeUpdate();
                        rightsUpdateStmt.close();
                    } else {
                        PreparedStatement rightsInsertStmt = conn.prepareStatement("INSERT INTO RIGHTS_INFO (VERBNAME, VERBARGNAME, VERBARGVAL,RIGHTSKEY) VALUES (?,?,?,?) ");
                        rightsInsertStmt.setString(1, userRightsInfo.verbElements[i].verbName);
                        rightsInsertStmt.setString(2, userRightsInfo.verbElements[i].verbArgName);
                        rightsInsertStmt.setString(3, userRightsInfo.verbElements[i].verbArgValue);
                        rightsInsertStmt.setString(4, rightskey);
                        System.out.println("Insert new data "+rightsInsertStmt);
                        rightsInsertStmt.executeUpdate();
                        rightsInsertStmt.close();
                    }
                }
            }
        } else {
            stmt = conn.prepareStatement("INSERT INTO OPERA_LICENSE_INFO (RIGHTSKEY, LICENSETYPE,USERID,SHOPID,CONTENTID,TOTAL,REMAINING,STARTTIME,ENDTIME) VALUES(?,?,?,?,?,?,?,?,?)");
            stmt.setQueryTimeout(0);
            String rightsKey = userid+contentid;
            stmt.setString(1, rightsKey);
            stmt.setInt(2, userRightsInfo.rightsType);
            stmt.setString(3, userid);
            stmt.setString(4, "operaShop0");
            stmt.setString(5, contentid);
            stmt.setInt(6, userRightsInfo.remainingRights);
            stmt.setInt(7, userRightsInfo.remainingRights);
            System.out.println("Insert new data for rightskey "+rightsKey);
            Timestamp st = null;
            if (userRightsInfo.rightsStartDate != null) 
                st = new Timestamp(userRightsInfo.rightsStartDate.getTime());
            else
                st = new Timestamp(1000 * 60 * 60 * 24 * 365 * 30);
            System.out.println ("StartDate is "+st);
            Timestamp dt = null; 
            if (userRightsInfo.rightsEndDate != null) 
                dt = new Timestamp(userRightsInfo.rightsEndDate.getTime());
            else
                dt = new Timestamp(1000 * 60 * 60 * 24 * 365 * 37);
            System.out.println ("EndDate is "+dt);
            stmt.setTimestamp(8, st);
            stmt.setTimestamp(9, dt);
            stmt.executeUpdate();
            System.out.println("Insert new License row "+stmt);
            if (userRightsInfo.verbElements.length > 0) {
                for (int i=0; i< userRightsInfo.verbElements.length; i++) {
                    PreparedStatement rightsInsertStmt = conn.prepareStatement("INSERT INTO RIGHTS_INFO (VERBNAME, VERBARGNAME, VERBARGVAL,RIGHTSKEY) VALUES (?,?,?,?) ");
                    rightsInsertStmt.setString(1, userRightsInfo.verbElements[i].verbName);
                    rightsInsertStmt.setString(2, userRightsInfo.verbElements[i].verbArgName);
                    rightsInsertStmt.setString(3, userRightsInfo.verbElements[i].verbArgValue);
                    rightsInsertStmt.setString(4, rightsKey);
                    rightsInsertStmt.executeUpdate();
                    System.out.println("Insert new data 2"+rightsInsertStmt);
                    rightsInsertStmt.close();
                }
            }
        }
        stmt.close();
        conn.close();
    }
    
    /*
     * inserts user information into subsubcriber table when user registers in the admin pages
     */
    public String registerUser(long masterID,String login, String password, String firstName, String lastName, String SIMNum) throws SQLException,java.rmi.RemoteException {
        String returnMsg= "User registration successfull";
        
        try {
            Connection conn = dataSource.getConnection();
            PreparedStatement stmt1 = conn.prepareStatement("SELECT * FROM  MAXIDVALUES");
            int nextUserID = -1;
            int maxUserID = -1;
            ResultSet rs = stmt1.executeQuery();
            if (rs.next()) {
                maxUserID = rs.getInt(1);
                //System.out.println("maxuserid from maxid table" +maxUserID);
                nextUserID = maxUserID +1;
            }
            stmt1.close();
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO SUBSUBSCRIBERS (MASTERID,ID,LOGIN,PIN,FIRSTNAME,LASTNAME,SIM) VALUES(?,?,?,?,?,?,?)");
            stmt.setQueryTimeout(QUERYTIMEOUT);
            stmt.setLong(1, masterID);
            stmt.setInt(2, nextUserID);
            stmt.setString(3, login);
            stmt.setString(4, password);
            stmt.setString(5, firstName);
            stmt.setString(6, lastName);
            stmt.setString(7, SIMNum);
            stmt.executeUpdate();
            stmt.close();
            
            PreparedStatement stmt2 = conn.prepareStatement("UPDATE  MAXIDVALUES SET MAXUSERID = ? WHERE (MAXIDVALUES.MAXUSERID = ?)");
            stmt2.setInt(1,nextUserID);
            stmt2.setInt(2,maxUserID);
            stmt2.executeUpdate();
            stmt2.close();
            
            conn.close();
        } catch(Exception ee) {
            System.out.println("exception in " +ee);
            returnMsg = " User registration failed " + ee;
        }
        return returnMsg;
    }
    
     /*
      * Shopped rights are added to the database.
      *
      */
    public void addRightsInfo(String Userid,String contentid,int rightsType, int NumRights) throws SQLException,java.rmi.RemoteException {
        
        //System.out.println(" userid " +Userid);
        //System.out.println(" rights" + rightsType);
        //System.out.println("contentid " + contentid);
        //System.out.println("No of rights" + NumRights);
        Timestamp startDate = new Timestamp(System.currentTimeMillis());
        Timestamp endDate = new Timestamp(System.currentTimeMillis() + 1000 * 60 * 60 * 24 * 365);
        int defaultrightsType = 0;
        //System.out.println(" rights" + defaultrightsType);
        try {
            
            Connection conn = dataSource.getConnection();
            PreparedStatement stmt = conn.prepareStatement("SELECT REMAINING FROM  OPERA_LICENSE_INFO WHERE (CONTENTID = ?) AND (LICENSETYPE = ?) AND (USERID = ?)");
            stmt.setQueryTimeout(QUERYTIMEOUT);
            stmt.setString(1, contentid);
            stmt.setInt(2, defaultrightsType);
            stmt.setString(3, Userid);
            ResultSet rs = stmt.executeQuery();
            RightsInfo r = new RightsInfo();
            if (rs.next()) {
                r.remainingRights = rs.getInt(1);
                System.out.println("remaining rights for now are" +r.remainingRights);
                int rightsNow = r.remainingRights + NumRights;
                System.out.println("remaining rights for now are" +rightsNow);
                
                PreparedStatement stmt1 = conn.prepareStatement("UPDATE  OPERA_LICENSE_INFO SET  OPERA_LICENSE_INFO.REMAINING = ? WHERE ( (OPERA_LICENSE_INFO.CONTENTID = ?) AND (OPERA_LICENSE_INFO.USERID = ?) AND (OPERA_LICENSE_INFO.LICENSETYPE=?))");
                stmt.setQueryTimeout(QUERYTIMEOUT);
                stmt1.setInt(1, rightsNow);
                stmt1.setString(2, contentid);
                stmt1.setString(3, Userid);
                stmt1.setInt(4, defaultrightsType);
                stmt1.executeUpdate();
                stmt1.close();
            } else {
                PreparedStatement stmt2 = conn.prepareStatement("INSERT INTO OPERA_LICENSE_INFO (RIGHTSKEY, LICENSETYPE,USERID,SHOPID,CONTENTID,TOTAL,REMAINING,STARTTIME,ENDTIME) VALUES(?,?,?,?,?,?,?,?,?)");
                stmt2.setQueryTimeout(0);
                
                String rightsKey = Userid+contentid;
                stmt2.setString(1, rightsKey);
                stmt2.setInt(2, defaultrightsType);
                stmt2.setString(3, Userid);
                stmt2.setString(4, "Shop0");
                stmt2.setString(5, contentid);
                stmt2.setInt(6, NumRights);
                stmt2.setInt(7, NumRights);
                stmt2.setTimestamp(8, startDate);
                stmt2.setTimestamp(9,endDate);
                System.out.println("statement 2 is :"+stmt2);
                stmt2.executeUpdate();
                //System.out.println("result  of query exec" +exec);
                stmt2.close();
            }
            
            stmt.close();
            conn.close();
        } catch(Exception ee) {
            System.out.println("exception in " +ee);
        }
        
    }
    
    private void logStrMessage (String varName, String varVal) {
        if (varVal == null)
            System.out.println("Warning : "+varName+" is null");
        else
            System.out.println("Value of "+varName+" is "+varVal);
    }
    
    private void printRightsInfo (RightsInfo rightsInfo) {
        if (rightsInfo != null) {
            logStrMessage("rightsInfo.userID", rightsInfo.userID);
            logStrMessage("rightsInfo.movieName", rightsInfo.movieName);
            System.out.println("rightsInfo.expired "+rightsInfo.expired);
            System.out.println("rightsInfo.remainingRights"+rightsInfo.remainingRights);
            if (rightsInfo.rightsStartDate!=null)
                logStrMessage("rightsInfo.rightsStartDate",rightsInfo.rightsStartDate.toString());
            if (rightsInfo.rightsEndDate!=null)
                logStrMessage("rightsInfo.rightsEndDate",rightsInfo.rightsEndDate.toString());
            if (rightsInfo.verbElements != null) {
                for(int k=0; k<rightsInfo.verbElements.length; k++) {
                    if (rightsInfo.verbElements[k]!= null){
                        logStrMessage("rightsInfo.verbElements["+k+"].verbName = ", rightsInfo.verbElements[k].verbName);
                        logStrMessage("rightsInfo.verbElements["+k+"].verbArgName = ", rightsInfo.verbElements[k].verbArgName);
                        logStrMessage("rightsInfo.verbElements["+k+"].verbArgValue = ", rightsInfo.verbElements[k].verbArgValue);
                    }
                }
            }
        } else
            System.out.println("rightsInfo is null !!");
    }    
    
}