java 销售系统_JAVA学习作品之销售管理系统V1.0

package org.screen;

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登录password

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 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 = ?";

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())

{

sales_price = rt.getString("unitprice");

}

// 关闭数据库连接

ps.close();

rt.close();

con.close();

} catch (SQLException e) {

JOptionPane.showMessageDialog(null, "销售单位价格数据载入异常！ ");

e.printStackTrace();

}

return sales_price;

}

public ArrayList getSalesRecordByDate(String date_begin, String date_end, Boolean sum_qty_totalprice)

{

ArrayList list = new ArrayList();

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 getStockRecordByDate(String date_begin, String date_end, int status, Boolean sum_amount_qty)

{

ArrayList list = new ArrayList();

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 void CommitSalesStockPricingToSql(DefaultTableModel model, JTable table) //传入用model定义的table

{

//这里先删除原有定价记录，再插入新纪录

String sql1 = "DELETE FROM price WHERE goods = ? ";

String sql2 = "INSERT INTO price (goods, unit, cost, unitprice) VALUES(?,?,?,?)";

try {

// 设置数据库链接，设置手动提交数据

con = DriverManager.getConnection(url, passname, password);

con.setAutoCommit(false);

PreparedStatement ps1 = con.prepareStatement(sql1);// SQL预处理

PreparedStatement ps2 = 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()))

{

ps1.setString(1, (String) model.getValueAt(i, 0));

ps1.addBatch();

ps2.setString(1, (String) model.getValueAt(i, 0));

ps2.setString(2, (String) model.getValueAt(i, 3));

ps2.setString(3, model.getValueAt(i, 4).toString());

ps2.setString(4, model.getValueAt(i, 5).toString());

ps2.addBatch();

}

}

}

ps1.executeBatch();// 运行批处理。弹出对话消息。显示成功失败

con.commit();

//关闭数据库相关链接

ps1.close();

int[] rt = ps2.executeBatch();// 运行批处理，弹出对话消息，显示成功失败

con.commit();

if (rt.length > 0)

JOptionPane.showMessageDialog(null, "提交成功！");

ps2.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 = ?";

// 连接数据库。运行查询语句

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 < ?

";

int answer = JOptionPane.showConfirmDialog(null, "确认要删除"+date_last+"之前的数据？ ", "提交信息",

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

con.commit();

ps1.close(); //关闭数据库链接

ps2.close();

con.close();

JOptionPane.showMessageDialog(null, "清除数据成功！

");

} catch (SQLException e) {

try {

con.rollback(); //失败回滚

} catch (SQLException e1) {

e1.printStackTrace();

}

JOptionPane.showMessageDialog(null, "清除数据失败");

e.printStackTrace();

}

}

}