JAVA学习作品之销售管理系统V1.1

     V1.1版本的更新主要集中在主界面方面,运用了菜单栏及工具栏以达到更简洁美观的效果,其中报表的功能也做了稍微的调整,整体功能没有太大变化。以下点击关于的说明:

具体更新如下:

1.主界面全面调优化

2.将当前库存报表独立出来,点击工具栏按钮直接查看

3.综合报表增加定价记录的处理:查看和删除

4.销售功能输入商品带出的价格为商品最近日期的定价

5.主界面历史菜单,现在可以清理定价记录历史

6.数据表price表增加栏位date,用于存储定价日期,且为主键

7.其他BUG的优化


详细操作不再做说明,子屏幕的操作与V1.0版本基本相同


直接看有更新的代码块:1.主界面:screen.java

package org.sales;

import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JMenu;
import javax.swing.ImageIcon;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;

import org.sales.OperationSqlData;

import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.GregorianCalendar;
import java.awt.event.ActionEvent;
import javax.swing.JToolBar;
import javax.swing.JButton;

import java.awt.Font;

public class screen {

	private JFrame frame;
	private int screenWidth;
	private int screenHeight;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					screen window = new screen();
					window.frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the application.
	 */
	public screen() {
		initialize();
	}

	/**
	 * Initialize the contents of the frame.
	 */
	private void initialize() {
		screenWidth = java.awt.Toolkit.getDefaultToolkit().getScreenSize().width; //根据用户屏幕,自动调整屏幕元素位置
		screenHeight = java.awt.Toolkit.getDefaultToolkit().getScreenSize().height;		
		frame = new JFrame("销售管理系统V1.1");
		frame.setBounds(0, 0, screenWidth, screenHeight);
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.getContentPane().setLayout(null);		
		
		JLabel lblNewLabel = new JLabel("");
		lblNewLabel.setIcon(new ImageIcon(screen.class.getResource("/org/sales/picture/01.jpg")));
		lblNewLabel.setBounds(0, 55, screenWidth, screenHeight-55);
		frame.add(lblNewLabel);

		JMenuBar mb = new JMenuBar();
		mb.setBounds(0, 0, screenWidth, 25);
		frame.getContentPane().add(mb);

		JMenu m0 = new JMenu("系统");
		m0.setFont(new Font("微软雅黑", Font.PLAIN, 12));
		mb.add(m0);	
		JMenuItem mt02 = new JMenuItem("退出系统");
		mt02.setEnabled(true);
		m0.add(mt02);
		mt02.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {	
				System.exit(0);
			}
		});		

		JMenu m2 = new JMenu("历史");
		m2.setFont(new Font("微软雅黑", Font.PLAIN, 12));
		mb.add(m2);

		JMenuItem mt21 = new JMenuItem("清理数据(1年前)");
		JMenuItem mt22 = new JMenuItem("清理数据(2年前)");
		JMenuItem mt23 = new JMenuItem("清理数据(3年前)");
		JMenuItem mt24 = new JMenuItem("清理数据(4年前)");
		JMenuItem mt25 = new JMenuItem("清理数据(5年前)");
		m2.add(mt21);
		m2.add(mt22);
		m2.add(mt23);
		m2.add(mt24);
		m2.add(mt25);

		mt21.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				GregorianCalendar gc=new GregorianCalendar(); //当前日期的前一年
				gc.setTime(new Date());
				gc.add(1, -1);	
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置指定格式的当前日期时间
				String date = df.format(gc.getTime());						
				new OperationSqlData().DeleteSqlDataByYear(date);
			}
		});

		mt22.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				GregorianCalendar gc=new GregorianCalendar(); //当前日期的前一年
				gc.setTime(new Date());
				gc.add(1, -2);	
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置指定格式的当前日期时间
				String date = df.format(gc.getTime());						
				new OperationSqlData().DeleteSqlDataByYear(date);
			}
		});

		mt23.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				GregorianCalendar gc=new GregorianCalendar(); //当前日期的前一年
				gc.setTime(new Date());
				gc.add(1, -3);	
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置指定格式的当前日期时间
				String date = df.format(gc.getTime());						
				new OperationSqlData().DeleteSqlDataByYear(date);
			}
		});

		mt24.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				GregorianCalendar gc=new GregorianCalendar(); //当前日期的前两年
				gc.setTime(new Date());
				gc.add(1, -4);	
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置指定格式的当前日期时间
				String date = df.format(gc.getTime());						
				new OperationSqlData().DeleteSqlDataByYear(date);
			}
		});

		mt25.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				GregorianCalendar gc=new GregorianCalendar(); //当前日期的前一年
				gc.setTime(new Date());
				gc.add(1, -5);	
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置指定格式的当前日期时间
				String date = df.format(gc.getTime());						
				new OperationSqlData().DeleteSqlDataByYear(date);
			}
		});		

		JMenu m3 = new JMenu("关于");
		m3.setFont(new Font("微软雅黑", Font.PLAIN, 12));
		mb.add(m3);
		m3.addMouseListener(new MouseAdapter() {
			public void mouseClicked(MouseEvent e) {
				if (e.getButton() == MouseEvent.BUTTON1) {
					about_screen.main(null);
				}
			}
		});	

		JToolBar toolBar = new JToolBar();
		toolBar.setBounds(0, 26, screenWidth, 30);
		frame.getContentPane().add(toolBar);

		JButton bt1 = new JButton("进货管理");
		bt1.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent arg0) {
				stock_screen.main(null);
			}
		});
		bt1.setFont(new Font("微软雅黑", Font.PLAIN, 14));
		bt1.setBorder(null);
		toolBar.add(bt1);
		toolBar.addSeparator();
		toolBar.addSeparator();
		

		JButton bt4 = new JButton("废品处理");
		bt4.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				waste_screen.main(null);
			}
		});
		bt4.setFont(new Font("微软雅黑", Font.PLAIN, 14));
		bt4.setBorder(null);
		toolBar.add(bt4);
		toolBar.addSeparator();	
		toolBar.addSeparator();		

		JButton bt2 = new JButton("商品定价");
		bt2.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				pricing_screen.main(null);
			}
		});
		bt2.setFont(new Font("微软雅黑", Font.PLAIN, 14));
		bt2.setBorder(null);
		toolBar.add(bt2);
		toolBar.addSeparator();
		toolBar.addSeparator();

		JButton bt3 = new JButton("销售商品");
		bt3.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				sales_screen.main(null);
			}
		});
		bt3.setFont(new Font("微软雅黑", Font.PLAIN, 14));
		bt3.setBorder(null);
		toolBar.add(bt3);
		toolBar.addSeparator();
		toolBar.addSeparator();	

		JButton bt6 = new JButton("当前库存");
		bt6.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				inventory_screen.main(null);
			}
		});
		bt6.setFont(new Font("微软雅黑", Font.PLAIN, 14));
		bt6.setBorder(null);
		toolBar.add(bt6);
		toolBar.addSeparator();
		toolBar.addSeparator();			
		
		JButton bt5 = new JButton("综合报表");
		bt5.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				report_screen.main(null);
			}
		});
		bt5.setFont(new Font("微软雅黑", Font.PLAIN, 14));
		bt5.setBorder(null);
		toolBar.add(bt5);
		toolBar.addSeparator();	
		toolBar.addSeparator();
	}
}	

