viewreport.java

一个基于JAVA技术的销售信息管理系统

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.eclipse.swt.SWT;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.Composite;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.MessageBox;
import org.eclipse.swt.widgets.Table;
import org.eclipse.swt.widgets.TableColumn;
import org.eclipse.swt.widgets.TableItem;
import org.eclipse.swt.widgets.Text;
import com.swtdesigner.SWTResourceManager;


public class ViewReport extends Composite {

	private Table table;
	private Combo combo_2;
	private Combo combo_1;
	private Text text;
	private Combo combo;
	private MessageBox messageBox;
	
	private String id;
	private String pwd;
	/**
	 * Create the composite
	 * @param parent
	 * @param style
	 */
	public ViewReport(final Composite parent, int style,String id,String pwd) {
		super(parent, style);

		this.id	=	id;
		this.pwd	=	pwd;
		this.setBounds(0, 0, 699, 470);

		final Label label = new Label(this, SWT.SEPARATOR | SWT.HORIZONTAL);
		label.setBounds(0, 45, 700, 2);

		combo = new Combo(this, SWT.READ_ONLY);
		combo.setItems(new String[] {"年报表", "月报表"});
		combo.setBounds(181, 70, 66, 20);

		text = new Text(this, SWT.BORDER);
		text.setBounds(45, 133, 80, 0);

		combo_1 = new Combo(this, SWT.READ_ONLY);
		combo_1.setVisibleItemCount(8);
		combo_1.setBounds(304, 70, 85, 20);
		searchSellYear();

		final Label label_2 = new Label(this, SWT.NONE);
		label_2.setAlignment(SWT.RIGHT);
		label_2.setFont(SWTResourceManager.getFont("宋体", 11, SWT.NONE));
		label_2.setText("生成报表时间类型");
		label_2.setBounds(55, 72, 120, 17);

		final Label label_3 = new Label(this, SWT.NONE);
		label_3.setFont(SWTResourceManager.getFont("宋体", 11, SWT.NONE));
		label_3.setAlignment(SWT.RIGHT);
		label_3.setText("年份");
		label_3.setBounds(253, 72, 45, 17);


		final Label label_4 = new Label(this, SWT.NONE);
		label_4.setFont(SWTResourceManager.getFont("经典行楷繁", 22, SWT.NONE));
		label_4.setAlignment(SWT.CENTER);
		label_4.setText("报表查看平台");
		label_4.setBounds(0, 10, 700, 29);

		final Label label_1 = new Label(this, SWT.NONE);
		label_1.setAlignment(SWT.RIGHT);
		label_1.setFont(SWTResourceManager.getFont("宋体", 11, SWT.NONE));
		label_1.setText("月份");
		label_1.setBounds(400, 72, 30, 17);

		combo_2 = new Combo(this, SWT.READ_ONLY);
		combo_2.setVisibleItemCount(12);
		combo_2.setBounds(436, 70, 53, 20);
		searchSellMonth();

		table = new Table(this, SWT.FULL_SELECTION | SWT.BORDER);
		table.setLinesVisible(true);
		table.setHeaderVisible(true);
		table.setBounds(147, 114, 342, 182);

		final TableColumn newColumnTableColumn = new TableColumn(table, SWT.CENTER);
		newColumnTableColumn.setAlignment(SWT.CENTER);
		newColumnTableColumn.setWidth(112);
		newColumnTableColumn.setText("总销售额(元)");

		final TableColumn newColumnTableColumn_1 = new TableColumn(table, SWT.NONE);
		newColumnTableColumn_1.setWidth(112);
		newColumnTableColumn_1.setText("时  间");
		newColumnTableColumn_1.setAlignment(SWT.CENTER);

		final TableColumn newColumnTableColumn_2 = new TableColumn(table, SWT.NONE);
		newColumnTableColumn_2.setWidth(112);
		newColumnTableColumn_2.setText("交易次数");

		final Button button_6 = new Button(this, SWT.NONE);
		button_6.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(final SelectionEvent e) {
				boolean flag	=	true;
				int timestyle,year,month=0;
				if(combo.getSelectionIndex()	==	-1)
				{
					messageBox = new MessageBox(parent.getShell(),SWT.ICON_INFORMATION);
					messageBox.setMessage("请选择[时间类型]");
					messageBox.setText("提示");
					messageBox.open();
					flag	=	false;
				}
				
				if(flag)
				{
					if(combo.getSelectionIndex() == 0 && combo_1.getSelectionIndex()	==	-1)
					{
						messageBox = new MessageBox(parent.getShell(),SWT.ICON_INFORMATION);
						messageBox.setMessage("请选择 [年份]");
						messageBox.setText("提示");
						messageBox.open();
						flag	=	false;
					}
				}
				if(flag)
				{
					timestyle	=	combo.getSelectionIndex() + 1;
					year	=	Integer.parseInt(combo_1.getItem(combo_1.getSelectionIndex()));
					if(timestyle	==	2)
					{
						if(combo_2.getSelectionIndex()	==	-1)
						{
							messageBox = new MessageBox(parent.getShell(),SWT.ICON_INFORMATION);
							messageBox.setMessage("请选择 [月份]");
							messageBox.setText("提示");
							messageBox.open();
							flag	=	false;
						}
						else
						{
							newColumnTableColumn_1.setText("日  期");
							month	=	Integer.parseInt(combo_2.getItem(combo_2.getSelectionIndex()));
						}
					}
					else 
						{
							month	=	0;
							newColumnTableColumn_1.setText("月  份");
						}
					if(flag)
						callSellReport(timestyle,year,month);
				}
			}
		});
		button_6.setText("生成报表");
		button_6.setBounds(512, 70, 85, 22);
		newColumnTableColumn_2.setAlignment(SWT.CENTER);
		//
	}


	public void searchSellYear(){//sql
		
		Connection con;
		Statement stmt;
		try{
			DBCon dbc = new DBCon();
			con = DriverManager.getConnection(dbc.url,id,pwd);
			stmt = con.createStatement();
			String query = "select DISTINCT (convert(varchar, year(SellDateTime))) as year " +
							"from Sell " +
							"ORDER BY year";
			ResultSet rs = stmt.executeQuery(query);
			while( rs.next()){
				combo_1.add(rs.getString("year"));
			}
			dbc.dbClose(con,rs);
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}	
	
	
	public void searchSellMonth(){//sql
		
		Connection con;
		Statement stmt;
		try{
			DBCon dbc = new DBCon();
			con = DriverManager.getConnection(dbc.url,id,pwd);
			stmt = con.createStatement();
			String query = "select DISTINCT (convert(varchar, Month(SellDateTime))) as month " +
							"from Sell " +
							"ORDER BY month";
			ResultSet rs = stmt.executeQuery(query);
			while( rs.next()){
				combo_2.add(rs.getString("month"));
			}
			dbc.dbClose(con,rs);
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}	
	
	
	public void callSellReport(int timetype,int year,int month){//sql
		Connection con;

		try{
			table.clearAll();
			table.removeAll();
			DBCon dbc = new DBCon();
			con = DriverManager.getConnection(dbc.url,id,pwd);
			CallableStatement cs = con.prepareCall("{call dbo.sp_SellReport(?,?,?)}");
			ResultSet rs;
			cs.setInt(1, timetype);
			cs.setInt(2, year);
			cs.setInt(3, month);
			cs.execute();
			rs = cs.getResultSet();
			while( rs.next())
			{
				new TableItem(table, SWT.BORDER);
				table.getItem(table.getItemCount()-1).setText(new String[]{rs.getString("TotalSale"),
						rs.getString("Coll"),rs.getString("ExchangeCount")});
			}
			dbc.dbClose(con,rs);
		}
		catch(Exception e){
			
			e.printStackTrace();
		}
	}
	
}