package com.csc.library.autonumber;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.DecimalFormat;

import com.csc.library.database.CscConnection;
import com.csc.library.database.StaticProperties;
import com.csc.library.session.DbRecord;
import com.csc.library.session.InitialEnvironment;
import com.csc.library.utilities.CscCalendar;
import com.csc.library.utilities.MyLog;
import com.csc.library.utilities.UProfile;

/**
 * @author Kim
 * @edit NoVaViVa
 * 
  PROCEDURE FOR SQL
  -------------------------
	DROP PROCEDURE CSC_AUTONUMBER2;
	
	CREATE PROCEDURE CSC_AUTONUMBER2
	   @COMPANYID VARCHAR(5),
	   @CODEID VARCHAR(50),
	   @AUTONUMBER_FORMAT VARCHAR(100),
	   @MYFORMAT VARCHAR(20),
	   @RESET_CH1 VARCHAR(2),
	   @RESET_CH2 VARCHAR(2),
	   @CNUM INT OUTPUT
	AS
	   IF EXISTS (SELECT CODEID FROM LAUTONUMBER WHERE CODEID = @CODEID)
	   BEGIN
		DECLARE @SQL NVARCHAR(500);
		DECLARE @PARAM_DEF NVARCHAR(100);
		DECLARE @RES_C1 VARCHAR(2);
		DECLARE @RES_C2 VARCHAR(3);
		DECLARE @FVALUE VARCHAR(10);
		DECLARE @NEWVALUE VARCHAR(10);
		SET		@SQL = N'SELECT @RES_C1 = RESET_CHAR1,@RES_C2 = RESET_CHAR2,@FVALUE = FORMAT_VALUE FROM LAUTONUMBER WHERE CODEID='''+@CODEID+'''';
		SET		@PARAM_DEF = N'@RES_C1 VARCHAR(2) OUTPUT,@RES_C2 VARCHAR(2) OUTPUT,@FVALUE VARCHAR(10) OUTPUT';
		EXEC	SP_EXECUTESQL @SQL, @PARAM_DEF, @RES_C1 OUTPUT,@RES_C2 OUTPUT,@FVALUE OUTPUT;
			-- ������ա�� set format ������� 
				IF @FVALUE!='' AND @RES_C1!='' AND @RES_C2!=''
					BEGIN
						SET		@NEWVALUE = SUBSTRING(@MYFORMAT,CAST(@RES_C1 AS INT),CAST(@RES_C2 AS INT)+1);
					END;
				ELSE
					BEGIN
						SET		@NEWVALUE = @FVALUE;
					END;
	
				-- ��Ҥ�ҷ���������ç�ѹ���ӡ�� reset �繤������������� 
				IF @NEWVALUE !=@FVALUE 
					BEGIN
						UPDATE LAUTONUMBER SET @CNUM = CNUM  = 1,FORMAT_VALUE = @NEWVALUE,RESET_CHAR1 = @RES_C1,RESET_CHAR2 = @RES_C2 WHERE CODEID = @CODEID;
					END;
				ELSE
					BEGIN
						UPDATE LAUTONUMBER SET @CNUM = CNUM  = CNUM + 1,RESET_CHAR1 = @RES_C1,RESET_CHAR2 = @RES_C2 WHERE CODEID = @CODEID;	
					END;
	   END
	   ELSE
	   BEGIN
	      DECLARE @TABLE_NAME VARCHAR(50);
	      SET @TABLE_NAME = SUBSTRING(@CODEID, 0, CHARINDEX('_RUNNO', @CODEID));
		  SET @NEWVALUE   = SUBSTRING(@MYFORMAT,CAST(@RESET_CH1 AS INT),CAST(@RESET_CH2 AS INT)+1);
	      IF @TABLE_NAME = ''
	      BEGIN
	         SET @CNUM = 1;
	      END
	      ELSE
	      BEGIN
	         SET @SQL = 'SELECT @CNUM = MAX(RUNNO) FROM ' + @TABLE_NAME;
	         SET @PARAM_DEF = N'@TABLE_NAME VARCHAR(50), @CNUM INT OUTPUT';
	         EXEC SP_EXECUTESQL @SQL, @PARAM_DEF, @TABLE_NAME, @CNUM OUTPUT;
	         SET @CNUM = @CNUM + 1;
	      END
	      INSERT INTO LAUTONUMBER (COMPANYID, CODEID, AUTONUMBER_FORMAT, CNUM,FORMAT_VALUE,RESET_CHAR1,RESET_CHAR2) VALUES(@COMPANYID, @CODEID, @AUTONUMBER_FORMAT, @CNUM,@NEWVALUE,@RESET_CH1,@RESET_CH2);
	   END
	   RETURN @CNUM;
	
	-- ����� EXCUTE SQL
	EXEC CSC_AUTONUMBER2 '100','Test','YYYY-000000','2554-000000','0','4',0;
	
	select * from lautonumber where codeid='Test';
 ---------------------------------------------------
 PROCEDURE FOR ORACLE
 
  
 * 
 * 
 */
