package pcdiysystem.Dao;
import javax.swing.JOptionPane;
import java.lang.StringBuffer;
import pcdiysystem.Beans.DefaultProjectBean;
import pcdiysystem.Panel.ProjectPanel;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.lang.Math;
import java.util.Calendar;
import javax.swing.table.TableModel;
import java.util.Vector;
/**
* Title: 自助装机系统
*
* Description:
*
* Copyright: Copyright (c) 2006
*
* Company:
*
* @author kamiiiyu
* @version 1.0
*/
public class DefaultProjectDAO {
ProjectPanel pPanObj;
public DefaultProjectDAO() {
}
public DefaultProjectDAO(ProjectPanel pPanObj) {
this.pPanObj = pPanObj;
}
public float calculate() {
try {
DefaultProjectBean dpb = new DefaultProjectBean();
dpb.setDataTable(pPanObj.getTabProjectData());
float total = 0;
for (int i = 0; i < dpb.getDataTable().getRowCount(); i++) {
String pTemp = String.valueOf(dpb.getDataTable().getValueAt(i, 3));
String qTemp = String.valueOf(dpb.getDataTable().getValueAt(i, 2));
if (!pTemp.equals("null") && !qTemp.equals("null")) {
float price = Float.valueOf(pTemp.substring(1));
float quantity = Integer.valueOf(qTemp);
total += (price * quantity);
}
}
pPanObj.getLblTotalValue().setText("￥" + String.valueOf(total) + "0元");
return total;
}
catch (NumberFormatException ne) {
JOptionPane.showMessageDialog(pPanObj, "请输入有效整数", "出错啦!",
JOptionPane.ERROR_MESSAGE);
pPanObj.getLblTotalValue().setText("无法计算!");
return -1f;
}
}
public float testCal(){//未完成
float [] priceOfComponent={0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f};
float [] totalPriceOfComponent={0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f};
int [] quantity={0,0,0,0,0,0,0,0,0,0,0,0,0,0};
float total=0;
return -1f;
}
public boolean creatSQLTable(String projectId) {
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "CREATE TABLE [dbo].["+projectId+"] ([ID] [numeric](18, 0) IDENTITY (0, 1) NOT NULL ,[ProjectId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,[Component] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[Quantity] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[Price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]ALTER TABLE [dbo].["+projectId+"] ADD CONSTRAINT [PK_"+projectId+"] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY] ALTER TABLE [dbo].["+projectId+"] ADD CONSTRAINT [FK_"+projectId+"_ProjectManagement] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[ProjectManagement] ([ProjectId]) ON DELETE CASCADE ON UPDATE CASCADE ";
pStatement = dbConnection.prepareStatement(ps);
pStatement.execute();
return true;
}
catch (SQLException ex) {
System.out.println("creatErr");
JOptionPane.showMessageDialog(pPanObj,"对不起，系统随机创建的方案名重复，建议重复刚才步骤或者指定方案名","数据库出错了",JOptionPane.ERROR_MESSAGE);
return false;
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
public void dropSQLTable(String projectId) {
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "drop table "+projectId+"";
pStatement = dbConnection.prepareStatement(ps);
pStatement.execute();
}
catch (SQLException ex) {
System.out.println("dropErr");
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
public String creatProjectId(){
Calendar date=Calendar.getInstance();
String month;
String day;
if(date.get(date.MONTH) month="0"+String.valueOf(date.get(date.MONTH)+1);
}else{
month=String.valueOf(date.get(date.MONTH)+1);
}
if(date.get(date.DATE) day="0"+String.valueOf(date.get(date.DATE));
}else{
day=String.valueOf(date.get(date.DATE));
}
String random=String.valueOf(Math.random()).substring(2,6);
String projectId="Pro"+month+day+random;
return projectId;
}
public void insertDataToProjectManagement(String id,String name,String price){
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
if(name.equals("") || name.equals("请为您的方案命名（可缺省）")){
name=id;
}
if(this.insertToProjectName(id,name)){
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "insert into projectManagement(projectid,projectname,projectcost) values(?,?,?)";
pStatement = dbConnection.prepareStatement(ps);
pStatement.setString(1, id);
pStatement.setString(2, name);
pStatement.setString(3, price);
int rowCount = pStatement.executeUpdate();
}
catch (SQLException ex) {
System.out.println("Err");
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
}
public void insertDataToDefaultProject(Vector dataSource,String targetTable){
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
int rowCount=dataSource.size();
int columnCount=((Vector)dataSource.get(0)).size();
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "insert into "+targetTable+"(projectId,Component,Type,Quantity,Price) values('"+targetTable+"',?,?,?,?)";
for(int i=0;i pStatement = dbConnection.prepareStatement(ps);
String [] item=new String[4];
for(int j=0;j item[j]=String.valueOf(((Vector)dataSource.get(i)).get(j));
pStatement.setString(j+1,item[j]);
}
int recordCount = pStatement.executeUpdate();
JOptionPane.showMessageDialog(this.pPanObj, "已经成功提交方案");
}
}
catch (SQLException ex) {
System.out.println("sqlErr");
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
public Vector getDataFromTableMOdel(TableModel dtm){
DefaultProjectBean dpb=new DefaultProjectBean();
for(int i=0;i Vector tdTemp=new Vector();
for(int j=0;j Object td=dtm.getValueAt(i,j);
tdTemp.add(td);
}
dpb.setFile(tdTemp);
}
return dpb.getFile();
}
private boolean insertToProjectName(String id,String name){
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "insert into projectName(projectname) values(?)";
pStatement = dbConnection.prepareStatement(ps);
pStatement.setString(1,name);
int rowCount=pStatement.executeUpdate();
return true;
}
catch (SQLException ex) {
System.out.println("NameErr");
JOptionPane.showMessageDialog(pPanObj,"对不起，方案名重复，请重新指定方案名","数据库出错了",JOptionPane.ERROR_MESSAGE);
this.dropSQLTable(id);
return false;
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
public Vector getProjectName(){
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "select projectname from projectmanagement";
pStatement = dbConnection.prepareStatement(ps);
resultSet=pStatement.executeQuery();
DefaultProjectBean dpb=new DefaultProjectBean();
while(resultSet.next()){
dpb.setRecord(resultSet.getString(1));
}
return dpb.getRecord();
}
catch (SQLException ex) {
System.out.println("gpErr");
return new Vector();
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
public Vector getProjectData(String projectName){
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
String tableName=this.getProIdfromProName(projectName);
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "select * from "+tableName;
pStatement = dbConnection.prepareStatement(ps);
resultSet=pStatement.executeQuery();
DefaultProjectBean dpb=new DefaultProjectBean();
while(resultSet.next()){
DefaultProjectBean temp=new DefaultProjectBean();
/*
temp.setRecord(resultSet.getObject(3));
temp.setRecord(resultSet.getObject(4));
temp.setRecord(resultSet.getObject(5));
temp.setRecord(resultSet.getObject(6));*///已过时，有可能造成界面混乱
for(int i=3;i Object tempItem=resultSet.getString(i);
if(tempItem.equals("null")){
tempItem=null;
}
temp.setRecord(tempItem);
}
dpb.setFile(temp.getRecord());
}
return dpb.getFile();
}
catch (SQLException ex) {
System.out.println("gpErr");
return new Vector();
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
public String getProIdfromProName(String name){
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "select projectId from projectmanagement where projectname='"+name+"'";
pStatement = dbConnection.prepareStatement(ps);
resultSet=pStatement.executeQuery();
resultSet.next();
String proId=resultSet.getString(1);
return proId;
}
catch (SQLException ex) {
System.out.println("gpErr");
return "null";
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
public String getProjectCost(String projectName){
Connection dbConnection = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
try {
dbConnection = ConnectionManager.getConnection();
if (dbConnection != null)
System.out.println("OK!");
String ps = "select projectcost from projectmanagement where projectname='"+projectName+"'";
pStatement = dbConnection.prepareStatement(ps);
resultSet=pStatement.executeQuery();
DefaultProjectBean dpb=new DefaultProjectBean();
while(resultSet.next()){
dpb.setRecord(resultSet.getString(1));
}
String cost=String.valueOf(dpb.getRecord().get(0));
pPanObj.getLblTotalValue().setText(cost + "元");
return cost;
}
catch (SQLException ex) {
System.out.println("gpErr");
return "null";
}
finally {
ConnectionManager.closeResultSet(resultSet);
ConnectionManager.closeStatement(pStatement);
ConnectionManager.closeConnection(dbConnection);
}
}
public boolean isTableEmpty(TableModel dtm){
DefaultProjectBean dpb=new DefaultProjectBean();
for(int i=0;i Object td=dtm.getValueAt(i,1);
if(td!=null){
return false;
}
}
return true;
}
}
