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.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;
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_ServiceInfoList_Bean;

/**
 * GBS_MServiceInfo_DB implements SystemConstants
 * created on 29-07-2004
 * @author mxx
 * @version  1.0
 */

public class GBS_MServiceInfo_DB implements SystemConstants {
	private DataSource datasource = null;

	public GBS_MServiceInfo_DB(DataSource datasource) {
		this.datasource = datasource;
	}

	/**
	 * method getServiceinfoList  
	 * @param 
	 * @return ReturnValue
	 * @throws Exception
	 */
	public ReturnValue getServiceinfoList(int plusHour) throws Exception {
		ReturnValue returnValueD = new ReturnValue();

		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		List retdata = new Vector();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append("  M_SERVICE_INFO.SEQ_NO, "); //
		sql.append("  M_SERVICE_INFO.TITLE, "); //
		sql.append("  (T_CREATE_USER.MEMBER_NAME_FIRST || ' ' ||  T_CREATE_USER.MEMBER_NAME_LAST) CREATE_USER, "); //
		sql.append("  M_SERVICE_INFO.CREATE_DATE, "); //
		sql.append("  (T_UPDATE_USER.MEMBER_NAME_FIRST || ' ' ||  T_UPDATE_USER.MEMBER_NAME_LAST) UPDATE_USER, "); //
		sql.append("  M_SERVICE_INFO.UPDATE_DATE, "); //
		sql.append("  M_SERVICE_INFO.ATTACHMENT_FILE_NAME "); //
		sql.append(" FROM ");
		sql.append("  M_SERVICE_INFO, M_MEMBER T_CREATE_USER,  M_MEMBER T_UPDATE_USER ");
		sql.append(" WHERE ");
		sql.append("  T_CREATE_USER.USER_ID(+) = M_SERVICE_INFO.CREATE_USER ");
		sql.append("  AND T_UPDATE_USER.USER_ID(+) = M_SERVICE_INFO.UPDATE_USER ");
		sql.append(" ORDER BY ");
		sql.append("  M_SERVICE_INFO.SEQ_NO DESC");

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

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			//System.out.println("[info]GBS_MServiceinfo_DB.getServiceinfoList.sql=" + sql);
			rset = st.executeQuery(sql.toString());
			while (rset.next()) {
				GBS_ServiceInfoList_Bean line = new GBS_ServiceInfoList_Bean();
				line.setSeqno(rset.getString("SEQ_NO"));
				line.setTitle(rset.getString("TITLE"));

				Calendar gmtCal = Calendar.getInstance();

				rset.getTimestamp("CREATE_DATE", gmtCal);
				gmtCal.add(Calendar.HOUR, plusHour);
				//System.out.println("2  " +gmtCal.getTime());
				SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);
				line.setCreatedate(formatter.format(gmtCal.getTime()));

				rset.getTimestamp("UPDATE_DATE", gmtCal);
				gmtCal.add(Calendar.HOUR, plusHour);
				line.setUpdatedate(formatter.format(gmtCal.getTime()));

				line.setCreateuser(rset.getString("CREATE_USER"));
				//line.setCreatedate(rset.getString("CREATE_DATE"));
				line.setUpdateuser(rset.getString("UPDATE_USER"));
				//line.setUpdatedate(rset.getString("UPDATE_DATE"));
				String filename = rset.getString("ATTACHMENT_FILE_NAME");
				String filetype = BaseCommonCheck.getFileType(filename);
				line.setFiletype(filetype);
				line.setAttachmentfilename(filename);
				retdata.add(line);
			}
			if (retdata.size() == 0) {
				messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
				returnValueD.setBussinessError();
			}
			returnValueD.setDataValue(retdata);
		} 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 selectBySeqno  
	 * @param String Seqno
	 * @return ReturnValue
	 * @throws Exception
	 */
	public ReturnValue selectBySeqno(String seqno) throws Exception {
		//Add by Gxk 2004/09/10 Start
		seqno = BaseCommonCheck.convertSql(seqno);
		//Add by Gxk 2004/09/10 End
		GBS_ServiceInfoList_Bean retdata = new GBS_ServiceInfoList_Bean();
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		sql.append(" SELECT");
		sql.append("  M_SERVICE_INFO.SEQ_NO, "); //
		sql.append("  M_SERVICE_INFO.TITLE, "); //
		sql.append("  M_SERVICE_INFO.CREATE_USER, "); //
		sql.append("  TO_CHAR(M_SERVICE_INFO.CREATE_DATE,'DD-Mon-YYYY') CREATE_DATE, "); //
		sql.append("  M_SERVICE_INFO.UPDATE_USER, "); //
		sql.append("  TO_CHAR(M_SERVICE_INFO.UPDATE_DATE,'DD-Mon-YYYY') UPDATE_DATE, "); //
		sql.append("  M_SERVICE_INFO.ATTACHMENT_FILE_NAME "); //
		sql.append(" FROM ");
		sql.append("  M_SERVICE_INFO ");
		sql.append(" WHERE ");
		sql.append("  M_SERVICE_INFO.SEQ_NO =");
		sql.append(seqno);

		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()) {
				retdata.setSeqno(rset.getString("SEQ_NO"));
				retdata.setTitle(rset.getString("TITLE"));
				retdata.setCreatedate(rset.getString("CREATE_DATE"));
				retdata.setCreateuser(rset.getString("CREATE_USER"));
				retdata.setUpdatedate(rset.getString("UPDATE_DATE"));
				retdata.setUpdateuser(rset.getString("UPDATE_USER"));
				retdata.setAttachmentfilename(rset.getString("ATTACHMENT_FILE_NAME"));
			} else {
				//data not found;
				messageList.setMessage("", "", "10000001", Integer.MIN_VALUE);
				returnValueD.setBussinessError();
			}
			returnValueD.setDataValue(retdata);
		} 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 updateService  
	 * @param String Seqno
	 * @param String Title
	 * @param String Updatedate
	 * @param String Updateuser
	 * @param FormFile AttachmentFile
	 * @param boolean FileDelete
	 * @return ReturnValue
	 * @throws Exception
	 */

	public ReturnValue updateService(
		String seqno,
		String title,
		String updateDate,
		String updateUser,
		FormFile AttachmentFile,
		boolean FileDelete)
		throws Exception {
		//Add by Gxk 2004/09/10 Start
		seqno = BaseCommonCheck.convertSql(seqno);
		title = BaseCommonCheck.convertSql(title);
		updateDate = BaseCommonCheck.convertSql(updateDate);
		updateUser = BaseCommonCheck.convertSql(updateUser);
		//Add by Gxk 2004/09/10 End
		GBS_ServiceInfoList_Bean retdata = new GBS_ServiceInfoList_Bean();
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		sql.append(" SELECT ");
		sql.append(" 		SEQ_NO ");
		sql.append(" FROM   ");
		sql.append("  		M_SERVICE_INFO");
		sql.append(" WHERE	 ");
		sql.append(" 		SEQ_NO = '" + seqno + "' ");
		sql.append(" FOR UPDATE ");
		System.out.println("[info]GBS_MServiceinfo_DB.updateService.sql=" + sql.toString());
		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		int bresult = 0;
		String strSql = "";
		try {
			strSql = sql.toString();
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(strSql);
			if (rset.next()) {
				bresult = 1;
			}
			if (bresult > 0) {
				strSql = "";
				sql = new StringBuffer();
				sql.append(" UPDATE ");
				sql.append(" M_SERVICE_INFO ");
				sql.append(" SET ");
				sql.append("  M_SERVICE_INFO.SEQ_NO = '"); //
				sql.append(seqno + "', ");
				sql.append("  M_SERVICE_INFO.TITLE = '"); //
				sql.append(title + "', ");
				sql.append("  M_SERVICE_INFO.UPDATE_DATE = SYSDATE,"); //
				if (FileDelete) {
					sql.append("   ATTACHMENT_FILE_NAME = '', ");
					sql.append("   ATTACHMENT_FILE = null, ");
				}
				sql.append("  M_SERVICE_INFO.UPDATE_USER = '"); //
				sql.append(updateUser + "' ");
				sql.append(" WHERE  SEQ_NO = '");
				sql.append(seqno + "'");
				System.out.println("[info]GBS_MServiceinfo_DB.updateService.sql=" + sql.toString());

				strSql = sql.toString();

				//st = conn.createStatement();
				pstmt = conn.prepareStatement(strSql);
				//updateAttachmentFile(Seqno,data);
				bresult = pstmt.executeUpdate();

				if (!FileDelete && bresult > 0 && AttachmentFile != null && !AttachmentFile.getFileName().equals("")) {
					ReturnValue returnValue = updateAttachmentFile(seqno, AttachmentFile, conn);
					if (returnValue.isError()) {
						if (returnValue.isBussinessError()) {
							messageList.addAll(returnValue.getMessageList());
						}
						returnValueD.setErrorCode(returnValue.getErrorCode());
						returnValueD.setMessageList(returnValue.getMessageList());
					} else {
						bresult = ((Integer) returnValue.getDataValue()).intValue();
					}
				}
			}
		} 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(new Integer(bresult));
			try {
				//COMMIT DATA
				if (bresult > 0 && !returnValueD.isError()) {
					conn.commit();
				} else {
					conn.rollback();
				}
				//CLOSE DB CONN
				if (rset != null) {
					rset.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
				return returnValueD;
			}
		}
		return returnValueD;
	}

	/**
	 * method updateService  
	 * @param String Seqno
	 * @param String Title
	 * @param String Updatedate
	 * @param String Updateuser
	 * @param FormFile AttachmentFile
	 * @return ReturnValue
	 * @throws Exception
	 */

	public ReturnValue addService(String seqno, String title, String createdate, String createuser, FormFile AttachmentFile)
		throws Exception {
		//Add by Gxk 2004/09/10 Start
		seqno = BaseCommonCheck.convertSql(seqno);
		title = BaseCommonCheck.convertSql(title);
		createdate = BaseCommonCheck.convertSql(createdate);
		createuser = BaseCommonCheck.convertSql(createuser);
		//Add by Gxk 2004/09/10 End
		GBS_ServiceInfoList_Bean retdata = new GBS_ServiceInfoList_Bean();
		StringBuffer sql = 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 seq = 1;
		String Sql;
		int bresult = 0;
		try {

			String selseq = " SELECT MAX(SEQ_NO) AS SEQ  FROM M_SERVICE_INFO";
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(selseq);
			if (rset.next()) {
				seq = rset.getInt("SEQ");
				seq = seq + 1;
			}
			seqno = seq + "";
			sql.append(" INSERT INTO M_SERVICE_INFO ");
			sql.append("(SEQ_NO,TITLE,ATTACHMENT_FILE,CREATE_USER,CREATE_DATE,UPDATE_USER,UPDATE_DATE)");
			sql.append(" VALUES( '");
			sql.append(seq + "','" + title + "',");
			sql.append("null,'");
			sql.append(createuser + "',SYSDATE,'");
			sql.append(createuser + "',SYSDATE)");
			//sql.append("','','')");
			Sql = sql.toString();
			System.out.println("[INFO] sql = " + Sql);
			//st = conn.createStatement();