public class MyAutoNumber implements AbstractAutoNumber {

	private UProfile upf;
	private String autonumberid = "";
	private String format = "";
	private boolean isupdate = false;
//	private transient CscConnection con = null;
	private DbRecord parent = null;
	private int init = 0;
	private String formatValue="";
	private String reset_char1="";
	private String reset_char2="";
	
	public MyAutoNumber() {
	}
	
	public UProfile getUProfile(){
		return this.upf;
	}
	
	public MyAutoNumber(UProfile upf,String autonumCode) {
		this.upf = upf;
		this.autonumberid=autonumCode;
	}
	public MyAutoNumber(UProfile upf,String autonumCode,String format) {
		this.upf = upf;
		this.autonumberid = autonumCode;
		this.format = format;
	}

	public MyAutoNumber(UProfile upf,String autonumCode,String format,DbRecord parent,int initvalue) {
		this.upf = upf;
		this.autonumberid = autonumCode;
		this.format = format;
		this.parent = parent;
		this.init = initvalue;
	}
	
	public MyAutoNumber(UProfile upf,String autonumCode,String format,DbRecord parent,int initvalue,String res1,String res2) {
		this.upf = upf;
		this.autonumberid = autonumCode;
		this.format = format;
		this.parent = parent;
		this.init = initvalue;
		this.reset_char1=res1;
		this.reset_char2=res2;
	}
	
	private CscConnection initConnection() {
		CscConnection con = null;
		try {
			if(this.autonumberid.toLowerCase().equals("csc_quque")){
				InitialEnvironment inv=new InitialEnvironment("GLOBAL");
				String[] db =inv.getValue("DBLIST-name").split(",");
				con = (CscConnection) StaticProperties.myConnection.getConnection(db[0]);
			}else{
				con = (CscConnection) StaticProperties.myConnection.getConnection(upf.getDbName());
			}
			con.setClassName("MyAutonumber.initConnection");
			con.setTransactionIsolation(CscConnection.TRANSACTION_READ_COMMITTED);
		} catch (Exception e) {
			MyLog.error(this, upf, e);
			return null;
		}
		return con;
	}
		
	private void closeConnection(ResultSet rs,Statement stmt,CscConnection con) {
		
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					MyLog.error(this, upf, e);		
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					MyLog.error(this, upf, e);		
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					MyLog.error(this, upf, e);		
				}
			}
		
	}
	
	public UProfile getUprofile() {
		return upf;
	}
	
	public void setUprofile(UProfile upf) {
		this.upf = upf;
	}
	
