📄 bestsalerpane.java
字号:
package file2;
import java.awt.*;
import javax.swing.*;
import java.sql.*;
import java.text.*;
import java.util.Calendar;
import java.util.StringTokenizer;
import java.text.SimpleDateFormat;
public class BestSalerPane extends JPanel{
private DBConnection con=null;
private JLabel tip1=new JLabel("本日/本月/本年最畅销商品");
private JLabel tip2=new JLabel("本日/本月/本年销售额最大商品");
private JLabel tip3=new JLabel("本日/本月/本年最赢利商品");
private JPanel tip1Pane=new JPanel();
private JPanel tip2Pane=new JPanel();
private JPanel tip3Pane=new JPanel();
private JTable table1=null;
private JTable table2=null;
private JTable table3=null;
private JScrollPane scrollPane1=null;
private JScrollPane scrollPane2=null;
private JScrollPane scrollPane3=null;
String[] titleForTables={"统计时间范围","商品名称","数量"};
public BestSalerPane(){
con=new DBConnection();
tip1Pane.setLayout(new FlowLayout(FlowLayout.CENTER));
tip2Pane.setLayout(new FlowLayout(FlowLayout.CENTER));
tip3Pane.setLayout(new FlowLayout(FlowLayout.CENTER));
tip1Pane.add(tip1);
tip2Pane.add(tip2);
tip3Pane.add(tip3);
java.util.Date date=new java.util.Date();
SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
String dateStr=formatter.format(date);
//JOptionPane.showMessageDialog(null, dateStr, "", JOptionPane.INFORMATION_MESSAGE);
int year=0;
int month=0;
StringTokenizer st=new StringTokenizer(dateStr,"-");
int numGet=0;//辅助获得月份
while(st.hasMoreTokens()){
if(numGet==0){
year=(new Integer(st.nextToken())).intValue();
}
if(numGet==1){
month=(new Integer(st.nextToken())).intValue();
break;
}
numGet++;
}
//JOptionPane.showMessageDialog(null,year+"-"+month,"测试",JOptionPane.INFORMATION_MESSAGE);//测试是否获得正确的年份和月份
ResultSet set=con.executeSelect1("SELECT COUNT(DISTINCT To_Third_name) AS num FROM operationRecord where operationState='1'");
int num=0;
try{
if(set.next()){
num=set.getInt(1);
}
}catch(Exception e){
}
int row=3*num;
if(row==0){
String[][] dataForTable1=new String[3][3];
dataForTable1[0][0]="本日最畅销商品";
dataForTable1[0][1]="无";
dataForTable1[0][2]="0";
dataForTable1[1][0]="本月最畅销商品";
dataForTable1[1][1]="无";
dataForTable1[1][2]="0";
dataForTable1[2][0]="本年最畅销商品";
dataForTable1[2][1]="无";
dataForTable1[2][2]="0";
table1=new JTable(dataForTable1,titleForTables);
scrollPane1=new JScrollPane(table1);
String[][] dataForTable2=new String[3][3];
dataForTable2[0][0]="本日销售额最大商品";
dataForTable2[0][1]="无";
dataForTable2[0][2]="0";
dataForTable2[1][0]="本月销售额最大商品";
dataForTable2[1][1]="无";
dataForTable2[1][2]="0";
dataForTable2[2][0]="本年销售额最大商品";
dataForTable2[2][1]="无";
dataForTable2[2][2]="0";
table2=new JTable(dataForTable2,titleForTables);
scrollPane2=new JScrollPane(table2);
String[][] dataForTable3=new String[3][3];
dataForTable3[0][0]="本日最赢利商品";
dataForTable3[0][1]="无";
dataForTable3[0][2]="0";
dataForTable3[1][0]="本月最赢利商品";
dataForTable3[1][1]="无";
dataForTable3[1][2]="0";
dataForTable3[2][0]="本年最赢利商品";
dataForTable3[2][1]="无";
dataForTable3[2][2]="0";
table3=new JTable(dataForTable3,titleForTables);
scrollPane3=new JScrollPane(table3);
this.setLayout(new GridLayout(6,1));
this.add(tip1);
this.add(scrollPane1);
this.add(tip2);
this.add(scrollPane2);
this.add(tip3);
this.add(scrollPane3);
}else{
String[][] dataForTable1=new String[row][3];
String query1="SELECT To_Third_name AS third_name, SUM(quantity) AS saled"+
" FROM operationRecord WHERE operationState = '1' AND operateTime ='"+
dateStr+"' GROUP BY To_Third_name";
set=con.executeSelect1(query1);
int sumOfBestToday=0;//置初始数量为零
int count=0;//置初始计数为零
try{
while(set.next()){
set.getString(1);
int quantity=set.getInt(2);
if(quantity>sumOfBestToday)
sumOfBestToday=quantity;
//JOptionPane.showMessageDialog(null,set.getInt(2)+"","测试",JOptionPane.INFORMATION_MESSAGE);//测试是否获得正确的本日最畅销商品数量
}
set.beforeFirst();
while(set.next()){
String name=set.getString(1);
int quantity=set.getInt(2);
if(quantity==sumOfBestToday){
dataForTable1[count][0]="本日最畅销商品";
dataForTable1[count][1]=name;
dataForTable1[count][2]=quantity+"";
count++;
}
}
}catch(Exception e){//e.printStackTrace();
}
//以上代码已经调试过，被证明没有错，可以成功运行并得到本日最畅销商品
//以下代码用来获得本年和本月最畅销商品
//JOptionPane.showMessageDialog(null, "count:"+count+",num:"+num, "提示", JOptionPane.INFORMATION_MESSAGE);//测试是否获得正确的num 和count
set=null;
String[][] nameAndQuantity1=new String[num][2];//存储本月所有销售商品名称和数量
String[][] nameAndQuantity2=new String[num][2];//存储本年所有销售商品名称和数量
set=con.executeSelect1("select distinct To_Third_name from operationRecord where operationState='1'");
int start=0;
try{
while(set.next()){
String name=set.getString(1);
nameAndQuantity1[start][0]=name;
nameAndQuantity1[start][1]="0";
nameAndQuantity2[start][0]=name;
nameAndQuantity2[start][1]="0";
start++;
}
}catch(Exception e){
e.printStackTrace();
}
set=null;
String query2="SELECT To_Third_name,quantity,operateTime FROM operationRecord WHERE operationState = '1'";
set=con.executeSelect1(query2);
try{
while(set.next()){
String name=set.getString(1);
int quantity=set.getInt(2);
String dateStrGet=formatter.format(set.getDate(3));
int yearGet=0;
int monthGet=0;
StringTokenizer st1=new StringTokenizer(dateStrGet,"-");
int countGet=0;
while(st1.hasMoreTokens()){
if(countGet==0)
yearGet=(new Integer(st1.nextToken())).intValue();
if(countGet==1){
monthGet=(new Integer(st1.nextToken())).intValue();
break;
}
countGet++;
}
if(yearGet==year&&monthGet==month){
for(int count1=0;count1<start;count1++){
if(nameAndQuantity1[count1][0].equals(name)){
int numNew=(new Integer(nameAndQuantity1[count1][1])).intValue()+quantity;
nameAndQuantity1[count1][1]=numNew+"";
}
if(nameAndQuantity2[count1][0].equals(name)){
int numNew=(new Integer(nameAndQuantity2[count1][1])).intValue()+quantity;
nameAndQuantity2[count1][1]=numNew+"";
}
}
}
if(yearGet==year&&monthGet!=month){
for(int count1=0;count1<start;count1++){
if(nameAndQuantity2[count1][0].equals(name)){
int numNew=(new Integer(nameAndQuantity2[count1][1])).intValue()+quantity;
nameAndQuantity2[count1][1]=numNew+"";
break;
}
}
}
}
int sumOfBestMonth=0;
int sumOfBestYear=0;
for(int startOfCount=0;startOfCount<num;startOfCount++){
int temp1=(new Integer(nameAndQuantity1[startOfCount][1])).intValue();
if(temp1>sumOfBestMonth){
sumOfBestMonth=temp1;
}
int temp2=(new Integer(nameAndQuantity2[startOfCount][1])).intValue();
if(temp2>sumOfBestYear){
sumOfBestYear=temp2;
}
}
for(int startOfCount=0;startOfCount<num;startOfCount++){
if(nameAndQuantity1[startOfCount][1].equals(""+sumOfBestMonth)){
dataForTable1[count][0]="本月最畅销商品";
dataForTable1[count][1]=nameAndQuantity1[startOfCount][0];
dataForTable1[count][2]=nameAndQuantity1[startOfCount][1];
count++;
}
}
for(int startOfCount=0;startOfCount<num;startOfCount++){
if(nameAndQuantity2[startOfCount][1].equals(""+sumOfBestYear)){
dataForTable1[count][0]="本年最畅销商品";
dataForTable1[count][1]=nameAndQuantity2[startOfCount][0];
dataForTable1[count][2]=nameAndQuantity2[startOfCount][1];
count++;
}
}
}catch(Exception e){
e.printStackTrace();
}
table1=new JTable(dataForTable1,titleForTables);
scrollPane1=new JScrollPane(table1);
//以下是获得本日/本月/本年销售额最大商品名称
set=null;
String[][] dataForTable2=new String[row][3];
String query3="SELECT To_Third_name AS third_name, SUM(price*quantity) AS saled FROM operationRecord,Third_Catalog"+
" WHERE To_Third_name=Third_name and operationState = '1' AND operateTime ='"+dateStr+"' GROUP BY To_Third_name";
set=con.executeSelect1(query3);
int saleOfBestToday=0;//置初始数量为零
int count1=0;//置初始计数为零
try{
while(set.next()){
set.getString(1);
int sale=set.getInt(2);
if(sale>saleOfBestToday)
saleOfBestToday=sale;
//JOptionPane.showMessageDialog(null,set.getInt(2)+"","测试",JOptionPane.INFORMATION_MESSAGE);//测试是否获得正确的本日最畅销商品数量
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -