📄 gbs_tpresentation_db.java

📁 对日软件外包为东芝做的一个全球商业管理系统
💻 JAVA
📖 第 1 页 / 共 3 页
字号:
12 3 下一页
/*
 * Created on 2004/08/11
 *
 */
package DB;

import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import oracle.sql.BLOB;

import org.apache.struts.upload.FormFile;

import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_File_stBean;
import DataBean.GBS_Presentation_stBean;

/**
 * GBS_TPresentation_DB implements SystemConstants
 * created on 11-08-2004
 * @author mxx
 * @version  1.0
 */

public class GBS_TPresentation_DB implements SystemConstants {
	private DataSource datasource = null;
	
	public GBS_TPresentation_DB(DataSource datasource){
		this.datasource = datasource;
	}
	
/**
 * method getPresentationList  
 * @param String strCustomerId
 * @param String strRfpNo
 * @return ReturnValue
 * @throws Exception
 */	
	
public ReturnValue getPresentationList(String strCustomerId,
										String strRfpNo) throws Exception {
			//Add by YM 2004/09/10 Start
			strCustomerId = BaseCommonCheck.convertSql(strCustomerId);
			strRfpNo = BaseCommonCheck.convertSql(strRfpNo);
			//Add by YM 2004/09/10 End
			StringBuffer sql = new StringBuffer();
			List retData = new Vector();
			ReturnValue returnValueD = new ReturnValue();
			MessageList messageList = new MessageList();
			returnValueD.setMessageList(messageList);
			
////////////////////////////////////////////////////////////////////////	
//EDIT SQL START
			sql.append(" SELECT ");
			sql.append(" T_PRESENTATION.CUSTOMER_ID, ");
			sql.append(" RFP_NO, ");
			sql.append(" CUSTOMER_NAME, ");
			sql.append(" LINE_NO, ");			
			sql.append(" TO_CHAR(T_PRESENTATION.ATTACH_DATE,'DD-Mon-YYYY') ATTACH_DATE, ");
			sql.append(" TITLE, ");			
			sql.append(" TO_CHAR(T_PRESENTATION.PRESENTATION_DATE,'DD-Mon-YYYY') PRESENTATION_DATE, ");
			sql.append(" PRESENTATION_BY,  ");
			sql.append(" LISTENER,  ");
			sql.append(" ATTACHMENT_FILE_NAME ");//
			sql.append(" FROM  ");
			sql.append(" T_PRESENTATION,M_CUSTOMER  ");
			sql.append(" WHERE ");
			sql.append(" T_PRESENTATION.CUSTOMER_ID ='");
			sql.append(strCustomerId );
			sql.append("' AND RFP_NO ='");
			sql.append(strRfpNo);
			sql.append("' AND M_CUSTOMER.CUSTOMER_ID = T_PRESENTATION.CUSTOMER_ID(+)  ");
			sql.append(" ORDER BY PRESENTATION_DATE DESC ");
//EDIT SQL END
////////////////////////////////////////////////////////////////////////
  			System.out.println( "[INFO] sql = " + sql.toString() );			
				
			ResultSet rset = null;
			Connection conn = null;
			Statement st = null;
			try{
				conn = this.datasource.getConnection();
				st = conn.createStatement();
				rset = st.executeQuery(sql.toString());
				
				while(rset.next()){
					//GET DATA FROM DB RESULTSET
					GBS_Presentation_stBean line = new GBS_Presentation_stBean();
					line.setCustomerId(rset.getString( "CUSTOMER_ID" )) ;
					line.setRfpNo(rset.getString("RFP_NO" ) );
					line.setCustomerName(rset.getString( "CUSTOMER_NAME" )) ;
					line.setLineNo(rset.getString("LINE_NO")) ;
					line.setAttachDate(rset.getString( "ATTACH_DATE" )) ;
					line.setTitle(rset.getString("TITLE")) ;
					line.setPresentationDate( rset.getString ( "PRESENTATION_DATE")) ;
					line.setPresentationBy(rset.getString("PRESENTATION_BY"));
					line.setListener(rset.getString ( "LISTENER")) ;	
					String fileName = rset.getString("ATTACHMENT_FILE_NAME");
					String fileType = BaseCommonCheck.getFileType( fileName );	
					line.setAttachmentFileName(fileName);
					line.setFileType(fileType);
					retData.add(line);								
				}
				if(retData.size() <= 0 ){					
					//SET ERROR MESSAGELIST	
					messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);					
					returnValueD.setBussinessError();
				}							
				}catch (Exception exception) {				
				System.out.println("[Error Happen!]");
				System.out.println("[Start Trace]");
				exception.printStackTrace();
				System.out.println("[End Trace]");
				throw exception;
			 }finally {
				returnValueD.setDataValue(retData);
			//RELEASE DB
			try {
				  if (rset != null) {
					rset.close();
				  }
				  if (st != null) {
					st.close();
				  }
				  if (conn != null) {
					conn.close();
				  }
				 }catch (SQLException se) {
					}
		  }
		 