/**
 * ���
 */
	/*
	public int getNextRecord() {
		int i=0;
		try{
			this.con = this.initConnection();
			this.stmt = this.con.createStatement();
			i = this.getMaxRecord();
			i++;
			String sql="";
			if(isupdate){
				sql = "update lautonumber set cnum=" + i + " where codeid='" +this.autonumberid + "'";
			}else{
				sql = "insert into lautonumber (codeid,companyid,cnum,autonumber_format)values('"+this.autonumberid
						+"','"+this.upf.get("companyid")+"','"+ findingRunno(autonumberid) +"','"+this.format+"')";
			}
			this.log.debug(" *** SQL : " + sql);				
			try {
				this.stmt.executeUpdate(sql);
			} catch (SQLException e) {
				MyLog.error(this, this.upf, e);				
			}
		}catch(Exception ex){
			
		}finally{
			this.closeConnection(null,null,this.con);
		}
		
		return i;
	}
	*/
	
/**
 * ���¡��ҹ procedure	
 */
	public int getNextRecord() {
		int i=0;
		/** get connection **/
		CscConnection con = this.initConnection();
		this.getFormat(con);
		CallableStatement callstmt=null;
		int c=0;
		boolean ok=true;
		for(;ok;){
			try {
				/** call procedure **/
				
				if(this.reset_char1!=null && this.reset_char1.length()>0 && this.reset_char2!=null &&this.reset_char2.length()>0){
					/** old version not check reset pattern **/
					callstmt = con.prepareCall("{CALL CSC_AUTONUMBER2(?,?,?,?,?,?,?)}");
					callstmt.setString(1, this.upf.get("companyid"));
					callstmt.setString(2, this.autonumberid);
					callstmt.setString(3, this.format);
					callstmt.setString(4, this.formatValue);
					callstmt.setString(5, this.reset_char1);
					callstmt.setString(6, this.reset_char2);
					callstmt.registerOutParameter(7, Types.INTEGER);
					callstmt.execute();
					i = callstmt.getInt(7);
				}else{
					/** old version not check reset pattern **/				
					callstmt = con.prepareCall("{CALL CSC_AUTONUMBER(?,?,?,?)}");
					callstmt.setString(1, this.upf.get("companyid"));
					callstmt.setString(2, this.autonumberid);
					callstmt.setString(3, this.format);
					callstmt.registerOutParameter(4, Types.INTEGER);
					callstmt.execute();
					i = callstmt.getInt(4);
				}				
				ok = false;
				con.commit();
			} catch(Exception ex) {
				c++;
				if(c>3){
					ok=false;
					MyLog.error(this, this.upf, ex);				
					break;
				}else{
					continue;
				}
			} finally {
				try {
					if(callstmt!= null) {
						callstmt.close();
					}
				} catch (SQLException e) {
					;
				}
				this.closeConnection(null,null,con);
			}
	  }
		
		return i;
	}	

	public int getMaxRecord() {
		String sql = "select codeid,cnum,autonumber_format from lautonumber where codeid='" + 
			this.autonumberid + "'";
		MyLog.debug(this, getUProfile() ," *** SQL : " + sql);
		ResultSet rs = null;
		int currentid = 0;
		Statement stmt=null;
		/** get connection **/
		CscConnection con = this.initConnection();
		try {
			stmt=con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				isupdate=true;
				currentid = rs.getInt("cnum");
				this.format=(format.trim().length()==0)?rs.getString("autonumber_format"):format;
				if(this.format==null)
					this.format="";
			}
		} catch (SQLException e) {
			MyLog.error(this, this.upf, e);
		} finally {
			this.closeConnection(rs, stmt, con);
		}
		return currentid;
	}
		
	public synchronized String getNumber() {
		return this.process();
	}
	
	private String process() {		
		this.formatValue=this.myFormat(this.getUProfile(),this.format,0);
		int nextid=this.getNextRecord();		
		String caption=this.myFormat(this.getUProfile(),this.format,nextid);
		return caption;		
	}
	
	public void setUprofile(Object uf) {
		this.upf = (UProfile) uf;
	}

	public String myFormat(UProfile up, String sform, int value) {
		String data ="";
		CscCalendar d = new CscCalendar();
		String buff="";
		char c;
		sform = sform.toLowerCase().replaceAll(" ","");
		if (sform.equals("")) return String.valueOf(value);
		
		for (int i=0; i < sform.length();i++) {
			if (sform.charAt(i)=='0') {
				buff += "0";
			}
		}
		DecimalFormat  form = new DecimalFormat(buff);
		buff = form.format(value);
		
		int j = 0;
		
		for (int i=0; i < sform.length();i++) {
			c= sform.charAt(i);
			if (c=='0') {
				data += String.valueOf(buff.charAt(j)) ;
				j++;
			} else {
				data += String.valueOf(c);
			}
		}
		
		if (sform.indexOf("yyyyth")!=-1) {
			data = data.replaceAll("yyyyth",d.getThaiYYYY());
		}
		if (sform.indexOf("yyyy")!=-1) {
			data = data.replaceAll("yyyy", d.getYYYYMMDD().substring(0,4));
		}
		if (sform.indexOf("yyth")!=-1) {
			data = data.replaceAll("yyth",d.getThaiYY());
		}
		if (sform.indexOf("yy")!=-1) {
			data = data.replaceAll("yy",d.getYYMMDD().substring(0,2));
		}
		if (sform.indexOf("mm")!=-1) {
			data = data.replaceAll("mm", d.getMM());
		}
		if (sform.indexOf("dd")!=-1) {
			data = data.replaceAll("dd", d.getDD());
		}
		if (sform.indexOf("$")!=-1) {
			data=swapFormat(data,sform);
		}
		return data;
	}
	
	private String swapFormat(String data,String sform){
		/***
		 * �ٻẺ =YYYY$F{FIELDNAME,2}-000000
		 * $F{FIEDNAME,digit} field in table
		 * $D{DECIMAL VALUE}  fix decimal value
		 * $U{keyname,digit}  keyname in UProfile
		 */
		
		String rp=sform.substring(sform.indexOf("$"),sform.lastIndexOf("}")+1);
		if(sform.indexOf("$")!=-1){
				if(rp.indexOf(",")>-1){
					String fieldName=rp.substring(3,rp.indexOf(","));			
					int maxlen=0;
					try{
						maxlen=Integer.parseInt(rp.substring(rp.indexOf(",")+1,rp.length()-1));
					}catch(Exception ex){
						maxlen=0;
					}
					try {
						if(sform.indexOf("$U")==-1)
							data=data.replace(rp,parent.getString(fieldName).substring(0,maxlen));							
						else
							data=data.replace(rp,upf.get(fieldName).substring(0,maxlen));
					} catch (Exception e) {
						data=sform.replace(rp,"");
					}
				}else{
					String fieldName=rp.substring(3,rp.indexOf("}"));
					try {
						data=data.replace(rp,parent.getString(fieldName));
					} catch (Exception e) {
						data=sform.replace(rp,"");
					}
				}		
		}else{
			String fieldName=rp.substring(3,rp.indexOf("}"));
			data=data.replace(rp,fieldName);			
		}
		return data;
	}
	
	
	private void getFormat(CscConnection con) {
		Statement stmt=null;
		String sql = "select codeid,cnum,autonumber_format from lautonumber where codeid='" +this.autonumberid + "'";
		MyLog.debug(this, getUProfile(), " *** SQL : " + sql);
		ResultSet rs = null;
		int c=0;
		boolean ok=true;
		for(;ok;){
		try {
			stmt=con.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				isupdate=true;
				this.format=(format.trim().length()==0)?rs.getString("autonumber_format"):format;
				if(this.format==null)
					this.format="";
			}
			ok=false;
			con.commit();
		} catch (SQLException e) {
			c++;
			if(c>3){
				ok=false;			
				MyLog.error(this,this.upf,e);
				break;
			}else{
				continue;
			}
			
		}finally{
			this.closeConnection(rs, stmt,null);
		}
	  }
	}	
}