2.报表report_screen.java:

package org.sales;

import java.awt.EventQueue;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import javax.swing.JCheckBox;
import javax.swing.JFrame;
import javax.swing.JTree;
import javax.swing.SpinnerDateModel;
import javax.swing.SpinnerModel;
import javax.swing.event.TreeSelectionEvent;
import javax.swing.event.TreeSelectionListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.JScrollPane;
import javax.swing.tree.DefaultTreeModel;

import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.JTable;
import javax.swing.JSpinner;
import javax.swing.JLabel;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JPopupMenu;
import java.awt.Font;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.ActionEvent;

public class report_screen {

	private JFrame frame;
	private JTable table;
	private JTree tree;
	private JScrollPane scrollPane;
	private JScrollPane scrollPane_1;
	private JSpinner spinner;
	private JSpinner spinner_1;	
	private JCheckBox checkBox;
	private String business;
	private DefaultTableModel model1;
	private DefaultTableModel model2;
	private DefaultTableModel model3;
	private DefaultTableModel model4;
	/**
	 * Launch the application.
	 */


	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					report_screen window = new report_screen();
					window.frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the application.
	 */
	public report_screen() {
		initialize();
	}

	/**
	 * Initialize the contents of the frame.
	 */
	@SuppressWarnings("serial")
	private void initialize() {
		frame = new JFrame();
		int screenWidth = java.awt.Toolkit.getDefaultToolkit().getScreenSize().width*2/3;
		int screenHeight = java.awt.Toolkit.getDefaultToolkit().getScreenSize().height*2/3;	
		frame.setBounds(screenWidth/6, screenHeight/6, screenWidth, screenHeight);
		frame.getContentPane().setLayout(null);

		JLabel label = new JLabel("开始日期");
		label.setFont(new Font("仿宋", Font.BOLD, 16));
		label.setBounds(180, 20, 80, 20);
		frame.getContentPane().add(label);		

		JLabel label_1 = new JLabel("结束日期");
		label_1.setFont(new Font("仿宋", Font.BOLD, 16));
		label_1.setBounds(390, 20, 80, 20);
		frame.getContentPane().add(label_1);	

		checkBox = new JCheckBox("锁定日期");
		checkBox.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent arg0) {
				//锁定后日期不可编辑
				if(checkBox.isSelected() == true)
				{
					spinner.setEnabled(false);
					spinner_1.setEnabled(false);
				}
				else{
					spinner.setEnabled(true);
					spinner_1.setEnabled(true);
				}
			}
		});
		checkBox.setFont(new Font("仿宋", Font.PLAIN, 16));
		checkBox.setBounds(600, 20, 100, 20);
		frame.getContentPane().add(checkBox);
		checkBox.setSelected(false);

		GregorianCalendar gc=new GregorianCalendar(); //当前日期的前一个月
		gc.setTime(new Date());
		gc.add(2, -1);		
		SpinnerModel dateModel=null;
		dateModel = new SpinnerDateModel(gc.getTime(), null, null,Calendar.DAY_OF_MONTH);
		spinner = new JSpinner();
		spinner.setFont(new Font("仿宋", Font.BOLD, 14));
		spinner.setBounds(270, 20, 105, 20);
		frame.getContentPane().add(spinner);
		spinner.setModel(dateModel);  
		spinner.setEditor(new JSpinner.DateEditor(spinner,"yyyy-MM-dd"));		

		SpinnerModel dateModel_1=null;
		dateModel_1 = new SpinnerDateModel(new Date(), null, null,Calendar.DAY_OF_MONTH);
		spinner_1 = new JSpinner();
		spinner_1.setFont(new Font("仿宋", Font.BOLD, 14));
		spinner_1.setBounds(480, 20, 105, 20);
		frame.getContentPane().add(spinner_1);
		spinner_1.setModel(dateModel_1);  
		spinner_1.setEditor(new JSpinner.DateEditor(spinner_1,"yyyy-MM-dd"));			

		//new一个树状导航,选择不同的报表种类
		tree = new JTree();
		tree.setShowsRootHandles(true);
		tree.setModel(new DefaultTreeModel(
				new DefaultMutableTreeNode("报表") {
					{
						DefaultMutableTreeNode node_1;
						node_1 = new DefaultMutableTreeNode("记录");
						node_1.add(new DefaultMutableTreeNode("进货记录"));					
						node_1.add(new DefaultMutableTreeNode("销售记录"));
						node_1.add(new DefaultMutableTreeNode("报废记录"));
						node_1.add(new DefaultMutableTreeNode("定价记录"));
						add(node_1);
						node_1 = new DefaultMutableTreeNode("利润");
						node_1.add(new DefaultMutableTreeNode("利润统计"));
						add(node_1);
					}
				}
				));
		tree.addTreeSelectionListener(new TreeSelectionListener() {

			@Override
			public void valueChanged(TreeSelectionEvent e) {
				DefaultMutableTreeNode node = (DefaultMutableTreeNode) tree.getLastSelectedPathComponent();
				if(node == null)
					return;

				if (checkBox.isSelected()==false)  //日期锁定后允许执行报表
				{
					JOptionPane.showMessageDialog(null, "请首先锁定日期");
					return;	
				}		

				Object select_node = node.getUserObject();
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");// 创建指定格式的当前时间

				switch (select_node.toString()) {
				case "进货记录": //根据日期抓取进货记录
					String[] header1 = { "日期", "品名", "进货数量", "单位", "进货单价", "进货总价"};// 设置表头
					Object[][] item1 = new Object[0][6];// 设置单身
					model1 = new DefaultTableModel(item1, header1) {
						public boolean isCellEditable(int row, int column) {
							return false;
						}
					};// 创建一个model,并设置model数据不可编辑
					table = new JTable(model1);// 根据model创建一个Jtable
					table.setFocusable(false);// 关闭鼠标选中单个坐标	
					scrollPane_1.setViewportView(table);

					int r1 = model1.getRowCount();
					while(r1>0)
					{
						model1.removeRow(r1-1);
						r1--;
					}

					Object[] obj1 = new Object[6];
					ArrayList<Object[]> list1 = new OperationSqlData().getStockRecordByDate(df.format(spinner.getValue())+" "+"00:00:00", df.format(spinner_1.getValue())+" "+"23:59:59",0 , false);
					int row1 = list1.size();
					float cost = 0;
					for(int i=0; i<row1; i++)
					{
						obj1 = list1.get(i);
						model1.addRow(new Object[6]);
						int j = model1.getRowCount();
						model1.setValueAt(obj1[0], j-1, 0);
						model1.setValueAt(obj1[1], j-1, 1);
						model1.setValueAt(obj1[2], j-1, 2);
						model1.setValueAt(obj1[3], j-1, 3);
						cost = Float.parseFloat(obj1[4].toString())/Float.parseFloat(obj1[2].toString());
						cost = (float)(Math.round(cost*100))/100;
						model1.setValueAt(cost, j-1, 4);
						model1.setValueAt(obj1[4], j-1, 5);
					}	
					business = "stock";
					reportMouseClick();
					break;
				case "销售记录"://根据日期抓取销售记录			
					String[] header2 = { "日期", "品名", "销售数量", "单位", "销售单价", "销售总价"};// 设置表头
					Object[][] item2 = new Object[0][6];// 设置单身
					model2 = new DefaultTableModel(item2, header2) {
						public boolean isCellEditable(int row, int column) {
							return false;
						}
					};// 创建一个model,并设置model数据不可编辑
					table = new JTable(model2);// 根据model创建一个Jtable
					table.setFocusable(false);// 关闭鼠标选中单个坐标	
					scrollPane_1.setViewportView(table);

					int r2 = model2.getRowCount();
					while(r2>0)
					{
						model2.removeRow(r2-1);
						r2--;
					}

					Object[] obj2 = new Object[6];
					ArrayList<Object[]> list2 = new OperationSqlData().getSalesRecordByDate(df.format(spinner.getValue())+" "+"00:00:00", df.format(spinner_1.getValue())+" "+"23:59:59", false);
					int row2 = list2.size();
					for(int i=0; i<row2; i++)
					{
						obj2 = list2.get(i);
						model2.addRow(new Object[6]);
						int j = model2.getRowCount();
						model2.setValueAt(obj2[0], j-1, 0);
						model2.setValueAt(obj2[1], j-1, 1);
						model2.setValueAt(obj2[2], j-1, 2);
						model2.setValueAt(obj2[3], j-1, 3);
						model2.setValueAt(obj2[4], j-1, 4);
						model2.setValueAt(obj2[5], j-1, 5);
					}
					business = "sales";
					reportMouseClick();
					break;
				case "报废记录": //根据日期抓取报废记录
					String[] header3 = { "日期", "品名", "报废数量", "单位", "成本单价", "报废总成本"};// 设置表头
					Object[][] item3 = new Object[0][6];// 设置单身
					model3 = new DefaultTableModel(item3, header3) {
						public boolean isCellEditable(int row, int column) {
							return false;
						}
					};// 创建一个model,并设置model数据不可编辑
					table = new JTable(model3);// 根据model创建一个Jtable
					table.setFocusable(false);// 关闭鼠标选中单个坐标	
					scrollPane_1.setViewportView(table);

					int r3 = model3.getRowCount();
					while(r3>0)
					{
						model3.removeRow(r3-1);
						r3--;
					}

					Object[] obj3 = new Object[6];
					ArrayList<Object[]> list3 = new OperationSqlData().getStockRecordByDate(df.format(spinner.getValue())+" "+"00:00:00", df.format(spinner_1.getValue())+" "+"23:59:59",-1 , false);
					int row3 = list3.size();
					float cost1 = 0;
					for(int i=0; i<row3; i++)
					{
						obj3 = list3.get(i);
						model3.addRow(new Object[6]);
						int j = model3.getRowCount();
						model3.setValueAt(obj3[0], j-1, 0);
						model3.setValueAt(obj3[1], j-1, 1);
						model3.setValueAt(obj3[2], j-1, 2);
						model3.setValueAt(obj3[3], j-1, 3);
						cost1 = Float.parseFloat(obj3[4].toString())/Float.parseFloat(obj3[2].toString());
						model3.setValueAt(cost1, j-1, 4);
						model3.setValueAt(obj3[4], j-1, 5);
					}
					business = "stock_waste";
					reportMouseClick();
					break;
				case "定价记录":
					String[] header4 = { "日期", "品名","单位", "销售单价"};// 设置表头
					Object[][] item4 = new Object[0][4];// 设置单身
					model4 = new DefaultTableModel(item4, header4) {
						public boolean isCellEditable(int row, int column) {
							return false;
						}
					};// 创建一个model,并设置model数据不可编辑
					table = new JTable(model4);// 根据model创建一个Jtable
					table.setFocusable(false);// 关闭鼠标选中单个坐标	
					scrollPane_1.setViewportView(table);

					int r4 = model4.getRowCount();
					while(r4>0)
					{
						model4.removeRow(r4-1);
						r4--;
					}

					Object[] obj4 = new Object[4];
					ArrayList<Object[]> list4 = new OperationSqlData().getPricingRecordByDate(df.format(spinner.getValue())+" "+"00:00:00", df.format(spinner_1.getValue())+" "+"23:59:59");
					int row4 = list4.size();
					for(int i=0; i<row4; i++)
					{
						obj4 = list4.get(i);
						model4.addRow(new Object[4]);
						int j = model4.getRowCount();
						model4.setValueAt(obj4[0], j-1, 0);
						model4.setValueAt(obj4[1], j-1, 1);
						model4.setValueAt(obj4[2], j-1, 2);
						model4.setValueAt(obj4[3], j-1, 3);
					}
					business = "sales_pricing";
					reportMouseClick();					
					break;
				case "利润统计": //根据销售利润 报废成本 统计最终净利润
					String[] header5 = { "品名", "销售数量","报废数量","单位", "平均销售单价", "库存成本单价", "销售利润", "报废成本","净利润"};// 设置表头
					Object[][] item5 = new Object[0][9];// 设置单身
					DefaultTableModel model5 = new DefaultTableModel(item5, header5) {
						public boolean isCellEditable(int row, int column) {
							return false;
						}
					};// 创建一个model,并设置model数据不可编辑
					table = new JTable(model5);// 根据model创建一个Jtable
					table.setFocusable(false);// 关闭鼠标选中单个坐标	
					scrollPane_1.setViewportView(table);	
					int r5 = model5.getRowCount();
					while(r5>0)
					{
						model5.removeRow(r5-1);
						r5--;
					}

					Object[] obj5_sales = new Object[4];
					Object[] obj5_waste = new Object[9];
					Object[] obj5 = new Object[9];
					ArrayList<Object[]> list5_sales = new OperationSqlData().getSalesRecordByDate(df.format(spinner.getValue())+" "+"00:00:00", df.format(spinner_1.getValue())+" "+"23:59:59", true);
					ArrayList<Object[]> list5_waste = new OperationSqlData().getStockRecordByDate(df.format(spinner.getValue())+" "+"00:00:00", df.format(spinner_1.getValue())+" "+"23:59:59", -1, true);

					int row5_sales = list5_sales.size();
					int row5_waste = list5_waste.size();

					for (int i=0; i<row5_sales; i++)
					{
						obj5_sales = list5_sales.get(i);
						float price = new OperationSqlData().getStockCostByGoodsName(obj5_sales[0].toString());
						price =  (float)(Math.round(price*100))/100;
						obj5[0] = obj5_sales[0];
						obj5[1] = obj5_sales[1];
						obj5[2] = 0.00;
						obj5[3] = obj5_sales[2];
						obj5[4] =  (float)(Math.round((Float.parseFloat(obj5_sales[3].toString())/Float.parseFloat(obj5_sales[1].toString()))*100))/100;				
						obj5[5] = price;
						obj5[6] = new BigDecimal(Float.parseFloat(obj5[1].toString())*(Float.parseFloat(obj5[4].toString())-price)).setScale(2, RoundingMode.HALF_UP);
						obj5[7] = 0.00;
						obj5[8] = obj5[6];
						for (int j=0; j<row5_waste; j++)
						{
							obj5_waste = list5_waste.get(j);
							if (obj5_waste[0].equals(obj5_sales[0]))
							{
								obj5[2] = obj5_waste[1];
								obj5[7] = obj5_waste[3];
								obj5[8] =  new BigDecimal(Float.parseFloat(obj5[6].toString()) + Float.parseFloat(obj5[7].toString())).setScale(2, RoundingMode.HALF_UP);
								list5_waste.remove(j);
								row5_waste =  row5_waste - 1;
								break;
							}
						}	

						model5.addRow(new Object[9]);
						int m = model5.getRowCount();
						model5.setValueAt(obj5[0], m-1, 0);
						model5.setValueAt(obj5[1], m-1, 1);
						model5.setValueAt(obj5[2], m-1, 2);					
						model5.setValueAt(obj5[3], m-1, 3);
						model5.setValueAt(obj5[4], m-1, 4);
						model5.setValueAt(obj5[5], m-1, 5);	
						model5.setValueAt(obj5[6], m-1, 6);					
						model5.setValueAt(obj5[7], m-1, 7);
						model5.setValueAt(obj5[8], m-1, 8);							
					}

					if (row5_waste > 0)
					{
						for(int k=0; k<row5_waste; k++)
						{
							obj5_waste = list5_waste.get(k);	
							obj5[0] = obj5_waste[0];
							obj5[1] = 0.00;
							obj5[2] = obj5_waste[1];
							obj5[3] = obj5_waste[2];
							obj5[4] = 0.00;
							float price = new OperationSqlData().getStockCostByGoodsName(obj5_waste[0].toString());
							price = (float)(Math.round(price*100))/100;
							obj5[5] = price;
							obj5[6] = 0.0;
							obj5[7] = obj5_waste[3];
							obj5[8] = Float.parseFloat(obj5_waste[3].toString());

							model5.addRow(new Object[9]);
							int m = model5.getRowCount();
							model5.setValueAt(obj5[0], m-1, 0);
							model5.setValueAt(obj5[1], m-1, 1);
							model5.setValueAt(obj5[2], m-1, 2);					
							model5.setValueAt(obj5[3], m-1, 3);
							model5.setValueAt(obj5[4], m-1, 4);
							model5.setValueAt(obj5[5], m-1, 5);	
							model5.setValueAt(obj5[6], m-1, 6);					
							model5.setValueAt(obj5[7], m-1, 7);
							model5.setValueAt(obj5[8], m-1, 8);					
						}
					}
					//最后一行插入利润总计
					int row_last = model5.getRowCount();
					float sales_bft = 0;
					float stock_cost = 0;
					float last_bft = 0;
					for(int p=0; p<row_last; p++)
					{
						if (!(model5.getValueAt(p, 6)==null))
						{
							sales_bft =  sales_bft + Float.parseFloat(model5.getValueAt(p, 6).toString());
						}
						if (!(model5.getValueAt(p, 7)==null))
						{
							stock_cost = stock_cost + Float.parseFloat(model5.getValueAt(p, 7).toString());
						}
						if (!(model5.getValueAt(p, 8)==null))
						{
							last_bft = last_bft + Float.parseFloat(model5.getValueAt(p, 8).toString());
						}
					}
					sales_bft = (float)(Math.round(sales_bft*100))/100;
					stock_cost = (float)(Math.round(stock_cost*100))/100;
					last_bft = (float)(Math.round(last_bft*100))/100;


					model5.addRow(new Object[9]);
					model5.setValueAt("总计", row_last, 0);
					model5.setValueAt(sales_bft, row_last, 6);
					model5.setValueAt(stock_cost, row_last, 7);
					model5.setValueAt(last_bft, row_last, 8);
					break;					
				default:
					break;
				}
			}
		});

		scrollPane = new JScrollPane();
		scrollPane.setViewportView(tree);
		scrollPane.setBounds(10, 50, 150, screenHeight-50);
		frame.getContentPane().add(scrollPane);		

		scrollPane_1 = new JScrollPane();
		scrollPane_1.setBounds(160, 50, screenWidth-180, screenHeight-50);
		frame.getContentPane().add(scrollPane_1);
	}

	class PopupActionListener implements ActionListener {
		public void actionPerformed(ActionEvent event) {
			int answer = JOptionPane.showConfirmDialog(null,  "删除数据不加入报表计算,确定要删除选中记录?", "提交信息",JOptionPane.YES_NO_OPTION);
			if (answer == 0)
			{
				int ret = 0;
				if (business.equals("stock"))
				{
					ret = new OperationSqlData().DeleteSqlDataByGoodsNameDate(model1.getValueAt(table.getSelectedRow(), 1).toString(), model1.getValueAt(table.getSelectedRow(), 0).toString(), "stock");
					model1.removeRow(table.getSelectedRow());
				}
				else if(business.equals("sales"))
				{
					ret = new OperationSqlData().DeleteSqlDataByGoodsNameDate(model2.getValueAt(table.getSelectedRow(), 1).toString(), model2.getValueAt(table.getSelectedRow(), 0).toString(), "sales");
					model2.removeRow(table.getSelectedRow());				
				}
				else if (business.equals("stock_waste"))
				{
					ret = new OperationSqlData().DeleteSqlDataByGoodsNameDate(model3.getValueAt(table.getSelectedRow(), 1).toString(), model3.getValueAt(table.getSelectedRow(), 0).toString(), "stock");	
					model3.removeRow(table.getSelectedRow());
				}
				else if (business.equals("sales_pricing"))
				{
					ret = new OperationSqlData().DeleteSqlDataByGoodsNameDate(model4.getValueAt(table.getSelectedRow(), 1).toString(), model4.getValueAt(table.getSelectedRow(), 0).toString(), "price");	
					model4.removeRow(table.getSelectedRow());
				}

				if (ret == 1)
					JOptionPane.showMessageDialog(null, "删除成功");
				else
					JOptionPane.showMessageDialog(null, "删除失败"); 
			}
		}
	}	

	ActionListener acitonListener = new PopupActionListener();   


	public void reportMouseClick()  //右键提供指定行区域删除选项,处理选项事件
	{
		final JPopupMenu jp = new JPopupMenu();
		final JMenuItem item = jp.add("删除该条记录");
		item.addActionListener(acitonListener);

		table.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseClicked(MouseEvent e) {
				if (e.getButton() == MouseEvent.BUTTON3) {
					// 弹出菜单
					if(table.getSelectedRow() > -1)
					{
						int y = table.getSelectedRow() * table.getRowHeight();
						if(e.getY() > y && e.getY() <= y+16)
						{
							jp.show(table, e.getX(), e.getY());
						}
					}
				}
			}
		});  
	}
}