		  return returnValueD;
		} 
/**
 * method selPresentationByKey  
 * @param String strCustomerId
 * @param String strRfpNo
 * @param String strLineNo
 * @return ReturnValue
 * @throws Exception
 */

public ReturnValue selPresentationByKey(String strCustomerId, String strRfpNo, String strLineNo) throws Exception {
	//Add by YM 2004/09/10 Start
	strCustomerId = BaseCommonCheck.convertSql(strCustomerId);
	strRfpNo = BaseCommonCheck.convertSql(strRfpNo);
	strLineNo = BaseCommonCheck.convertSql(strLineNo);
	//Add by YM 2004/09/10 End
	GBS_Presentation_stBean retData = new GBS_Presentation_stBean();
	StringBuffer sql = new StringBuffer();
	ReturnValue returnValueD = new ReturnValue();
	MessageList messageList = new MessageList();
	returnValueD.setMessageList(messageList);
	//	  //////////////////////////////////////////////////////////////////////	
	//	  EDIT SQL START
	sql.append(" SELECT ");
	sql.append(" CUSTOMER_ID, ");
	sql.append(" RFP_NO, ");
	sql.append(" LINE_NO, ");
	sql.append(" ATTACHMENT_FILE_NAME, ");
	sql.append(" TITLE, ");
	sql.append(" LISTENER, ");
	sql.append(" TO_CHAR(T_PRESENTATION.PRESENTATION_DATE,'DD-Mon-YYYY') PRESENTATION_DATE, ");
	sql.append(" PRESENTATION_BY ");
	sql.append(" FROM  ");
	sql.append(" T_PRESENTATION ");
	sql.append(" WHERE ");
	sql.append(" CUSTOMER_ID ='");
	sql.append(strCustomerId);
	sql.append("' AND RFP_NO ='");
	sql.append(strRfpNo);
	sql.append("' AND LINE_NO ='");
	sql.append(strLineNo + "'");

	//	  EDIT SQL END
	//	  //////////////////////////////////////////////////////////////////////
	System.out.println("[INFO] sql = " + sql.toString());

	ResultSet rset = null;
	Connection conn = null;
	Statement st = null;
	try {
		conn = this.datasource.getConnection();
		st = conn.createStatement();
		rset = st.executeQuery(sql.toString());
		if (rset.next()) {
			//GET DATA FROM DB RESULTSET
			retData.setCustomerId(rset.getString("CUSTOMER_ID"));
			retData.setRfpNo(rset.getString("RFP_NO"));
			retData.setLineNo(rset.getString("LINE_NO"));
			retData.setAttachmentFileName(rset.getString("ATTACHMENT_FILE_NAME"));
			retData.setTitle(rset.getString("TITLE"));
			retData.setListener(rset.getString("LISTENER"));
			retData.setPresentationDate(rset.getString("PRESENTATION_DATE"));
			retData.setPresentationBy(rset.getString("PRESENTATION_BY"));
			//SET RETURNVALUE
			returnValueD.setDataValue(retData);
		} else {
			//SET ERROR MESSAGELIST	
			messageList.setMessage("", "", "10000001", Integer.MIN_VALUE);
			returnValueD.setBussinessError();
		}
	} catch (Exception exception) {
		System.out.println("[Error Happen!]");
		System.out.println("[Start Trace]");
		exception.printStackTrace();
		System.out.println("[End Trace]");
		throw exception;
	} finally {
		//RELEASE DB
		try {
			if (rset != null) {
				rset.close();
			}
			if (st != null) {
				st.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException se) {
		}
	}
	return returnValueD;
} 

/**
 * method insertPresentation  
 * @param String strCustomerId
 * @param String strRfpNo
 * @param String strLineNo
 * @param String strTitle
 * @param String strPresentationDate
 * @param String strPresentationBy
 * @param String strCreateUser
 * @param FormFile AttachmentFile
 * @return ReturnValue
 * @throws Exception
 */

public ReturnValue insertPresentation(
	String strCustomerId,
	String strRfpNo,
	String strListener,
	String strTitle,
	String strPresentationDate,
	String strPresentationBy,
	String strCreateUser,
	FormFile AttachmentFile)
	throws Exception {
	//Add by YM 2004/09/10 Start
	strCustomerId = BaseCommonCheck.convertSql(strCustomerId);
	strRfpNo = BaseCommonCheck.convertSql(strRfpNo);
	strListener = BaseCommonCheck.convertSql(strListener);
	strTitle = BaseCommonCheck.convertSql(strTitle);
	strPresentationDate = BaseCommonCheck.convertSql(strPresentationDate);
	strPresentationBy = BaseCommonCheck.convertSql(strPresentationBy);
	strCreateUser = BaseCommonCheck.convertSql(strCreateUser);
	//Add by YM 2004/09/10 End
	StringBuffer sql = new StringBuffer();
	StringBuffer sql1 = new StringBuffer();
	ReturnValue returnValueD = new ReturnValue();
	MessageList messageList = new MessageList();
	returnValueD.setMessageList(messageList);
	ResultSet rset = null;
	Connection conn = null;
	Statement st = null;
	PreparedStatement pstmt = null;
	int lineNo = 1;
	int bresult = 0;
	//EDIT SQL TO SELECT THE MAX LINENO
	sql1.append(" SELECT MAX(T_PRESENTATION.LINE_NO) AS LINE_NO FROM T_PRESENTATION ");
	sql1.append(" WHERE CUSTOMER_ID =");
	sql1.append("'" + strCustomerId + "'");
	sql1.append(" AND RFP_NO =");
	sql1.append("'" + strRfpNo + "'");

	System.out.println("[INFO] sql = " + sql1.toString());

	try {
		conn = this.datasource.getConnection();
		st = conn.createStatement();
		rset = st.executeQuery(sql1.toString());
		if (rset.next()) {
			lineNo = rset.getInt("LINE_NO");
			lineNo = lineNo + 1;
		}
		//EDIT SQL TO INSERT DB		 
		sql.append(" INSERT INTO T_PRESENTATION ");
		sql.append(" (CUSTOMER_ID,RFP_NO,LINE_NO,ATTACH_DATE,TITLE,");
		sql.append("ATTACHMENT_FILE_NAME,ATTACHMENT_FILE,CREATE_USER,");
		sql.append("CREATE_DATE,PRESENTATION_DATE,PRESENTATION_BY,LISTENER) ");
		sql.append(" VALUES( '");
		sql.append(strCustomerId + "','" + strRfpNo + "','");
		sql.append(lineNo + "',SYSDATE " + ",'");
		sql.append(strTitle + "','',null,'");
		sql.append(
			strCreateUser
				+ "',SYSDATE,"
				+ "to_date('"+ strPresentationDate + "','yyyy/MM/dd')"
				+ ",'"
				+ strPresentationBy
				+ "','"
				+ strListener
				+ "')");
		//sql.append("to_date(	'" + detail_st.getInstallDate() + "','yyyy/MM/dd'),");
		System.out.println("[INFO] sql = " + sql.toString());

		//st = conn.createStatement();
		pstmt = conn.prepareStatement(sql.toString());
12 3 下一页
💿 文件大小 2494 K
👤 上传用户 liu2237329
📂 所属分类企业管理
🏷️ 相关标签

#软件 #东芝 #商 #管理系统
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -