stat.jsp.bak

用jsp编写的留言本

<%@ page import="java.sql.*,Db_sql"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.Date"%>
<%@ page import="com.stevesoft.pat.*"%>

<%
String strSql;
Db_sql db = null;
String username="",usercode="";
String agent="",browser="",os="";
String ip="",referer="";
boolean bContinue = true;
int todayhits=0,totalhits=0,maxhits = 0,avghits=0;
int iID,iTemp;
Date dTodayDate;
boolean bNewday = false;
boolean bUpdateMax = false;

String iUid = request.getParameter("uid");

//String strOut = "document.write(\"<a href='http://10.1.1.202:18080/apps/stat/view.jsp?uid=";
String strOut = "document.write(\"<a href='http://202.84.10.71/apps/stat/view.jsp?uid=";
strOut += iUid + "' target='_BLANK'>";
String strBack="";

try {
//   db = new Db_sql("10.1.1.202","1521","oracle8","home4u","home4u");
   db = new Db_sql("202.84.10.191","7137","appdb","liu","liujie8");
   strSql = "select * from userinfo where username like '" + iUid + "' ";

   db.query(strSql);
   
   if (!db.next())
   {
      strSql = "delete from stat where puid='" + iUid + "'";
      db.query(strSql);
      strSql = "delete from statdetail where puid='" + iUid + "'";
      db.query(strSql);
      strSql = "delete from yearstat where puid='" + iUid + "'";
      db.query(strSql);
      strSql = "delete from weekstat where puid='" + iUid + "'";
      db.query(strSql);
      
      strOut += strBack;
   }
   else
   {
      username = db.getString("username").trim();
      //usercode = username.substring(0,4);
      usercode = db.getString("category");
      ip = request.getRemoteAddr();
      referer = request.getHeader("Referer");

      strSql  = " select (SYSDATE - theDate)*10000 as datediff " ;
      strSql += " from statdetail ";
      strSql += " where puid = '" + iUid + "'";
      strSql += " and ip = '" + ip + "' order by theDate desc ";

      db.query(strSql);
      
      if(db.next())
      {
	 		int iCount = (new Double(db.getDouble("datediff"))).intValue();
	 		//out.println("iCount=" + iCount);
	 		//70 equals 10 min
	 		if (iCount < 7)
	 		{
	 		  bContinue = false;
  	    	  strSql = "select totalhits from stat where puid='" + iUid + "'";
	    	  db.query(strSql);
	    	  db.next();
	    	  totalhits = db.getInt("totalhits") + 1;
	 		  
	 		}
      }
 		
  		//don't set limit for only home4U users
  		if (bContinue && referer != null)
      {
	 		if (referer.indexOf("202.106.186.28") != -1 &&
	     		referer.indexOf("china.com") != -1)
	    		bContinue = false;
      }
      
      if (bContinue)
      {
	    	strSql = "select * from stat where puid='" + iUid + "'";
	    	db.query(strSql);
	    	db.next();
	 		
	 		if (!db.next())
	 		{
	    		//New user
	    		strSql = "select max(id) as maxid from stat";
	    		db.query(strSql);
	    		db.next();
	    		int iNextID = db.getInt("maxid") + 1;
	    		

	    		strSql = "insert into stat(id,puid,starttime,totalhits,";
	    		strSql += "sitetype,todayhits,maxhits,avghits,todaydate) values(";
	    		strSql += iNextID + ",'" + iUid + "',SYSDATE,1,'";
	    		strSql += usercode + "',1,1,1,SYSDATE)";
	    		db.query(strSql);
	 		}
	 		else
	 		{
	 		   strSql = "select id,puid,starttime,totalhits,sitetype,maxhits,todayhits,";
	 		   strSql += " (round(SYSDATE-starttime)+1) as days,todaydate from stat ";
	    		strSql += " where puid='" + iUid + "'";
	    		db.query(strSql);
	    		db.next();
	    		totalhits = db.getInt("totalhits") + 1;
	    		
	    		if (db.getInt("days") != 0)
	    		  avghits = totalhits/db.getInt("days");
	    		else
	    		  avghits = totalhits;
	    		  
	    		todayhits = db.getInt("todayhits") + 1;
	    		
	    		iID = db.getInt("id");
	    		
	    		dTodayDate = db.getDate("todaydate");
	    		Date dCurr = new Date();
	    		if (dCurr.getDate() != dTodayDate.getDate()) 
            {
	       		bNewday = true;
	       		todayhits = 1;
            }
	    		else
	    		{
	       		if (todayhits > db.getInt("maxhits"))
	       		
	       		{
		  				bUpdateMax = true;
		  				maxhits = todayhits;
	       		}
	    		}
	    		
	    		if (bNewday)
	    		{
	       		strSql = "update stat set totalhits = " + totalhits + ",";
	       		strSql += " todayhits = " + todayhits + "," ;
	       		strSql += " avghits = " + avghits + "," ;
	       		strSql += " todaydate = SYSDATE ";
	       		strSql += " where puid = '" + iUid + "' and id = " + iID ;
	       		db.query(strSql);
	    		}
	    		else
	    		{
				  	strSql = "update stat set totalhits = " + totalhits + ",";
	  			  	strSql += " todayhits = " + todayhits + ",";
	  				strSql += " avghits = " + avghits + "," ;		  			  	
	  				strSql += " maxhits = " + maxhits ;
	  				strSql += " where puid = '" + iUid + "' and id = " + iID;
	  				db.query(strSql);
	    		}
	 		}
	 		
   //Record the detailed accesses
   agent = request.getHeader("User-Agent");
   //Regular expression
   //processing IE
   Regex reg = 
     Regex.perlCode("m#msie (\\d)|msie/(\\d)|internet explorer/(\\d)#i");
   reg.search(agent);
   if (reg.didMatch())
   {
      browser = "MSIE " + reg.stringMatched(1);
   } else 
   {
      reg = Regex.perlCode("m#msie|microsoft#i");
      reg.search(agent);
      if (reg.didMatch())
      {
	 		browser = "MSIE";
      } else
      {
         reg = Regex.perlCode("m#opera#i");
         reg.search(agent);
         if (reg.didMatch())
         {
	    		reg = Regex.perlCode("m#opera/(\\d)|opera (\\d)#i");
	    		reg.search(agent);
	    		if (reg.didMatch())
	    		{
	       		browser = "Opera " + reg.stringMatched(1);
	    		} else
	    		{
	       		browser = "Opera";
	    		}
         } else {
            reg =Regex.perlCode("m#ncsa|mosaic#i");
            reg.search(agent);
            if (reg.didMatch())
            {
	       		browser = "Mosaic";
            } else {
                //processing netscape
					reg = Regex.perlCode("m#netscape\\d/(\\d)#i");
					reg.search(agent);
					if (reg.didMatch())
					{
		   			browser = "Netscape " + reg.stringMatched(1);
               } else {
                   reg = Regex.perlCode("m#mozilla/(\\d)#i");
                   reg.search(agent);
                   if (reg.didMatch())
                   {
                      browser = "Nescape " + reg.stringMatched(1);
                   }
		   			 else {
		      			 browser = "Other";
		   			 }
              }
            }
         }
      }
   }
   
   reg = Regex.perlCode("m#win#i");
   reg.search(agent);
   if (reg.didMatch())
   {
      reg = Regex.perlCode("m#win95|windows 95|windows95|win 95#i");
      reg.search(agent);
      if (reg.didMatch())
      {
         os = "Windows 95";
      }
      else
      {
         reg = Regex.perlCode("m#win98|windows 98|windows98|win 98#i");
         reg.search(agent);
         if (reg.didMatch())
         {
            os = "Windows 98";
         }
         else
         {
            reg = Regex.perlCode("m#winnt|windowsnt|windows nt|win nt#i");
            reg.search(agent);
            if (reg.didMatch())
            {
               os = "Windows NT";
            }
            else
            {
               reg = Regex.perlCode("m#windows 3.1|win3.1|win 3.1#i");
               reg.search(agent);
               if (reg.didMatch())
               {
                  os = "Windows 3.1";
               }
               else
               {
                  os = "Windows";
               }
            }
         }
      }
   } else
   {
      reg = Regex.perlCode("m#mac#i");
      reg.search(agent);
      if (reg.didMatch())
      {
         reg = Regex.perlCode("m#ppc|powerpc#i");
         reg.search(agent);
         if (reg.didMatch())
         {
            os = "Mac PowerPC";
         }
         else
         {
            reg = Regex.perlCode("m#imac#i");
            reg.search(agent);
            if (reg.didMatch())
            {
               os = "IMac";
            }
            else
            {
               os = "Mac";
            }
         }
      }
      else
      {
         reg = Regex.perlCode("m#x11#i");
         reg.search(agent);
         if (reg.didMatch())
         {
            reg = Regex.perlCode("m#hp-ux#i");
            reg.search(agent);
            if (reg.didMatch())
            {
               os = "UNIX (HP-UX)";
            }
            else
            {
               reg = Regex.perlCode("m#linux#i");
               reg.search(agent);
               if (reg.didMatch())
               {
                  os = "Linux";
               }
               else
               {
                  reg = Regex.perlCode("m#sunos#i");
                  reg.search(agent);
                  if (reg.didMatch())
                  {
                     os = "UNIX (SunOS)";
                  }
                  else
                  {
                     reg = Regex.perlCode("m#bsd#i");
                     reg.search(agent);
                     if (reg.didMatch())
                     {
                        os = "UNIX (BSD)";
                     }
                     else
                     {
                        reg = Regex.perlCode("m#aix#i");
                        reg.search(agent);
                        if (reg.didMatch())
                        {
                           os = "UNIX (AIX)";
                        }
                        else
                        {
                           reg = Regex.perlCode("m#osf1#i");
                           reg.search(agent);
                           if (reg.didMatch())
                           {
                              os = "UNIX (OSF1)";
                           }
                           else
                           {
                              reg = Regex.perlCode("m#irix#i");
                              if (reg.didMatch())
                              {
                                 os = "UNIX (IRIX)";
                              }
                              else
                              {
                                 os = "UNIX";
                              }
                           }
                        }
                     }
                  }
               }
            }
         }
         else
         {
            os = "Other";
         }
      }
   }

//   strSql = "select count(*) as total from statdetail where puid = " + iUid;
   strSql = "select count(*) as total from statdetail where puid = '" + iUid + "'";
   db.query(strSql);
   db.next();
   int iTotal = db.getInt("total");
   
   if (iTotal >= 50)
   {
//      strSql = "select min(id) as minid from statdetail where puid = " + iUid;
      strSql = "select min(id) as minid from statdetail where puid = '" + iUid + "'";
      db.query(strSql);
      db.next();
      iTemp = db.getInt("minid");
      
      strSql = " delete from statdetail where id = " + iTemp;
      db.query(strSql);
   }
   strSql = "Select max(id) as maxid from statdetail";
   db.query(strSql);
   db.next();
   iTemp = db.getInt("maxid") + 1;
   
      
   strSql = " insert into statdetail(id,puid,thedate,ip,browser,os,referer) values(";
//   strSql += iTemp + "," + iUid + ",SYSDATE,'" + ip + "','";
   strSql += iTemp + ",'" + iUid + "',SYSDATE,'" + ip + "','";
   strSql += browser + "','" + os + "',";
   if (referer != null)
   {
      if (referer.length() > 50)
      referer = referer.substring(0,50);
      strSql += "'" + referer + "')";
   }
   else
   {
      strSql += "NULL)";
   }
   db.query(strSql);
   
   //update statistics data
   String weeks[] = new String[7];
   String months[] = new String[13];
   weeks[0] = "Sunday";
   weeks[1] = "Monday";
   weeks[2] = "Tuesday";
   weeks[3] = "Wednesday";
   weeks[4] = "Thursday";
   weeks[5] = "Friday";
   weeks[6] = "Saturday";
   months[1] = "Jan";
   months[2] = "Feb";
   months[3] = "Mar";
   months[4] = "Apr";
   months[5] = "May";
   months[6] = "Jun";
   months[7] = "Jul";
   months[8] = "Aug";
   months[9] = "Sep";
   months[10] = "Oct";
   months[11] = "Nov";
   months[12] = "Dec";
   
   Date dToday = new Date();
   String sWeek = weeks[dToday.getDay()];
   String sMonth = months[dToday.getMonth()+1];
   
//   strSql = "select * from weekstat where puid = " + iUid;
   strSql = "select * from weekstat where puid = '" + iUid + "'";
   db.query(strSql);
   boolean bTemp = db.next();
   if (!bTemp)
   {
      strSql = "select max(id) as maxid from weekstat";
      db.query(strSql);
      if (!db.next()) iTemp = 1;
      else iTemp = db.getInt("maxid") + 1;
      
      
      strSql = "insert into weekstat(id,puid,monday,tuesday,wednesday,";
      strSql += "thursday,friday,saturday,sunday) values(";
//      strSql += iTemp + "," + iUid + ",0,0,0,0,0,0,0)";
      strSql += iTemp + ",'" + iUid + "',0,0,0,0,0,0,0)";
      db.query(strSql);
   }
   strSql = " update weekstat set " + sWeek + " = " + sWeek + "+1 ";
//   strSql += " where puid = " + iUid;
   strSql += " where puid = '" + iUid + "'";

   db.query(strSql);

//   strSql = "select * from yearstat where puid = " + iUid;
   strSql = "select * from yearstat where puid = '" + iUid + "'";
   db.query(strSql);
   if (!db.next())
   {
      strSql = "select max(id) as maxid from yearstat";
      db.query(strSql);
      if (!db.next()) iTemp = 1;
      else iTemp = db.getInt("maxid") + 1;
      

      strSql = "insert into yearstat(id,puid,jan,feb,mar,apr,may,jun,";
      strSql += "jul,aug,sep,oct,nov,dec) values(";
//      strSql += iTemp + "," + iUid + ",0,0,0,0,0,0,0,0,0,0,0,0)";
      strSql += iTemp + ",'" + iUid + "',0,0,0,0,0,0,0,0,0,0,0,0)";
      db.query(strSql);
   }
   strSql = " update yearstat set " + sMonth + " = " + sMonth + "+1 ";
//   strSql += " where puid = " + iUid;
   strSql += " where puid = '" + iUid + "'";
   db.query(strSql);
  }
}
     
} catch(Exception e)
{
  out.println(e);
} finally
{
   String tokens = String.valueOf(totalhits);
   tokens = tokens.trim();
   strBack = "";
   for (int i=0;i<tokens.length();i++)
   {
      strBack += "<img src=http://202.84.10.71/apps/stat/" + tokens.charAt(i) + ".gif border=0>";
   }
   strBack += "</a>\");";
   strOut += strBack;
   
   try { db.close(); }
   catch(Exception e)
   {
   }
   out.println(strOut);
}
%>