package service;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import beans.Profit;

import jdbc.JdbcConn;

public class Service {

private Connection dbConnection;

private Statement st,st1,st2;

private ResultSet rs,rs1,rs2;

private String sql;

@SuppressWarnings("rawtypes")

private List list;

private Profit profit;

@SuppressWarnings({ "rawtypes", "unchecked" })

public List getProfit(){

list=new ArrayList();

dbConnection=JdbcConn.getConnection();

try {

st=dbConnection.createStatement();

st1=dbConnection.createStatement();

st2=dbConnection.createStatement();

sql="select g.goods_name goodsName,g.selling_price selling,g.cost_price costPrice, g.goods_id goodsId from goodslist g,trading_information t where t.trading_goods_id=g.goods_id group by g.goods_name,g.selling_price,g.cost_price, g.goods_id";

rs=st.executeQuery(sql);

int temp;

while(rs.next()){

profit=new Profit();

profit.setGoodsName(rs.getString("goodsName"));

profit.setSellingPrice(rs.getInt("selling"));

profit.setCostPrice(rs.getInt("costPrice"));

profit.setGoodsId(rs.getInt("goodsId"));

temp=0;

temp=profit.getSellingPrice()-profit.getCostPrice();

sql="select sum(t.trading_number)sumNum from trading_information t where t.trading_goods_id="+profit.getGoodsId();

rs1=st1.executeQuery(sql);

while(rs1.next()){

profit.setTradingNum(rs1.getInt("sumNum"));

}

profit.setProfit(temp*profit.getTradingNum());

sql="select count(t.trading_id)times from trading_information t where trading_goods_id="+profit.getGoodsId();

rs2=st2.executeQuery(sql);

while(rs2.next()){

profit.setTimes(rs2.getInt("times"));

}

list.add(profit);

System.out.println(list);

}

} catch (SQLException e) {

e.printStackTrace();

}

return list;

}

/**

* 測(cè)試

*/

// public static void main(String[] args) {

// Service service = new Service();

// service.getProfit();

// }

}