3.当前库存inventory_screen.java(从V1.0版本的报表功能中独立出来,因为与日期无关)

package org.sales;

import java.awt.EventQueue;
import java.util.ArrayList;

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class inventory_screen {

	private JFrame frame;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					inventory_screen window = new inventory_screen();
					window.frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the application.
	 */
	public inventory_screen() {
		initialize();
	}

	/**
	 * Initialize the contents of the frame.
	 */
	private void initialize() {
		frame = new JFrame();
		int screenWidth = java.awt.Toolkit.getDefaultToolkit().getScreenSize().width*2/3;
		int screenHeight = java.awt.Toolkit.getDefaultToolkit().getScreenSize().height*2/3;	
		frame.setBounds(screenWidth/6, screenHeight/6, screenWidth, screenHeight);
		frame.getContentPane().setLayout(null);
		
		String[] header = {"品名", "库存数量", "单位"};// 设置表头
		Object[][] item = new Object[0][3];// 设置单身
		@SuppressWarnings("serial")
		DefaultTableModel model = new DefaultTableModel(item, header) {
			public boolean isCellEditable(int row, int column) {
				return false;
			}
		};// 创建一个model,并设置model数据不可编辑
		JTable table = new JTable(model);// 根据model创建一个Jtable
		table.setFocusable(false);// 关闭鼠标选中单个坐标	
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setViewportView(table);
		scrollPane.setBounds(5, 5, screenWidth-5, screenHeight-5);
		frame.getContentPane().add(scrollPane);
		
		int r1 = model.getRowCount();
		while(r1>0)
		{
			model.removeRow(r1-1);
			r1--;
		}

		Object[] obj4 = new Object[6];
		Object[] obj4_sales = new Object[4];
		ArrayList<Object[]> list4 = new OperationSqlData().getStockRecordByDate("0000-00-00 00:00:00", "9999-99-99 99:99:99",0 , true);
		ArrayList<Object[]> list4_sales = new OperationSqlData().getSalesRecordByDate("0000-00-00 00:00:00", "9999-99-99 99:99:99", true);
		int row4 = list4.size();
		int row4_sales = list4_sales.size();
		float stock_last = 0;
		for(int i=0; i<row4; i++)
		{
			model.addRow(new Object[3]);
			int j = model.getRowCount();
			obj4 = list4.get(i);

			for (int k=0; k<row4_sales; k++)
			{
				obj4_sales = list4_sales.get(k);
				if (obj4_sales[0].toString().equals(obj4[0].toString()))
				{
					stock_last = Float.parseFloat(obj4[1].toString()) - Float.parseFloat(obj4_sales[1].toString());
					model.setValueAt(stock_last, j-1, 1);
					break;
				}
				else 
				{
					model.setValueAt(obj4[1], j-1, 1);
				}	
			}

			if (row4_sales == 0)
				model.setValueAt(obj4[1], j-1, 1);
			model.setValueAt(obj4[0], j-1, 0);
			model.setValueAt(obj4[2], j-1, 2);		
	}
	}
	}


