<%@page contentType="text/html; charset=UTF-8" language="java" %>

<%@page import="org.jdom.*" %>
<%@page import="java.sql.*" %>
<%@page import="java.util.*" %>
<%@page import="com.csc.library.database.*" %>
<%@page import="com.csc.library.session.*" %>
<%@page import="com.csc.library.utilities.*" %>

<%!

/**
 * EncryptData ใช้ในการ เข้ารหัส/ถอดรหัส ขอมูลในดาต้าเบส
 * @author James
 *
 */
public class EncryptData {

	private String dblog = null;
	private String mode = null;
	private String table = null;
	private String fieldsStr = null;
	private String dbName = null;
	private String schemaName = null;
	private String condition = null;
	private ArrayList fields;
	private UProfile uprofile = null;
	private Connection con;

	public static final String MODE_ENCRYPT = "1";
	public static final String MODE_DECRYPT = "0";

	public EncryptData() {
		fields = new ArrayList();
		initUProfile();
	}

	private void initUProfile() {
		this.uprofile = new UProfile();
		uprofile.set("employeename", "Guest");
		uprofile.set("fullname", "Guest");
		uprofile.set("lang", "THA");
	}

	private void getConection() {
		try {
			con = (CscConnection) StaticProperties.myConnection.getConnection( this.dbName, uprofile);
		} catch (SQLException e) {
			System.out.println("Can not initial connection.");
			e.printStackTrace();

		}
	}

	public String encode(String str) {
		String result = "";
		Base64Encoder encoder = new Base64Encoder(str);
		try {
			result = encoder.processString();
		} catch (Exception e) {
			System.out.println("Error for decode :" + str);
			e.printStackTrace();
			result = str;
		}
		return result;
	}

	public String decode(String str) {
		String result = "";
		Base64Decoder dcoder = new Base64Decoder(str);
		try {
			result = dcoder.processString();
		} catch (Exception e) {
			System.out.println("Error for decode :" + str);
			e.printStackTrace();
			result = str;
		}
		return result;
	}

public int processDataSet() {
		int returnvalue =0;
		
		String tmpField = null;
		String tmpValue = null;
		String value = null;
		
		System.out.println("Initial...");

		getConection();

		try {

			StringBuilder sql = new StringBuilder();

			ArrayList pkList = getPk(uprofile.getSchemaName(),this.table);
			StringBuilder pk = new StringBuilder();
			for (int i = 0; i < pkList.size(); i++) {
				pk.append((String)pkList.get(i)).append(",");
			}

			sql.append("SELECT ").append(pk.toString());
			sql.append(fieldsStr);
			sql.append(" FROM ").append(this.table);

			if (condition != null && !condition.equals("")) {
				sql.append(" WHERE ").append(condition);
			}

			DataSet ds = new DataSet(sql.toString(), uprofile.getDbName(),uprofile.getSchemaName());
			ds.setFetchAll(false);
			ds.initConnection(this.getUProfile());

			StringBuilder updateSql = new StringBuilder();
			updateSql.append("UPDATE ").append(table).append(" SET ");
			for (int i = 0; i < fields.size(); i++) {
				if (i > 0) {
					updateSql.append(", ");
				}
				updateSql.append((String)fields.get(i)).append("=").append("? ");
			}
			updateSql.append(" WHERE ");
			for (int i = 0; i < pkList.size(); i++) {
				if (i > 0) {
					updateSql.append(" AND ");
				}
				updateSql.append((String)pkList.get(i)).append("=").append("? ");
			}

			PreparedStatement pstmt = con.prepareStatement(updateSql.toString());

			System.out.println();
			System.out.println(updateSql.toString());
			while (ds.next()) {

				int index = 1;
				for (int i = 0; i < this.fields.size(); i++) {
					tmpField = (String)fields.get(i);
					tmpValue = ds.getString(tmpField);

					if (this.mode.equals(MODE_ENCRYPT)) {
						value = encode(tmpValue); 
						//value = encode(tmpValue.replaceAll(",", ""));
					} else {
						value = decode(tmpValue);
					}

					System.out.println(index +" (SET)  " + tmpField + "=" + value);
					pstmt.setString(index, value);
					index++;
				}

				for (int i = 0; i < pkList.size(); i++) {
					tmpField = (String)pkList.get(i);
					value = ds.getString(tmpField);

					System.out.println(index +" (PK)  " + tmpField + "=" + value);
					pstmt.setString(index, value);
					index++;
				}

				pstmt.addBatch();
				System.out.println();

			}
			System.out.println("Begin updating...");
			int [] updateCounts = pstmt.executeBatch();
			
			System.out.println("Success " +updateCounts.length);
			System.out.println("Finish");
			returnvalue = updateCounts.length;

		} catch (Exception e) {
			MyLog.error(this, e);
			returnvalue = -1;
		}
		
		return returnvalue;

	}

