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); } } } }