4.数据库操作OperationSqlData.java

package org.sales;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.swing.JComboBox;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class OperationSqlData {
	//定义数据库变量	
	private String  url;
	private String passname;
	private String password;
	private String driver;
	private Statement st;
	private Connection con;
	private ResultSet rst;	

	public OperationSqlData()  //构造数据库链接默认值
	{
		passname = "admin";  //SQL Server登陆账号
		password = "init1234";	//SQL Server登陆密码
		driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //驱动加载
		try {
			Class.forName(driver);

		} catch (ClassNotFoundException e) {
			JOptionPane.showMessageDialog(null, "数据库加载失败!");
			e.printStackTrace();
		}
		try {
			url = "jdbc:sqlserver://"+InetAddress.getLocalHost().getHostAddress()+";"+"DatabaseName=goodsData";//URL链接
		} catch (UnknownHostException e) {
			JOptionPane.showMessageDialog(null, "数据库加载失败!");
			e.printStackTrace();
		}	
	}

	public OperationSqlData(String p_name, String p_word)  //构造指定帐号密码数据库链接
	{
		passname = p_name; 
		password = p_word;
		driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		try {
			Class.forName(driver);

		} catch (ClassNotFoundException e) {
			JOptionPane.showMessageDialog(null, "数据库加载失败!");
			e.printStackTrace();
		}
		try {
			url = "jdbc:sqlserver://"+InetAddress.getLocalHost().getHostAddress()+";"+"DatabaseName=goodsData";
		} catch (UnknownHostException e) {
			JOptionPane.showMessageDialog(null, "数据库加载失败!");
			e.printStackTrace();
		}		
	}

	public void getGoodsNameToCombox(JComboBox<String> combox)
	{
		String sql = "SELECT distinct goods FROM stock where qty > 0";// 数据库取品名
		// 连接数据库,执行查询语句
		try {
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(true);
			st = con.createStatement();
			rst = st.executeQuery(sql);
			// 取出的结果加入combox的Item
			while (rst.next()) {
				combox.addItem(rst.getString("goods"));
			}

			// 关闭数据库连接
			rst.close();
			st.close();
			con.close();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "商品名数据加载异常! ");
			e.printStackTrace();
		}	   
	}

	public String getUnitByGoodsName(String goods) {
		String unit = null;
		//从库存表抓取商品单位
		String sql = "SELECT distinct unit FROM stock WHERE qty> 0 AND goods = ?";
		try {
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(true);
			PreparedStatement ps = con.prepareStatement(sql);  //SQL预处理
			ps.setString(1, goods); //SQL参数
			ResultSet rt = ps.executeQuery();  //执行SQL
			while(rt.next())
			{
				unit = rt.getString("unit");  //从执行结果中得到商品单位
			}
			// 关闭数据库连接
			ps.close();
			rt.close();
			con.close();		
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "商品单位数据加载异常! ");
			e.printStackTrace();
		}
		return unit;   //返回单位
	}

	public String getSalesPriceByGoodsName(String goods)
	{
		String sales_price = null;
		//从定价表中抓取销售单价
		String sql = "SELECT unitprice FROM Price WHERE goods = ? ORDER BY date DESC";

		try {
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(true);
			PreparedStatement ps = con.prepareStatement(sql);// SQL预处理
			ps.setString(1, goods);
			ResultSet rt = ps.executeQuery();
			rt.next();
			sales_price = rt.getString("unitprice");
			// 关闭数据库连接
			ps.close();
			rt.close();
			con.close();		
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "销售单价数据加载异常! ");
			e.printStackTrace();
		}		
		return sales_price;
	}

	public ArrayList<Object[]> getSalesRecordByDate(String date_begin, String date_end, Boolean sum_qty_totalprice)
	{
		ArrayList<Object[]> list = new ArrayList<Object[]>(); 
		String sql = null;
		if (sum_qty_totalprice)
		{   
			sql = "SELECT goods, SUM(qty) AS s_qty, unit, SUM(totalprice) AS s_totalprice FROM sales where date between ? AND ? GROUP BY goods, unit";//抓取销售记录数据按商品名和单位汇总
		}
		else{
			sql = "SELECT date, goods, qty, unit, unitprice, totalprice FROM sales where date between ? AND ? ORDER BY date DESC";//抓取销售记录数据按时间排序
		}
		try {
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(true);
			PreparedStatement ps = con.prepareStatement(sql);// SQL预处理
			ps.setString(1, date_begin);  //指定抓取销售记录起始时间
			ps.setString(2, date_end);  //指定抓取销售记录终止时间
			ResultSet rt = ps.executeQuery();
			Object[] obj = null;

			if (sum_qty_totalprice)
			{
				while(rt.next())
				{
					obj = new Object[4];//Object数组加入arraylist
					obj[0] = rt.getString("goods");
					obj[1] = rt.getFloat("s_qty");
					obj[2] = rt.getString("unit");
					obj[3] = rt.getFloat("s_totalprice");
					list.add(obj);
				}				
			}
			else{
				while(rt.next())
				{
					obj = new Object[6];
					obj[0] = rt.getString("date");
					obj[1] = rt.getString("goods");
					obj[2] = rt.getFloat("qty");
					obj[3] = rt.getString("unit");
					obj[4] = rt.getFloat("unitprice");
					obj[5] = rt.getFloat("totalprice");
					list.add(obj);
				}
			}

			// 关闭数据库连接
			ps.close();
			rt.close();
			con.close();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "销售记录数据加载异常! ");
			e.printStackTrace();                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
		}

		return list; 
	}	

	public float getStockCostByGoodsName(String goods)
	{
		float stock_cost = 0;
		//  这里计算的是商品进货成本(qty>0)
		String sql = "SELECT goods,SUM(qty) AS s_qty,SUM(amount) AS s_amount FROM stock WHERE goods = ? AND qty>0 GROUP by goods";
		try {
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(true);
			PreparedStatement ps = con.prepareStatement(sql);// SQL预处理
			ps.setString(1, goods);
			ResultSet rt = ps.executeQuery();
			while(rt.next())
			{
				float s_amount = rt.getFloat("s_amount");
				float s_qty = rt.getFloat("s_qty");
				stock_cost = s_amount/s_qty;
			}
			// 关闭数据库连接
			ps.close();
			rt.close();
			con.close();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "成本数据加载异常! ");
			e.printStackTrace();                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
		}

		return stock_cost;
	}		

	public ArrayList<Object[]> getStockRecordByDate(String date_begin, String date_end, int status, Boolean sum_amount_qty)
	{
		ArrayList<Object[]> list = new ArrayList<Object[]>();
		String sql = null;
		//sun_amount_qty是否根据商品名和单位加总库存数量和成本,
		//status的值: -1计算报废库存;0计算总库存; 1计算进货库存
		if (sum_amount_qty)
		{
			if (status == -1)  
				sql = "SELECT goods, SUM(qty) AS s_qty, unit, SUM(amount) AS s_amount FROM stock where qty < 0 AND date between ? AND ? GROUP by goods,unit";
			else if (status == 1)
				sql = "SELECT goods, SUM(qty) AS s_qty, unit, SUM(amount) AS s_amount FROM stock where qty > 0 AND date between ? AND ? GROUP by goods,unit";
			else if (status == 0)
				sql = "SELECT goods, SUM(qty) AS s_qty, unit, SUM(amount) AS s_amount FROM stock where date between ? AND ? GROUP by goods,unit";
		}
		else {
			if(status == -1)
				sql = "SELECT date, goods, qty, unit, amount FROM stock where qty < 0 AND date between ? AND ? ORDER BY date DESC";	
			else
				sql = "SELECT date, goods, qty, unit, amount FROM stock where qty > 0 AND date between ? AND ? ORDER BY date DESC";
		}
		try {
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(true);
			PreparedStatement ps = con.prepareStatement(sql);// SQL预处理
			ps.setString(1, date_begin);
			ps.setString(2, date_end);
			ResultSet rt = ps.executeQuery();
			Object[] obj = null;
			if (sum_amount_qty)  //依据是否汇总处理输出结果
			{
				while(rt.next())
				{
					obj = new Object[4];
					obj[0] = rt.getString("goods");
					obj[1] = rt.getFloat("s_qty");
					obj[2] = rt.getString("unit");
					obj[3] = rt.getFloat("s_amount");
					list.add(obj);
				}
			}
			else {
				while(rt.next())
				{
					obj = new Object[5];
					obj[0] = rt.getString("date");
					obj[1] = rt.getString("goods");
					obj[2] = rt.getFloat("qty");
					obj[3] = rt.getString("unit");
					obj[4] = rt.getFloat("amount");
					list.add(obj);
				}				
			}
			// 关闭数据库连接
			ps.close();
			rt.close();
			con.close();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "库存数据加载异常! ");
			e.printStackTrace();                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
		}

		return list;
	}	


	public ArrayList<Object[]> getPricingRecordByDate(String date_begin, String date_end)
	{
		ArrayList<Object[]> list = new ArrayList<Object[]>();
		String sql = null;

		sql = "SELECT date, goods, unit, unitprice FROM price where date between ? AND ?";

		try {
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(true);
			PreparedStatement ps = con.prepareStatement(sql);// SQL预处理
			ps.setString(1, date_begin);
			ps.setString(2, date_end);
			ResultSet rt = ps.executeQuery();
			Object[] obj = null;
			while(rt.next())
			{
				obj = new Object[4];
				obj[0] = rt.getString("date");
				obj[1] = rt.getString("goods");
				obj[2] = rt.getString("unit");
				obj[3] = rt.getFloat("unitprice");
				list.add(obj);
			}				
			// 关闭数据库连接
			ps.close();
			rt.close();
			con.close();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "库存数据加载异常! ");
			e.printStackTrace();                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
		}

		return list;
	}	


	public void CommitSalesStockPricingToSql(DefaultTableModel model, JTable table)  //传入用model定义的table
	{
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 创建指定格式的当前时间
		String date = df.format(new Date());
		//这里先删除原有定价记录,再插入新纪录
		String sql2 = "INSERT INTO price (goods, date, unit, cost, unitprice) VALUES(?,?,?,?,?)";

		try {
			// 设置数据库链接,设置手动提交数据
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(false);

			PreparedStatement ps = con.prepareStatement(sql2);// SQL预处理

			int line = table.getRowCount();
			// 循环每一行,如果有值加入数据库批处理
			for (int i = 0; i < line; i++) {
				if(!(model.getValueAt(i, 5)==null))  //首先判断是否有输入值,再判断是否为空值,有值则删除
				{
					if (!(model.getValueAt(i, 5).toString().isEmpty()))
					{
						ps.setString(1, (String) model.getValueAt(i, 0));
						ps.setString(2, date);
						ps.setString(3, (String) model.getValueAt(i, 3));
						ps.setString(4, model.getValueAt(i, 4).toString());
						ps.setString(5, model.getValueAt(i, 5).toString());
						ps.addBatch();						
					}
				}
			}

			int[] rt  = ps.executeBatch();// 执行批处理,弹出对话消息,显示成功失败
			con.commit();
			//关闭数据库相关链接
			ps.close();

			if (rt.length > 0)
				JOptionPane.showMessageDialog(null, "提交成功!");
			ps.close();
			con.close();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "提交失败! ");
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}	

	public Integer StockIn(String name, String qty, String unit, String amount)
	{

		int ret = 0;
		String sql = "INSERT INTO stock (goods, date, qty, unit, amount) VALUES (?, ?, ?, ?, ?)"; // 定义SQL语句

		try {
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(true); // 设置数据自动提交数据库

			PreparedStatement ps = con.prepareStatement(sql); // SQL预处理
			ps.setString(1, name);// 动态参数执行SQL
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 创建指定格式的当前时间
			ps.setString(2, df.format(new Date()));
			ps.setString(3, qty);
			ps.setString(4, unit);
			ps.setString(5, amount);
			ret = ps.executeUpdate(); // 执行SQL
			ps.close(); //关闭数据库链接
			con.close();
			return ret;
		} catch (SQLException e) {
			e.printStackTrace();
			return ret;
		}
	}	

	public void CommitSalesToSql(DefaultTableModel model, JTable table)
	{
		//  插入销售记录到数据库
		String sql = "INSERT INTO sales (goods, date, qty, unit, unitprice, totalprice) VALUES (?, ?, ?, ?, ?, ?)";// 批量插入Jtable中数据

		try {
			// 设置数据库链接,设置手动提交数据
			con = DriverManager.getConnection(url, passname, password);
			con.setAutoCommit(false);

			PreparedStatement ps = con.prepareStatement(sql);// SQL预处理

			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置指定格式的当前日期时间
			String date = df.format(new Date());
			float f4 = 0;
			int line = table.getRowCount();
			// 循环每一行,如果有值加入数据库批处理
			for (int i = 0; i < line; i++) {
				if ((String) model.getValueAt(i, 0) != null) {
					ps.setString(1, (String) model.getValueAt(i, 0));
					ps.setString(2, date);
					float f1 = Float.parseFloat(model.getValueAt(i, 1).toString());
					ps.setFloat(3, f1);
					ps.setString(4, (String)model.getValueAt(i, 2));
					float f2 = Float.parseFloat(model.getValueAt(i, 3).toString());
					ps.setFloat(5, f2);
					float f3 = Float.parseFloat(model.getValueAt(i, 4).toString());
					ps.setFloat(6, f3);
					ps.addBatch();
					f4 = f3 + f4;
				}
			}

			f4 = (float)(Math.round(f4*100))/100;

			// 弹出选择对话框,计算总金额,提示是否提交
			int answer = JOptionPane.showConfirmDialog(null, "总金额" + f4 + "元" + " " + "确认提交?", "提交信息",
					JOptionPane.YES_NO_OPTION);
			if (answer == 0) {
				int rst[] = ps.executeBatch();// 执行批处理,弹出对话消息,显示成功失败
				if (rst.length > 0) {
					JOptionPane.showMessageDialog(null, "交易成功");
					con.commit();		
				}
				else if(rst.length == 0)
				{
					JOptionPane.showMessageDialog(null, "无数据");
				}
			}
			//关闭数据库相关链接
			ps.close();
			con.close();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "交易失败");
			try {
				con.rollback();
				con.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

	}	

	public Integer DeleteSqlDataByGoodsNameDate(String goods, String date, String business)
	{
		String sql = null;
		int ret = 0;
		if(business.equals("stock")||business.equals("stock_waste"))
			sql = "DELETE FROM stock where goods = ? AND date = ?";
		else if (business.equals("sales"))
			sql = "DELETE FROM sales where goods = ? AND date = ?";
		else if (business.equals("price"))
			sql = "DELETE FROM price where goods = ? AND date = ?";


		// 连接数据库,执行查询语句
		try {
			con = DriverManager.getConnection(url, passname,password);
			con.setAutoCommit(false); // 设置数据自动提交数据库

			PreparedStatement ps = con.prepareStatement(sql); // SQL预处理
			ps.setString(1, goods);
			ps.setString(2, date);
			ret = ps.executeUpdate(); // 执行SQL
			con.commit();
			ps.close(); //关闭数据库链接
			con.close();
			return ret;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return ret;
		} 	
	}	

	public void DeleteSqlDataByYear(String date_last)//清理数据库旧记录
	{
		String sql1 = "DELETE FROM stock where date < ?";
		String sql2 = "DELETE FROM sales where date  < ?";
		String sql3 = "DELETE FROM price where date  < ?";

		int answer = JOptionPane.showConfirmDialog(null, "确认要删除"+date_last.substring(0,10)+"之前的数据? ", "提交信息",
				JOptionPane.YES_NO_OPTION);
		if (answer != 0) {
			return;
		}		  

		// 连接数据库,执行查询语句
		try {
			con = DriverManager.getConnection(url, passname,password);
			con.setAutoCommit(false); // 设置数据不自动提交数据库

			PreparedStatement ps1 = con.prepareStatement(sql1); // SQL预处理
			ps1.setString(1, date_last);
			ps1.executeUpdate(); // 执行SQL
			PreparedStatement ps2 = con.prepareStatement(sql2); // SQL预处理
			ps2.setString(1, date_last);
			ps2.executeUpdate(); // 执行SQL
			PreparedStatement ps3 = con.prepareStatement(sql3); // SQL预处理
			ps3.setString(1, date_last);
			ps3.executeUpdate(); // 执行SQL				
			con.commit();
			ps1.close(); //关闭数据库链接
			ps2.close();
			ps3.close();
			con.close();
			JOptionPane.showMessageDialog(null, "清除数据成功!");

		} catch (SQLException e) {
			try {
				con.rollback(); //失败回滚
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			JOptionPane.showMessageDialog(null, "清除数据失败");
			e.printStackTrace();
		} 	
	}		  
}




  • 10
    点赞
  • 60
    收藏
    觉得还不错? 一键收藏
  • 4
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值