public int CreateArchLog(int totalDocs, string srcDB, string tarDB, string userID, out bool isSuccess, out string errorMSG)
{
//OracleConnection connection = null;
//OracleCommand oracleCommand = null;
isSuccess = false;
errorMSG = "";
int logID = 0;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from CreateArchLog :DB Connection not established: create aborted");
return(0);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
try
{
using (var connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
string sql = "INSERT INTO PWN_DOC_REG_ARCH_LOG(ID,ARCH_DATE,TOTAL_DOCS,SOURCE_DOC_DB,TARGET_DOC_DB," +
"STARTTIME,USER_ID) VALUES (:ID,:ARCH_DATE,:TOTAL_DOCS,:SOURCE_DOC_DB,:TARGET_DOC_DB,:STARTTIME,:USER_ID)";
string str = null;
using (var oracleCommand = new OracleCommand(null, connection))
{
log.Debug(string.Format("Executing: {0}", sql));
oracleCommand.CommandText = sql;
oracleCommand.Parameters.Add(":ID", OracleDbType.Int32);
oracleCommand.Parameters.Add(":ARCH_DATE", OracleDbType.Date);
oracleCommand.Parameters.Add(":TOTAL_DOCS", OracleDbType.Int32);
oracleCommand.Parameters.Add(":SOURCE_DOC_DB", OracleDbType.Varchar2);
oracleCommand.Parameters.Add(":TARGET_DOC_DB", OracleDbType.Varchar2);
oracleCommand.Parameters.Add(":STARTTIME", OracleDbType.TimeStampTZ);
oracleCommand.Parameters.Add(":USER_ID", OracleDbType.Varchar2);
oracleCommand.Prepare();
logID = ConnDAO.getInstance().getUniqueID(ref str, connection);
oracleCommand.Parameters[0].Value = logID;
oracleCommand.Parameters[1].Value = DateTime.Today;
oracleCommand.Parameters[2].Value = totalDocs;
oracleCommand.Parameters[3].Value = srcDB;
oracleCommand.Parameters[4].Value = tarDB;
oracleCommand.Parameters[5].Value = Utilities.GetTimestampValue(DateTime.Now);
oracleCommand.Parameters[6].Value = userID;
oracleCommand.ExecuteNonQuery();
isSuccess = true;
}
}
}
catch (Exception e)
{
log.Error("CreateArchLog Failed:", e);
errorMSG = "CreateArchLog Failed:" + e.Message;
log.Debug(e.StackTrace.ToString());
isSuccess = false;
}
finally
{
stopwatch.Stop();
log.Info(string.Format("Time Taken for CreateArchLog : {0} Msec", stopwatch.ElapsedMilliseconds));
// oracleCommand = null;
}
return(logID);
}
/*public bool CreateArchLogWithError(PawnDocRegVO vo, int errorCode, string msg, Char errorCodeChar, bool isRecovery,out string oraError)
* {
* bool ret = false;
* string retVal = "";
* oraError = "";
* //OracleDataReader reader = null;
* //OracleConnection connection = null;
* //OracleCommand oracleCommand = null;
* int errorID = 0;
* if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
* {
* log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
* return false;
* }
* var stopwatch = new Stopwatch();
* stopwatch.Start();
* var stopwatch1 = new Stopwatch();
* OracleTransaction myTrans = null;
* long t0 = 0;
* long t1 = 0;
* long t2 = 0;
* long t3 = 0;
* long t4 = 0;
*
* using (var connection = new OracleConnection())
* {
* try
* {
* connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
* stopwatch1.Start();
* if (!getConnectionWithRetry(connection))
* {
* log.Error("AddErrorsToDB Failed: due to connection error");
* string str =
* string.Format("---DB Archive Log Failure Doc ID{0} Storage ID {1} Status {2} Target DB ID{3} Target DB Name{4} Error Code{5} ErrorMSG {6}---",
* vo.DocID, vo.StorageID, errorCodeChar, vo.TargetCouchDBID, vo.TargetCouchDBName, errorCode, msg);
* log.Error(str);
* return false;
* }
* t0 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
* //connection.Open();
* if (errorCode == -1)
* {
* errorID = getErrorCodeSeq(connection);
* }
* else
* {
* errorID = errorCode;
* }
*
*
* myTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
* if (errorCode == -1)
* insertErrorMSG(connection, msg, errorID);
*
* t1 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
*
* //log.Debug("Time for Insert Error " + stopwatch.ElapsedMilliseconds);
* //stopwatch.Restart();
* if (isRecovery)
* updateArchLog(connection, vo, errorID, errorCodeChar);
* else
* insertArchLog(connection, vo, errorID, errorCodeChar);
*
* t2 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
* //log.Debug("Time for insertArchLog " + stopwatch.ElapsedMilliseconds);
* // stopwatch.Restart();
* updatePwnDocReg(connection, vo, errorCodeChar);
* t3 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
* //log.Debug("Time for updatePwnDocReg " + stopwatch.ElapsedMilliseconds);
* myTrans.Commit();
*
* t4 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Stop();
* ret = true;
* }
* catch (Exception e)
* {
* log.Error("AddErrorsToDB Failed:", e);
* oraError = e.Message;
* string str =
* string.Format("---DB Archive Log Failure Doc ID{0} Storage ID {1} Status {2} Target DB ID{3} Target DB Name{4} Error Code{5} ErrorMSG {6}---",
* vo.DocID, vo.StorageID, errorCodeChar, vo.TargetCouchDBID, vo.TargetCouchDBName, errorCode, msg);
* log.Error(str);
* log.Debug(e.StackTrace.ToString());
* if (myTrans != null && connection.State != ConnectionState.Closed)
* {
* try
* {
* myTrans.Rollback();
* }
* catch (OracleException oracleException)
* {
* log.Error("Oracle Exception" + oracleException.Message);
* return false;
* }
*
* }
* ret = false;
* }
* finally
* {
* stopwatch.Stop();
* log.Info(string.Format("Time Taken for AddErrorsToDB : {0} Msec", stopwatch.ElapsedMilliseconds));
* log.Info(string.Format(" conn {0} error insert {1} arch log insert {2} update reg{3} final commit {4}", t0, t1, t2, t3, t4));
* //oracleCommand = null;
* }
* }
*
* return ret;
* }*/
public bool CreateBatchArchLogWithError(List <ArchiveJob3> jobList, bool isRecovery)
{
bool ret = false;
string retVal = "";
//OracleDataReader reader = null;
//OracleConnection connection = null;
//OracleCommand oracleCommand = null;
int errorID = 0;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
OracleTransaction myTrans = null;
using (var connection = new OracleConnection())
{
try
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
foreach (var archiveJob3 in jobList)
{
if (archiveJob3.getErrorCode() == -1)
{
errorID = getErrorCodeSeq(connection);
}
else
{
errorID = archiveJob3.getErrorCode();
}
int uqID = 0;
if (!isRecovery)
{
uqID = ConnDAO.getInstance().getUniqueIDNew(ref retVal, connection);
//insertArchLogNew
}
myTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
if (archiveJob3.getErrorCode() == -1)
{
insertErrorMSG(connection, archiveJob3.getErrorMessageForDB(), errorID);
}
if (isRecovery)
{
updateArchLog(connection, archiveJob3.getDocObject(), errorID, archiveJob3.getErrorCodeChar());
}
else
{
insertArchLog(connection, archiveJob3.getDocObject(), errorID, archiveJob3.getErrorCodeChar(), uqID);
}
//log.Debug("Time for insertArchLog " + stopwatch.ElapsedMilliseconds);
// stopwatch.Restart();
updatePwnDocReg(connection, archiveJob3.getDocObject(), archiveJob3.getErrorCodeChar());
//log.Debug("Time for updatePwnDocReg " + stopwatch.ElapsedMilliseconds);
myTrans.Commit();
ret = true;
}
}
catch (Exception e)
{
log.Error("AddErrorsToDB Failed:", e);
log.Debug(e.StackTrace.ToString());
if (myTrans != null && connection.State != ConnectionState.Closed)
{
try
{
myTrans.Rollback();
}
catch (OracleException oracleException)
{
log.Error("Oracle Exception" + oracleException.Message);
return(false);
}
}
ret = false;
throw e;
}
finally
{
stopwatch.Stop();
log.Debug(string.Format("Time Taken for AddErrorsToDB : {0} Msec", stopwatch.ElapsedMilliseconds));
//oracleCommand = null;
}
}
return(ret);
}
public bool docListGet(ref DataTable table, int recCount)
{
bool ret = false;
string retVal = "";
OracleDataReader reader = null;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from docListGet :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
try
{
using (connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
string sql = "SELECT ROWNUM,ID,STORAGE_DATE,TICKET_NUMBER,STORENUMBER,CREATIONDATE FROM CCSOWNER.PAWNDOCUMENTREGISTRY REG" +
" WHERE REG.STORAGE_DATE <= TRUNC(SYSDATE) - 90 AND ARCH_STATUS IS NULL AND ROWNUM<=" + recCount + " ORDER BY ID";
using (oracleCommand = new OracleCommand(sql, connection))
{
log.Debug(string.Format("Executing: {0}", sql));
// oracleCommand.FetchSize = 100;
reader = oracleCommand.ExecuteReader();
log.Debug("Execution Completed..........");
if (reader.RowSize > 0 && reader.HasRows)
{
//Create data table with same table name as the one being queried
table = new DataTable("PAWNDOCUMENTREGISTRY");
log.Debug("Loading data......docListGet");
//Load the data table with the reader data
table.Load(reader, LoadOption.OverwriteChanges);
log.Debug("Loading Complete......docListGet");
ret = true;
}
}
}
}
catch (Exception e)
{
//msg = e.Message;
log.Error("docListGet Failed:", e);
log.Debug(e.StackTrace.ToString());
}
finally
{
stopwatch.Stop();
log.Debug(string.Format("Time Taken for docListGet : {0} Msec", stopwatch.Elapsed));
if (reader != null)
{
reader.Dispose();
reader.Close();
}
oracleCommand = null;
}
return(ret);
}
public bool CreateArchLog(PawnDocRegVO vo, char succCodeChar, bool isRecovery)
{
bool ret = false;
string retVal = "";
//OracleDataReader reader = null;
//OracleConnection connection = null;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
return(false);
}
int uqID = 0;
var stopwatch = new Stopwatch();
stopwatch.Start();
OracleTransaction myTrans = null;
using (var connection = new OracleConnection())
{
try
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
if (!isRecovery)
{
uqID = ConnDAO.getInstance().getUniqueIDNew(ref retVal, connection);
//insertArchLogNew
}
myTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
if (isRecovery)
{
updateArchLog(connection, vo, succCodeChar);
}
else
{
insertArchLogNew(connection, vo, succCodeChar, uqID);
}
//log.Debug("Time for insertArchLog " + stopwatch.ElapsedMilliseconds);
//stopwatch.Restart();
updatePwnDocReg(connection, vo, succCodeChar);
//log.Debug("Time for updatePwnDocReg " + stopwatch.ElapsedMilliseconds);
myTrans.Commit();
ret = true;
}
catch (Exception e)
{
log.Error("CreateArchLog Failed:", e);
string str =
string.Format("---DB Archive Log Failure Doc ID{0} Storage ID {1} Status {2} Target DB ID{3} Target DB Name{4} ---",
vo.DocID, vo.StorageID, succCodeChar, vo.TargetCouchDBID, vo.TargetCouchDBName);
log.Error(str);
log.Debug(e.StackTrace.ToString());
if (myTrans != null && connection.State != ConnectionState.Closed)
{
try
{
myTrans.Rollback();
}
catch (OracleException oracleException)
{
log.Error("Oracle Exception" + oracleException.Message);
return(false);
}
}
ret = false;
}
finally
{
stopwatch.Stop();
log.Info(string.Format("Time Taken for Success CreateArchLog : {0} Msec", stopwatch.ElapsedMilliseconds));
//oracleCommand = null;
}
return(ret);
}
}
public bool CreateArchLogWithError(PawnDocRegVO vo, int errorCode, string msg, Char errorCodeChar, bool isRecovery)
{
bool ret = false;
string retVal = "";
//OracleDataReader reader = null;
//OracleConnection connection = null;
//OracleCommand oracleCommand = null;
int errorID = 0;
int uqID = 0;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
//var stopwatch1 = new Stopwatch();
OracleTransaction myTrans = null;
/* long t0 = 0;
* long t1 = 0;
* long t2 = 0;
* long t3 = 0;
* long t4 = 0;*/
using (var connection = new OracleConnection())
{
try
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
// stopwatch1.Start();
connection.Open();
/*if(!getConnectionWithRetry(connection))
* {
* log.Error("AddErrorsToDB Failed: due to connection error");
* string str =
* string.Format("---DB Archive Log Failure Doc ID{0} Storage ID {1} Status {2} Target DB ID{3} Target DB Name{4} Error Code{5} ErrorMSG {6}---",
* vo.DocID, vo.StorageID, errorCodeChar, vo.TargetCouchDBID, vo.TargetCouchDBName, errorCode, msg);
* log.Error(str);
* return false;
* }*/
if (!isRecovery)
{
uqID = ConnDAO.getInstance().getUniqueIDNew(ref retVal, connection);
//insertArchLogNew
}
// t0 = stopwatch1.ElapsedMilliseconds;
// stopwatch1.Restart();
//connection.Open();
if (errorCode == -1)
{
errorID = getErrorCodeSeq(connection);
}
else
{
errorID = errorCode;
}
myTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
if (errorCode == -1)
{
insertErrorMSG(connection, msg, errorID);
}
// t1 = stopwatch1.ElapsedMilliseconds;
// stopwatch1.Restart();
//log.Debug("Time for Insert Error " + stopwatch.ElapsedMilliseconds);
//stopwatch.Restart();
if (isRecovery)
{
updateArchLog(connection, vo, errorID, errorCodeChar);
}
else
{
insertArchLog(connection, vo, errorID, errorCodeChar, uqID);
}
// t2 = stopwatch1.ElapsedMilliseconds;
// stopwatch1.Restart();
//log.Debug("Time for insertArchLog " + stopwatch.ElapsedMilliseconds);
// stopwatch.Restart();
updatePwnDocReg(connection, vo, errorCodeChar);
// t3 = stopwatch1.ElapsedMilliseconds;
// stopwatch1.Restart();
//log.Debug("Time for updatePwnDocReg " + stopwatch.ElapsedMilliseconds);
myTrans.Commit();
// t4 = stopwatch1.ElapsedMilliseconds;
// stopwatch1.Stop();
ret = true;
}
catch (Exception e)
{
log.Error("AddErrorsToDB Failed:", e);
string str =
string.Format("---DB Archive Log Failure Doc ID{0} Storage ID {1} Status {2} Target DB ID{3} Target DB Name{4} Error Code{5} ErrorMSG {6}---",
vo.DocID, vo.StorageID, errorCodeChar, vo.TargetCouchDBID, vo.TargetCouchDBName, errorCode, msg);
log.Error(str);
log.Debug(e.StackTrace.ToString());
if (myTrans != null && connection.State != ConnectionState.Closed)
{
try
{
myTrans.Rollback();
}
catch (OracleException oracleException)
{
log.Error("Oracle Exception" + oracleException.Message);
return(false);
}
}
ret = false;
}
finally
{
stopwatch.Stop();
log.Info(string.Format("Time Taken for Error ArchLog : {0} Msec", stopwatch.ElapsedMilliseconds));
//log.Info(string.Format(" conn {0} error insert {1} arch log insert {2} update reg{3} final commit {4}", t0, t1, t2, t3, t4));
//oracleCommand = null;
}
}
return(ret);
}
public bool AddCouchDbToRepos(List <CouchConsoleApp.vo.PwnDocArchDBRepVO> dbList, out int dbid)
{
bool ret = false;
string retVal = "";
dbid = 0;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
try
{
using (var connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
string sql = "INSERT INTO PWN_DOC_ARCH_DB_REP(ID,DBNAME,DBINFO,CREATIONDATE) VALUES " +
"(:ID,:DBNAME,:DBINFO,:CREATIONDATE)";
string str = null;
using (var oracleCommand = new OracleCommand(null, connection))
{
log.Debug(string.Format("Executing: {0}", sql));
oracleCommand.CommandText = sql;
oracleCommand.Parameters.Add(":ID", OracleDbType.Int32);
oracleCommand.Parameters.Add(":DBNAME", OracleDbType.Varchar2);
oracleCommand.Parameters.Add(":DBINFO", OracleDbType.Varchar2);
oracleCommand.Parameters.Add(":CREATIONDATE", OracleDbType.TimeStampTZ);
oracleCommand.Prepare(); // Calling Prepare after having set the Commandtext and parameters.
//oracleCommand.ExecuteNonQuery();
foreach (var vo in dbList)
{
dbid = ConnDAO.getInstance().getUniqueID(ref str, connection);
oracleCommand.Parameters[0].Value = dbid;
oracleCommand.Parameters[1].Value = vo.DBName;
oracleCommand.Parameters[2].Value = vo.DBInfo;
oracleCommand.Parameters[3].Value = Utilities.GetTimestampValue(DateTime.Now);
oracleCommand.ExecuteNonQuery();
}
ret = true;
}
}
}
catch (Exception e)
{
log.Error("AddCouchDbToRepos Failed:", e);
log.Debug(e.StackTrace.ToString());
ret = false;
}
finally
{
stopwatch.Stop();
log.Debug(string.Format("Time Taken for AddCouchDbToRepos : {0} Msec", stopwatch.ElapsedMilliseconds));
//oracleCommand = null;
}
return(ret);
}
/* public bool CreateArchLog(PawnDocRegVO vo, char succCodeChar, bool isRecovery,out string errorMessage)
* {
* bool ret = false;
* string retVal = "";
* errorMessage = "";
* //OracleDataReader reader = null;
* //OracleConnection connection = null;
* if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
* {
* log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
* return false;
* }
* int uqID = 0;
* var stopwatch = new Stopwatch();
* stopwatch.Start();
*
* var stopwatch1 = new Stopwatch();
*
* long t0 = 0;
* long t1 = 0;
* long t2 = 0;
* long t3 = 0;
* long t4 = 0;
*
* OracleTransaction myTrans = null;
*
* using (var connection = new OracleConnection())
* {
* try
* {
* connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
*
* connection.Open();
* t0 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
*
* if (!isRecovery)
* {
* uqID = ConnDAO.getInstance().getUniqueIDNew(ref retVal, connection);
* //insertArchLogNew
* }
* t1 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
* myTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
* if (isRecovery)
* {
* updateArchLog(connection, vo, succCodeChar);
* }
* else
* {
* insertArchLogNew(connection, vo, succCodeChar, uqID);
* }
* t2 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
*
* //log.Debug("Time for insertArchLog " + stopwatch.ElapsedMilliseconds);
* //stopwatch.Restart();
* updatePwnDocReg(connection, vo, succCodeChar);
* t3 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
* //log.Debug("Time for updatePwnDocReg " + stopwatch.ElapsedMilliseconds);
* myTrans.Commit();
* t4 = stopwatch1.ElapsedMilliseconds;
* stopwatch1.Restart();
* ret = true;
*
* }
* catch (Exception e)
* {
* errorMessage = e.Message;
* log.Error("CreateArchLog Failed:", e);
* string str =
* string.Format("---DB Archive Log Failure Doc ID{0} Storage ID {1} Status {2} Target DB ID{3} Target DB Name{4} ---",
* vo.DocID, vo.StorageID, succCodeChar, vo.TargetCouchDBID, vo.TargetCouchDBName);
* log.Error(str);
* log.Debug(e.StackTrace.ToString());
* if (myTrans != null && connection.State != ConnectionState.Closed)
* {
* try
* {
* myTrans.Rollback();
* }
* catch (OracleException oracleException)
* {
* log.Error("Oracle Exception" + oracleException.Message);
* return false;
* }
* }
* ret = false;
* }
* finally
* {
* stopwatch.Stop();
* log.Debug(string.Format("Time Taken for CreateArchLog : {0} Msec", stopwatch.ElapsedMilliseconds));
* //oracleCommand = null;
* log.Info(string.Format(" conn {0} success UQ ID {1} arch log insert {2} update reg{3} final commit {4}", t0, t1, t2, t3, t4));
* }
* return ret;
* }
* }*/
public bool CreateArchLogForBatch(List <PawnDocRegVO> voList, char succCodeChar, bool isRecovery)
{
bool ret = false;
string retVal = "";
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
return(false);
}
int uqID = 0;
var stopwatch = new Stopwatch();
stopwatch.Start();
OracleTransaction myTrans = null;
using (var connection = new OracleConnection())
{
try
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
foreach (var pawnDocRegVO in voList)
{
if (!isRecovery)
{
uqID = ConnDAO.getInstance().getUniqueIDNew(ref retVal, connection);
//insertArchLogNew
}
myTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
if (isRecovery)
{
updateArchLog(connection, pawnDocRegVO, succCodeChar);
}
else
{
insertArchLogNew(connection, pawnDocRegVO, succCodeChar, uqID);
}
updatePwnDocReg(connection, pawnDocRegVO, succCodeChar);
myTrans.Commit();
}
ret = true;
}
catch (Exception e)
{
log.Error("CreateArchLog Failed:", e);
log.Debug(e.StackTrace.ToString());
if (myTrans != null && connection.State != ConnectionState.Closed)
{
try
{
myTrans.Rollback();
}
catch (OracleException oracleException)
{
log.Error("Oracle Exception" + oracleException.Message);
return(false);
}
}
ret = false;
throw e;
}
finally
{
stopwatch.Stop();
log.Debug(string.Format("Time Taken for CreateArchLog : {0} Msec", stopwatch.ElapsedMilliseconds));
//oracleCommand = null;
}
return(ret);
}
}
public bool GetTempGetDocsIDs(ref List <PawnDocRegVO> docList, int fetchCount, bool isFirst, int lastIndex)
{
bool ret = true;
string retVal = "";
OracleDataReader reader = null;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from docListGet :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
try
{
using (var connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
string sql = "";
if (isFirst)
{
sql = "SELECT REG.ID,REG.STORAGE_ID,CREATIONDATE FROM CCSOWNER.PAWNDOCUMENTREGISTRY REG" +
" WHERE REG.CREATIONDATE <= TRUNC(SYSDATE) - 90 AND REG.ARCH_STATUS IS NULL" + " AND ROWNUM<=" +
fetchCount + " ORDER BY ID";
}
else
{
sql = "SELECT REG.ID,REG.STORAGE_ID,CREATIONDATE FROM CCSOWNER.PAWNDOCUMENTREGISTRY REG" +
" WHERE REG.CREATIONDATE <= TRUNC(SYSDATE) - 90 AND REG.ARCH_STATUS IS NULL AND REG.ID > " + lastIndex +
" AND ROWNUM <=" + fetchCount + " ORDER BY REG.ID";
}
using (var oracleCommand = new OracleCommand(sql, connection))
{
log.Info(string.Format("Executing: {0}", sql));
reader = oracleCommand.ExecuteReader();
if (reader.RowSize > 0 && reader.HasRows)
{
docList = new List <PawnDocRegVO>();
CouchConsoleApp.vo.PawnDocRegVO vo = null;
while (reader.Read())
{
vo = new CouchConsoleApp.vo.PawnDocRegVO();
vo.DocID = Utilities.GetIntegerValue(reader["ID"]);
vo.StorageID = Utilities.GetStringValue(reader["STORAGE_ID"]);
vo.CreationDate = Utilities.GetDateTimeValue(reader["CREATIONDATE"]);
docList.Add(vo);
}
ret = false;
}
}
}
}
catch (Exception e)
{
log.Error("Recovery document get failed:", e);
log.Error(e.StackTrace);
//log.Debug(e.StackTrace.ToString());
}
finally
{
stopwatch.Stop();
log.Info(string.Format("Time Taken for Recovery doc get : {0} Msec", stopwatch.Elapsed));
if (reader != null)
{
reader.Dispose();
reader.Close();
}
//oracleCommand = null;
}
return(ret);
}
public bool GetDocumentSetsForCount(int startWith, out int pendCount, out int errorCode, out string errorMsg)
{
//OracleDataReader reader = null;
pendCount = 0;
errorCode = 0;
errorMsg = "";
bool ret = false;
List <PawnDocRegVO> voList = null;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from docListGet :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
DataTable tbl = new DataTable("ARCHIVEDOCUMENT");
try
{
using (var connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
using (var oracleCommand = new OracleCommand(null, connection))
{
oracleCommand.CommandText = "ccsowner.PAWN_GENERATE_DOCUMENTS.getDocumentsToArchive";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("p_current_date", OracleDbType.Varchar2, DateTime.Now.ToShortDateString(), ParameterDirection.Input);
oracleCommand.Parameters.Add("p_time_span", OracleDbType.Int32, 90, ParameterDirection.Input);
oracleCommand.Parameters.Add("p_record_start", OracleDbType.Int32, startWith, ParameterDirection.Input);
oracleCommand.Parameters.Add("o_total_documents", OracleDbType.Int32, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_documents", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_return_code", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_return_text", OracleDbType.Varchar2, 32768, DBNull.Value, ParameterDirection.Output);
log.Info("Executing..:" + oracleCommand.CommandText);
log.Info("Input " + startWith);
oracleCommand.ExecuteNonQuery();
pendCount = Utilities.GetIntegerValue(oracleCommand.Parameters[3].Value);
errorCode = Utilities.GetIntegerValue(oracleCommand.Parameters[5].Value);
errorMsg = Utilities.GetStringValue(oracleCommand.Parameters[6].Value);
log.Info("Total Documents " + pendCount);
log.Info("o_return_code " + errorCode);
log.Info("o_return_text " + errorMsg);
if (errorCode != 0)
{
return(false);
}
}
}
}
catch (Exception e)
{
log.Error("GetDocumentSets", e);
errorMsg = e.Message;
ret = false;
}
finally
{
stopwatch.Stop();
/*if (reader != null)
* {
* reader.Dispose();
* reader.Close();
* }*/
log.Info(string.Format("Time Taken for GetDocumentSetsForCount : {0} Msec", stopwatch.ElapsedMilliseconds));
//oracleCommand = null;
}
log.Debug("Data Returned.." + tbl);
return(ret);
}
public bool GetDocumentSets(int startWith, out List <PawnDocRegVO> docList, out int totalCount, out int errorCode, out string errorMSG)
{
//OracleDataReader reader = null;
totalCount = 0;
errorCode = 0;
errorMSG = "";
bool ret = false;
docList = new List <PawnDocRegVO>();
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from docListGet :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
DataTable tbl = new DataTable("ARCHIVEDOCUMENT");
try
{
using (var connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
using (var oracleCommand = new OracleCommand(null, connection))
{
oracleCommand.CommandText = "ccsowner.PAWN_GENERATE_DOCUMENTS.getDocumentsToArchive";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("p_current_date", OracleDbType.Varchar2, DateTime.Now.ToShortDateString(), ParameterDirection.Input);
oracleCommand.Parameters.Add("p_time_span", OracleDbType.Int32, 90, ParameterDirection.Input);
oracleCommand.Parameters.Add("p_record_start", OracleDbType.Int32, startWith, ParameterDirection.Input);
oracleCommand.Parameters.Add("o_total_documents", OracleDbType.Int32, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_documents", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_return_code", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_return_text", OracleDbType.Varchar2, 32768, DBNull.Value, ParameterDirection.Output);
log.Info("Executing..:" + oracleCommand.CommandText);
log.Info("Input " + startWith);
oracleCommand.ExecuteNonQuery();
totalCount = Utilities.GetIntegerValue(oracleCommand.Parameters[3].Value);
errorCode = Utilities.GetIntegerValue(oracleCommand.Parameters[5].Value);
errorMSG = Utilities.GetStringValue(oracleCommand.Parameters[6].Value);
log.Info("Total Documents " + totalCount);
log.Info("o_return_code " + errorCode);
log.Info("o_return_text " + errorMSG);
if (errorCode != 0)
{
return(false);
}
var oraRefCursor = (OracleRefCursor)oracleCommand.Parameters[4].Value;
if (oraRefCursor != null && oraRefCursor.IsNull == false)
{
using (var reader = oraRefCursor.GetDataReader())
{
if (reader.RowSize > 0 && reader.HasRows)
{
CouchConsoleApp.vo.PawnDocRegVO vo = null;
while (reader.Read())
{
vo = new CouchConsoleApp.vo.PawnDocRegVO();
vo.DocID = Utilities.GetIntegerValue(reader["ID"]);
vo.StorageID = Utilities.GetStringValue(reader["STORAGE_ID"]);
vo.CreationDate = Utilities.GetDateTimeValue(reader["STORAGE_DATE"]);
docList.Add(vo);
}
ret = true;
}
}
}
oraRefCursor.Dispose();
}
log.Info("Count from Proc " + docList.Count);
}
}
catch (Exception e)
{
log.Error("GetDocumentSets", e);
errorMSG = e.Message;
ret = false;
}
finally
{
stopwatch.Stop();
/*if (reader != null)
* {
* reader.Dispose();
* reader.Close();
* }*/
log.Info(string.Format("Time Taken for GetDocumentSets : {0} Msec", stopwatch.ElapsedMilliseconds));
//oracleCommand = null;
}
log.Debug("Data Returned.." + tbl);
return(ret);
}
public bool docSourceDocsListByDate(ref List <SourceDocTreeVO> voList, ref List <SourceDocTreeVO> targetvoList,
out string errorMsg)
{
bool ret = false;
string retVal = "";
// OracleDataReader reader = null;
errorMsg = "";
voList = new List <SourceDocTreeVO>();
targetvoList = new List <SourceDocTreeVO>();
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error("DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
SourceDocTreeVO vo = null;
try
{
using (var connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
string countSql = "SELECT TRUNC(STORAGE_DATE) dt,COUNT(*) CNT FROM PAWNDOCUMENTREGISTRY REG,PWN_DOC_REG_ARCH_STAT STAT" +
" WHERE STAT.DOC_REG_ID=REG.ID AND ARCH_STATUS IS NOT NULL GROUP BY STORAGE_DATE ORDER BY STORAGE_DATE";
using (var oracleCommand = new OracleCommand(countSql, connection))
{
log.Debug(string.Format("Executing: {0}", countSql));
using (var reader = oracleCommand.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
vo = new SourceDocTreeVO
{
date = (DateTime)reader["dt"],
count = Utilities.GetIntegerValue(reader["CNT"])
};
targetvoList.Add(vo);
}
}
}
}
// log.Debug("Time to get count(*)" + stopwatch.Elapsed + ": Count: " + totalRecords);
string sql = "SELECT TRUNC(STORAGE_DATE) dt,COUNT(*) CNT FROM PAWNDOCUMENTREGISTRY REG WHERE" +
" REG.STORAGE_DATE <= TRUNC(SYSDATE) - 90 AND ARCH_STATUS IS NULL" +
" GROUP BY STORAGE_DATE ORDER BY STORAGE_DATE";
using (var oracleCommand = new OracleCommand(sql, connection))
{
log.Debug(string.Format("Executing: {0}", sql));
// oracleCommand.FetchSize = 100;
using (var reader = oracleCommand.ExecuteReader())
{
log.Debug("Execution Completed..........");
if (reader.HasRows)
{
while (reader.Read())
{
vo = new SourceDocTreeVO
{
date = (DateTime)reader["dt"],
count = Utilities.GetIntegerValue(reader["CNT"])
};
voList.Add(vo);
}
}
}
}
ret = true;
}
}
catch (Exception e)
{
//msg = e.Message;
log.Error("docSourceDocsListByDate Failed:", e);
log.Debug(e.Message);
errorMsg = e.Message;
//log.Debug(e.StackTrace.ToString());
}
finally
{
stopwatch.Stop();
log.Debug(string.Format("Time Taken for docSourceDocsListByDate for tree : {0} Msec", stopwatch.Elapsed));
/*if (reader != null)
* {
* reader.Dispose();
* reader.Close();
* }
* oracleCommand = null;*/
}
return(ret);
}
public DataTable GetDocuments(int startWith, out int totalCount, out int errorCode, out string errorMSG)
{
OracleDataReader reader = null;
totalCount = 0;
errorCode = 0;
errorMSG = "";
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from GetDocuments :DB Connection not established: Search aborted");
return(null);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
DataTable tbl = new DataTable("ARCHIVEDOCUMENT");
try
{
using (connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
using (oracleCommand = new OracleCommand(null, connection))
{
oracleCommand.CommandText = "ccsowner.PAWN_GENERATE_DOCUMENTS.getDocumentsToArchive";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("p_current_date", OracleDbType.Varchar2, DateTime.Now.ToShortDateString(), ParameterDirection.Input);
oracleCommand.Parameters.Add("p_time_span", OracleDbType.Int32, 90, ParameterDirection.Input);
oracleCommand.Parameters.Add("p_record_start", OracleDbType.Int32, startWith, ParameterDirection.Input);
oracleCommand.Parameters.Add("o_total_documents", OracleDbType.Int32, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_documents", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_return_code", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output);
oracleCommand.Parameters.Add("o_return_text", OracleDbType.Varchar2, 32768, DBNull.Value, ParameterDirection.Output);
log.Debug("Executing..:" + oracleCommand.CommandText);
oracleCommand.ExecuteNonQuery();
log.Debug("Input " + startWith);
totalCount = Utilities.GetIntegerValue(oracleCommand.Parameters[3].Value);
errorCode = Utilities.GetIntegerValue(oracleCommand.Parameters[5].Value);
errorMSG = Utilities.GetStringValue(oracleCommand.Parameters[6].Value);
log.Debug("Total Documents " + totalCount);
log.Debug("o_return_code " + errorCode);
log.Debug("o_return_text " + errorMSG);
var oraRefCursor = (OracleRefCursor)oracleCommand.Parameters[4].Value;
if (oraRefCursor != null && oraRefCursor.IsNull == false)
{
reader = oraRefCursor.GetDataReader();
if (reader.HasRows)
{
reader.FetchSize = oraRefCursor.RowSize * 100;
tbl.Load(reader);
}
}
log.Debug("Count of Results :" + tbl.Rows.Count);
}
}
}
catch (Exception e)
{
log.Error("GetDocuments", e);
}finally
{
stopwatch.Stop();
if (reader != null)
{
reader.Dispose();
reader.Close();
}
log.Info(string.Format("Time Taken for Get Doc Count SP : {0} Msec", stopwatch.ElapsedMilliseconds));
oracleCommand = null;
if (oracleCommand != null)
{
oracleCommand.Cancel();
oracleCommand = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
log.Debug("Data Returned.." + tbl);
return(tbl);
}
/*public void killCommand()
* {
*
* if (reader != null && !reader.IsClosed)
* {
* reader.Close();
* }
*
* if (this.oracleCommand != null)
* {
* this.oracleCommand.Cancel();
* }
*
* if (connection!=null &&connection.State==ConnectionState.Open)
* {
* connection.Close();
* }
*
* }*/
public bool AddErrorsToDB(Dictionary <string, int> errorDict)
{
bool ret = false;
string retVal = "";
// OracleDataReader reader = null;
OracleTransaction myTrans = null;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
try
{
using (var connection = new OracleConnection())
{
connection.ConnectionString = DBConnector.getInstance().databaseServiceName();
connection.Open();
myTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
string del_sql = "DELETE FROM CCSOWNER.PWN_DOC_REG_ARCH_ERROR WHERE ID<" + ":IDVAL";
using (var oracleCommand = new OracleCommand(null, connection))
{
log.Debug(string.Format("Executing: {0}", del_sql));
oracleCommand.CommandText = del_sql;
oracleCommand.Parameters.Add(":IDVAL", OracleDbType.Int32);
oracleCommand.Prepare();
oracleCommand.Parameters[0].Value = 10;
oracleCommand.ExecuteNonQuery();
}
string sql = "INSERT INTO CCSOWNER.PWN_DOC_REG_ARCH_ERROR(ID,ERROR_MSG) VALUES " +
"(:ID,:ERROR_MSG)";
string str = null;
using (var oracleCommand = new OracleCommand(null, connection))
{
log.Debug(string.Format("Executing: {0}", sql));
oracleCommand.CommandText = sql;
oracleCommand.Parameters.Add(":ID", OracleDbType.Int32);
oracleCommand.Parameters.Add(":ERROR_MSG", OracleDbType.Varchar2);
oracleCommand.Prepare();
foreach (var i in errorDict)
{
oracleCommand.Parameters[0].Value = i.Value;
oracleCommand.Parameters[1].Value = i.Key;
oracleCommand.ExecuteNonQuery();
}
ret = true;
}
myTrans.Commit();
}
}
catch (Exception e)
{
log.Error("AddErrorsToDB Failed:", e);
log.Debug(e.StackTrace.ToString());
ret = false;
if (myTrans != null) //&& connection.State != ConnectionState.Closed)
{
try
{
myTrans.Rollback();
}
catch (OracleException oracleException)
{
log.Error("Oracle Exception" + oracleException.Message);
return(false);
}
}
}
finally
{
stopwatch.Stop();
log.Debug(string.Format("Time Taken for AddErrorsToDB : {0} Msec", stopwatch.ElapsedMilliseconds));
//oracleCommand = null;
}
return(ret);
}
public bool AddErrorToDB(string msg, out int idVal)
{
bool ret = false;
string retVal = "";
//OracleDataReader reader = null;
idVal = -1;
if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED)
{
log.Error(" from GetDocumentSets :DB Connection not established: Search aborted");
return(false);
}
var stopwatch = new Stopwatch();
stopwatch.Start();
try
{
using (var connection = new OracleConnection())
{
string seqSql = "SELECT CCSOWNER.PWN_DOC_REG_ARCH_ERROR_SEQ.NEXTVAL FROM DUAL";
using (var oracleCommand = new OracleCommand(seqSql, connection))
{
log.Debug(string.Format("Executing: {0}", seqSql));
using (var reader = oracleCommand.ExecuteReader())
{
if (reader.RowSize > 0 && reader.HasRows)
{
while (reader.Read())
{
idVal = Utilities.GetIntegerValue(reader["val"]);
log.Debug("Result :" + retVal);
}
}
else
{
log.Error("AddErrorsToDB Failed due to sequance:");
return(false);
}
}
}
string sql = "INSERT INTO CCSOWNER.PWN_DOC_REG_ARCH_ERROR(ID,ERROR_MSG) VALUES " +
"(:ID,:ERROR_MSG)";
string str = null;
using (var oracleCommand = new OracleCommand(null, connection))
{
log.Debug(string.Format("Executing: {0}", sql));
oracleCommand.CommandText = sql;
oracleCommand.Parameters.Add(":ID", OracleDbType.Int32);
oracleCommand.Parameters.Add(":ERROR_MSG", OracleDbType.Varchar2);
oracleCommand.Prepare();
oracleCommand.Parameters[0].Value = idVal;
oracleCommand.Parameters[1].Value = msg;
oracleCommand.ExecuteNonQuery();
}
ret = true;
}
}
catch (Exception e)
{
log.Error("AddErrorsToDB Failed:", e);
log.Debug(e.StackTrace.ToString());
ret = false;
}
finally
{
stopwatch.Stop();
log.Debug(string.Format("Time Taken for AddErrorsToDB : {0} Msec", stopwatch.ElapsedMilliseconds));
//oracleCommand = null;
}
return(ret);
}
