} // checkTableID /// <summary> /// Check/Initialize DocumentNo/Value Sequences for all Clients /// </summary> /// <param name="ctx">context</param> /// <param name="sp">server process or null</param> private static void CheckClientSequences(Ctx ctx, SvrProcess sp) { Trx trxName = null; if (sp != null) { trxName = sp.Get_Trx(); } // Sequence for DocumentNo/Value MClient[] clients = MClient.GetAll(ctx); for (int i = 0; i < clients.Length; i++) { MClient client = clients[i]; if (!client.IsActive()) { continue; } MSequence.CheckClientSequences(ctx, client.GetAD_Client_ID(), trxName); } // for all clients } // checkClientSequences
} // validate /// <summary> /// Check existence of Table Sequences. /// </summary> /// <param name="ctx">context</param> /// <param name="sp">server process or null</param> private static void CheckTableSequences(Ctx ctx, SvrProcess sp) { Trx trxName = null; if (sp != null) { trxName = sp.Get_Trx(); } String sql = "SELECT TableName " + "FROM AD_Table t " + "WHERE IsActive='Y' AND IsView='N'" + " AND NOT EXISTS (SELECT * FROM AD_Sequence s " + "WHERE UPPER(s.Name)=UPPER(t.TableName) AND s.IsTableID='Y')"; IDataReader idr = null; try { //pstmt = DataBase.prepareStatement(sql, trxName); idr = DataBase.DB.ExecuteReader(sql, null, trxName); while (idr.Read()) { String tableName = Utility.Util.GetValueOfString(idr[0]);// rs.getString(1); if (MSequence.CreateTableSequence(ctx, tableName, trxName)) { if (sp != null) { sp.AddLog(0, null, null, tableName); } else { _log.Fine(tableName); } } else { idr.Close(); throw new Exception("Error creating Table Sequence for " + tableName); } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } // Sync Table Name case //jz replace s with AD_Sequence sql = "UPDATE AD_Sequence " + "SET Name = (SELECT TableName FROM AD_Table t " + "WHERE t.IsView='N' AND UPPER(AD_Sequence.Name)=UPPER(t.TableName)) " + "WHERE AD_Sequence.IsTableID='Y'" + " AND EXISTS (SELECT * FROM AD_Table t " + "WHERE t.IsActive='Y' AND t.IsView='N'" + " AND UPPER(AD_Sequence.Name)=UPPER(t.TableName) AND AD_Sequence.Name<>t.TableName)"; int no = DataBase.DB.ExecuteQuery(sql, null, trxName);// DataBase.executeUpdate(sql, trxName); if (no > 0) { if (sp != null) { sp.AddLog(0, null, null, "SyncName #" + no); } else { _log.Fine("Sync #" + no); } } if (no >= 0) { return; } /** Find Duplicates */ sql = "SELECT TableName, s.Name " + "FROM AD_Table t, AD_Sequence s " + "WHERE t.IsActive='Y' AND t.IsView='N'" + " AND UPPER(s.Name)=UPPER(t.TableName) AND s.Name<>t.TableName"; // try { //pstmt = DataBase.prepareStatement (sql, null); idr = DataBase.DB.ExecuteReader(sql, null, trxName); while (idr.Read()) { String TableName = Utility.Util.GetValueOfString(idr[0]);// rs.getString(1); String SeqName = Utility.Util.GetValueOfString(idr[1]); sp.AddLog(0, null, null, "ERROR: TableName=" + TableName + " - Sequence=" + SeqName); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } } // checkTableSequences
} // checkTableSequences /// <summary> /// Check Table Sequence ID values /// </summary> /// <param name="ctx">context</param> /// <param name="sp">server process or null</param> private static void CheckTableID(Ctx ctx, SvrProcess sp) { if (MSysConfig.IsNativeSequence(false)) { String sql = "SELECT * FROM AD_Sequence " + "WHERE IsTableID='Y' " + "ORDER BY Name"; DataSet ds = null; int counter = 0; Trx trxName = null; if (sp != null) { trxName = sp.Get_Trx(); } try { ds = DataBase.DB.ExecuteDataset(sql, null, trxName); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName); int curVal = DB.GetSQLValue(trxName, "select " + ds.Tables[0].Rows[i]["Name"].ToString() + "_SEQ.currval+1 from DUAL"); seq.SetCurrentNext(curVal); if (seq.Save()) { sp.AddLog(0, null, null, "Sequence Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString()); counter++; } else { _log.Severe("Sequence Not Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString()); } } } catch (Exception ex) { _log.Log(Level.SEVERE, sql, ex); } } else { int IDRangeEnd = DataBase.DB.GetSQLValue(null, "SELECT IDRangeEnd FROM AD_System"); if (IDRangeEnd <= 0) { IDRangeEnd = DataBase.DB.GetSQLValue(null, "SELECT MIN(IDRangeStart)-1 FROM AD_Replication"); } _log.Info("IDRangeEnd = " + IDRangeEnd); // String sql = "SELECT * FROM AD_Sequence " + "WHERE IsTableID='Y' " + "ORDER BY Name"; int counter = 0; //IDataReader idr = null; DataSet ds = null; Trx trxName = null; if (sp != null) { trxName = sp.Get_Trx(); } try { //pstmt = DataBase.prepareStatement(sql, trxName); //idr = DataBase.DB.ExecuteReader(sql, null, trxName); ds = DataBase.DB.ExecuteDataset(sql, null, trxName); //while (idr.Read()) for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName); int old = seq.GetCurrentNext(); int oldSys = seq.GetCurrentNextSys(); if (seq.ValidateTableIDValue()) { if (seq.GetCurrentNext() != old) { String msg = seq.GetName() + " ID " + old + " -> " + seq.GetCurrentNext(); if (sp != null) { sp.AddLog(0, null, null, msg); } else { _log.Fine(msg); } } if (seq.GetCurrentNextSys() != oldSys) { String msg = seq.GetName() + " Sys " + oldSys + " -> " + seq.GetCurrentNextSys(); if (sp != null) { sp.AddLog(0, null, null, msg); } else { _log.Fine(msg); } } if (seq.Save()) { counter++; } else { _log.Severe("Not updated: " + seq); } } // else if (CLogMgt.isLevel(6)) // log.fine("OK - " + tableName); } // idr.Close(); } catch (Exception e) { //if (idr != null) //{ // idr.Close(); //} _log.Log(Level.SEVERE, sql, e); } _log.Fine("#" + counter); } } // checkTableID
} // checkTableSequences /// <summary> /// Check Table Sequence ID values /// </summary> /// <param name="ctx">context</param> /// <param name="sp">server process or null</param> private static void CheckTableID(Ctx ctx, SvrProcess sp) { if (MSysConfig.IsNativeSequence(false)) { String sql = "SELECT * FROM AD_Sequence " + "WHERE IsTableID='Y' " + "ORDER BY Name"; DataSet ds = null; int counter = 0; Trx trxName = null; if (sp != null) { trxName = sp.Get_Trx(); } try { ds = DataBase.DB.ExecuteDataset(sql, null, trxName); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { //Get Max value from Table_ID or AD_Sequence and update. sql = @"Update AD_Sequence set updatedby=" + ctx.GetAD_User_ID() + ", updated=getdate(), CurrentNext=(SELECT max(maximum) FROM ( SELECT max(" + ds.Tables[0].Rows[i]["Name"].ToString() + @"_ID)+1 as maximum from " + ds.Tables[0].Rows[i]["Name"].ToString() + @" Union SELECT currentnext AS maximum FROM AD_Sequence WHERE Name = '" + ds.Tables[0].Rows[i]["Name"].ToString() + "') "; if (DB.IsPostgreSQL()) { sql += " foo "; } sql += ") WHERE Name = '" + ds.Tables[0].Rows[i]["Name"].ToString() + "'"; int curVal = DB.ExecuteQuery(sql, null, trxName); if (curVal > 0) { sp.AddLog(0, null, null, "Sequence Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString()); counter++; } else { _log.Severe("Sequence Not Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString()); } } } catch (Exception ex) { _log.Log(Level.SEVERE, sql, ex); } } else { int IDRangeEnd = DataBase.DB.GetSQLValue(null, "SELECT IDRangeEnd FROM AD_System"); if (IDRangeEnd <= 0) { IDRangeEnd = DataBase.DB.GetSQLValue(null, "SELECT MIN(IDRangeStart)-1 FROM AD_Replication"); } _log.Info("IDRangeEnd = " + IDRangeEnd); // String sql = "SELECT * FROM AD_Sequence " + "WHERE IsTableID='Y' " + "ORDER BY Name"; int counter = 0; //IDataReader idr = null; DataSet ds = null; Trx trxName = null; if (sp != null) { trxName = sp.Get_Trx(); } try { //pstmt = DataBase.prepareStatement(sql, trxName); //idr = DataBase.DB.ExecuteReader(sql, null, trxName); ds = DataBase.DB.ExecuteDataset(sql, null, trxName); //while (idr.Read()) for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName); int old = seq.GetCurrentNext(); int oldSys = seq.GetCurrentNextSys(); if (seq.ValidateTableIDValue()) { if (seq.GetCurrentNext() != old) { String msg = seq.GetName() + " ID " + old + " -> " + seq.GetCurrentNext(); if (sp != null) { sp.AddLog(0, null, null, msg); } else { _log.Fine(msg); } } if (seq.GetCurrentNextSys() != oldSys) { String msg = seq.GetName() + " Sys " + oldSys + " -> " + seq.GetCurrentNextSys(); if (sp != null) { sp.AddLog(0, null, null, msg); } else { _log.Fine(msg); } } if (seq.Save()) { counter++; } else { _log.Severe("Not updated: " + seq); } } // else if (CLogMgt.isLevel(6)) // log.fine("OK - " + tableName); } // idr.Close(); } catch (Exception e) { //if (idr != null) //{ // idr.Close(); //} _log.Log(Level.SEVERE, sql, e); } _log.Fine("#" + counter); } } // checkTableID
} // checkTableSequences /// <summary> /// Check Table Sequence ID values /// </summary> /// <param name="ctx">context</param> /// <param name="sp">server process or null</param> private static void CheckTableID(Ctx ctx, SvrProcess sp) { int IDRangeEnd = DataBase.DB.GetSQLValue(null, "SELECT IDRangeEnd FROM AD_System"); if (IDRangeEnd <= 0) { IDRangeEnd = DataBase.DB.GetSQLValue(null, "SELECT MIN(IDRangeStart)-1 FROM AD_Replication"); } _log.Info("IDRangeEnd = " + IDRangeEnd); // String sql = "SELECT * FROM AD_Sequence " + "WHERE IsTableID='Y' " + "ORDER BY Name"; int counter = 0; IDataReader idr = null; Trx trxName = null; if (sp != null) { trxName = sp.Get_Trx(); } try { //pstmt = DataBase.prepareStatement(sql, trxName); idr = DataBase.DB.ExecuteReader(sql, null, trxName); while (idr.Read()) { MSequence seq = new MSequence(ctx, idr, trxName); int old = seq.GetCurrentNext(); int oldSys = seq.GetCurrentNextSys(); if (seq.ValidateTableIDValue()) { if (seq.GetCurrentNext() != old) { String msg = seq.GetName() + " ID " + old + " -> " + seq.GetCurrentNext(); if (sp != null) { sp.AddLog(0, null, null, msg); } else { _log.Fine(msg); } } if (seq.GetCurrentNextSys() != oldSys) { String msg = seq.GetName() + " Sys " + oldSys + " -> " + seq.GetCurrentNextSys(); if (sp != null) { sp.AddLog(0, null, null, msg); } else { _log.Fine(msg); } } if (seq.Save()) { counter++; } else { _log.Severe("Not updated: " + seq); } } // else if (CLogMgt.isLevel(6)) // log.fine("OK - " + tableName); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } _log.Fine("#" + counter); } // checkTableID