?? matebuydao.java
字號:
package imis_mate.DAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import database.DBPoolManager;
import imis_mate.bean.MaterialBuyBean;
import imis_mate.bean.MaterialBuyDetailBean;
import imis_mate.bean.MaterialBuySearchBean;
import imis_mate.bean.MaterialChangeBean;
public class MateBuyDAO {
private DBPoolManager dbpool;
private PreparedStatement pstmt;
private Statement st;
public MateBuyDAO(){
dbpool = new DBPoolManager();
}
public boolean save(MaterialBuyBean mbb) {
// TODO Auto-generated method stub
boolean flag=false;
dbpool.getConnection();
try{
//String sql ="insert into Material_Buy_Table(BuyNo,PayType,RePay,AcPay,InDate,BuyDep,BuyBy,ApplyDep,ApplyBy,UseDep,UseBy,BuyFor,SupplyNo,PerBy,ReMark) values(?,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?,?,?,?,?,?,?,?)";
String sql ="insert into Material_Buy_Table(BuyNo,PayType,RePay,AcPay,InDate,BuyDep,BuyBy,ApplyDep,ApplyBy,UseDep,UseBy,BuyFor,SupplyNo,PerBy,ReMark) values('";
sql = sql + mbb.getBuyNo()+"','";
sql = sql + mbb.getPayType() + "',";
sql = sql + mbb.getRePay() +",";
sql = sql + mbb.getAcPay() +",to_date('";
sql = sql + mbb.getInDate() +"','yyyy-mm-dd'),";
sql = sql + mbb.getBuyDep() +",'";
sql = sql + mbb.getBuyBy() +"',";
sql = sql + mbb.getApplyDep() +",'";
sql = sql + mbb.getApplyBy() +"',";
sql = sql + mbb.getUseDep()+",'";
sql = sql + mbb.getUseBy() +"','";
sql = sql + mbb.getBuyFor() +"',";
sql = sql + mbb.getSupplyNo() +",'";
sql = sql + mbb.getPerBy() +"','";
sql = sql + mbb.getReMark()+"')";
st = dbpool.conn.createStatement();
int i =st.executeUpdate(sql);
flag = i>0?true:false;
}catch(Exception e){
flag = false;
e.printStackTrace();
}finally{
dbpool.freeConnection();
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
st=null;
}
}
return flag;
}
public boolean update(MaterialBuyBean mbb){
boolean flag = false;
dbpool.getConnection();
try{
String sql ="update Material_Buy_Table set PayType='";
sql = sql+mbb.getPayType()+"',RePay=";
sql = sql+mbb.getRePay()+",AcPay=";
sql = sql+mbb.getAcPay()+",InDate=to_date('"+mbb.getInDate()+"','yyyy-mm-dd'),BuyDep=";
sql = sql+mbb.getBuyDep()+",BuyBy='"+mbb.getBuyBy()+"',ApplyDep="+mbb.getApplyDep()+"," +
"ApplyBy='"+mbb.getApplyBy()+"',UseDep=";
sql = sql+mbb.getUseDep()+",UseBy='"+mbb.getUseBy()+"',BuyFor='"+mbb.getBuyFor()+"'," +
"SupplyNo='"+mbb.getSupplyNo()+"',PerBy='";
sql = sql+mbb.getPerBy()+"',ReMark='"+mbb.getReMark()+"' where BuyNo='"+mbb.getBuyNo()+"'";
st=dbpool.conn.createStatement();
flag = st.executeUpdate(sql)>0?true:false;
}catch(Exception e){
flag = false;
e.printStackTrace();
}finally{
dbpool.freeConnection();
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pstmt=null;
}
}
return flag;
}
public boolean delete(String buyNo){
boolean flag = false;
dbpool.getConnection();
String sql = "delete from Material_Buy_Table where BuyNo='"+buyNo+"'";
try {
st = dbpool.conn.createStatement();
flag = st.executeUpdate(sql)>0?true:false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
flag = false;
}finally{
dbpool.freeConnection();
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
st=null;
}
}
return flag;
}
public ArrayList getMateBuy(){
ArrayList list =new ArrayList();
dbpool.getConnection();
MaterialBuyBean mbb = new MaterialBuyBean();
String sql ="select buyNo,payType,rePay,acPay,inDate,buyDep,buyBy,applyDep,applyBy,useDep,useBy,buyFor,supplyNo,perBy,reMark from Material_Buy_Table";
try {
st = dbpool.conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
mbb.setBuyNo(rs.getString("buyNo"));
if(rs.getString("payType")==null){
mbb.setPayType("");
}else{
mbb.setPayType(rs.getString("payType").trim());
}
mbb.setRePay(rs.getDouble("rePay"));
mbb.setAcPay(rs.getDouble("inDate"));
if(rs.getString("inDate")==null){
mbb.setInDate("");
}else{
mbb.setInDate(rs.getString("inDate").trim());
}
mbb.setBuyDep(rs.getInt("buyDep"));
if(rs.getString("buyBy")==null){
mbb.setBuyBy("");
}else{
mbb.setBuyBy(rs.getString("buyBy"));
}
mbb.setApplyDep(rs.getInt("applyDep"));
if(rs.getString("applyBy")==null){
mbb.setApplyBy("");
}else{
mbb.setApplyBy(rs.getString("applyBy").trim());
}
mbb.setUseDep(rs.getInt("useDep"));
if(rs.getString("useBy")==null){
mbb.setUseBy("");
}else{
mbb.setUseBy(rs.getString("useBy").trim());
}
if(rs.getString("buyFor")==null){
mbb.setBuyFor("");
}else{
mbb.setBuyFor(rs.getString("buyFor"));
}
mbb.setSupplyNo(rs.getInt("supplyNo"));
if(rs.getString("perBy")==null){
mbb.setPerBy("");
}else{
mbb.setPerBy(rs.getString("perBy").trim());
}
if(rs.getString("reMark")==null){
mbb.setReMark("");
}else{
mbb.setReMark(rs.getString("reMark").trim());
}
list.add(mbb);
}
st.close();
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbpool.freeConnection();
}
return list;
}
public MaterialBuyBean getMateBuyByBuyNo(String buyNo){
MaterialBuyBean mbb = new MaterialBuyBean();
String sql ="select buyNo,payType,rePay,acPay,inDate,buyDep,buyBy,applyDep,applyBy,useDep,useBy,buyFor,supplyNo,perBy,reMark from Material_Buy_Table where buyNo = '"+buyNo+"'";
dbpool.getConnection();
try {
st = dbpool.conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
mbb.setBuyNo(rs.getString("buyNo"));
if(rs.getString("payType")==null){
mbb.setPayType("");
}else{
mbb.setPayType(rs.getString("payType").trim());
}
mbb.setRePay(rs.getDouble("rePay"));
mbb.setAcPay(rs.getDouble("acPay"));
if(rs.getDate("inDate")==null){
mbb.setInDate("");
}else{
mbb.setInDate(rs.getDate("inDate").toString());
}
mbb.setBuyDep(rs.getInt("buyDep"));
if(rs.getString("buyBy")==null){
mbb.setBuyBy("");
}else{
mbb.setBuyBy(rs.getString("buyBy"));
}
mbb.setApplyDep(rs.getInt("applyDep"));
if(rs.getString("applyBy")==null){
mbb.setApplyBy("");
}else{
mbb.setApplyBy(rs.getString("applyBy").trim());
}
mbb.setUseDep(rs.getInt("useDep"));
if(rs.getString("useBy")==null){
mbb.setUseBy("");
}else{
mbb.setUseBy(rs.getString("useBy").trim());
}
if(rs.getString("buyFor")==null){
mbb.setBuyFor("");
}else{
mbb.setBuyFor(rs.getString("buyFor"));
}
mbb.setSupplyNo(rs.getInt("supplyNo"));
if(rs.getString("perBy")==null){
mbb.setPerBy("");
}else{
mbb.setPerBy(rs.getString("perBy").trim());
}
if(rs.getString("reMark")==null){
mbb.setReMark("");
}else{
mbb.setReMark(rs.getString("reMark").trim());
}
}
st.close();
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbpool.freeConnection();
}
if(mbb.getBuyNo()==null)
{
return null;
}
else
return mbb;
}
public ArrayList search(MaterialBuyBean mate, MaterialBuyDetailBean mateD, String startDate, String endDate) throws Exception {
//多條件查詢
ArrayList array = new ArrayList();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
MaterialTypeDAO mateTypeDAO = new MaterialTypeDAO();
OrganDAO organDAO = new OrganDAO();
//Build the search criterias
StringBuffer sql = new StringBuffer(512);
sql.append( "SELECT Material_Buy_Table.BuyNo BuyNo, Material_Buy_Detail.MaterialTypeName, Material_Buy_Detail.MaModel, Material_Buy_Detail.PriceUnit, Material_Buy_Detail.InCount, Material_Buy_Table.ApplyDep, Material_Buy_Table.BuyFor " +
"FROM Material_Buy_Table,Material_Buy_Detail " +
"WHERE Material_Buy_Table.BuyNo = Material_Buy_Detail.BuyNo AND " );
// if(mate.getApplyDep() != 0) {
// sql.append("ApplyDep = '" +
// DBUtil.fixSqlFieldValue(String.valueOf(mate.getApplyDep())) + "' AND ");
// }
if(mate.getApplyDep() != 0) {
sql.append("(ApplyDep IN " +organDAO.getOrganSQL(mate.getApplyDep(),"ApplyDep")+") AND ");
}
// if(mateD.getMaterialTypeName() != null && mateD.getMaterialTypeName().equals("") != true) {
// sql.append("MaterialTypeName LIKE '%" +
// DBUtil.fixSqlFieldValue(mateD.getMaterialTypeName()) + "%' AND ");
// }
// if (mateD.getMaterialTypeNo() != null && mateD.getMaterialTypeNo().equals("") != true) {
// sql.append("MaterialTypeNo = '" +
// DBUtil.fixSqlFieldValue(mateD.getMaterialTypeNo()) + "' AND ");
// }
if (mateD.getMaterialTypeNo() != null && mateD.getMaterialTypeNo().equals("") != true) {
sql.append("(MaterialTypeNo IN "+mateTypeDAO.getMaterialSQL(mateD.getMaterialTypeNo())+") AND ");
}
if (mateD.getFlag() != null && mateD.getFlag().equals("") != true && !mateD.getFlag().equals("-1")) {
//入庫條件 不選擇 的情況
sql.append("Flag = '" +
DBUtil.fixSqlFieldValue(mateD.getFlag()) + "' AND ");
} else if (mateD.getFlag() != null && mateD.getFlag().trim().equals("-1")) {
//入庫標志 條件為 否 的情況
sql.append("Flag IS NULL AND ");
}
// if (mateD.getFlag() != null && mateD.getFlag().trim().equals("*")) {
// //入庫標志 條件為 是 的情況
// sql.append("Flag = '" +
// DBUtil.fixSqlFieldValue(mateD.getFlag()) + "' AND ");
// }
if (mateD.getReFlag() != null && mateD.getReFlag().equals("") != true && !mateD.getReFlag().equals("-1")) {
//退貨標志 條件不選擇的情況
sql.append("ReFlag = '" +
DBUtil.fixSqlFieldValue(mateD.getReFlag()) + "' AND ");
} else if (mateD.getReFlag() != null && mateD.getReFlag().trim().equals("-1")) {
//退貨標志 條件 否 的情況
sql.append("ReFlag IS NULL AND ");
}
// if (mateD.getReFlag() != null && mateD.getReFlag().trim().equals("*")) {
// //退貨標志 條件 是 的情況
// sql.append("ReFlag = '" +
// DBUtil.fixSqlFieldValue(mateD.getReFlag()) + "' AND ");
// }
if((startDate != null && startDate.equals("") != true) && (endDate != null && endDate.equals("") != true)) {
sql.append("inDate BETWEEN TO_DATE('" +
DBUtil.fixSqlFieldValue(startDate.trim()) + "','YYYY-MM-DD') AND TO_DATE('"+endDate.trim()+"','YYYY-MM-DD')");
}
//Remove unused 'AND' & 'WHERE'
if(sql.substring(sql.length()-5).equals(" AND ")) {
sql.delete(sql.length()-5, sql.length()-1);
}
if(sql.substring(sql.length()-7).equals(" WHERE ")) {
sql.delete(sql.length()-7, sql.length()-1);
}
sql.append(" order by Material_Buy_Table.BuyNo");
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql.toString());
MaterialBuySearchBean mate1 = null;
while(rs.next()) {
mate1 = new MaterialBuySearchBean();
mate1.setBuyNo(rs.getString("BuyNo"));
mate1.setApplyDep(rs.getInt("ApplyDep"));
mate1.setBuyFor(rs.getString("BuyFor"));
mate1.setMaterialTypeName(rs.getString("MaterialTypeName"));
mate1.setMaModel(rs.getString("MaModel"));
mate1.setPriceUnit(rs.getDouble("PriceUnit"));
mate1.setInCount(rs.getInt("InCount"));
array.add(mate1);
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(rs);
DBConnection.close(stmt);
DBConnection.close(conn);
}
return array;
}
//修改采購表所有信息
public boolean update(String buyNo,MaterialBuyBean mbb){
boolean flag = false;
dbpool.getConnection();
try{
String sql ="update Material_Buy_Table set BuyNo='"+mbb.getBuyNo()+"',PayType='";
sql = sql+mbb.getPayType()+"',RePay=";
sql = sql+mbb.getRePay()+",AcPay=";
sql = sql+mbb.getAcPay()+",InDate=to_date('"+mbb.getInDate()+"','yyyy-mm-dd'),BuyDep=";
sql = sql+mbb.getBuyDep()+",BuyBy='"+mbb.getBuyBy()+"',ApplyDep="+mbb.getApplyDep()+"," +
"ApplyBy='"+mbb.getApplyBy()+"',UseDep=";
sql = sql+mbb.getUseDep()+",UseBy='"+mbb.getUseBy()+"',BuyFor='"+mbb.getBuyFor()+"'," +
"SupplyNo='"+mbb.getSupplyNo()+"',PerBy='";
sql = sql+mbb.getPerBy()+"',ReMark='"+mbb.getReMark()+"' where BuyNo='"+buyNo+"'";
st=dbpool.conn.createStatement();
flag = st.executeUpdate(sql)>0?true:false;
}catch(Exception e){
flag = false;
e.printStackTrace();
}finally{
dbpool.freeConnection();
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pstmt=null;
}
}
return flag;
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -