} // 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