op_sale_statistics.java

java+javabean开发的一套网上购物系统.

package cdshop.run;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @Singer not attributable
 * @version 1.0
 */
import java.util.*;
import cdshop.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 cd.cdname cdname,sum(allorder.amount) num "+
                   " from allorder,cd,cdclass "+
                   "  where allorder.cdno=cd.id  "+
                   "  and cd.cdclass=cdclass.id "+
                   "  and cdclass.id='"+classid+"'"+
                   "  group by cdname  "+
                   "  order by num desc ";
          }
        else  if((classid==null||classid.equals(""))&&(keyword!=null&&!keyword.equals("")))
           {  sql= "select top 100 cd.cdname cdname,sum(allorder.amount) num "+
                    " from allorder,cd,cdclass "+
                    "  where allorder.cdno=cd.id  "+
                    "  and cd.cdclass=cdclass.id "+
                    "  and cd.cdname like '%"+dataFormat.toUni(keyword)+"%' "+
                    "  group by cdname  "+
                    "  order by num desc ";
           }
        else   if((classid!=null&&!classid.equals(""))&&(keyword!=null&&!keyword.equals("")))
            {  sql= "select top 100 cd.cdname cdname,sum(allorder.amount) num "+
                     " from allorder,cd,cdclass "+
                     "  where allorder.cdno=cd.id  "+
                     "  and cd.cdclass=cdclass.id "+
                     "  and cdclass.id='"+classid+"'"+
                     "  and cd.cdname like '%"+dataFormat.toUni(keyword)+"%' "+
                     "  group by cdname  "+
                     "  order by num desc ";
            }
          else
          {
            sql = "select top 100 cd.cdname cdname,sum(allorder.amount) num from allorder,cd " +
                    " where allorder.cdno=cd.id " +
                    " group by cdname " +
                    " order by num desc";
          }

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

  }

}