/// <summary>
/// Retrieves Location and PartnerLocation information and the rest of the PartnerInfo data,
/// but not the 'Head' data.
/// </summary>
/// <param name="APartnerKey">PartnerKey of Partner to find the short name for</param>
/// <param name="ALocationKey">Location Key of the Location that the information should be
/// retrieved for.</param>
/// <param name="APartnerInfoDS">Typed DataSet that contains the requested data.</param>
/// <param name="AIncludeRest">Include 'Rest' data as well</param>
/// <param name="AReadTransaction" >Open DB Transaction.</param>
private static bool LocationPartnerLocationInternal(Int64 APartnerKey, TLocationPK ALocationKey,
ref PartnerInfoTDS APartnerInfoDS,
bool AIncludeRest, TDBTransaction AReadTransaction)
{
bool ReturnValue = false;
PPartnerRow PartnerDR;
PLocationTable LocationDT;
PPartnerLocationTable PartnerLocationDT;
try
{
/*
* Check for existance of Partner
*/
PartnerDR = MCommonMain.CheckPartnerExists2(APartnerKey, true, AReadTransaction.DataBaseObj);
if (PartnerDR != null)
{
/*
* Perform security checks; these throw ESecurityPartnerAccessDeniedException
* in case access isn't granted.
*/
TSecurity.CanAccessPartnerExc(PartnerDR, AReadTransaction.DataBaseObj);
/*
* Load Partner Location data and rest of data first
*/
PartnerLocationInternal(APartnerKey, ALocationKey, AReadTransaction, ref APartnerInfoDS);
/*
* Load Location Information; this gets merged into the already retrieved
* information in APartnerInfoDS (eg. Partner Location data and rest of data)
*/
APartnerInfoDS.Merge(TPPartnerAddressAggregate.LoadByPrimaryKey(
ALocationKey.SiteKey, ALocationKey.LocationKey, AReadTransaction));
// Apply Address Security
LocationDT = APartnerInfoDS.PLocation;
PartnerLocationDT = APartnerInfoDS.PPartnerLocation;
TPPartnerAddressAggregate.ApplySecurity(ref PartnerLocationDT,
ref LocationDT);
if (AIncludeRest)
{
RestInternal(PartnerDR, ref APartnerInfoDS, AReadTransaction);
}
ReturnValue = true;
}
}
catch (ESecurityPartnerAccessDeniedException)
{
// don't log this Exception - this is thrown on purpose here and the Client knows how to deal with it.
throw;
}
catch (EDBAccessLackingCoordinationException)
{
// don't log this Exception - the Client knows how to deal with it.
throw;
}
catch (Exception Exp)
{
TLogging.Log("TServerLookups_PartnerInfo.PartnerLocationInternal exception: " + Exp.ToString(), TLoggingType.ToLogfile);
TLogging.Log(Exp.StackTrace, TLoggingType.ToLogfile);
throw;
}
return(ReturnValue);
}
/// <summary>
/// generate the partners from a text file that was generated with Benerator
/// </summary>
/// <param name="AInputBeneratorFile"></param>
public static void GenerateWorkers(string AInputBeneratorFile)
{
PartnerEditTDS MainDS = new PartnerEditTDS();
PersonnelTDS PersonnelDS = new PersonnelTDS();
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys = "SELECT p_partner_key_n, p_unit_name_c FROM PUB_p_unit WHERE u_unit_type_code_c = 'F'";
DataTable FieldKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetFieldPartnerKeys, "keys", null);
// get a list of banks (all class BANK)
string sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank";
DataTable BankKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetBankPartnerKeys, "keys", null);
XmlDocument doc = TCsv2Xml.ParseCSV2Xml(AInputBeneratorFile, ",", Encoding.UTF8);
XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;
while (RecordNode != null)
{
string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation");
PFamilyRow familyRecord = null;
if (familySituation == "singleMan")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
}
else if (familySituation == "singleWoman")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Female", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
}
else if (familySituation == "family")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
int AgeDifferenceSpouse = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "AgeDifferenceSpouse"));
DataView FamilyView = new DataView(MainDS.PPerson);
FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + familyRecord.PartnerKey.ToString();
FamilyView.Sort = PPersonTable.GetFamilyIdDBName();
PPersonRow HusbandPersonRow = (PPersonRow)FamilyView[0].Row;
PPersonRow WifePersonRow = (PPersonRow)FamilyView[1].Row;
WifePersonRow.DateOfBirth =
WifePersonRow.DateOfBirth.Value.AddYears(
AgeDifferenceSpouse - (WifePersonRow.DateOfBirth.Value.Year - HusbandPersonRow.DateOfBirth.Value.Year));
if (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year < 19)
{
WifePersonRow.DateOfBirth.Value.AddYears(
19 - (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year));
}
int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren"));
for (int countChild = 0; countChild < NumberOfChildren; countChild++)
{
DateTime DateOfBirthChild = Convert.ToDateTime(
TXMLParser.GetAttribute(RecordNode,
"Child" + (countChild + 1).ToString() + "DateOfBirth"));
// mother must have been 19 when the child was born
if (DateOfBirthChild.Year < WifePersonRow.DateOfBirth.Value.Year + 19)
{
continue;
}
GeneratePersonRecord(RecordNode, familyRecord, "Child" + (countChild + 1).ToString(), MainDS);
}
}
GenerateAddressForFamily(RecordNode, familyRecord, MainDS);
GenerateCommitmentRecord(RecordNode, familyRecord, MainDS, PersonnelDS, FieldKeys);
GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys);
if (MainDS.PFamily.Rows.Count % 100 == 0)
{
TLogging.Log("created worker " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName);
}
RecordNode = RecordNode.NextSibling;
}
MainDS.ThrowAwayAfterSubmitChanges = true;
PartnerEditTDSAccess.SubmitChanges(MainDS);
PersonnelDS.ThrowAwayAfterSubmitChanges = true;
PersonnelTDSAccess.SubmitChanges(PersonnelDS);
TLogging.Log("after saving workers");
}
/// <summary>
/// Inserts the parameters into a template
/// </summary>
public string InsertParameters(string searchOpen, string searchClose, string template, ReplaceOptions options)
{
int bracket = template.IndexOf(searchOpen);
int prevBracket = bracket;
while (bracket != -1)
{
int firstRealChar = bracket + searchOpen.Length;
int paramEndIdx = template.IndexOf(searchClose, firstRealChar);
if (paramEndIdx <= 0)
{
// missing closing bracket; can happen with e.g. #testdate; should be #testdate#
if (template.Length > bracket + 20)
{
throw new Exception("Cannot find closing bracket " + searchClose + " for " + template.Substring(bracket, 20));
}
else
{
throw new Exception("Cannot find closing bracket " + searchClose + " for " + template.Substring(bracket));
}
}
String parameter = template.Substring(firstRealChar, paramEndIdx - firstRealChar);
string placeHolder = searchOpen + parameter + searchClose;
string otherValue = string.Empty;
if (parameter.Contains(" "))
{
string[] split = parameter.Split(new char[] { ' ' });
parameter = split[0];
// for LISTCMP
otherValue = split[1];
}
bool ParameterExists = false;
TVariant newvalue;
if (FParameters != null)
{
newvalue = FParameters.Get(parameter, -1, -1, eParameterFit.eBestFitEvenLowerLevel);
ParameterExists = (newvalue.TypeVariant != eVariantTypes.eEmpty);
}
else
{
newvalue = new TVariant();
}
if (!ParameterExists)
{
// if date is given, use the parameter itself
if ((parameter[0] >= '0') && (parameter[0] <= '9'))
{
newvalue = new TVariant(parameter);
}
else
{
int CountWarning = 1;
// do not print warning too many times for the same variable
if (!VariablesNotFound.ContainsKey(parameter))
{
VariablesNotFound.Add(parameter, 1);
}
else
{
VariablesNotFound[parameter] = VariablesNotFound[parameter] + 1;
CountWarning = VariablesNotFound[parameter];
}
if (CountWarning < 5)
{
// this can be alright, for empty values; for example method of giving can be empty; for report GiftTransactions
TLogging.Log(
"Variable " + parameter + " empty or not found");
}
else if (CountWarning % 20 == 0)
{
TLogging.Log("20 times: Variable " + parameter + " empty or not found.");
}
}
}
try
{
if (newvalue.TypeVariant == eVariantTypes.eDateTime)
{
// remove the time from the timestamp, only use the date at 0:00
DateTime date = newvalue.ToDate();
newvalue = new TVariant(new DateTime(date.Year, date.Month, date.Day));
}
string strValue = newvalue.ToString();
string Quotes = (options == ReplaceOptions.NoQuotes ? "" : "'");
if (searchOpen == "{LIST ")
{
string[] elements = newvalue.ToString().Split(new char[] { ',' });
strValue = "(";
foreach (string element in elements)
{
if (strValue.Length > 1)
{
strValue += ",";
}
strValue += Quotes + element + Quotes;
}
strValue += ")";
}
else if (searchOpen == "{LISTCMP ")
{
string[] elements = newvalue.ToString().Split(new char[] { ',' });
strValue = "(";
foreach (string element in elements)
{
if (strValue.Length > 1)
{
strValue += " AND ";
}
strValue += Quotes + element + Quotes + " = " + otherValue;
}
strValue += ")";
}
else if (newvalue.TypeVariant == eVariantTypes.eDateTime)
{
strValue = Quotes + newvalue.ToDate().ToString("yyyy-MM-dd") + Quotes;
}
else if ((searchOpen != "{{") &&
!(parameter.Length > 2 && parameter.Substring(parameter.Length - 2) == "_i") &&
(newvalue.TypeVariant == eVariantTypes.eString))
{
if (newvalue.ToString() != "*NOTUSED*")
{
strValue = Quotes + newvalue.ToString().Replace('*', '%') + Quotes;
}
else
{
strValue = Quotes + newvalue.ToString() + Quotes;
}
}
else if (
(newvalue.TypeVariant == eVariantTypes.eCurrency) ||
((newvalue.TypeVariant == eVariantTypes.eDecimal) && (Quotes.Length == 0)))
{
strValue = newvalue.ToDecimal().ToString("0.00");
}
template = template.Replace(placeHolder, strValue);
}
catch (Exception e)
{
throw new Exception(
"While trying to format parameter " + parameter + ", there was a problem with formatting." + Environment.NewLine + e.Message);
}
prevBracket = bracket;
bracket = template.IndexOf(searchOpen);
if ((prevBracket >= bracket) && (bracket >= 0))
{
TLogging.Log("endless loop in InsertParameters at pos " + bracket.ToString());
TLogging.Log("after replacing " + placeHolder);
TLogging.Log(template);
throw new Exception("Problem in InsertParameters, endless loop");
}
} // while
return(template);
}
// TODO: should this go to the server side? will we have Postgresql at all on the client?
/// <summary>
/// Start the PostgreSql database.
/// If user has admin rights then start as a service.
/// If user has no admin rights then start as an executible.
/// </summary>
/// <returns>true if startup was successful</returns>
private static bool StartPostgreSqlServer()
{
System.Diagnostics.Process PostgreSqlServerProcess;
if (TClientSettings.RunAsStandalone)
{
// start the PostgreSql server as exe(e.g. c:\Program Files\Postgres\8.3\bin\pg_ctl.exe -D C:\petra2\db23_pg start
try
{
FSplashScreen.ProgressText = "Starting PostgreSql Server...";
PostgreSqlServerProcess = new System.Diagnostics.Process();
PostgreSqlServerProcess.StartInfo.FileName = "\"" + TClientSettings.PostgreSql_BaseDir + "\\bin\\pg_ctl.exe\"";
PostgreSqlServerProcess.StartInfo.Arguments = "-D " + TClientSettings.PostgreSql_DataDir + " start";
PostgreSqlServerProcess.StartInfo.WorkingDirectory = TClientSettings.PostgreSql_DataDir;
PostgreSqlServerProcess.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
PostgreSqlServerProcess.EnableRaisingEvents = false;
PostgreSqlServerProcess.StartInfo.UseShellExecute = false;
System.Security.SecureString MyPassword = new System.Security.SecureString();
String Pwd = "petra";
foreach (char c in Pwd)
{
MyPassword.AppendChar(c);
}
PostgreSqlServerProcess.StartInfo.Password = MyPassword;
PostgreSqlServerProcess.StartInfo.UserName = "******";
if (PostgreSqlServerProcess.Start())
{
}
else
{
#if TESTMODE
TLogging.Log("failed to start " + PostgreSqlServerProcess.StartInfo.FileName);
#endif
#if TESTMODE
#else
FSplashScreen.ShowMessageBox("failed to start " + PostgreSqlServerProcess.StartInfo.FileName);
#endif
return(false);
}
}
catch (Exception exp)
{
#if TESTMODE
TLogging.Log("Exception while starting PostgreSql process: " + exp.ToString());
#endif
#if TESTMODE
#else
FSplashScreen.ShowMessageBox("Exception while starting PostgreSql process: " + exp.ToString());
#endif
return(false);
}
PostgreSqlServerProcess.WaitForExit(20000);
return(true);
}
return(true);
}
public static ExchangeRateTDS LoadDailyExchangeRateData(bool ADeleteAgedExchangeRatesFirst, DateTime AFromDate, DateTime AToDate)
{
// If relevant, we do a clean of the data table first, purging 'aged' data
if (ADeleteAgedExchangeRatesFirst)
{
// We clean up the DER table unless there is an app setting in the server configuration
// If you want to set this as a developer you create a copy of /inc/template/etc/Server-postgresql.config
// and rename it to Server-postgresql.config.my. Then add a new <add> element with this value set to true.
// Then (re)start the server using nant or OPDA, which will generate the working copy of this file.
if (!TAppSettingsManager.GetBoolean("KeepAgedExchangeRates", false))
{
DoDailyExchangeRateClean();
}
}
ExchangeRateTDS WorkingDS = new ExchangeRateTDS();
WorkingDS.EnforceConstraints = false;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadDailyExchangeRateData");
db.ReadTransaction(
ref Transaction,
delegate
{
// Populate the ExchangeRateTDSADailyExchangeRate table
//-- This is the complete query for the DAILYEXCHANGERATE TABLE
//-- It returns all rows from the Journal and Gift Batch tables
//-- PLUS all the rows from the DailyExchangeRate table that are NOT referenced by the Journal and Gift Batch tables.
string strSQL = "SELECT * FROM ";
strSQL += "( ";
// This returns all the rows in Daily Exchange rate that do NOT match any journal or gift
strSQL += "SELECT ";
strSQL += String.Format(
" 0 AS {0}, 0 AS {1}, 'DER' AS {2}, ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += " der.* ";
strSQL += "FROM PUB_a_daily_exchange_rate AS der ";
// By doing a left join and only selecting the NULL rows we get the rows from DER that are NOT used
strSQL += "LEFT JOIN ";
strSQL += "( ";
// This SELECT returns all the used rows (372 rows in the case of SA-DB)
strSQL += "SELECT ";
strSQL += " j.a_batch_number_i AS a_batch_number_i, ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_journal AS j ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " j.a_batch_number_i AS a_batch_number_i, ";
strSQL += " r.a_revaluation_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM a_journal AS j ";
strSQL += "JOIN a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ON ";
strSQL +=
" r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " gb.a_batch_number_i AS a_batch_number_i, ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_gift_batch AS gb ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS j_and_gb ";
strSQL += "ON ";
strSQL += " der.a_from_currency_code_c = j_and_gb.a_from_currency_code_c ";
strSQL += " AND der.a_to_currency_code_c = j_and_gb.a_to_currency_code_c ";
strSQL += " AND der.a_date_effective_from_d = j_and_gb.a_date_effective_from_d ";
strSQL += " AND der.a_rate_of_exchange_n = j_and_gb.a_rate_of_exchange_n ";
strSQL += "WHERE ";
strSQL += " a_batch_number_i IS NULL ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
// The second half of the UNION returns all the Forex rows from journal and gift
// They are aggregated by from/to/date/rate and the time is the min time.
// We also get the usage count as well as whether the row originated in the DER table or one of gift or batch
strSQL += "SELECT ";
strSQL += String.Format(
" sum(journalUsage) AS {0}, sum(giftBatchUsage) AS {1}, 'GBJ' AS {2}, ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += " a_from_currency_code_c, ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_rate_of_exchange_n, ";
strSQL += " a_date_effective_from_d, ";
strSQL += " min(a_time_effective_from_i), ";
strSQL += " NULL AS s_date_created_d, ";
strSQL += " NULL AS s_created_by_c, ";
strSQL += " NULL AS s_date_modified_d, ";
strSQL += " NULL AS s_modified_by_c, ";
strSQL += " NULL AS s_modification_id_t ";
strSQL += "FROM ";
strSQL += "( ";
// These are all the used rows again (same as part of the query above) but this time we can count the usages from the two tables
strSQL += "SELECT ";
strSQL += " 1 AS journalUsage, ";
strSQL += " 0 AS giftBatchUsage, ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_journal AS j ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " 1 AS journalUsage, ";
strSQL += " 0 AS giftBatchUsage, ";
strSQL += " r.a_revaluation_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM a_journal AS j ";
strSQL += "JOIN a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ON ";
strSQL +=
" r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " 0 AS journalUsage, ";
strSQL += " 1 AS giftBatchUsage, ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " 0 AS a_time_effective_from_i, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_gift_batch AS gb ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS j_and_gb ";
// GROUP the second half of the query (the UNION of used rates)
strSQL += "GROUP BY ";
strSQL += " a_from_currency_code_c, ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_date_effective_from_d, ";
strSQL += " a_rate_of_exchange_n ";
strSQL += ") AS all_rates ";
strSQL += ((AFromDate < DateTime.MaxValue) && (AToDate < DateTime.MaxValue)) ?
String.Format(" WHERE all_rates.{0}>='{1}' AND all_rates.{0}<='{2}' ",
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
AFromDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
AToDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)) :
String.Empty;
// ORDER of the outermost SELECT
strSQL += "ORDER BY ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_from_currency_code_c, ";
strSQL += " a_date_effective_from_d DESC, ";
strSQL += " a_time_effective_from_i DESC ";
db.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRate.TableName, Transaction);
// Now populate the ExchangeRateTDSADailyExchangerateUsage table
//-- COMPLETE QUERY TO RETURN ADailyExchangeRateUsage
//-- Query to return the Daily Exchange Rate Usage details
//-- Only returns rows that are in a foreign currency
//-- Querying this table by from/to/date/time will return one row per use case
//-- If the Journal is 0 the batch refers to a gift batch, otherwise it is a GL batch
strSQL = "SELECT * FROM ( ";
//-- This part of the query returns the use cases from the Journal table
strSQL += "SELECT ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += String.Format(
" j.a_ledger_number_i AS {0}, j.a_batch_number_i AS {1}, j.a_journal_number_i AS {2}, b.a_batch_status_c AS {3}, j.a_journal_description_c AS {4}, b.a_batch_year_i AS {5}, b.a_batch_period_i AS {6}, 'J' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_journal j ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_batch b ";
strSQL += " ON b.a_batch_number_i = j.a_batch_number_i ";
strSQL += " AND b.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
//-- This part of the query returns the revaluation rows
strSQL += "SELECT ";
strSQL += " r.a_revaluation_currency_c as a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += String.Format(
" j.a_ledger_number_i AS {0}, j.a_batch_number_i AS {1}, j.a_journal_number_i AS {2}, b.a_batch_status_c AS {3}, j.a_journal_description_c AS {4}, b.a_batch_year_i AS {5}, b.a_batch_period_i AS {6}, 'J' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_journal j ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_batch b ";
strSQL += " ON b.a_batch_number_i = j.a_batch_number_i ";
strSQL += " AND b.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ";
strSQL +=
" ON r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
//-- This part of the query returns the use cases from the Gift Batch table
strSQL += "SELECT ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " 0 AS a_time_effective_from_i, ";
strSQL += String.Format(
" gb.a_ledger_number_i AS {0}, gb.a_batch_number_i AS {1}, 0 AS {2}, gb.a_batch_status_c AS {3}, gb.a_batch_description_c AS {4}, gb.a_batch_year_i AS {5}, gb.a_batch_period_i AS {6}, 'GB' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_gift_batch gb ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS usage ";
strSQL += ((AFromDate < DateTime.MaxValue) && (AToDate < DateTime.MaxValue)) ?
String.Format(" WHERE usage.{0}>='{1}' AND usage.{0}<='{2}' ",
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
AFromDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
AToDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)) :
String.Empty;
strSQL += "ORDER BY usage.a_date_effective_from_d DESC, usage.a_time_effective_from_i DESC ";
db.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRateUsage.TableName, Transaction);
// Now we start a tricky bit to resolve potential primary key conflicts when the constraints are turned on.
// By combining the Journal and Gift Batch data that is not referenced in the exchange rate table we can easily
// have introduced conflicts where more than one rate has been used for a given currency pair and effective date/time.
// This is because there is no constraint that enforces the batch/journal tables to use a time from the exch rate table.
// So we have to go through all the rows in our data table and potentially change the time to make it possible to get our primary key.
// Start by creating a data view on the whole result set. The ordering is important because we are going to step through the set row by row.
// Within one group of from/to/date it is essential that the first 'source' is the DER table because we don't change the time on that one -
// and of course that must stay the same because the user can modify that one.
// We need to deal with the following possibilities:
// From To Date Time Source Rate
// EUR GBP 2014-01-01 1234 DER 2.11
// EUR GBP 2014-01-01 1234 GBJ 2.115
// EUR GBP 2014-01-01 1234 GBJ 2.22
// EUR GBP 2014-01-01 1234 GBJ 3.11
//
// In the first row we have an entry from the DER table that is not used anywhere, but a (slightly) different rate is actually used
// in a Journal.
// In the other rows we have 3 different rates - all used somewhere. We need to adjust the times so they are different.
DataView dv = new DataView(WorkingDS.ADailyExchangeRate, "",
String.Format("{0}, {1}, {2} DESC, {3} DESC, {4}, {5}",
ADailyExchangeRateTable.GetFromCurrencyCodeDBName(),
ADailyExchangeRateTable.GetToCurrencyCodeDBName(),
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
ADailyExchangeRateTable.GetTimeEffectiveFromDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName(),
ADailyExchangeRateTable.GetRateOfExchangeDBName()), DataViewRowState.CurrentRows);
for (int i = 0; i < dv.Count - 1; i++)
{
// Get the 'current' row and the 'next' one...
ExchangeRateTDSADailyExchangeRateRow drThis = (ExchangeRateTDSADailyExchangeRateRow)dv[i].Row;
ExchangeRateTDSADailyExchangeRateRow drNext = (ExchangeRateTDSADailyExchangeRateRow)dv[i + 1].Row;
if (!drThis.FromCurrencyCode.Equals(drNext.FromCurrencyCode) ||
!drThis.ToCurrencyCode.Equals(drNext.ToCurrencyCode) ||
!drThis.DateEffectiveFrom.Equals(drNext.DateEffectiveFrom) ||
!drThis.TimeEffectiveFrom.Equals(drNext.TimeEffectiveFrom))
{
// Something is different so our primary key will be ok for the current row
continue;
}
// We have got two (or more) rows with the same potential primary key and different rates/usages.
// We need to work out how many rows ahead also have the same time and adjust them all
bool moveForwards = (drThis.TimeEffectiveFrom < 43200);
int timeOffset = 60; // 1 minute
// Start by adjusting our 'next' row we are already working with
drNext.BeginEdit();
int prevTimeEffectiveFrom = drNext.TimeEffectiveFrom;
drNext.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
drNext.EndEdit();
i++; // we can increment our main loop counter now that we have dealt with our 'next' row.
TLogging.LogAtLevel(2, String.Format("Modifying {0} row: From {1}, To {2}, Date {3}, Time {4}, new Time {5}",
drThis.TableSource, drThis.FromCurrencyCode, drThis.ToCurrencyCode, drThis.DateEffectiveFrom.ToString("yyyy-MM-dd"),
prevTimeEffectiveFrom, drNext.TimeEffectiveFrom), TLoggingType.ToLogfile);
// Modify all the rows in the usage table that refer to the previous time
OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drNext.FromCurrencyCode, drNext.ToCurrencyCode,
drNext.DateEffectiveFrom,
prevTimeEffectiveFrom, drNext.TimeEffectiveFrom, drNext.RateOfExchange);
// Now look ahead even further than the 'next' row and modify those times too, adding 1 more minute to each
for (int k = i + 1; k < dv.Count; k++)
{
ExchangeRateTDSADailyExchangeRateRow drLookAhead = (ExchangeRateTDSADailyExchangeRateRow)dv[k].Row;
if (!drThis.FromCurrencyCode.Equals(drLookAhead.FromCurrencyCode) ||
!drThis.ToCurrencyCode.Equals(drLookAhead.ToCurrencyCode) ||
!drThis.DateEffectiveFrom.Equals(drLookAhead.DateEffectiveFrom) ||
!drThis.TimeEffectiveFrom.Equals(drLookAhead.TimeEffectiveFrom))
{
// No more rows match our potential primary key conflict on the 'current' row.
break;
}
// Do exactly the same to this row as we did to the 'next' row above
drLookAhead.BeginEdit();
prevTimeEffectiveFrom = drLookAhead.TimeEffectiveFrom;
drLookAhead.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
drLookAhead.EndEdit();
i++;
TLogging.LogAtLevel(2, String.Format("Modifying additional {0} row: From {1}, To {2}, Date {3}, Time {4}, new Time {5}",
drThis.TableSource, drThis.FromCurrencyCode, drThis.ToCurrencyCode,
drThis.DateEffectiveFrom.ToString("yyyy-MM-dd"),
prevTimeEffectiveFrom, drLookAhead.TimeEffectiveFrom), TLoggingType.ToLogfile);
OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drLookAhead.FromCurrencyCode, drLookAhead.ToCurrencyCode,
drLookAhead.DateEffectiveFrom, prevTimeEffectiveFrom, drLookAhead.TimeEffectiveFrom, drLookAhead.RateOfExchange);
}
} // check the next row in the table so that it becomes the 'current' row.
WorkingDS.EnforceConstraints = true;
// We only load the following data if we are returning ALL exchange rate data
if ((AFromDate == DateTime.MaxValue) && (AToDate == DateTime.MaxValue))
{
// Load the Corporate exchange rate table using the usual method
ACorporateExchangeRateAccess.LoadAll(WorkingDS, Transaction);
// Load the daily exchange rate table as the 'raw' table. The client needs this for adding new rows to check for constraints.
// Note: April 2015. The MissingSchemaAction was added because SQLite gave a mismatched DataType on a_effective_time_i.
// As a result the GUI tests failed on SQLite - as well as the screen not loading(!)
// There should be no difference with PostgreSQL, which worked fine without the parameter.
WorkingDS.ARawDailyExchangeRate.Merge(db.SelectDT("SELECT *, 0 AS Unused FROM PUB_a_daily_exchange_rate",
"a_raw_daily_exchange_rate",
Transaction), false, MissingSchemaAction.Ignore);
strSQL = "SELECT ";
strSQL += " a_ledger_number_i, ";
strSQL += " a_ledger_status_l, ";
strSQL += " max(a_ledger_name_c) AS a_ledger_name_c, ";
strSQL += " max(a_base_currency_c) AS a_base_currency_c, ";
strSQL += " max(a_intl_currency_c) AS a_intl_currency_c, ";
strSQL += " max(a_current_financial_year_i) AS a_current_financial_year_i, ";
strSQL += " max(a_current_period_i) AS a_current_period_i, ";
strSQL += " max(a_number_of_accounting_periods_i) AS a_number_of_accounting_periods_i, ";
strSQL += " max(a_number_fwd_posting_periods_i) AS a_number_fwd_posting_periods_i, ";
strSQL += " min(CurrentPeriodStartDate) AS CurrentPeriodStartDate, ";
strSQL += " max(CurrentPeriodEndDate) AS CurrentPeriodEndDate, ";
strSQL += " max(ForwardPeriodEndDate) AS ForwardPeriodEndDate ";
strSQL += "FROM ";
strSQL += "( ";
strSQL +=
"SELECT ldg.*, pd.a_period_start_date_d AS CurrentPeriodStartDate, pd.a_period_end_date_d AS CurrentPeriodEndDate, NULL AS ForwardPeriodEndDate ";
strSQL += "FROM a_ledger ldg ";
strSQL += "JOIN a_accounting_period pd ";
strSQL += "ON ldg.a_ledger_number_i=pd.a_ledger_number_i and ldg.a_current_period_i=pd.a_accounting_period_number_i ";
strSQL += "UNION ";
strSQL +=
"SELECT ldg.*, pd.a_period_start_date_d AS CurrentPeriodStartDate, NULL AS CurrentPeriodEndDate, pd.a_period_end_date_d AS ForwardPeriodEndDate ";
strSQL += "FROM a_ledger ldg ";
strSQL += "JOIN a_accounting_period pd ";
strSQL +=
"ON ldg.a_ledger_number_i=pd.a_ledger_number_i and (ldg.a_current_period_i + a_number_fwd_posting_periods_i)=pd.a_accounting_period_number_i ";
strSQL += ") AS all_info ";
strSQL += "GROUP BY a_ledger_number_i, a_ledger_status_l ";
db.Select(WorkingDS, strSQL, WorkingDS.ALedgerInfo.TableName, Transaction);
}
});
// Accept row changes here so that the Client gets 'unmodified' rows
WorkingDS.AcceptChanges();
return(WorkingDS);
}
/// <summary>
/// shows the menu and processes the selections of the administrator
/// </summary>
public static void Menu()
{
bool ReadLineLoopEnd;
bool EntryParsedOK;
String ServerAdminCommand;
String ConsoleInput;
String ClientTaskCode;
String ClientTaskGroup;
String AdministrativeMessage;
System.Int16 ClientID = 0; // assignment only to make code compile; has no functional implication
System.Int16 ClientTaskPriority = 1; // assignment only to make code compile; has no functional implication
String GpgList;
// label
// ReadClientID, used only for repeating invalid command line input
// ReadClientTaskPriority; used only for repeating invalid command line input
DisplayPetraServerInformation();
//
// Startup done.
// From now on just listen on menu commands...
//
Console.WriteLine(Environment.NewLine + "-> Press \"m\" for menu.");
Console.Write(ServerAdminPrompt);
// ServerAdmin stops after leaving the following loop...!
ReadLineLoopEnd = false;
do
{
ServerAdminCommand = (Console.ReadLine());
if (ServerAdminCommand.Length > 0)
{
ServerAdminCommand = ServerAdminCommand.Substring(0, 1);
switch (Convert.ToChar(ServerAdminCommand))
{
case 'm':
case 'M':
Console.WriteLine(Environment.NewLine + "-> Available commands <-");
Console.WriteLine(" c: list connected Clients / C: list disconnected Clients");
Console.WriteLine(" d: disconnect a certain Client");
Console.WriteLine(" p: perform timed server processing manually now");
Console.WriteLine(" q: queue a Client Task for a certain Client");
Console.WriteLine(" s: Server Status");
if (TLogging.DebugLevel > 0)
{
Console.WriteLine(" y: show Server memory");
Console.WriteLine(" g: perform Server garbage collection (for debugging purposes only!)");
}
Console.WriteLine(" e: export the database to yml.gz");
Console.WriteLine(" i: import a yml.gz, which will overwrite the database");
Console.WriteLine(" j: upgrade the database");
if (TLogging.DebugLevel > 0)
{
Console.WriteLine(" r: Mark all Cached Tables for Refreshing");
}
Console.WriteLine(" a: send administrative message to all connected clients");
Console.WriteLine(" o: controlled Server shutdown (gets all connected clients to disconnect)");
Console.WriteLine(" u: unconditional Server shutdown (forces 'hard' disconnection of all Clients!)");
#if DEBUG
Console.WriteLine(" v: Clear all RDBMS Connection Pools");
#endif
Console.WriteLine(" k: List Intranet GPG encryption keys / K: Import keys");
Console.WriteLine(" x: exit PETRAServerADMIN");
Console.Write(ServerAdminPrompt);
break;
case 'c':
Console.WriteLine(Environment.NewLine + "-> Connected Clients <-");
Console.WriteLine(TRemote.FormatClientList(false));
Console.Write(ServerAdminPrompt);
break;
case 'C':
Console.WriteLine(Environment.NewLine + "-> Disconnected Clients <-");
Console.WriteLine(TRemote.FormatClientList(true));
Console.Write(ServerAdminPrompt);
break;
case 'd':
case 'D':
Console.WriteLine(Environment.NewLine + "-> Disconnect a certain Client <-");
if (TRemote.GetClientList().Count > 0)
{
Console.WriteLine(TRemote.FormatClientList(false));
Console.Write(" Enter ClientID: ");
ConsoleInput = Console.ReadLine();
DisconnectClient(ConsoleInput);
}
else
{
Console.WriteLine(" * no Clients connected *");
}
Console.Write(ServerAdminPrompt);
break;
case 'e':
case 'E':
Console.WriteLine(Environment.NewLine + "-> Export the database to yml.gz file <-");
ExportDatabase();
Console.Write(ServerAdminPrompt);
break;
case 'i':
case 'I':
Console.WriteLine(Environment.NewLine + "-> Restore the database from yml.gz file <-");
RestoreDatabase();
Console.Write(ServerAdminPrompt);
break;
case 'j':
Console.WriteLine(Environment.NewLine + "-> Upgrade the database ...");
UpgradeDatabase();
break;
case 'r':
case 'R':
Console.WriteLine(Environment.NewLine + "-> Marking all Cached Tables for Refreshing... <-");
RefreshAllCachedTables();
Console.Write(ServerAdminPrompt);
break;
case 'v':
case 'V':
Console.WriteLine(Environment.NewLine + "-> Clearing all RDBMS Connection Pools... <-");
int NumberOfDBConnectionsAfterClearing = ClearConnectionPoolAndGetNumberOfDBConnections();
Console.WriteLine(Environment.NewLine + " Number of DB Connections after clearing all " +
"RDBMS Connection Pools: " + NumberOfDBConnectionsAfterClearing.ToString());
Console.Write(ServerAdminPrompt);
break;
case 'p':
case 'P':
#if TODORemoting
string resp = "";
if (!TRemote.ServerTimedProcessingSetup)
{
Console.WriteLine(" Server Timed Processing Status: NOT SET UP YET - processing cannot be done!");
Console.Write(ServerAdminPrompt);
break;
}
Console.WriteLine(" Server Timed Processing Status: " +
"runs daily at " + TRemote.GetTimedProcessingDailyStartTime24Hrs + ".");
Console.WriteLine(" Partner Reminders: " +
(TRemote.TimedProcessingJobEnabled("TProcessPartnerReminders") ? "On" : "Off"));
Console.WriteLine(" Automatic Intranet Export: " +
(TRemote.TimedProcessingJobEnabled("TProcessAutomatedIntranetExport") ? "On" : "Off"));
Console.WriteLine(" Data Checks: " + (TRemote.TimedProcessingJobEnabled("TProcessDataChecks") ? "On" : "Off"));
Console.WriteLine(" SMTP Server used for sending e-mails: " + TRemote.SmtpHost);
if (TRemote.TimedProcessingJobEnabled("TProcessPartnerReminders"))
{
Console.WriteLine("");
Console.WriteLine("Do you want to run Reminder Processing now?");
Console.Write("Type YES to continue, anything else to skip:");
resp = Console.ReadLine();
if (resp == "YES")
{
TRemote.PerformTimedProcessingNow("TProcessPartnerReminders");
}
}
if (TRemote.TimedProcessingJobEnabled("TProcessAutomatedIntranetExport"))
{
Console.WriteLine("");
Console.WriteLine("Do you want to run Intranet Export Processing now?");
Console.Write("Type YES to continue, anything else to skip:");
resp = Console.ReadLine();
if (resp == "YES")
{
TRemote.PerformTimedProcessingNow("TProcessAutomatedIntranetExport");
}
}
if (TRemote.TimedProcessingJobEnabled("TProcessDataChecks"))
{
Console.WriteLine("");
Console.WriteLine("Do you want to run Data Checks Processing now?");
Console.Write("Type YES to continue, anything else to skip:");
resp = Console.ReadLine();
if (resp == "YES")
{
TRemote.PerformTimedProcessingNow("TProcessDataChecks");
}
}
Console.Write(ServerAdminPrompt);
#endif
break;
case 's':
case 'S':
Console.WriteLine(Environment.NewLine + "-> Server Status <-");
Console.WriteLine();
DisplayPetraServerInformation();
Console.Write(ServerAdminPrompt);
break;
case 'q':
case 'Q':
Console.WriteLine(Environment.NewLine + "-> Queue a Client Task for a certain Client <-");
if (TRemote.GetClientList().Count > 0)
{
Console.WriteLine(TRemote.FormatClientList(false));
// ReadClientID:
Console.Write(" Enter ClientID: ");
ConsoleInput = Console.ReadLine();
EntryParsedOK = false;
try
{
ClientID = System.Int16.Parse(ConsoleInput);
EntryParsedOK = true;
}
catch (System.FormatException)
{
Console.WriteLine(" Entered ClientID is not numeric!");
}
if (!EntryParsedOK)
{
}
// goto ReadClientID;
Console.Write(" Enter Client Task Group: ");
ClientTaskGroup = Console.ReadLine();
Console.Write(" Enter Client Task Code: ");
ClientTaskCode = Console.ReadLine();
// ReadClientTaskPriority:
Console.Write(" Enter Client Task Priority: ");
ConsoleInput = Console.ReadLine();
ClientTaskPriority = -1;
try
{
ClientTaskPriority = System.Int16.Parse(ConsoleInput);
EntryParsedOK = true;
}
catch (System.FormatException)
{
Console.WriteLine(" Entered Client Task Priority is not numeric!");
EntryParsedOK = false;
}
if (!EntryParsedOK)
{
}
try
{
if (TRemote.QueueClientTask(ClientID, ClientTaskGroup, ClientTaskCode, null, null, null, null,
ClientTaskPriority))
{
TLogging.Log("Client Task queued for Client #" + ClientID.ToString() + " on admin request.");
}
else
{
TLogging.Log("Client Task for Client #" + ClientID.ToString() + " could not be queued on admin request.");
}
}
catch (Exception exp)
{
TLogging.Log(
Environment.NewLine + "Exception occured while queueing a Client Task on admin request:" +
Environment.NewLine +
exp.ToString());
}
}
else
{
Console.WriteLine(" * no Clients connected *");
}
Console.Write(ServerAdminPrompt);
break;
case 'y':
case 'Y':
Console.WriteLine("Server memory: " + TRemote.GetServerInfoMemory().ToString());
Console.Write(ServerAdminPrompt);
break;
case 'g':
case 'G':
GC.Collect();
Console.WriteLine("GarbageCollection performed. Server memory: " + TRemote.PerformGC().ToString());
Console.Write(ServerAdminPrompt);
break;
case 'a':
case 'A':
Console.Write(" Enter Administrative Message (leave blank not to send a message): ");
AdministrativeMessage = Console.ReadLine();
if (AdministrativeMessage.Trim() != String.Empty)
{
try
{
if (TRemote.QueueClientTask(-1, SharedConstants.CLIENTTASKGROUP_USERMESSAGE,
AdministrativeMessage, "MODAL", null, Catalog.GetString("Administrative Message"), null,
ClientTaskPriority))
{
TLogging.Log("Administrative message queued for all connected clients.");
}
else
{
TLogging.Log("An error occured when trying to queue an administrative message for all clients.");
}
}
catch (Exception exp)
{
TLogging.Log(
Environment.NewLine +
"Exception occured while queueing a Client Task for sending an administrative message on admin request:" +
Environment.NewLine +
exp.ToString());
}
}
Console.Write(ServerAdminPrompt);
break;
case 'o':
case 'O':
ReadLineLoopEnd = ShutDownControlled(true);
break;
case 'u':
case 'U':
ReadLineLoopEnd = ShutDown(true);
break;
case 'k':
ListGpgKeys(out GpgList);
Console.WriteLine(GpgList);
Console.Write(ServerAdminPrompt);
break;
case 'K':
ImportGpgKeys(out GpgList);
Console.WriteLine(GpgList);
Console.Write(ServerAdminPrompt);
break;
case 'x':
case 'X':
// exit loop, ServerAdmin stops then
ReadLineLoopEnd = true;
break;
default:
Console.WriteLine(Environment.NewLine + "-> Unrecognised command '" + ServerAdminCommand + "' <- (Press 'm' for menu)");
Console.Write(ServerAdminPrompt);
break;
}
// case Convert.ToChar( ServerAdminCommand )
}
// if ServerAdminCommand.Length > 0
else
{
Console.Write(ServerAdminPrompt);
}
} while (!(ReadLineLoopEnd == true));
}
/// <summary>
/// this is usually only used for remote clients; standalone clients are patched with a windows installer program
/// </summary>
private static void CheckForPatches()
{
FSplashScreen.ProgressText = "Running checks that are specific to Remote Installation...";
// todo: check whether the user has SYSADMIN rights; should not be required
// todo: check whether the user has write access to the bin directory
// check whether the user has access to the server and the Petra patches directory
if ((TClientSettings.Petra_Path_RemotePatches.Length > 0) &&
!(TClientSettings.Petra_Path_RemotePatches.ToLower().StartsWith("http://") ||
TClientSettings.Petra_Path_RemotePatches.ToLower().StartsWith("https://")) &&
!System.IO.Directory.Exists(TClientSettings.Petra_Path_RemotePatches))
{
FSplashScreen.ShowMessageBox(
String.Format(
Catalog.GetString(
"Please make sure that you have logged in to your network drive\nand can access the directory\n{0}\nIf this is the case and you still get this message,\nyou might use an IP address rather than a hostname for the server.\nPlease ask your local System Administrator for help."),
TClientSettings.Petra_Path_RemotePatches),
Catalog.GetString("Cannot check for patches"));
}
// check whether there is a patch available; if this is a remote version, try to download a patch from the server
TPatchTools patchTools = new TPatchTools(Path.GetFullPath(TClientSettings.Petra_Path_Bin + Path.DirectorySeparatorChar + ".."),
TClientSettings.Petra_Path_Bin,
TPatchTools.OPENPETRA_VERSIONPREFIX,
TClientSettings.PathTemp,
"",
TClientSettings.Petra_Path_Patches,
TClientSettings.Petra_Path_RemotePatches);
string PatchStatusMessage;
// TODO: run this only if necessary. seem adding cost centre does not update the cache?
TDataCache.ClearAllCaches();
if (patchTools.CheckForRecentPatch(false, out PatchStatusMessage))
{
// todo: display a list of all patches that will be installed? or confusing with different builds?
if (FSplashScreen.ShowMessageBox(String.Format(Catalog.GetString("There is a new patch available: {0}" +
".\r\nThe currently installed version is {1}" +
".\r\nThe patch will be installed to directory '{2}'.\r\nDo you want to install now?"),
patchTools.GetLatestPatchVersion(), patchTools.GetCurrentPatchVersion(), TClientSettings.Petra_Path_Bin),
String.Format(Catalog.GetString("Install new OpenPetra patch")), MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button1) == DialogResult.Yes)
{
// reset the caches in IsolatedStorage. This can help if things have changed drastically in the database
// TODO: run this also after the software has been reinstalled with the InnoSetup installer? Remember the current patch number in the IsolatedStorage?
TDataCache.ClearAllCaches();
// create the temp directory; using the Petra tmp directory, so that we don't need to change the drive in the batch file
string TempPath = TClientSettings.PathTemp + Path.DirectorySeparatorChar + "petrapatch";
Directory.CreateDirectory(TempPath);
// check for newer patchtool
patchTools.CopyLatestPatchProgram(TempPath);
string PatchToolExe = TempPath + Path.DirectorySeparatorChar + "Ict.Tools.PtchTool.exe";
if (!File.Exists(PatchToolExe))
{
TLogging.Log("cannot find file " + PatchToolExe);
}
// need to stop petra client, start the patch in temppath, restart Petra client
Process PatchProcess = new System.Diagnostics.Process();
PatchProcess.EnableRaisingEvents = false;
PatchProcess.StartInfo.FileName = PatchToolExe;
PatchProcess.StartInfo.Arguments = "-action:patchRemote " +
"-ClientConfig:\"" + Path.GetFullPath(
TAppSettingsManager.ConfigFileName) + "\" " +
"-OpenPetra.Path.Patches:\"" + Path.GetFullPath(
TClientSettings.Petra_Path_Bin + "/../patches30") + "\" " +
"-OpenPetra.PathTemp:\"" + Path.GetFullPath(
TClientSettings.Petra_Path_Bin + "/../tmp30") + "\" " +
"-OpenPetra.Path:\"" + Path.GetFullPath(
TClientSettings.Petra_Path_Bin + Path.DirectorySeparatorChar + "..") + "\" " +
"-OpenPetra.Path.Bin:\"" + Path.GetFullPath(
TClientSettings.Petra_Path_Bin) + "\"";
PatchProcess.Start();
// Application stops here !!!
Environment.Exit(0);
}
}
else
{
if (PatchStatusMessage != String.Empty)
{
FSplashScreen.ShowMessageBox(PatchStatusMessage, "");
}
}
}
private ProcessTemplate CreateModuleAccessPermissionCheck(ProcessTemplate ATemplate, string AConnectorClassWithNamespace, MethodDeclaration m)
{
if (m.Attributes != null)
{
foreach (AttributeSection attrSection in m.Attributes)
{
foreach (ICSharpCode.NRefactory.Ast.Attribute attr in attrSection.Attributes)
{
if (attr.Name == "RequireModulePermission")
{
ProcessTemplate snippet = ATemplate.GetSnippet("CHECKUSERMODULEPERMISSIONS");
snippet.SetCodelet("METHODNAME", m.Name);
snippet.SetCodelet("CONNECTORWITHNAMESPACE", AConnectorClassWithNamespace);
snippet.SetCodelet("LEDGERNUMBER", "");
string ParameterTypes = ";";
foreach (ParameterDeclarationExpression p in m.Parameters)
{
if (p.ParameterName == "ALedgerNumber")
{
snippet.SetCodelet("LEDGERNUMBER", ", ALedgerNumber");
}
string ParameterType = p.TypeReference.Type.Replace("&", "").Replace("System.", String.Empty);
if (ParameterType == "List")
{
ParameterType = ParameterType.Replace("List", "List[" + p.TypeReference.GenericTypes[0].ToString() + "]");
ParameterType = ParameterType.Replace("System.", String.Empty);
}
if (ParameterType == "Dictionary")
{
ParameterType = ParameterType.Replace("Dictionary", "Dictionary[" +
p.TypeReference.GenericTypes[0].ToString() + "," +
p.TypeReference.GenericTypes[1].ToString() + "]");
ParameterType = ParameterType.Replace("System.", String.Empty);
}
if (ParameterType.Contains("."))
{
ParameterType = ParameterType.Substring(ParameterType.LastIndexOf(".") + 1);
}
if (p.TypeReference.Type == "System.Nullable")
{
ParameterType = ParameterType.Replace("Nullable", "Nullable[" + p.TypeReference.GenericTypes[0].ToString() + "]");
}
if (p.TypeReference.IsArrayType)
{
ParameterType += ".ARRAY";
}
ParameterType = ParameterType.Replace("Boolean", "bool");
ParameterType = ParameterType.Replace("Int32", "int");
ParameterType = ParameterType.Replace("Int64", "long");
ParameterTypes += ParameterType + ";";
}
ParameterTypes = ParameterTypes.ToUpper();
snippet.SetCodelet("PARAMETERTYPES", ParameterTypes);
return(snippet);
}
}
}
}
TLogging.Log("Warning !!! Missing module access permissions for " + AConnectorClassWithNamespace + "::" + m.Name);
return(new ProcessTemplate());
}
public static SelectConferenceTDS GetConferences(String AConferenceName, String APrefix)
{
SelectConferenceTDS ResultTable = new SelectConferenceTDS();
PcConferenceTable ConferenceTable = new PcConferenceTable();
PcConferenceRow TemplateRow = (PcConferenceRow)ConferenceTable.NewRow();
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
if (APrefix == "*")
{
APrefix = "";
}
if (AConferenceName == "*")
{
AConferenceName = "";
}
else if (AConferenceName.EndsWith("*"))
{
AConferenceName = AConferenceName.Substring(0, AConferenceName.Length - 1);
}
TLogging.LogAtLevel(9, "TConferenceOptions.GetConferences called!");
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
/* Load data */
if (APrefix.Length > 0)
{
APrefix = APrefix.Replace('*', '%') + "%";
TemplateRow.OutreachPrefix = APrefix;
StringCollection Operators = new StringCollection();
Operators.Add("LIKE");
ConferenceTable = PcConferenceAccess.LoadUsingTemplate(TemplateRow, Operators, null, ReadTransaction);
}
else
{
ConferenceTable = PcConferenceAccess.LoadAll(ReadTransaction);
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TConferenceOptions.GetConferences: committed own transaction.");
}
}
String ShortName;
TPartnerClass PartnerClass;
foreach (PcConferenceRow ConferenceRow in ConferenceTable.Rows)
{
TPartnerServerLookups.GetPartnerShortName(ConferenceRow.ConferenceKey, out ShortName, out PartnerClass);
if ((AConferenceName.Length > 0) &&
(!ShortName.StartsWith(AConferenceName, true, null)))
{
continue;
}
ResultTable.PcConference.ImportRow(ConferenceRow);
DataRow NewRow = ResultTable.PPartner.NewRow();
NewRow[PPartnerTable.GetPartnerShortNameDBName()] = ShortName;
NewRow[PPartnerTable.GetPartnerKeyDBName()] = ConferenceRow.ConferenceKey;
ResultTable.PPartner.Rows.Add(NewRow);
}
return(ResultTable);
}
/// <summary>
/// static run function
/// </summary>
public static bool Run()
{
try
{
// check command line
new TAppSettingsManager(false);
string TempPath = TAppSettingsManager.GetValue("OpenPetra.PathTemp");
new TLogging(TempPath + Path.DirectorySeparatorChar + "PetraPatch.log");
// Catalog.Init();
String action = TAppSettingsManager.GetValue("action", false);
if (action.Equals("create"))
{
PatchCreation.CreateDiff(TempPath,
TAppSettingsManager.GetValue("deliverypath"),
TAppSettingsManager.GetValue("appname"),
TAppSettingsManager.GetValue("zipname"),
TAppSettingsManager.GetValue("oldversion"),
TAppSettingsManager.GetValue("newversion"));
}
else if (action.Equals("preparePatch"))
{
// to be called before installing the patch;
// will only copy the files if there is a new patch available
TPetraPatchTools patchTools = new TPetraPatchTools(
TAppSettingsManager.GetValue("OpenPetra.Path"),
TAppSettingsManager.GetValue("OpenPetra.Path") + Path.DirectorySeparatorChar + "bin" + TPatchTools.OPENPETRA_VERSIONPREFIX,
TempPath,
"",
TAppSettingsManager.GetValue("OpenPetra.Path.Patches"),
"");
if (patchTools.CheckForRecentPatch())
{
patchTools.CopyLatestPatchProgram(TAppSettingsManager.GetValue("OpenPetra.PathTemp"));
}
else
{
System.Console.WriteLine(Catalog.GetString("There is no new patch to be installed."));
return(false);
}
}
else if (action.Equals("patchRemote"))
{
// basically the same as patchFiles, but will use the status window.
PatchApplication.PatchRemoteInstallation();
}
else if (action.Equals("patchFiles"))
{
// need to call first preparePatch;
// and then run the patch from TmpPatchPath so that the files can be overwritten
// this will patch the application files
TPetraPatchTools patchTools = new TPetraPatchTools(TAppSettingsManager.GetValue("OpenPetra.Path"),
TAppSettingsManager.GetValue("OpenPetra.Path") + Path.DirectorySeparatorChar + "bin" + TPatchTools.OPENPETRA_VERSIONPREFIX,
TempPath,
TAppSettingsManager.GetValue("OpenPetra.Path.Dat"),
TAppSettingsManager.GetValue("OpenPetra.Path.Patches"),
"");
if (patchTools.CheckForRecentPatch())
{
if (!patchTools.PatchTheFiles())
{
System.Console.WriteLine(Catalog.GetString("There was a problem installing the patch."));
return(false);
}
}
else
{
if ((!patchTools.GetCurrentPatchVersion().Equals(patchTools.GetLatestPatchVersion())))
{
System.Console.WriteLine(Catalog.GetString(
"You don't have all patches that are necessary for patching to the latest patch."));
return(false);
}
else
{
System.Console.WriteLine(Catalog.GetString("There is no new patch to be installed."));
return(false);
}
}
}
else
{
System.Console.WriteLine(
"patch creation: patchtool -action:create -OpenPetra.PathTemp:u:/tmp/patch -deliverypath:u:/delivery -oldversion:0.0.8-0 -newversion:0.0.10-0");
System.Console.WriteLine(
"patch application: patchtool -action:patchFiles -C:\"C:\\Program Files (x86)\\OpenPetra\\etc30\\PetraClientRemote.config\" -OpenPetra.Path:\"C:\\Program Files (x86)\\OpenPetra\"");
//-OpenPetra.PathTemp:u:/tmp/patch -diffzip:u:/tmp/patch/Patch2.2.3-5_2.2.4-3.zip -apppath:c:/Programme/OpenPetra.org -datpath:c:/Programme/OpenPetra.org/data30");
System.Console.WriteLine(
"patch application: patchtool -action:patchRemote -C:\"C:\\Program Files (x86)\\OpenPetra\\etc30\\PetraClientRemote.config\" -OpenPetra.Path:\"C:\\Program Files (x86)\\OpenPetra\"");
return(false);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
TLogging.Log(e.Message, TLoggingType.ToLogfile);
TLogging.Log(e.StackTrace, TLoggingType.ToLogfile);
return(false);
}
return(true);
}
/// main method
public static void Main(string[] args)
{
TPetraServerConnector.Connect("../../etc/TestServer.config");
try
{
string OutputPath = TAppSettingsManager.GetValue("OutputPath", "../../delivery/GDPdU/data");
if (!Directory.Exists(OutputPath))
{
Directory.CreateDirectory(OutputPath);
}
string SummaryCostCentres = TAppSettingsManager.GetValue("SummaryCostCentres", "4300S");
string IgnoreCostCentres = TAppSettingsManager.GetValue("IgnoreCostCentres", "xyz");
string IgnoreAccounts = TAppSettingsManager.GetValue("IgnoreAccounts", "4300S,GIFT");
string IncludeAccounts = TAppSettingsManager.GetValue("IncludeAccounts", "4310");
string FinancialYears = TAppSettingsManager.GetValue("FinancialYearNumber", "0");
string IgnoreTransactionsByReference = TAppSettingsManager.GetValue("IgnoreReference", "L1,L2,L3,L4,L5,L6,L7,L8,L9,L10,L11,L12");
int FirstFinancialYear = TAppSettingsManager.GetInt32("FirstFinancialYear", DateTime.Now.Year);
int LedgerNumber = TAppSettingsManager.GetInt32("LedgerNumber", 43);
char CSVSeparator = TAppSettingsManager.GetValue("CSVSeparator", ";")[0];
string NewLine = "\r\n";
string culture = TAppSettingsManager.GetValue("culture", "de-DE");
string ReportingCostCentres =
TFinanceReportingWebConnector.GetReportingCostCentres(LedgerNumber, SummaryCostCentres, IgnoreCostCentres);
if (TAppSettingsManager.GetBoolean("IgnorePersonCostCentres", true))
{
ReportingCostCentres = TGDPdUExportAccountsAndCostCentres.WithoutPersonCostCentres(LedgerNumber, ReportingCostCentres);
}
IgnoreAccounts =
TFinanceReportingWebConnector.GetReportingAccounts(LedgerNumber, IgnoreAccounts, IncludeAccounts);
// set decimal separator, and thousands separator
Ict.Common.Catalog.SetCulture(culture);
List <string> CostCentresInvolved = new List <string>();
List <string> AccountsInvolved = new List <string>();
SortedList <string, string> TaxAnalysisAttributes = TGDPdUExportTransactions.GetTaxAnalysisAttributes();
foreach (string FinancialYearString in FinancialYears.Split(new char[] { ',' }))
{
Int32 FinancialYear = Convert.ToInt32(FinancialYearString);
string OutputPathForYear = Path.Combine(OutputPath, (FirstFinancialYear + FinancialYear).ToString());
if (!Directory.Exists(OutputPathForYear))
{
Directory.CreateDirectory(OutputPathForYear);
}
TGDPdUExportTransactions.ExportGLTransactions(OutputPathForYear,
CSVSeparator,
NewLine,
LedgerNumber,
FinancialYear,
ReportingCostCentres,
IgnoreAccounts,
IgnoreTransactionsByReference,
TaxAnalysisAttributes,
ref CostCentresInvolved,
ref AccountsInvolved);
TGDPdUExportBalances.ExportGLBalances(OutputPathForYear, CSVSeparator, NewLine, LedgerNumber,
FinancialYear, ReportingCostCentres,
IgnoreAccounts);
TGDPdUExportAccountsPayable.Export(OutputPathForYear, CSVSeparator, NewLine, LedgerNumber, FinancialYear, ReportingCostCentres);
TGDPdUExportParticipants.Export(OutputPathForYear, CSVSeparator, NewLine,
LedgerNumber, FinancialYear,
ReportingCostCentres);
}
TGDPdUExportAccountsAndCostCentres.ExportCostCentres(OutputPath, CSVSeparator, NewLine, LedgerNumber,
CostCentresInvolved);
TGDPdUExportAccountsAndCostCentres.ExportAccounts(OutputPath, CSVSeparator, NewLine, LedgerNumber,
AccountsInvolved);
TGDPdUExportTransactions.ExportTaxAnalysisAttributes(OutputPath, CSVSeparator, NewLine, TaxAnalysisAttributes);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
if (TAppSettingsManager.GetValue("interactive", "true") == "true")
{
Console.WriteLine("Please press Enter to continue...");
Console.ReadLine();
}
}
/// <summary>
/// run the report
/// </summary>
private static void Run(string ASessionID, string AReportID, TRptDataCalculator ADatacalculator, TParameterList AParameterList)
{
// need to initialize the database session
TSession.InitThread(ASessionID);
TDataBase db = DBAccess.Connect("TReportGeneratorWebConnector");
TDBTransaction Transaction = new TDBTransaction();
bool Success = false;
bool Submit = true;
string HTMLOutput = String.Empty;
HtmlDocument HTMLDocument = new HtmlDocument();
string ErrorMessage = String.Empty;
try
{
db.ReadTransaction(ref Transaction,
delegate
{
Exception myException = null;
if (ADatacalculator.GenerateResult(ref AParameterList, ref HTMLOutput, out HTMLDocument, ref ErrorMessage, ref myException, Transaction))
{
Success = true;
}
else
{
TLogging.Log(ErrorMessage);
}
});
}
catch (Exception Exc)
{
TLogging.Log("Problem calculating report: " + Exc.ToString());
TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile);
Success = false;
ErrorMessage = Exc.Message;
}
/*
* if (TDBExceptionHelper.IsTransactionSerialisationException(FException))
* {
* // do nothing - we want this exception to bubble up
* }
* else if (FException is Exception && FException.InnerException is EOPDBException)
* {
* EOPDBException DbExc = (EOPDBException)FException.InnerException;
*
* if (DbExc.InnerException is Exception)
* {
* if (DbExc.InnerException is PostgresException)
* {
* PostgresException PgExc = (PostgresException)DbExc.InnerException;
*
* if (PgExc.SqlState == "57014") // SQL statement timeout problem
* {
* FErrorMessage = Catalog.GetString(
* "Error - Database took too long to respond. Try different parameters to return fewer results.");
* }
* }
* else
* {
* FErrorMessage = DbExc.InnerException.Message;
* }
*
* FException = null;
* }
* }
*/
try
{
// store the report result
db.WriteTransaction(ref Transaction,
ref Submit,
delegate
{
// delete report results that are expired.
string sql = "DELETE FROM PUB_s_report_result WHERE s_valid_until_d < NOW()";
db.ExecuteNonQuery(sql, Transaction);
// TODO: only keep maximum of 10 report results per user (s_created_by_c)
// store success, store parameter list, store html document
SReportResultTable table = new SReportResultTable();
SReportResultRow row = table.NewRowTyped();
row.ReportId = AReportID;
row.SessionId = TSession.GetSessionID();
row.ValidUntil = DateTime.Now.AddHours(12);
row.ParameterList = AParameterList.ToJson();
row.ResultHtml = HTMLOutput;
row.Success = Success;
row.ErrorMessage = ErrorMessage;
table.Rows.Add(row);
SReportResultAccess.SubmitChanges(table, Transaction);
Submit = true;
});
}
catch (Exception Exc)
{
TLogging.Log("Problem storing report result: " + Exc.ToString());
TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile);
Success = false;
ErrorMessage = Exc.Message;
}
db.CloseDBConnection();
TProgressTracker.FinishJob(AReportID);
}
public static bool GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable)
{
// TODO: check access permissions for the current user
bool NewTransaction = false;
TDBTransaction ReadTransaction;
TTypedDataTable tempTable = null;
try
{
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// TODO: auto generate
if (ATablename == AApSupplierTable.GetTableDBName())
{
tempTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == AApDocumentTable.GetTableDBName())
{
tempTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == ATransactionTypeTable.GetTableDBName())
{
tempTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
tempTable = ACurrencyAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
tempTable = ADailyExchangeRateAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
tempTable = ACorporateExchangeRateAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
tempTable = ACurrencyLanguageAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
tempTable = AFeesPayableAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
tempTable = AFeesReceivableAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
tempTable = AAnalysisTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
tempTable = AGiftBatchAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
tempTable = AJournalAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
tempTable = ALedgerAccess.LoadAll(ReadTransaction);
}
else if (ATablename == MExtractMasterTable.GetTableDBName())
{
if (ASearchCriteria == null)
{
tempTable = MExtractMasterAccess.LoadAll(ReadTransaction);
}
else
{
tempTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
}
else if (ATablename == MExtractTable.GetTableDBName())
{
// it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
if (ASearchCriteria != null)
{
tempTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
tempTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
tempTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
tempTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
tempTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
tempTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
tempTable = PInternationalPostalTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
tempTable = PtApplicationTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
tempTable = PMailingAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
tempTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
tempTable = PmDocumentTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
tempTable = SGroupAccess.LoadAll(ReadTransaction);
}
else
{
throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
}
}
catch (Exception Exp)
{
DBAccess.GDBAccessObj.RollbackTransaction();
TLogging.Log("TCommonDataReader.GetData exception: " + Exp.ToString(), TLoggingType.ToLogfile);
TLogging.Log(Exp.StackTrace, TLoggingType.ToLogfile);
throw;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TCommonDataReader.GetData: committed own transaction.");
}
}
// Accept row changes here so that the Client gets 'unmodified' rows
tempTable.AcceptChanges();
// return the table
AResultTable = tempTable;
return(true);
}
public static TSubmitChangesResult SaveData(string ATablename,
ref TTypedDataTable ASubmitTable,
out TVerificationResultCollection AVerificationResult)
{
TDBTransaction SubmitChangesTransaction;
AVerificationResult = null;
// TODO: check write permissions
if (ASubmitTable != null)
{
AVerificationResult = new TVerificationResultCollection();
SubmitChangesTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
if (ATablename == AAccountingPeriodTable.GetTableDBName())
{
AAccountingPeriodAccess.SubmitChanges((AAccountingPeriodTable)ASubmitTable, SubmitChangesTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
ACurrencyAccess.SubmitChanges((ACurrencyTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
ADailyExchangeRateAccess.SubmitChanges((ADailyExchangeRateTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
ACorporateExchangeRateAccess.SubmitChanges((ACorporateExchangeRateTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
ACurrencyLanguageAccess.SubmitChanges((ACurrencyLanguageTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
AFeesPayableAccess.SubmitChanges((AFeesPayableTable)ASubmitTable, SubmitChangesTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesPayableList.ToString());
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
AFeesReceivableAccess.SubmitChanges((AFeesReceivableTable)ASubmitTable, SubmitChangesTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesReceivableList.ToString());
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the gift batch screens, which use a TDS
AGiftBatchAccess.SubmitChanges((AGiftBatchTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
AJournalAccess.SubmitChanges((AJournalTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Testing - please do not remove
ALedgerAccess.SubmitChanges((ALedgerTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
AAnalysisTypeAccess.SubmitChanges((AAnalysisTypeTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ASuspenseAccountTable.GetTableDBName())
{
ASuspenseAccountAccess.SubmitChanges((ASuspenseAccountTable)ASubmitTable, SubmitChangesTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.SuspenseAccountList.ToString());
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
PcAttendeeAccess.SubmitChanges((PcAttendeeTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcConferenceTable.GetTableDBName())
{
PcConferenceAccess.SubmitChanges((PcConferenceTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
PcConferenceCostAccess.SubmitChanges((PcConferenceCostTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
PcEarlyLateAccess.SubmitChanges((PcEarlyLateTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
PcSupplementAccess.SubmitChanges((PcSupplementTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
PcDiscountAccess.SubmitChanges((PcDiscountTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
ValidateInternationalPostalType(ref AVerificationResult, ASubmitTable);
ValidateInternationalPostalTypeManual(ref AVerificationResult, ASubmitTable);
if (TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult))
{
PInternationalPostalTypeAccess.SubmitChanges((PInternationalPostalTypeTable)ASubmitTable, SubmitChangesTransaction);
}
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
PtApplicationTypeAccess.SubmitChanges((PtApplicationTypeTable)ASubmitTable, SubmitChangesTransaction);
// mark dependent lists for needing to be refreshed since there was a change in base list
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.EventApplicationTypeList.ToString());
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.FieldApplicationTypeList.ToString());
}
else if (ATablename == PMailingTable.GetTableDBName())
{
PMailingAccess.SubmitChanges((PMailingTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
PPartnerGiftDestinationAccess.SubmitChanges((PPartnerGiftDestinationTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
PmDocumentTypeAccess.SubmitChanges((PmDocumentTypeTable)ASubmitTable, SubmitChangesTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
SGroupAccess.SubmitChanges((SGroupTable)ASubmitTable, SubmitChangesTransaction);
}
else
{
throw new EOPAppException("TCommonDataReader.SaveData: unknown table '" + ATablename + "'");
}
DBAccess.GDBAccessObj.CommitTransaction();
}
catch (Exception Exc)
{
TLogging.Log("after submitchanges: exception " + Exc.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
}
if ((AVerificationResult != null) &&
(AVerificationResult.Count > 0))
{
// Downgrade TScreenVerificationResults to TVerificationResults in order to allow
// Serialisation (needed for .NET Remoting).
TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);
return(AVerificationResult.HasCriticalErrors ? TSubmitChangesResult.scrError : TSubmitChangesResult.scrOK);
}
return(TSubmitChangesResult.scrOK);
}
/// <summary>
/// Creates a new Recurring gift the new child
/// serverMSponsorship.asmx/TSponsorshipWebConnector_MaintainSponsorshipRecurringGifts
/// </summary>
private void CreateNewSponsorship()
{
// SponsorshipFindTDSSearchResultTable Result = TSponsorshipWebConnector.FindChildren("", "", "", "", "", "");
TLogging.Log("Created new Sponsorship:");
}
public static bool GetEarliestAndLatestDate(Int64 AConferenceKey, out DateTime AEarliestArrivalDate,
out DateTime ALatestDepartureDate, out DateTime AStartDate, out DateTime AEndDate)
{
AEarliestArrivalDate = DateTime.Today;
ALatestDepartureDate = DateTime.Today;
AStartDate = DateTime.Today;
AEndDate = DateTime.Today;
PmShortTermApplicationTable ShortTermerTable;
PcConferenceTable ConferenceTable;
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
TLogging.LogAtLevel(9, "TConferenceOptions.GetEarliestAndLatestDates called!");
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
/* Load data */
if (AConferenceKey == -1)
{
ShortTermerTable = PmShortTermApplicationAccess.LoadAll(ReadTransaction);
ConferenceTable = PcConferenceAccess.LoadAll(ReadTransaction);
}
else
{
ShortTermerTable = PmShortTermApplicationAccess.LoadViaPUnitStConfirmedOption(AConferenceKey, ReadTransaction);
ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, ReadTransaction);
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TConferenceOptions.GetEarliestAndLatestDates: committed own transaction.");
}
}
DateTime TmpEarliestArrivalTime = DateTime.MaxValue;
DateTime TmpLatestDepartureTime = DateTime.MinValue;
DateTime TmpStartTime = DateTime.MaxValue;
DateTime TmpEndTime = DateTime.MinValue;
foreach (PmShortTermApplicationRow ShortTermerRow in ShortTermerTable.Rows)
{
if ((!ShortTermerRow.IsArrivalNull()) &&
(ShortTermerRow.Arrival < TmpEarliestArrivalTime))
{
TmpEarliestArrivalTime = ShortTermerRow.Arrival.Value;
}
if ((!ShortTermerRow.IsDepartureNull()) &&
(ShortTermerRow.Departure > TmpLatestDepartureTime))
{
TmpLatestDepartureTime = ShortTermerRow.Departure.Value;
}
}
foreach (PcConferenceRow ConferenceRow in ConferenceTable.Rows)
{
if ((!ConferenceRow.IsStartNull()) &&
(ConferenceRow.Start.Value < TmpStartTime))
{
TmpStartTime = ConferenceRow.Start.Value;
}
if ((!ConferenceRow.IsEndNull()) &&
(ConferenceRow.End.Value > TmpEndTime))
{
TmpEndTime = ConferenceRow.End.Value;
}
}
if (TmpEarliestArrivalTime != DateTime.MaxValue)
{
AEarliestArrivalDate = TmpEarliestArrivalTime;
}
if (TmpLatestDepartureTime != DateTime.MinValue)
{
ALatestDepartureDate = TmpLatestDepartureTime;
}
if (TmpStartTime != DateTime.MaxValue)
{
AStartDate = TmpStartTime;
}
if (TmpEndTime != DateTime.MinValue)
{
AEndDate = TmpEndTime;
}
return(true);
}
public void Init()
{
TPetraServerConnector.Connect("../../etc/TestServer.config");
FLedgerNumber = TAppSettingsManager.GetInt32("LedgerNumber", 43);
TLogging.Log("Selected Ledger Number = " + FLedgerNumber);
}
public static bool GetFieldUnits(Int64 AConferenceKey, TUnitTypeEnum AFieldTypes, out DataTable AFieldsTable, out String AConferencePrefix)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
AFieldsTable = new DataTable("Field Units");
AConferencePrefix = "";
PUnitTable UnitTable;
DataTable TmpTable;
PmShortTermApplicationTable ShortTermerTable = new PmShortTermApplicationTable();
AFieldsTable.Columns.Add("Selection", System.Type.GetType("System.Boolean"));
AFieldsTable.Columns.Add("Unit_Key", System.Type.GetType("System.Int64"));
AFieldsTable.Columns.Add("Unit_Name", System.Type.GetType("System.String"));
AFieldsTable.Columns.Add("Outreach_Code", System.Type.GetType("System.String"));
AFieldsTable.Columns.Add("Used_in_Conference", System.Type.GetType("System.Boolean"));
AConferencePrefix = TConferenceOptions.GetConferencePrefix(AConferenceKey);
switch (AFieldTypes)
{
case TUnitTypeEnum.utSendingFields:
return(TConferenceOptions.GetSendingFields(AConferenceKey, ref AFieldsTable));
case TUnitTypeEnum.utReceivingFields:
return(TConferenceOptions.GetReceivingFields(AConferenceKey, ref AFieldsTable));
case TUnitTypeEnum.utOutreachOptions:
if (TConferenceOptions.GetOutreachOptions(AConferenceKey, out TmpTable))
{
foreach (DataRow Row in TmpTable.Rows)
{
DataRow NewRow = AFieldsTable.NewRow();
NewRow[SELECTION] = false;
NewRow[UNIT_KEY] = Row["Partner Key"];
NewRow[UNIT_NAME] = Row["Unit Name"];
NewRow[CAMPAIGN_CODE] = Row["Outreach Code"];
NewRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(NewRow);
}
return(true);
}
return(false);
default:
break;
}
TLogging.LogAtLevel(9, "TConferenceOptions.GetFieldUnits called!");
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
StringCollection FieldList = new StringCollection();
FieldList.Add(PmShortTermApplicationTable.GetStFieldChargedDBName());
FieldList.Add(PmShortTermApplicationTable.GetConfirmedOptionCodeDBName());
FieldList.Add(PmShortTermApplicationTable.GetRegistrationOfficeDBName());
ShortTermerTable = PmShortTermApplicationAccess.LoadAll(FieldList, ReadTransaction);
long LastUnitKey = 0;
long NewUnitKey = 0;
bool IsUsedInOneConference = false;
String ConfirmedOptionCode = "";
System.Type StringType = System.Type.GetType("System.String");
String SearchedColumnName = "";
switch (AFieldTypes)
{
case TUnitTypeEnum.utChargedFields:
SearchedColumnName = PmShortTermApplicationTable.GetStFieldChargedDBName();
break;
case TUnitTypeEnum.utRegisteringFields:
SearchedColumnName = PmShortTermApplicationTable.GetRegistrationOfficeDBName();
break;
default:
break;
}
foreach (DataRow ShortTermerRow in ShortTermerTable.Select("", SearchedColumnName))
{
if ((ShortTermerRow[SearchedColumnName] != null) &&
(ShortTermerRow[SearchedColumnName].ToString().Length > 0))
{
NewUnitKey = (long)ShortTermerRow[SearchedColumnName];
}
else
{
continue;
}
if (LastUnitKey != NewUnitKey)
{
if ((AFieldsTable.Rows.Count > 0) &&
(IsUsedInOneConference))
{
AFieldsTable.Rows[AFieldsTable.Rows.Count - 1][USED_IN_CONFERENCE] = true;
}
IsUsedInOneConference = false;
}
// We have to check from every shorttermer if the charged field is used
// in this conference
if (IsUsedInOneConference)
{
continue;
}
if (ShortTermerRow[PmShortTermApplicationTable.GetConfirmedOptionCodeDBName()].GetType() == StringType)
{
ConfirmedOptionCode = (string)ShortTermerRow[PmShortTermApplicationTable.GetConfirmedOptionCodeDBName()];
}
else
{
ConfirmedOptionCode = "";
}
if (ConfirmedOptionCode.StartsWith(AConferencePrefix))
{
IsUsedInOneConference = true;
}
if (LastUnitKey == NewUnitKey)
{
continue;
}
UnitTable = PUnitAccess.LoadByPrimaryKey(NewUnitKey, ReadTransaction);
if (UnitTable.Rows.Count > 0)
{
DataRow ResultRow = AFieldsTable.NewRow();
ResultRow[SELECTION] = false;
ResultRow[UNIT_KEY] = NewUnitKey;
ResultRow[UNIT_NAME] = UnitTable[0][PUnitTable.GetUnitNameDBName()];
ResultRow[CAMPAIGN_CODE] = ConfirmedOptionCode;
ResultRow[USED_IN_CONFERENCE] = IsUsedInOneConference;
AFieldsTable.Rows.Add(ResultRow);
LastUnitKey = NewUnitKey;
}
}
// Check for the previous entry the "IsUsedInConference" field
if ((AFieldsTable.Rows.Count > 0) &&
(IsUsedInOneConference))
{
AFieldsTable.Rows[AFieldsTable.Rows.Count - 1][USED_IN_CONFERENCE] = true;
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TConferenceOptions.GetFieldUnits: committed own transaction.");
}
}
return(true);
}
/// <summary>
/// Connects to the PetraServer and provides a menu with a number of functions,
/// including stopping the PetraServer.
/// </summary>
/// <returns>void</returns>
public static void Start()
{
String ClientID;
bool ExitError = false;
Boolean SilentSysadm;
SilentSysadm = false;
try
{
new TLogging();
new TAppSettingsManager();
SilentSysadm = true;
if (TAppSettingsManager.HasValue("DebugLevel"))
{
TLogging.DebugLevel = TAppSettingsManager.GetInt32("DebugLevel");
}
if ((!TAppSettingsManager.HasValue("Command") || (TAppSettingsManager.GetValue("Command") == "Stop")))
{
SilentSysadm = false;
}
if (TAppSettingsManager.HasValue("ServerAdmin.LogFile"))
{
new TLogging(TAppSettingsManager.GetValue("ServerAdmin.LogFile"));
}
if ((!SilentSysadm))
{
string ExePath = TAppSettingsManager.ApplicationDirectory + Path.DirectorySeparatorChar +
"PetraServerAdminConsole.exe";
Console.WriteLine();
TLogging.Log(
"PETRAServerADMIN " + System.Reflection.Assembly.GetEntryAssembly().GetName().Version.ToString() + ' ' + "Build " +
System.IO.File.GetLastWriteTime(ExePath).ToString() + " (OS: " +
CommonTypes.ExecutingOSEnumToString(Utilities.DetermineExecutingOS()) + ')');
TLogging.Log(Catalog.GetString("Configuration file: " + TAppSettingsManager.ConfigFileName));
// System.Reflection.Assembly.GetEntryAssembly.FullName does not return the file path
TLogging.Log("Connecting to PETRAServer...");
Console.WriteLine();
}
// Instantiate a remote object, which provides access to the server
THttpConnector.ServerAdminSecurityToken = NewSecurityToken();
THttpConnector.InitConnection(TAppSettingsManager.GetValue("OpenPetra.HTTPServer"));
TRemote = new TMServerAdminNamespace().WebConnectors;
try {
TRemote.LoginServerAdmin();
}
catch (Exception)
{
if (TAppSettingsManager.HasValue("Command") && (TAppSettingsManager.GetValue("Command") == "Stop"))
{
// don't exit with error code because the server is not running anyway
if ((!SilentSysadm))
{
Console.WriteLine("The server is not running, so it cannot be stopped");
}
System.Environment.Exit(0);
}
else
{
throw;
}
}
if (TAppSettingsManager.HasValue("Command"))
{
if (TAppSettingsManager.GetValue("Command") == "Stop")
{
ShutDown(false);
}
else if (TAppSettingsManager.GetValue("Command") == "StopAndCloseClients")
{
ShutDownControlled(false);
}
else if (TAppSettingsManager.GetValue("Command") == "ConnectedClients")
{
System.Console.WriteLine(TRemote.FormatClientList(false));
}
else if (TAppSettingsManager.GetValue("Command") == "ConnectedClientsSysadm")
{
System.Console.WriteLine(TRemote.FormatClientListSysadm(false));
}
else if (TAppSettingsManager.GetValue("Command") == "DisconnectedClients")
{
System.Console.WriteLine(TRemote.FormatClientList(true));
}
else if (TAppSettingsManager.GetValue("Command") == "DisconnectClient")
{
ClientID = TAppSettingsManager.GetValue("ClientID");
DisconnectClient(ClientID);
}
else if (TAppSettingsManager.GetValue("Command") == "LoadYmlGz")
{
RestoreDatabase(TAppSettingsManager.GetValue("YmlGzFile"));
}
else if (TAppSettingsManager.GetValue("Command") == "SaveYmlGz")
{
if (!ExportDatabase(TAppSettingsManager.GetValue("YmlGzFile")))
{
ExitError = true;
}
}
else if (TAppSettingsManager.GetValue("Command") == "UpgradeDatabase")
{
UpgradeDatabase();
}
else if (TAppSettingsManager.GetValue("Command") == "RefreshAllCachedTables")
{
RefreshAllCachedTables();
}
else if (TAppSettingsManager.GetValue("Command") == "SetPassword")
{
SetPassword(TAppSettingsManager.GetValue("UserID"), TAppSettingsManager.GetValue("NewPassword"));
}
else if (TAppSettingsManager.GetValue("Command") == "AddUser")
{
AddUser(TAppSettingsManager.GetValue("UserId"));
}
else if (TAppSettingsManager.GetValue("Command") == "Menu")
{
Menu();
}
}
else
{
Menu();
}
// All exceptions that are raised are handled here
// Note: ServerAdmin stops after handling these exceptions!!!
}
catch (Exception exp)
{
if ((!SilentSysadm))
{
Console.WriteLine("Exception occured while connecting/communicating to PETRAServer: " + exp.ToString());
}
ExitError = true;
}
ClearSecurityToken();
if (ExitError)
{
System.Environment.Exit(-1);
}
// THE VERY END OF SERVERADMIN :(
}
private static bool GetAllSendingFields(long AConferenceKey, ref DataTable AFieldsTable)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
PUnitTable UnitTable;
PcAttendeeTable AttendeeTable = new PcAttendeeTable();
TLogging.LogAtLevel(9, "TConferenceOptions.GetAllSendingFields called!");
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
StringCollection FieldList = new StringCollection();
FieldList.Add(PcAttendeeTable.GetHomeOfficeKeyDBName());
AttendeeTable = PcAttendeeAccess.LoadAll(FieldList, ReadTransaction);
long LastUnitKey = 0;
long NewUnitKey = 0;
String HomeOfficeColumnName = PcAttendeeTable.GetHomeOfficeKeyDBName();
foreach (DataRow AttendeeRow in AttendeeTable.Select("", HomeOfficeColumnName))
{
if (AttendeeRow[HomeOfficeColumnName] != null)
{
NewUnitKey = (long)AttendeeRow[HomeOfficeColumnName];
}
else
{
continue;
}
if (LastUnitKey == NewUnitKey)
{
continue;
}
UnitTable = PUnitAccess.LoadByPrimaryKey(NewUnitKey, ReadTransaction);
if (UnitTable.Rows.Count > 0)
{
DataRow ResultRow = AFieldsTable.NewRow();
ResultRow[SELECTION] = false;
ResultRow[UNIT_KEY] = NewUnitKey;
ResultRow[UNIT_NAME] = UnitTable[0][PUnitTable.GetUnitNameDBName()];
ResultRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(ResultRow);
LastUnitKey = NewUnitKey;
}
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TConferenceOptions.GetAllSendingFields: committed own transaction.");
}
}
return(true);
}
/// <summary>
/// start the client
/// </summary>
public static void StartUp()
{
// for the moment default to english, because translations are not fully supported, and the layout does not adjust
string UsersLanguageCode = "en-EN";
string UsersCultureCode = CultureInfo.CurrentCulture.Name;
try
{
new TAppSettingsManager();
ExceptionHandling.GApplicationShutdownCallback = Shutdown.SaveUserDefaultsAndDisconnectAndStop;
TLogging Logger = new TLogging(TClientSettings.GetPathLog() + Path.DirectorySeparatorChar + "PetraClient.log");
String LogFileMsg;
if (!Logger.CanWriteLogFile(out LogFileMsg))
{
MessageBox.Show(LogFileMsg, Catalog.GetString("Failed to open logfile"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
Catalog.Init();
#if DEBUG
TApplicationVCSInfo.DetermineApplicationVCSInfo();
#endif
UserInfo.RunningOnClientSide = true;
// Register Types that can throw Error Codes (Ict.Common.CommonErrorCodes is automatically added)
ErrorCodeInventory.RegisteredTypes.Add(new Ict.Petra.Shared.PetraErrorCodes().GetType());
ErrorCodeInventory.RegisteredTypes.Add(new Ict.Common.Verification.TStringChecks().GetType());
// Initialize the client
TClientTasksQueue.ClientTasksInstanceType = typeof(TClientTaskInstance);
TConnectionManagementBase.GConnectionManagement = new TConnectionManagement();
new TCallForwarding();
// System.Windows.Forms.MessageBox.Show(ErrorCodes.GetErrorInfo("GENC.00001V").ShortDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00001V").FullDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00001V").Category.ToString("G") + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00001V").HelpID);
// System.Windows.Forms.MessageBox.Show(ErrorCodes.GetErrorInfo("GENC.00002V").ShortDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00002V").FullDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00002V").ErrorMessageText + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00002V").ErrorMessageTitle + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00002V").Category.ToString("G") + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00002V").HelpID);
// System.Windows.Forms.MessageBox.Show(ErrorCodes.GetErrorInfo("GEN.00004E").ShortDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GEN.00004E").FullDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GEN.00004E").Category.ToString("G") + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GEN.00004E").HelpID);
// System.Windows.Forms.MessageBox.Show(ErrorCodes.GetErrorInfo("PARTN.00005V").ShortDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("PARTN.00005V").FullDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("PARTN.00005V").Category.ToString("G") + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("PARTN.00005V").HelpID);
// System.Windows.Forms.MessageBox.Show(ErrorCodes.GetErrorInfo("GENC.00017V").ShortDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00017V").FullDescription + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00017V").Category.ToString("G") + Environment.NewLine + Environment.NewLine +
// ErrorCodes.GetErrorInfo("GENC.00017V").HelpID);
//MessageBox.Show(ErrorCodes.GetErrorInfo(ERR_EMAILADDRESSINVALID).ShortDescription);
// TODO another Catalog.Init("org", "./locale") for organisation specific words?
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return;
}
/* Show Splash Screen.
* This is non-blocking since it is done in a separate Thread, that means
* that the startup procedure continues while the Splash Screen is initialised and shown!!! */
FSplashScreen = new TSplashScreenManager(new TSplashScreenCallback(SplashScreenInfoCallback));
FSplashScreen.Show();
/*
* IMPORTANT: Always use FSplashScreen.ShowMessageBox instead of MessageBox.Show
* as long as the Splash Screen is displayed to show the MessageBox on the correct
* Thread and in front of the Splash Screen!!!
*/
try
{
InitialiseClasses();
}
catch (Exception e)
{
FSplashScreen.Close();
TLogging.Log(e.ToString());
MessageBox.Show(e.Message);
Shutdown.StopPetraClient(false);
}
if (!LoadClientSettings())
{
Environment.Exit(0);
}
/*
* Initialise Application Help
*/
Ict.Common.HelpLauncher.LocalHTMLHelp = TClientSettings.LocalHTMLHelp;
if (TClientSettings.LocalHTMLHelp)
{
Ict.Common.HelpLauncher.HelpHTMLBaseURL = TClientSettings.HTMLHelpBaseURLLocal;
}
else
{
Ict.Common.HelpLauncher.HelpHTMLBaseURL = TClientSettings.HTMLHelpBaseURLOnInternet;
if (Ict.Common.HelpLauncher.HelpHTMLBaseURL.EndsWith("/"))
{
Ict.Common.HelpLauncher.HelpHTMLBaseURL = Ict.Common.HelpLauncher.HelpHTMLBaseURL.Substring(0,
Ict.Common.HelpLauncher.HelpHTMLBaseURL.Length - 1);
}
}
Ict.Common.HelpLauncher.DetermineHelpTopic += new Ict.Common.HelpLauncher.TDetermineHelpTopic(
Ict.Petra.Client.App.Core.THelpContext.DetermineHelpTopic);
/*
* Specific information about this Petra installation can only be shown in the
* Splash Screen after Client settings are loaded (done in LoadClientSettings).
*/
FSplashScreen.UpdateTexts();
// only do automatic patch installation on remote situation
// needs to be done before login, because the login connects to the updated server, and could go wrong because of changed interfaces
if (TClientSettings.RunAsRemote == true)
{
try
{
CheckForPatches();
}
catch (Exception e)
{
TLogging.Log("Problem during checking for patches: " + e.Message);
TLogging.Log(e.StackTrace);
}
}
if (TClientSettings.RunAsStandalone == true)
{
FSplashScreen.ProgressText = "Starting OpenPetra Server Environment...";
if (!StartServer())
{
Environment.Exit(0);
}
}
FSplashScreen.ProgressText = "Connecting to your OpenPetra.org Server...";
/*
* Show Petra Login screen.
* Connections to PetraServer are established in here as well.
*/
try
{
PerformLogin();
}
catch (Exception)
{
#if TESTMODE
// in Testmode, if no connection to applink, just stop here
Environment.Exit(0);
#endif
#if TESTMODE
#else
throw;
#endif
}
if (FLoginSuccessful)
{
try
{
// Set Application Help language to the User's preferred language
TRemote.MSysMan.Maintenance.WebConnectors.GetLanguageAndCulture(ref UsersLanguageCode, ref UsersCultureCode);
if (UsersLanguageCode != String.Empty)
{
Ict.Common.HelpLauncher.HelpLanguage = UsersLanguageCode;
}
if (TClientSettings.RunAsStandalone == true)
{
ProcessReminders.StartStandaloneRemindersProcessing();
}
DataTable CurrencyFormatTable = TDataCache.TMPartner.GetCacheablePartnerTable(TCacheablePartnerTablesEnum.CurrencyCodeList);
StringHelper.CurrencyFormatTable = CurrencyFormatTable;
// This loads the Main Window of Petra
Form MainWindow;
MainWindow = new TFrmMainWindowNew(null);
// TODO: user defined constructor with more details
// FProcessID, FWelcomeMessage, FSystemEnabled);
Application.Run(MainWindow);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
TLogging.Log(e.Message);
TLogging.Log(e.StackTrace);
}
finally
{
/*
* This code gets executed only after the Main Window of Petra has
* closed.
* At the moment, we will never get here, since we call Environment.Exit in the MainWindow (both old and new navigation)
*/
Shutdown.SaveUserDefaultsAndDisconnect();
}
}
else
{
// No successful login
// APPLICATION STOPS IN THIS PROCEDURE !!!
Shutdown.StopPetraClient(false);
}
// APPLICATION STOPS IN THIS PROCEDURE !!!
Shutdown.StopPetraClient(false);
}
/// <summary>
/// Executes the Client Task.
/// </summary>
public override void Execute()
{
try
{
switch (FClientTaskDataRow["TaskGroup"].ToString())
{
// MessageBox.Show('Executing Client Task #' + FClientTaskDataRow['TaskID'].ToString + ' in Thread.');
case SharedConstants.CLIENTTASKGROUP_USERMESSAGE:
{
// MessageBox.Show(CLIENTTASKGROUP_USERMESSAGE + ' (Client Task #' + FClientTaskDataRow['TaskID'].ToString + '): ' + FClientTaskDataRow['TaskCode'].ToString, 'Client #' + UClientID.ToString + ' received a ClientTask.');
MessageBox.Show(FClientTaskDataRow["TaskCode"].ToString(), "OpenPetra Message");
break;
}
case SharedConstants.CLIENTTASKGROUP_CACHEREFRESH:
{
if (FClientTaskDataRow["TaskParameter1"].ToString() == "")
{
TDataCache.ReloadCacheTable(FClientTaskDataRow["TaskCode"].ToString());
}
else
{
TDataCache.ReloadCacheTable(FClientTaskDataRow["TaskCode"].ToString(), FClientTaskDataRow["TaskParameter1"]);
}
break;
}
case SharedConstants.CLIENTTASKGROUP_USERDEFAULTSREFRESH:
{
if (FClientTaskDataRow["TaskCode"].ToString() == "All")
{
// MessageBox.Show('FClientTaskDataRow[''TaskCode''] = All!');
TUserDefaults.ReloadCachedUserDefaults();
TUserDefaults.SaveChangedUserDefaults();
}
else
{
// MessageBox.Show('FClientTaskDataRow[''TaskCode''] <> All, but ''' + FClientTaskDataRow['TaskCode'].ToString + '''');
// MessageBox.Show('FClientTaskDataRow[''TaskParameter1'']: ' + FClientTaskDataRow['TaskParameter1'].ToString + "\r\n" +
// 'FClientTaskDataRow[''TaskParameter2'']: ' + FClientTaskDataRow['TaskParameter2'].ToString + "\r\n" +
// 'FClientTaskDataRow[''TaskParameter3'']: ' + FClientTaskDataRow['TaskParameter3'].ToString);
TUserDefaults.RefreshCachedUserDefault(
FClientTaskDataRow["TaskParameter1"].ToString(), FClientTaskDataRow["TaskParameter2"].ToString(),
FClientTaskDataRow["TaskParameter3"].ToString());
}
break;
}
case SharedConstants.CLIENTTASKGROUP_USERINFOREFRESH:
{
TUserInfo.ReloadCachedUserInfo();
break;
}
case RemotingConstants.CLIENTTASKGROUP_DISCONNECT:
{
if (FClientTaskDataRow["TaskCode"].ToString() == "IMMEDIATE")
{
TLogging.Log("Client disconnected due to server disconnection request.");
PetraClientShutdown.Shutdown.SaveUserDefaultsAndDisconnectAndStop();
}
if (FClientTaskDataRow["TaskCode"].ToString() == "IMMEDIATE-HARDEXIT")
{
TLogging.Log(
"Application stopped due to server disconnection request (without saving of User Defaults or disconnection).");
// APPLICATION STOPS HERE !!!
Environment.Exit(0);
}
break;
}
case SharedConstants.CLIENTTASKGROUP_REPORT:
{
FastReportsPrintReportNoUiDelegate(FClientTaskDataRow["TaskCode"].ToString(), FClientTaskDataRow["TaskParameter1"].ToString());
break;
}
}
}
catch (Exception Exp)
{
// MessageBox.Show("Exception occured in TClientTaskInstance.Execute: \r\n" + Exp.ToString());
TLogging.Log("Exception occured in TClientTaskInstance.Execute: \r\n" + Exp.ToString());
}
}
public static DataTable FindContacts(string AContactor,
DateTime?AContactDate,
string ACommentContains,
string AMethodOfContact,
string AModuleID,
string AMailingCode,
PPartnerContactAttributeTable AContactAttributes)
{
Boolean NewTransaction;
DataTable Contacts = new DataTable();
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
string Query = "SELECT p_contact_log.*, p_partner_contact.p_partner_key_n, p_partner.p_partner_short_name_c" +
" FROM p_contact_log, p_partner_contact, p_partner" +
" WHERE" +
" p_partner_contact.p_contact_log_id_i = p_contact_log.p_contact_log_id_i" +
" AND p_partner.p_partner_key_n = p_partner_contact.p_partner_key_n";
if (AContactor.Length > 0)
{
Query += " AND p_contact_log.p_contactor_c = '" + AContactor + "'";
}
if (AContactDate.HasValue)
{
Query += " AND p_contact_log.s_contact_date_d = '" + AContactDate + "'";
}
if (AMethodOfContact.Length > 0)
{
Query += " AND p_contact_log.p_contact_code_c = '" + AMethodOfContact + "'";
}
if (AModuleID.Length > 0)
{
Query += " AND p_contact_log.s_module_id_c = '" + AModuleID + "'";
}
if (AMailingCode.Length > 0)
{
Query += " AND p_contact_log.p_mailing_code_c = '" + AMailingCode + "'";
}
if (ACommentContains.Length > 0)
{
Query += " AND p_contact_log.p_contact_comment_c LIKE '%" + ACommentContains + "%'";
}
if ((AContactAttributes != null) && (AContactAttributes.Rows.Count > 0))
{
Query += " AND EXISTS (SELECT * " +
" FROM p_partner_contact_attribute" +
" WHERE" +
" p_partner_contact_attribute.p_contact_id_i = p_contact_log.p_contact_log_id_i" +
" AND (";
foreach (PPartnerContactAttributeRow Row in AContactAttributes.Rows)
{
Query += " (p_partner_contact_attribute.p_contact_attribute_code_c = '" + Row.ContactAttributeCode + "'" +
" AND p_partner_contact_attribute.p_contact_attr_detail_code_c = '" + Row.ContactAttrDetailCode + "') OR";
}
// remove the final " OR"
Query = Query.Substring(0, Query.Length - 3) + "))";
}
DBAccess.GDBAccessObj.SelectDT(Contacts, Query, WriteTransaction);
Contacts.PrimaryKey = new DataColumn[] {
Contacts.Columns["p_partner_key_n"], Contacts.Columns["p_contact_log_id_i"]
};
}
catch (Exception e)
{
TLogging.Log(e.Message);
TLogging.Log(e.StackTrace);
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(Contacts);
}
/// <summary>
/// Set the session id for this current thread.
/// Each request has its own thread.
/// Threads can be reused for different users.
/// </summary>
public static void InitThread(string AThreadDescription, string AConfigFileName, string ASessionID = null)
{
TLogWriter.ResetStaticVariables();
TLogging.ResetStaticVariables();
new TAppSettingsManager(AConfigFileName);
new TLogging(TSrvSetting.ServerLogFile);
TLogging.DebugLevel = TAppSettingsManager.GetInt16("Server.DebugLevel", 0);
string httprequest = "";
if ((HttpContext.Current != null) && (HttpContext.Current.Request != null))
{
httprequest = " for path " + HttpContext.Current.Request.PathInfo;
}
TLogging.LogAtLevel(4, AThreadDescription + ": Running InitThread for thread id " + Thread.CurrentThread.ManagedThreadId.ToString() + httprequest);
FSessionID = ASessionID;
FSessionValues = null;
string sessionID;
if (ASessionID == null)
{
sessionID = FindSessionID();
}
else
{
sessionID = ASessionID;
}
// avoid dead lock on parallel logins
FDeleteSessionMutex.WaitOne();
TDataBase db = ConnectDB("SessionInitThread");
TDBTransaction t = new TDBTransaction();
bool SubmissionOK = false;
bool newSession = false;
db.WriteTransaction(ref t,
ref SubmissionOK,
delegate
{
// get the session ID, or start a new session
// load the session values from the database
// update the session last access in the database
// clean old sessions
newSession = InitSession(sessionID, t);
SubmissionOK = true;
});
if (newSession)
{
// use a separate transaction to clean old sessions
db.WriteTransaction(ref t,
ref SubmissionOK,
delegate
{
CleanOldSessions(t);
SubmissionOK = true;
});
}
db.CloseDBConnection();
FDeleteSessionMutex.ReleaseMutex();
}
/// <summary>
/// This method is called when clients access the Daily Exchange Rate data.
/// The Daily Exchange Rate table is unusual in that it doesn't really need to hold any data because the DataSet that the client receives
/// contains all the used rates from the GL/Gift tables whether or not those rates are in the DER table itself. Any rates in the DER table
/// that are NOT used are also returned, but, of course, because they are not used anywhere they are not very inetresting!
/// Additionally, because the GL/Gift tables do not necessarily hold a time or a time that matches the same rate in the DER table, it is possible
/// for the DER table to have a rate that is used on the date but at a different time. As a result the client sometimes does not see all rows
/// from the DER table - and so has no way of deleting them.
///
/// That is the reason why we need to automatically clean the table.
///
/// But there is some value in having some 'unused' rows that are work-in-progress. So we delete everything in the DER table that
/// applies to dates older than 30 days. In the future this might become a configurable server option.
/// </summary>
private static void DoDailyExchangeRateClean()
{
DateTime PreviousDailyExchangeRateAccessTime = DateTime.UtcNow.AddHours(-24);
if (TSession.HasVariable("PreviousDailyExchangeRateAccessTime"))
{
PreviousDailyExchangeRateAccessTime = (DateTime)TSession.GetVariable("PreviousDailyExchangeRateAccessTime");
}
DateTime PreviousDailyExchangeRateCleanTime = DateTime.UtcNow.AddDays(-30);
if (TSession.HasVariable("PreviousDailyExchangeRateCleanTime"))
{
PreviousDailyExchangeRateCleanTime = (DateTime)TSession.GetVariable("PreviousDailyExchangeRateCleanTime");
}
if ((DateTime.UtcNow - PreviousDailyExchangeRateAccessTime).TotalHours > 8)
{
// Nobody has opened a DailyExchangeRate screen for 8 hours
if ((DateTime.UtcNow - PreviousDailyExchangeRateCleanTime).TotalHours > 24)
{
// It is more than 24 hours since our last clean
TDBTransaction t = new TDBTransaction();
TDataBase db = DBAccess.Connect("DoDailyExchangeRateClean");
bool bSubmissionOk = false;
db.WriteTransaction(ref t, ref bSubmissionOk,
delegate
{
string logMsg = String.Empty;
int affectedRowCount = 0;
// Standard is that we delete rows applicable to dates more than 60 days old
string criticalDate = DateTime.Now.AddDays(-60).ToString("yyyy-MM-dd");
try
{
// Our deletion rule is to delete rows where
// either the effective date is too old and we have no info about creation or modification
// or the creation date is too old and we have no info about any modification
// or the modification date is too old
// These rules ensure that if rates are added to a DB that is past its last accounting period (like SA-DB)
// we can still continue to use the DER screen to add unused rates because they will have create/modify times
// that can be long past the final accounting period because we will keep
// any row that has been modified recently, whatever the effective date or creation date
// any row that was created recently but not subsequently modified, whatever the effective date
// any row where we don't have info about create/modify but where the effective date is recent
string sql = String.Format(
"DELETE FROM PUB_{0} WHERE (({1}<'{2}') and {3} is NULL and {4} is NULL) or (({3}<'{2}') and {4} is NULL) or ({4}<'{2}')",
ADailyExchangeRateTable.GetTableDBName(),
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
criticalDate,
ADailyExchangeRateTable.GetDateCreatedDBName(),
ADailyExchangeRateTable.GetDateModifiedDBName());
affectedRowCount = db.ExecuteNonQuery(sql, t);
bSubmissionOk = true;
TSession.SetVariable("PreviousDailyExchangeRateCleanTime", DateTime.UtcNow);
}
catch (Exception ex)
{
logMsg = "An error occurred while trying to purge the Daily Exchange Rate table of 'aged' rows.";
logMsg += String.Format(" The exception message was: {0}", ex.Message);
}
if ((affectedRowCount > 0) && (logMsg == String.Empty))
{
logMsg =
String.Format("The Daily Exchange Rate table was purged of {0} entries applicable prior to ",
affectedRowCount) + criticalDate;
}
if (logMsg != String.Empty)
{
TLogging.Log(logMsg);
}
});
}
}
TSession.SetVariable("PreviousDailyExchangeRateAccessTime", DateTime.UtcNow);
}
/// <summary>
/// run the report
/// </summary>
private void Run(string ASessionID)
{
// need to initialize the database session
TSession.InitThread(ASessionID);
IsolationLevel Level;
if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "readuncommitted")
{
// for long reports, that should not take out locks;
// the data does not need to be consistent or will most likely not be changed during the generation of the report
Level = IsolationLevel.ReadUncommitted;
}
else if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "repeatableread")
{
// for financial reports: it is important to have consistent data; e.g. for totals
Level = IsolationLevel.RepeatableRead;
}
else if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "serializable")
{
// for creating extracts: we need to write to the database
Level = IsolationLevel.Serializable;
}
else
{
// default behaviour for normal reports
Level = IsolationLevel.ReadCommitted;
}
FSuccess = false;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
try
{
DBAccess.GDBAccessObj.BeginAutoTransaction(Level, ref Transaction,
ref SubmissionOK,
delegate
{
if (FDatacalculator.GenerateResult(ref FParameterList, ref FResultList, ref FErrorMessage, ref FException))
{
FSuccess = true;
SubmissionOK = true;
}
else
{
TLogging.Log(FErrorMessage);
}
});
}
catch (Exception Exc)
{
TLogging.Log("Problem calculating report: " + Exc.ToString());
TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile);
FSuccess = false;
FErrorMessage = Exc.Message;
FException = Exc;
}
if (TDBExceptionHelper.IsTransactionSerialisationException(FException))
{
// do nothing - we want this exception to bubble up
}
else if (FException is Exception && FException.InnerException is EOPDBException)
{
EOPDBException DbExc = (EOPDBException)FException.InnerException;
if (DbExc.InnerException is Exception)
{
if (DbExc.InnerException is NpgsqlException)
{
NpgsqlException PgExc = (NpgsqlException)DbExc.InnerException;
if (PgExc.Code == "57014") // SQL statement timeout problem
{
FErrorMessage = Catalog.GetString(
"Error - Database took too long to respond. Try different parameters to return fewer results.");
}
}
else
{
FErrorMessage = DbExc.InnerException.Message;
}
FException = null;
}
}
TProgressTracker.FinishJob(FProgressID);
}
/// <summary>
/// This method needs to be implemented by extracts that can't follow the default processing with just
/// one query.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
{
Boolean ReturnValue = false;
Boolean NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
out NewTransaction);
DataTable giftdetails;
string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByAmount.sql");
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
bool AddressFilterAdded;
DataTable partnerkeys = new DataTable();
AExtractId = -1;
// call to derived class to retrieve parameters specific for extract
RetrieveParameters(AParameters, ref SqlStmt, ref SqlParameterList);
try
{
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("getting the data from the database", TLoggingType.ToStatusBar);
giftdetails = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", Transaction,
SqlParameterList.ToArray());
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(false);
}
TLogging.Log("preparing the extract", TLoggingType.ToStatusBar);
// With the result of the original query process the data and identify the partner keys for
// the extract.
partnerkeys.Columns.Add("0", typeof(Int64));
partnerkeys.Columns.Add("1", typeof(string));
partnerkeys.Columns.Add("p_site_key_n", typeof(Int64));
partnerkeys.Columns.Add("p_location_key_i", typeof(Int32));
ProcessGiftDetailRecords(giftdetails, AddressFilterAdded, AParameters, ref partnerkeys);
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction);
// create an extract with the given name in the parameters
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out AExtractId,
partnerkeys,
0,
AddressFilterAdded,
true);
return(ReturnValue);
}
/// <summary>
/// send report as email
/// </summary>
public Boolean SendEmail(string AEmailAddresses,
bool AAttachExcelFile,
bool AAttachCSVFile,
bool AAttachPDF,
bool AWrapColumn,
out TVerificationResultCollection AVerification)
{
TSmtpSender EmailSender = null;
string EmailBody = "";
AVerification = new TVerificationResultCollection();
try
{
EmailSender = new TSmtpSender();
List <string> FilesToAttach = new List <string>();
if (AAttachExcelFile)
{
string ExcelFile = TFileHelper.GetTempFileName(
FParameterList.Get("currentReport").ToString(),
".xlsx");
if (ExportToExcelFile(ExcelFile))
{
FilesToAttach.Add(ExcelFile);
}
}
if (AAttachCSVFile)
{
string CSVFile = TFileHelper.GetTempFileName(
FParameterList.Get("currentReport").ToString(),
".csv");
if (ExportToCSVFile(CSVFile))
{
FilesToAttach.Add(CSVFile);
}
}
if (AAttachPDF)
{
string PDFFile = TFileHelper.GetTempFileName(
FParameterList.Get("currentReport").ToString(),
".pdf");
if (PrintToPDF(PDFFile, AWrapColumn))
{
FilesToAttach.Add(PDFFile);
}
}
if (FilesToAttach.Count == 0)
{
AVerification.Add(new TVerificationResult(
Catalog.GetString("Sending Email"),
Catalog.GetString("Missing any attachments, not sending the email"),
"Missing Attachments",
TResultSeverity.Resv_Critical,
new System.Guid()));
return(false);
}
try
{
EmailSender.SetSender(TUserDefaults.GetStringDefault("SmtpFromAccount"),
TUserDefaults.GetStringDefault("SmtpDisplayName"));
EmailSender.CcEverythingTo = TUserDefaults.GetStringDefault("SmtpCcTo");
EmailSender.ReplyTo = TUserDefaults.GetStringDefault("SmtpReplyTo");
EmailBody = TUserDefaults.GetStringDefault("SmtpEmailBody");
}
catch (ESmtpSenderInitializeException e)
{
AVerification.Add(new TVerificationResult(
Catalog.GetString("Sending Email"),
String.Format("{0}\n{1}", e.Message, Catalog.GetString("Check the Email tab in User Settings >> Preferences.")),
CommonErrorCodes.ERR_MISSINGEMAILCONFIGURATION,
TResultSeverity.Resv_Critical,
new System.Guid()));
if (e.InnerException != null)
{
TLogging.Log("Email XML Report: " + e.InnerException);
}
return(false);
}
if (EmailBody == "")
{
EmailBody = Catalog.GetString("OpenPetra report attached.");
}
if (EmailSender.SendEmail(
AEmailAddresses,
FParameterList.Get("currentReport").ToString(),
EmailBody,
FilesToAttach.ToArray()))
{
foreach (string file in FilesToAttach)
{
File.Delete(file);
}
return(true);
}
AVerification.Add(new TVerificationResult(
Catalog.GetString("Sending Email"),
Catalog.GetString("Problem sending email"),
"Server problems",
TResultSeverity.Resv_Critical,
new System.Guid()));
return(false);
} // try
catch (ESmtpSenderInitializeException e)
{
AVerification.Add(new TVerificationResult(
Catalog.GetString("Sending Email"),
e.Message,
CommonErrorCodes.ERR_MISSINGEMAILCONFIGURATION,
TResultSeverity.Resv_Critical,
new System.Guid()));
if (e.InnerException != null)
{
TLogging.Log("Email XML Report: " + e.InnerException);
}
return(false);
}
finally
{
if (EmailSender != null)
{
EmailSender.Dispose();
}
}
}
private void ExecuteFullQuery()
{
// TDBTransaction ReadTransaction;
// Boolean NewTransaction = false;
if (FFindParameters.FParametersGivenSeparately)
{
string SQLOrderBy = "";
string SQLWhereCriteria = "";
if (FFindParameters.FPagedTableWhereCriteria != "")
{
SQLWhereCriteria = "WHERE " + FFindParameters.FPagedTableWhereCriteria;
}
if (FFindParameters.FPagedTableOrderBy != "")
{
SQLOrderBy = " ORDER BY " + FFindParameters.FPagedTableOrderBy;
}
FSelectSQL = "SELECT " + FFindParameters.FPagedTableColumns + " FROM " + FFindParameters.FPagedTable +
' ' +
SQLWhereCriteria + SQLOrderBy;
}
else
{
FSelectSQL = FFindParameters.FSqlQuery;
}
TLogging.LogAtLevel(9, (this.GetType().FullName + ".ExecuteFullQuery SQL:" + FSelectSQL));
// clear temp table. do not recreate because it may be typed
FTmpDataTable.Clear();
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
try
{
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// Fill temporary table with query results (all records)
FTotalRecords = DBAccess.GDBAccessObj.SelectUsingDataAdapter(FSelectSQL, ReadTransaction,
ref FTmpDataTable, out FDataAdapterCanceller,
delegate(ref IDictionaryEnumerator AEnumerator)
{
if (FFindParameters.FColumNameMapping != null)
{
AEnumerator = FFindParameters.FColumNameMapping.GetEnumerator();
return(FFindParameters.FPagedTable + "_for_paging");
}
else
{
return(String.Empty);
}
}, true, 60, FFindParameters.FParametersArray);
}
catch (NpgsqlException Exp)
{
if (Exp.Code == "57014") // Exception with Code 57014 is what Npgsql raises as a response to a Cancel request of a Command
{
TLogging.LogAtLevel(7, this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; proper reply from Npgsql!");
}
else
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general NpgsqlException occured: " + Exp.ToString());
}
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
return;
}
catch (Exception Exp)
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general Exception occured: " + Exp.ToString());
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
return;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
TLogging.LogAtLevel(7,
(this.GetType().FullName + ".ExecuteFullQuery: FDataAdapter.Fill finished. FTotalRecords: " + FTotalRecords.ToString()));
FPageDataTable = FTmpDataTable.Clone();
FPageDataTable.TableName = FFindParameters.FSearchName;
TProgressTracker.SetCurrentState(FProgressID, "Query executed.", 100.0m);
TProgressTracker.FinishJob(FProgressID);
}
/// <summary>
/// Retrieves all of the PartnerInfo data.
/// </summary>
/// <param name="APartnerKey">PartnerKey of Partner to find the PartnerInfo data for</param>
/// <param name="APartnerInfoDS">Typed DataSet that contains the requested data.</param>
/// <param name="AReadTransaction" >Open DB Transaction.</param>
/// <returns>True if Partner exists, otherwise false.</returns>
public static bool AllPartnerInfoData(Int64 APartnerKey, ref PartnerInfoTDS APartnerInfoDS,
TDBTransaction AReadTransaction)
{
bool ReturnValue = false;
PPartnerRow PartnerDR;
TLocationPK BestLocationPK;
PLocationRow LocationDR;
PLocationRow LocationDR2;
PPartnerLocationRow PartnerLocationDR;
PPartnerLocationRow PartnerLocationDR2;
PLocationTable LocationDT;
PPartnerLocationTable PartnerLocationDT;
try
{
/*
* Check for existance of Partner
*/
PartnerDR = MCommonMain.CheckPartnerExists2(APartnerKey, true, AReadTransaction.DataBaseObj);
if (PartnerDR != null)
{
/*
* Perform security checks; these throw ESecurityPartnerAccessDeniedException
* in case access isn't granted.
*/
TSecurity.CanAccessPartnerExc(PartnerDR, AReadTransaction.DataBaseObj);
/*
* Get the Partner's Address data of its 'Best' Address
*/
if (TMailing.GetPartnersBestLocationData(APartnerKey, out BestLocationPK,
out LocationDR, out PartnerLocationDR, AReadTransaction.DataBaseObj))
{
#region Process Address
/*
* Check for existance of PLocation and PPartnerLocation Tables in the passed in
* DataSet APartnerInfoDS.
*/
if (!APartnerInfoDS.Tables.Contains(PLocationTable.GetTableName()))
{
// Need to create Table here
APartnerInfoDS.Tables.Add(new PLocationTable());
}
if (!APartnerInfoDS.Tables.Contains(PPartnerLocationTable.GetTableName()))
{
// Need to create Table here
APartnerInfoDS.Tables.Add(new PPartnerLocationTable());
}
// Add copies of the Location and PartnerLocation DataRows of the 'Best Address'
/*
* Remove DataColumns that might have been added by the call to
* TMailing.GetPartnersBestLocationData - otherwise PartnerLocationDR2.ItemArray
* assignment will fail.
*/
if (PartnerLocationDR.Table.Columns.Contains(Calculations.PARTNERLOCATION_BESTADDR_COLUMN))
{
PartnerLocationDR.Table.Columns.Remove(Calculations.PARTNERLOCATION_BESTADDR_COLUMN);
}
if (PartnerLocationDR.Table.Columns.Contains(Calculations.PARTNERLOCATION_ICON_COLUMN))
{
PartnerLocationDR.Table.Columns.Remove(Calculations.PARTNERLOCATION_ICON_COLUMN);
}
LocationDR2 = APartnerInfoDS.PLocation.NewRowTyped(false);
LocationDR2.ItemArray = LocationDR.ItemArray;
PartnerLocationDR2 = APartnerInfoDS.PPartnerLocation.NewRowTyped(false);
PartnerLocationDR2.ItemArray = PartnerLocationDR.ItemArray;
APartnerInfoDS.PLocation.Rows.Add(LocationDR2);
APartnerInfoDS.PPartnerLocation.Rows.Add(PartnerLocationDR2);
#endregion
// Apply Address Security
LocationDT = APartnerInfoDS.PLocation;
PartnerLocationDT = APartnerInfoDS.PPartnerLocation;
TPPartnerAddressAggregate.ApplySecurity(ref PartnerLocationDT,
ref LocationDT);
// Process 'Head' data and rest of data for the Partner
HeadInternal(PartnerDR, ref APartnerInfoDS);
RestInternal(PartnerDR, ref APartnerInfoDS, AReadTransaction);
ReturnValue = true;
}
}
}
catch (ESecurityPartnerAccessDeniedException)
{
// don't log this exception - this is thrown on purpose here and the Client needs to deal with it.
throw;
}
catch (Exception Exp)
{
TLogging.Log("TServerLookups_PartnerInfo.AllPartnerInfoData exception: " + Exp.ToString(), TLoggingType.ToLogfile);
TLogging.Log(Exp.StackTrace, TLoggingType.ToLogfile);
throw;
}
return(ReturnValue);
}
