knowmate_s.jsp

用手机浏览的一个类似于聊天室 博克 homepage 等 对这方面有 兴趣的朋友 有所帮助的

<!-------------------------------------------------------------
* @ name: knowmate_s.jsp
* @ Author : ranxu 
* @ date :2008-06-05
-------------------------------------------------------------->
<%@ page import='java.io.*,java.text.*,java.util.Date,java.util.Vector, java.sql.*, com.entaz.lib.db.*, com.entaz.relay.net.*' contentType='text/html;charset=euc-kr'%>
<%@ include file="../imgpath.jsp"%>
<%@ include file="../common_func.jsp"%>
<%@ include file="../makeKOR.jsp"%>
<%! 
	/*------------------------------------------------------------------------------------------------------
	+	method:getAnswer()
		action:Answer 1(db)--"O"(jsp)  0(db)--"X"(jsp)
		author:ranxu 2008-06-05
	-------------------------------------------------------------------------------------------------------*/
	int ANSWER_O = 1;
	int ANSWER_X = 0;
	
	public String getAnswer(int key) 
	{
		String ret = "";
		if(key==ANSWER_O){
			ret = "O";
		}else if(key==ANSWER_X){
			ret = "X"; 
		}
		return ret;
	}	
%>
<%
	/*------------------------------------------------------------------------------------------------------
		parameter from last page:soulmate_main.jap
	-------------------------------------------------------------------------------------------------------*/
	String nickname = java.net.URLDecoder.decode(makeKOR(request.getParameter("nickname")));
	String mphone = request.getParameter("mphone");
	String gphone = request.getParameter("gphone");
	String status = java.net.URLDecoder.decode(makeKOR(request.getParameter("status")));	
	String guestAnswer = java.net.URLDecoder.decode(makeKOR(request.getParameter("ox")));
	
	
	/*------------------------------------------------------------------------------------------------------
	+	DB 
	-------------------------------------------------------------------------------------------------------*/
	PreparedStatement pstmt = null;
	Connection con = null;
	Statement stmt = null;
	ResultSet rs = null;
	String sql = null;
	int ret = -1;
	
	/*------------------------------------------------------------------------------------------------------
	 +	parameter of current page
	 -------------------------------------------------------------------------------------------------------*/
	int quiz_idx=0;
	String title = "";
	int hostAnswer = 0;
	int nregdate = 0;
	String regdate = "";
	double allCount = 0.0;
	double answeredCount = 0.0;
	double answeredRightCount = 0.0;
	int unansweredCount = 0;
	List unansweredIdxs = null;
	List unansweredTitles = null;
	List hostAnswers = null;
	int matchPoint=0;
	int knowPoint=0;
	
	
	
	
	
	try
	{
	/*------------------------------------------------------------------------------------------------
	 *		DB 目池记
	 -------------------------------------------------------------------------------------------------*/
	%><%@ include file="../getConnection.jsp"%><%
    
    /**************************************
			胶鸥客 嘎苗焊绰 矾宏款技!
	***************************************/
	
	
	
	
	
	/*-------------------------------------------------------------------------------------------------------
		get title and answer of the blog's host
	-------------------------------------------------------------------------------------------------------*/	
	
	sql = " select top 1 a.* from gt_lucky_quiz_mylog a,gt_lucky_quiz b"+
		  " where a.quiz_idx = b.idx and b.flag=1 "+
		  " and a.phonenum =? and a.guest_phone=?"+
		  " and a.quiz_idx not in"+
		  " (select distinct a.quiz_idx from gt_lucky_quiz_mylog a,gt_lucky_quiz b"+
		  " where  a.quiz_idx = b.idx and b.flag=1 "+
		  " and a.phonenum =? and a.guest_phone =?"+
		  " )order by a.idx desc";
		  
	pstmt = con.prepareStatement(sql);
	pstmt.setLong(1,Long.parseLong(gphone));
	pstmt.setLong(2,Long.parseLong(gphone));
	pstmt.setLong(3,Long.parseLong(gphone));
	pstmt.setLong(4,Long.parseLong(mphone));
	rs=pstmt.executeQuery();
	if(rs.next()){
		quiz_idx = rs.getInt("quiz_idx");
		title = rs.getString("title");
		hostAnswer = rs.getInt("answer");
	}
	rs.close();
	pstmt.close();
	/*-------------------------------------------------------------------------------------------------------
		insert guest's answer 
	-------------------------------------------------------------------------------------------------------*/
	if(status.equals("insertOne")){
					
		sql="select * from gt_lucky_quiz_mylog where quiz_idx =? and phonenum=? and guest_phone=?";
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,quiz_idx);
		pstmt.setLong(2,Long.parseLong(gphone));
		pstmt.setLong(3,Long.parseLong(mphone));
		
		rs = pstmt.executeQuery();
		if(rs.next()){
		}else{
						
			sql="insert into gt_lucky_quiz_mylog (quiz_idx,title,answer,phonenum,guest_phone)values (?,?,?,?,?)";
			
			pstmt = con.prepareStatement(sql);	
			pstmt.setInt(1,quiz_idx);	
			pstmt.setString(2,title);		
			pstmt.setString(3,guestAnswer);	
			pstmt.setString(4,gphone);	
			pstmt.setString(5,mphone);

			ret = pstmt.executeUpdate();
			if(ret==-1){		
				return;
			}
			pstmt.close();
		}
		rs.close();
		pstmt.close();				
	}
	
	/*-------------------------------------------------------------------------------------------------------
		get the count of all title which the host have answered
	-------------------------------------------------------------------------------------------------------*/
	
	sql = " select  count(1) allcount from gt_lucky_quiz_mylog a,gt_lucky_quiz b "+
		  " where a.quiz_idx = b.idx and b.flag=1 "+
		  " and a.phonenum =? and a.guest_phone=? ";
	pstmt = con.prepareStatement(sql);
	pstmt.setLong(1,Long.parseLong(gphone));
	pstmt.setLong(2,Long.parseLong(gphone));
	rs=pstmt.executeQuery();
	if(rs.next()){
		allCount = rs.getDouble("allcount");
	}
	rs.close();
	pstmt.close();
	/*-------------------------------------------------------------------------------------------------------
		get the count of guest answered title
	-------------------------------------------------------------------------------------------------------*/
	
	sql = " select count(distinct a.idx) answeredcount from gt_lucky_quiz_mylog a,gt_lucky_quiz b"+
		  " where  a.quiz_idx = b.idx and b.flag=1  "+
		  " and a.phonenum =? and a.guest_phone =?";
	pstmt = con.prepareStatement(sql);
	pstmt.setLong(1,Long.parseLong(gphone));
	pstmt.setLong(2,Long.parseLong(mphone));
	rs=pstmt.executeQuery();
	if(rs.next()){
		answeredCount = rs.getDouble("answeredcount");
	}
	rs.close();
	pstmt.close();
	/*-------------------------------------------------------------------------------------------------------
		update attention point when the guest answer the question at the first time
	-------------------------------------------------------------------------------------------------------*/
	System.out.println(answeredCount+"====answeredCount===1.0====");
	if(answeredCount==1.0){
		sql = "select * from gt_lucky_attention_point where phonenum=? and guest_phone=? ";
		pstmt = con.prepareStatement(sql);
		pstmt.setLong(1,Long.parseLong(gphone));
		pstmt.setLong(2,Long.parseLong(mphone));
		rs=pstmt.executeQuery();
		if(rs.next()){
			sql = "update gt_lucky_attention_point set attention_point=attention_point+1 where phonenum=? and guest_phone=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1,Long.parseLong(gphone));
			pstmt.setLong(2,Long.parseLong(mphone));
			ret = pstmt.executeUpdate();
			pstmt.close();
		}else{
			sql = "insert into gt_lucky_attention_point (phonenum,guest_phone,attention_point,attention_point_old) values (?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1,Long.parseLong(gphone));
			pstmt.setLong(2,Long.parseLong(mphone));
			pstmt.setLong(3,1);
			pstmt.setLong(4,0);
			ret = pstmt.executeUpdate();
			pstmt.close();
		}
		rs.close();
	}
	
	
	
	
	/*-------------------------------------------------------------------------------------------------------
		get the count of guest answered right title
	-------------------------------------------------------------------------------------------------------*/
	
	sql = " select count(a.idx) answeredrightcount from gt_lucky_quiz_mylog a,gt_lucky_quiz b,gt_lucky_quiz_mylog c"+
		  " where a.quiz_idx = b.idx and b.flag=1 and a.quiz_idx=c.quiz_idx and a.answer = c.answer"+
		  " and a.phonenum =? and a.guest_phone =? "+
		  " and c.phonenum =? and c.guest_phone =? ";
	pstmt = con.prepareStatement(sql);
	pstmt.setLong(1,Long.parseLong(gphone));
	pstmt.setLong(2,Long.parseLong(gphone));
	pstmt.setLong(3,Long.parseLong(gphone));
	pstmt.setLong(4,Long.parseLong(mphone));
	rs=pstmt.executeQuery();
	if(rs.next()){
		answeredRightCount = rs.getDouble("answeredrightcount");
	}
	rs.close();
	pstmt.close();
	/*-------------------------------------------------------------------------------------------------------
		get the count of guest unanswered title
	-------------------------------------------------------------------------------------------------------*/
	
	
	sql = " select  count(a.idx) answeredcount from gt_lucky_quiz_mylog a,gt_lucky_quiz b"+
		  " where a.quiz_idx = b.idx and b.flag=1 "+
		  " and a.phonenum =? and a.guest_phone=?"+
		  " and a.quiz_idx not in"+
		  " (select distinct a.quiz_idx from gt_lucky_quiz_mylog a,gt_lucky_quiz b"+
		  " where  a.quiz_idx = b.idx and b.flag=1 "+
		  " and a.phonenum =? and a.guest_phone =? )";
	
	pstmt = con.prepareStatement(sql);
	pstmt.setLong(1,Long.parseLong(gphone));
	pstmt.setLong(2,Long.parseLong(gphone));
	pstmt.setLong(3,Long.parseLong(gphone));
	pstmt.setLong(4,Long.parseLong(mphone));
	rs=pstmt.executeQuery();
	if(rs.next()){
		unansweredCount = rs.getInt("answeredcount");
	}
	rs.close();
	pstmt.close();	
	/*-------------------------------------------------------------------------------------------------------
		get the unanswered title
	-------------------------------------------------------------------------------------------------------*/
		unansweredTitles = new ArrayList();
	 	hostAnswers = new ArrayList();
	 	unansweredIdxs = new ArrayList();
		
		sql = " select top 5 a.* from gt_lucky_quiz_mylog a,gt_lucky_quiz b"+
			  " where a.quiz_idx = b.idx and b.flag=1 "+
			  " and a.phonenum =? and a.guest_phone=? "+
			  " and a.quiz_idx not in "+
			  " (select distinct a.quiz_idx from gt_lucky_quiz_mylog a,gt_lucky_quiz b"+
			  " where  a.quiz_idx = b.idx and b.flag=1 "+
			  " and a.phonenum =? and a.guest_phone =? "+
			  " )order by a.idx desc";
		pstmt = con.prepareStatement(sql);
		pstmt.setLong(1,Long.parseLong(gphone));
		pstmt.setLong(2,Long.parseLong(gphone));
		pstmt.setLong(3,Long.parseLong(gphone));
		pstmt.setLong(4,Long.parseLong(mphone));
		rs=pstmt.executeQuery();
		while(rs.next()){
			unansweredIdxs.add(rs.getString("quiz_idx"));
			unansweredTitles.add(rs.getString("title"));
			hostAnswers.add(rs.getString("answer"));
		}
		rs.close();
		pstmt.close();
		for(int i=0;i<unansweredIdxs.size();i++){
		System.out.println(unansweredTitles.get(i)+"===unansweredTitles====s");
		System.out.println(unansweredIdxs.get(i)+"===unansweredIdxs====s");}
		
		/*-------------------------------------------------------------------------------------------------------
		概莫档 捞秦档 insert,update
	-------------------------------------------------------------------------------------------------------*/
	matchPoint = (int)(answeredRightCount/answeredCount*100);
	knowPoint = (int)(answeredRightCount/allCount*100);
	if(matchPoint>100){
		matchPoint = 100;
	}
	if(knowPoint>100){
		knowPoint = 100;
	}
	sql = "select * from gt_lucky_match_point where phonenum=? and guest_phone=? ";
		pstmt = con.prepareStatement(sql);
		pstmt.setLong(1,Long.parseLong(gphone));
		pstmt.setLong(2,Long.parseLong(mphone));
		rs=pstmt.executeQuery();
		if(rs.next()){
			sql = "update gt_lucky_match_point set match_point=?,know_point = ? where phonenum=? and guest_phone=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1,matchPoint);
			pstmt.setLong(2,knowPoint);
			pstmt.setLong(3,Long.parseLong(gphone));
			pstmt.setLong(4,Long.parseLong(mphone));
			ret = pstmt.executeUpdate();
			pstmt.close();
		}else{
			sql = "insert into gt_lucky_match_point (phonenum,guest_phone,match_point,know_point) values (?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1,Long.parseLong(gphone));
			pstmt.setLong(2,Long.parseLong(mphone));
			pstmt.setLong(3,matchPoint);
			pstmt.setLong(4,knowPoint);
			ret = pstmt.executeUpdate();
			pstmt.close();
		}
		rs.close();
		pstmt.close();

	// QVGA HTML
	//
	if( isQVGA )
	{
		%><%@ include file="/html/knowmate_s.jsp"%><%
	}
	// QCIF HTML
	//
	else
	{
		%><%@ include file="/html/qcif/knowmate_s.jsp"%><%
	}
		}
	catch(Exception e)
	{
		throw e;
	}
	finally
	{ 
		if ( rs != null ) { try { rs.close(); } catch ( Exception e ) { } }
		if ( stmt != null ) { try { stmt.close(); } catch ( Exception e ) { } }
		if ( con != null ) { try { con.close(); } catch ( Exception e ) { } }
	}
%>