public static bool ExportAllTables(out string ADataYmlGzBase64)
{
TDataBase DBConnectionObj = null;
TDBTransaction ReadTransaction = null;
XmlDocument OpenPetraData = TYml2Xml.CreateXmlDocument();
XmlNode rootNode = OpenPetraData.FirstChild.NextSibling;
Assembly TypedTablesAssembly = Assembly.LoadFrom(
TAppSettingsManager.ApplicationDirectory + Path.DirectorySeparatorChar + "Ict.Petra.Shared.lib.data.dll");
try
{
// Open a separate DB Connection for the exporting of the data...
DBConnectionObj = DBAccess.SimpleEstablishDBConnection("ExportAllTables");
// ...and start a DB Transaction on that separate DB Connection
ReadTransaction = DBConnectionObj.BeginTransaction(IsolationLevel.Serializable, 0, "ExportAllTables");
ExportTables(rootNode, "MSysMan", "", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MCommon", "", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MPartner", "Partner", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MPartner", "Mailroom", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MFinance", "Account", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MFinance", "Gift", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MFinance", "AP", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MFinance", "AR", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MPersonnel", "Personnel", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MPersonnel", "Units", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MConference", "", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MHospitality", "", TypedTablesAssembly, ReadTransaction);
ExportSequences(rootNode, ReadTransaction);
}
finally
{
if (DBConnectionObj != null)
{
DBConnectionObj.RollbackTransaction();
DBConnectionObj.CloseDBConnection();
}
}
ADataYmlGzBase64 = TYml2Xml.Xml2YmlGz(OpenPetraData);
return(true);
}
/// <summary>
/// Gets called in regular intervals from a Timer in Class TTimedProcessing.
/// </summary>
/// <param name="ADataBaseObj">Already instatiated DB Access object with opened DB connection.</param>
/// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
public static void Process(TDataBase ADataBaseObj, bool ARunManually)
{
TDBTransaction ReadWriteTransaction;
bool NewTransaction;
bool LastReminderDateAcquired;
DateTime LastReminderDate;
DataSet ReminderResultsDS;
SSystemDefaultsRow SystemDefaultsDR;
PPartnerReminderTable PartnerReminderDT;
int ReminderFreqency;
TSmtpSender Sender = null;
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("Entering TProcessPartnerReminders.Process...");
}
ReadWriteTransaction = ADataBaseObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
/*
* This whole process must either succeed or fail, therefore the whole thing is in a try-catch.
*/
try
{
/*
* Obtain date when PartnerReminders last ran. This is stored in a SystemDefault. If it doesn't exist already,
* a new SystemDefault with an ancient date is created for us.
*/
LastReminderDateAcquired = GetLastReminderDate(out LastReminderDate, out SystemDefaultsDR, ReadWriteTransaction);
if (!LastReminderDateAcquired)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Could not send Partner Reminders because Petra couldn't create the required SystemDefault setting for the Last Reminder Date!");
ADataBaseObj.RollbackTransaction();
return;
}
try
{
Sender = new TSmtpSender();
}
catch (ESmtpSenderInitializeException e)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": " + e.Message);
if (e.InnerException != null)
{
TLogging.Log(e.InnerException.ToString(), TLoggingType.ToLogfile);
}
return;
}
// Retrieve all PartnerReminders we need to process.
ReminderResultsDS = GetRemindersToProcess(LastReminderDate, out PartnerReminderDT,
ADataBaseObj, ReadWriteTransaction);
/*
* We now have a Typed DataTable with the PartnerReminders that we need to process.
* Iterate through the PartnerReminders, update data, and send an email for each PartnerReminder.
*/
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
TLogging.Log("PartnerReminders data before we start processing all PartnerReminders....");
TLogging.Log(ReminderResultsDS.GetXml().ToString());
}
foreach (PPartnerReminderRow PartnerReminderDR in PartnerReminderDT.Rows)
{
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Processing Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
ReminderFreqency = (PartnerReminderDR.IsReminderFrequencyNull()) ? 0 : PartnerReminderDR.ReminderFrequency;
PartnerReminderDR.BeginEdit();
PartnerReminderDR.LastReminderSent = DateTime.Now.Date;
PartnerReminderDR.NextReminderDate = DateTime.Now.Date.AddDays(ReminderFreqency);
if (!PartnerReminderDR.IsEventDateNull()) // Reminder has an Event Date
{
if (PartnerReminderDR.NextReminderDate > PartnerReminderDR.EventDate)
{
if (TLogging.DebugLevel >= 5)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Deactivating Reminder ID {0} for Partner {1} as its Event Date is in the past.",
PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
PartnerReminderDR.ReminderActive = false;
}
}
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Sending email for Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
Boolean emailSentOk = false;
try
{
emailSentOk = SendReminderEmail(PartnerReminderDR, ReadWriteTransaction, Sender);
}
catch (ESmtpSenderInitializeException e) // if an exception was thrown, assume the email didn't go.
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1}: {2}", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey,
e.Message));
if (e.InnerException != null)
{
TLogging.Log(e.InnerException.Message);
}
}
catch (ESmtpSenderSendException e)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1}: {2}", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey,
e.Message));
if (e.InnerException != null)
{
TLogging.Log(e.InnerException.Message);
}
}
catch (Exception e)
{
TLogging.Log(e.Message);
}
if (emailSentOk)
{
// Accept the edit
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1} accepted by SMTP server.", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey));
}
PartnerReminderDR.EndEdit();
}
else
{
// Cancel the edit
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1} REJECTED by SMTP server.", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey));
PartnerReminderDR.CancelEdit();
}
}
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
TLogging.Log("PartnerReminders data after processing all PartnerReminders, before writing it to DB....");
TLogging.Log(PartnerReminderDT.DataSet.GetXml().ToString());
}
// Update all the changed PartnerReminder Rows
PPartnerReminderAccess.SubmitChanges(PartnerReminderDT, ReadWriteTransaction);
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
}
/*
* Update the SystemDefault that keeps track of when Partner Reminders last ran.
* (SystemDefaultsDR will point to the row we loaded earlier on, OR the row we added earlier on
* if there wasn't already a SystemDefault row.)
*/
UpdateLastReminderDate(SystemDefaultsDR, ReadWriteTransaction);
if (NewTransaction)
{
ADataBaseObj.CommitTransaction();
}
TLogging.LogAtLevel(1, TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " ran succesfully.");
}
catch (Exception Exc)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " encountered an Exception:" + Environment.NewLine +
Exc.ToString());
if (NewTransaction)
{
ADataBaseObj.RollbackTransaction();
}
throw;
}
finally
{
if (Sender != null)
{
Sender.Dispose();
}
}
}
public static void Main(string[] args)
{
try
{
// establish connection to database
TCmdOpts settings = new TCmdOpts();
if (!settings.IsFlagSet("Server.ODBC_DSN"))
{
Console.WriteLine(
"sample call: " +
"ExportDataProgress.exe -Server.ODBC_DSN:Petra2_2sa -username:demo_sql -password:demo -sql:\"SELECT * from pub.a_account\" -output:test.xml");
Environment.Exit(-1);
}
new TLogging("debug.log");
TDataBase db = new TDataBase();
TDBType dbtype = TDBType.ProgressODBC;
if (settings.IsFlagSet("Server.RDBMSType"))
{
dbtype = CommonTypes.ParseDBType(settings.GetOptValue("Server.RDBMSType"));
}
if (dbtype != TDBType.ProgressODBC)
{
throw new Exception("at the moment only Progress ODBC db is supported");
}
db.EstablishDBConnection(dbtype,
settings.GetOptValue("Server.ODBC_DSN"),
"",
"",
settings.GetOptValue("username"),
settings.GetOptValue("password"),
"");
DBAccess.GDBAccessObj = db;
TLogging.DebugLevel = 10;
string sqlText = "";
if (!settings.IsFlagSet("sql"))
{
Console.WriteLine("Please enter sql and finish with semicolon: ");
while (!sqlText.Trim().EndsWith(";"))
{
sqlText += " " + Console.ReadLine();
}
sqlText = sqlText.Substring(0, sqlText.Length - 1);
}
else
{
sqlText = settings.GetOptValue("sql");
Console.WriteLine(sqlText);
}
TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted);
DataTable table = db.SelectDT(sqlText, "temp", transaction);
XmlDocument doc = TDataBase.DataTableToXml(table);
if (settings.IsFlagSet("output"))
{
if (settings.GetOptValue("output").EndsWith("yml"))
{
TYml2Xml.Xml2Yml(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("csv"))
{
TCsv2Xml.Xml2Csv(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("xml"))
{
StreamWriter sw = new StreamWriter(settings.GetOptValue("output"));
sw.Write(TXMLParser.XmlToString2(doc));
sw.Close();
}
}
else
{
TYml2Xml.Xml2Yml(doc, "temp.yml");
StreamReader sr = new StreamReader("temp.yml");
Console.WriteLine(sr.ReadToEnd());
sr.Close();
}
db.RollbackTransaction();
db.CloseDBConnection();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
}
public static void Main(string[] args)
{
try
{
// establish connection to database
TCmdOpts settings = new TCmdOpts();
if (!settings.IsFlagSet("Server.ODBC_DSN"))
{
Console.WriteLine(
"sample call: " +
"ExportDataProgress.exe -Server.ODBC_DSN:Petra2_2sa -username:demo_sql -password:demo -sql:\"SELECT * from pub.a_account\" -output:test.xml");
Environment.Exit(-1);
}
new TLogging("debug.log");
TDataBase db = new TDataBase();
TDBType dbtype = TDBType.ProgressODBC;
if (settings.IsFlagSet("Server.RDBMSType"))
{
dbtype = CommonTypes.ParseDBType(settings.GetOptValue("Server.RDBMSType"));
}
if (dbtype != TDBType.ProgressODBC)
{
throw new Exception("at the moment only Progress ODBC db is supported");
}
db.EstablishDBConnection(dbtype,
settings.GetOptValue("Server.ODBC_DSN"),
"",
"",
settings.GetOptValue("username"),
settings.GetOptValue("password"),
"");
DBAccess.GDBAccessObj = db;
TLogging.DebugLevel = 10;
string sqlText = "";
if (!settings.IsFlagSet("sql"))
{
Console.WriteLine("Please enter sql and finish with semicolon: ");
while (!sqlText.Trim().EndsWith(";"))
{
sqlText += " " + Console.ReadLine();
}
sqlText = sqlText.Substring(0, sqlText.Length - 1);
}
else
{
sqlText = settings.GetOptValue("sql");
Console.WriteLine(sqlText);
}
TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted);
DataTable table = db.SelectDT(sqlText, "temp", transaction);
XmlDocument doc = TDataBase.DataTableToXml(table);
if (settings.IsFlagSet("output"))
{
if (settings.GetOptValue("output").EndsWith("yml"))
{
TYml2Xml.Xml2Yml(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("csv"))
{
TCsv2Xml.Xml2Csv(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("xml"))
{
StreamWriter sw = new StreamWriter(settings.GetOptValue("output"));
sw.Write(TXMLParser.XmlToString2(doc));
sw.Close();
}
}
else
{
TYml2Xml.Xml2Yml(doc, "temp.yml");
StreamReader sr = new StreamReader("temp.yml");
Console.WriteLine(sr.ReadToEnd());
sr.Close();
}
db.RollbackTransaction();
db.CloseDBConnection();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
}
/// <summary>
/// Records the logging-out (=disconnection) of a Client to the s_login DB Table. That DB Table contains a log
/// of all the log-ins/log-in attempts to the system, and of log-outs from the system.
/// </summary>
/// <param name="AUserID">UserID of the User for which a logout should be recorded.</param>
/// <param name="AProcessID">ProcessID of the User for which a logout should be recorded.
/// This will need to be the number that got returned from an earlier call to
/// <see cref="AddLoginLogEntry(string, string, string, out int, TDBTransaction)"/>!</param>
/// <param name="ATransaction">Either an instantiated DB Transaction, or null. In the latter case
/// a separate DB Connection gets opened, a DB Transaction on that separate DB Connection gets started,
/// then committed/rolled back and the separate DB Connection gets closed. This is needed when this Method
/// gets called from Method 'Ict.Common.Remoting.Server.TDisconnectClientThread.StartClientDisconnection()'!</param>
public void RecordUserLogout(String AUserID, int AProcessID, TDBTransaction ATransaction)
{
TDataBase DBConnectionObj = null;
TDBTransaction WriteTransaction = null;
SLoginTable LoginTable = new SLoginTable();
SLoginRow NewLoginRow = LoginTable.NewRowTyped(false);
bool SubmissionOK = false;
DateTime LogoutDateTime = DateTime.Now;
// Set DataRow values
NewLoginRow.LoginProcessId = -1;
NewLoginRow.UserId = AUserID.ToUpper();
NewLoginRow.LoginType = LOGIN_STATUS_TYPE_LOGOUT;
NewLoginRow.LoginDetails = Catalog.GetString("User logout.");
NewLoginRow.LoginProcessIdRef = AProcessID;
NewLoginRow.Date = LogoutDateTime.Date;
NewLoginRow.Time = Conversions.DateTimeToInt32Time(LogoutDateTime);
//TLogging.Log(String.Format("RecordUserLogout: NewLoginRow.Date: {0}; NewLoginRow.Time: {1}",
// NewLoginRow.Date, NewLoginRow.Time));
LoginTable.Rows.Add(NewLoginRow);
if (ATransaction == null)
{
// Open a separate DB Connection (necessary because this Method gets executed in the Server's (Main) AppDomain
// which hasn't got an instance of DBAccess.GDBAccess!) ...
DBConnectionObj = DBAccess.SimpleEstablishDBConnection("RecordUserLogout");
// ...and start a DB Transaction on that separate DB Connection
WriteTransaction = DBConnectionObj.BeginTransaction(IsolationLevel.RepeatableRead, 0, "RecordUserLogout");
}
else
{
DBConnectionObj = ATransaction.DataBaseObj;
WriteTransaction = ATransaction;
}
try
{
try
{
SLoginAccess.SubmitChanges(LoginTable, WriteTransaction);
SubmissionOK = true;
}
catch (Exception Exc)
{
TLogging.Log("RecordUserLogout: An Exception occured during the saving of a Login Log entry:" +
Environment.NewLine + Exc.ToString());
throw;
}
}
finally
{
if (SubmissionOK)
{
DBConnectionObj.CommitTransaction();
}
else
{
DBConnectionObj.RollbackTransaction();
}
if (DBConnectionObj != null)
{
DBConnectionObj.CloseDBConnection();
}
}
}