	private ArrayList getPk(String schema, String tablename) {
		ArrayList pkList = new ArrayList();
		try {
			DatabaseMetaData db_meta = con.getMetaData();
			String catalog = (con.getCatalog() != null && con.getCatalog()	.length() == 0) ? null : con.getCatalog();
			ResultSet rset = db_meta.getPrimaryKeys(catalog, schema.toUpperCase(), tablename);

			while (rset.next()) {
				pkList.add(rset.getString("COLUMN_NAME").toLowerCase());
			}
			rset.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pkList;
	}

	public void setDbName(String str) {
		try {
			this.dblog = str.substring(0, str.indexOf("-"));
			// this.complog = str.substring(str.indexOf("-") + 1, str.length());
			if (this.dblog.trim().equals("")) {
				this.dblog = "db";
			}
			this.dbName = new InitialEnvironment("GLOBAL").getValue(this.dblog+ "-name");
			this.schemaName = new InitialEnvironment(this.dbName).getValue("schema-name");
			this.uprofile.setDbName(this.dbName);
			this.uprofile.setSchemaName(this.schemaName);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void setMode(String str) {
		if (str.equals("1")) {
			this.mode = MODE_ENCRYPT;
		} else {
			this.mode = MODE_DECRYPT;
		}
	}

	public void setTable(String str) {
		this.table = str;
	}

	public void setFields(String str) {
		this.fieldsStr = str.toLowerCase();
		String[] tmp = str.split(",");
		for (int i = 0; i < tmp.length; i++) {
			if (!tmp[i].trim().equals("")) {
				this.fields.add(tmp[i].toLowerCase());
			}
		}
	}

	public UProfile getUProfile() {
		return this.uprofile;
	}

	public String getDbName() {
		return this.dbName;
	}

	public String getSchemaName() {
		return this.schemaName;
	}

	public String getMode() {
		return this.mode;
	}

	public String getTable() {
		return this.table;
	}

	public String getFields() {
		return this.fieldsStr;
	}

	public void setCondition(String condition) {
		if (condition != null) {
			this.condition = condition;
		}
	}

	public String getCondition() {
		return condition;
	}

}

%>


<%	  
      InitialEnvironment en = new InitialEnvironment("GLOBAL");
	   Element root=en.getRootElement();	
	   List list = root.getChildren("DATABASE");
	   List listchild=null;
	   Iterator it = list.iterator();
	   Iterator it2=null;
	   Element em=null;
	   Element emchild=null;
	   String nodename="";
	   String companyid="";
	   String display="";
	   String dbname="";
	   String option="";
	   
	   if(it.hasNext()){
	      em=(Element)it.next();
		     listchild=em.getChildren();
			 it2=listchild.iterator();
			 while(it2.hasNext()){
			 		emchild=(Element)it2.next();
					nodename=emchild.getName();
					if((String.valueOf(nodename.charAt(0))).equalsIgnoreCase("D") && (String.valueOf(nodename.charAt(1))).equalsIgnoreCase("B") && !nodename.equalsIgnoreCase("DBLIST")){
						companyid=emchild.getAttributeValue("companyid");
						display=emchild.getAttributeValue("display");
						dbname=emchild.getAttributeValue("name");
						if(companyid !=null && display !=null ) {
							option+="<option value=\""+nodename+"-"+companyid+"\">"+display+"</option>";
					    }else {
							option+="<option >"+dbname+"</option>";
						}
					}
			 }
	   }
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Encrypt</title>
</head>

<body>
<% 

	if (!StaticProperties.isload) {
		System.out.println("::::: Start load protable  waiting......");
		LoadStaticProperties load = new LoadStaticProperties();
		load.process();
		StaticProperties.isload = true;
		System.out.println(">>>> Protable IN Load >>> "+ StaticProperties.proTable.keySet());
		System.out.println("::::: Load protable  commited......");
		String resource = new InitialEnvironment("GLOBAL").getValue("config-dir");
		new SystemCode().initValue(resource + "systemcode.xml");
	}

	EncryptData encrypt = new EncryptData();
	if(request.getParameter("__submit")!=null){
		
		
		encrypt.setDbName( request.getParameter("__dbname"));
		encrypt.setMode( request.getParameter("__mode"));
		encrypt.setTable( request.getParameter("__table"));
		encrypt.setFields( request.getParameter("__fields"));
		encrypt.setCondition( request.getParameter("__condition"));
%>
  <table width="780" border="0" align="center" cellpadding="2" cellspacing="0">
    <tr>
      <td width="120">Databese Name</td>
      <td><%=encrypt.getDbName() %></td>
    </tr>
    <tr>
      <td>Schema Name</td>
      <td><%=encrypt.getSchemaName() %></td>
    </tr>
    <tr>
      <td>Table Name</td>
      <td><%=encrypt.getTable() %></td>
    </tr>
    <tr>
      <td>Fields</td>
      <td><%=encrypt.getFields() %></td>
    </tr>
	 <tr>
      <td>Condition</td>
      <td><%=encrypt.getCondition() %></td>
    </tr>
    <tr>
      <td>Mode</td>
      <td><%=(EncryptData.MODE_ENCRYPT==encrypt.getMode())?"ENCRYPT" :"DECRYPT" %></td>
    </tr>
	<tr>
      <td>Success</td>
      <td><%=encrypt.processDataSet() %></td>
    </tr>
</table>
  <br  />
<%
	}
%>
<form id="cscform" name="cscform" method="get" action="?">
<div align="center"></div>
  <table width="780" border="0" align="center" cellpadding="2" cellspacing="0">
    <tr>
      <td width="120">&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><label for="__dbname">Databese Name</label></td>
      <td><select name="__dbname" size="1" id="__dbname"><%=option %></select></td>
    </tr>
    <tr>
      <td colspan="2">&nbsp;</td>
    </tr>
    <tr>
      <td><label for="__table">Table Name</label></td>
      <td>
      	<input name="__table" type="text" id="__table" size="30" maxlength="30" />&nbsp;Ex: memployee
       </td>
    </tr>
    <tr>
      <td><label for="__fields">Field</label></td>
      <td>
      	<input name="__fields" type="text" id="__fields" size="50" maxlength="50" /> &nbsp;Ex: salary,oldsalary
       </td>
    </tr>
	 <tr>
      <td><label for="__condition">Condition</label></td>
      <td>
      	<input name="__condition" type="text" id="__condition" size="50" maxlength="50" /> &nbsp;Ex: bu1='001'
       </td>
    </tr>
    <tr>
      <td><label for="encrypt">Mode</label></td>
      <td>
      	<span><input name="__mode" type="radio" id="encrypt" value="1" checked="checked" /><label for="encrypt">Encrypt</label></span>
      	<span><input type="radio" name="__mode" id="decrypt" value="0" /><label for="decrypt">Decrypt</label></span>      </td>
    </tr>
    <tr>
      <td colspan="2">&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>
      		<input type="submit" name="__submit" id="__submit" value="Submit" />
      		<input type="reset" name="__reset" id="__reset" value="Reset" />      </td>
    </tr>
    <tr>
      <td colspan="2">&nbsp;</td>
    </tr>
  </table>
</form>
</body>
</html>