op_sale_statistics.java~8~

基于JSP的网上书店.功能较为全面 开发环境:sql server2000+tomcat+jdk

package bookshop.run;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */
import java.util.*;
import bookshop.util.*;
public class op_sale_statistics extends DataBase {

  ArrayList al=new ArrayList();

  public op_sale_statistics() {
  }

public ArrayList getSaleNumOrder(String classid,String keyword)
       {
         String sql="";
         if((classid!=null&&!classid.equals(""))&&(keyword==null||keyword.equals("")))
          {  sql= "select top 100 book.bookname bookname,sum(allorder.amount) num "+
                   " from allorder,book,bookclass "+
                   "  where allorder.bookno=book.id  "+
                   "  and book.bookclass=bookclass.id "+
                   "  and bookclass.id='"+classid+"'"+
                   "  group by bookname  "+
                   "  order by num desc ";
          }
        else  if((classid==null||classid.equals(""))&&(keyword!=null&&!keyword.equals("")))
           {  sql= "select top 100 book.bookname bookname,sum(allorder.amount) num "+
                    " from allorder,book,bookclass "+
                    "  where allorder.bookno=book.id  "+
                    "  and book.bookclass=bookclass.id "+
                    "  and book.bookname like '%"+keyword+"%' "+
                    "  group by bookname  "+
                    "  order by num desc ";
           }
        else   if((classid!=null&&!classid.equals(""))&&(keyword!=null&&!keyword.equals("")))
            {  sql= "select top 100 book.bookname bookname,sum(allorder.amount) num "+
                     " from allorder,book,bookclass "+
                     "  where allorder.bookno=book.id  "+
                     "  and book.bookclass=bookclass.id "+
                     "  and bookclass.id='"+classid+"'"+
                     "  and book.bookname like '%"+keyword+"%' "+
                     "  group by bookname  "+
                     "  order by num desc ";
            }
          else
          {
            sql = "select top 100 book.bookname bookname,sum(allorder.amount) num from allorder,book " +
                    " where allorder.bookno=book.id " +
                    " group by bookname " +
                    " order by num desc";

          }

            try{
                    DataBase db = new DataBase();
                    db.connect();
                    stmt = db.conn.createStatement ();
                    rs=stmt.executeQuery(sql);
                    while (rs.next())
                     {
                         String [] result=new String[2];
                         result[0]=rs.getString("bookname");
                         result[1]=rs.getString("num");
                         al.add(result);
                       }
                              return al;
                         } catch (Exception e){
                           System.out.println(e.getMessage());
                          return null;

                         }

  }

}