private void RunOnceOnActivationManual() { FPetraUtilsObject.UnhookControl(pnlDetails, true); // I don't want changes in these values to cause SetChangedFlag - I'll set it myself. FPetraUtilsObject.UnhookControl(txtStatus, false); // This control is not to be spied on! txtDetailCostCentreName.TextChanged += new EventHandler(UpdateOnControlChanged); chkDetailCostCentreActiveFlag.CheckedChanged += new EventHandler(UpdateOnControlChanged); cmbDetailCostCentreType.SelectedIndexChanged += new EventHandler(UpdateOnControlChanged); txtDetailCostCentreCode.Validated -= ControlValidatedHandler; // Don't trigger validation on change - I need to do it manually FPetraUtilsObject.ControlChanged += new TValueChangedHandler(FPetraUtilsObject_ControlChanged); FnameForNewCostCentre = Catalog.GetString("NEWCOSTCENTRE"); txtDetailCostCentreCode.TextChanged += new EventHandler(txtDetailCostCentreCode_TextChanged); txtDetailCostCentreCode.Leave += txtDetailCostCentreCode_Leave; FPetraUtilsObject.DataSaved += OnHierarchySaved; mniFilePrint.Click += FilePrint; mniFilePrint.Enabled = true; chkDetailSummaryFlag.CheckedChanged += chkDetailSummaryFlag_CheckedChanged; if (TAppSettingsManager.GetBoolean("OmBuild", false)) // In OM, no-one needs to see the import or export functions: { tbrMain.Items.Remove(tbbImportHierarchy); tbrMain.Items.Remove(tbbExportHierarchy); /* For some reason, this screen never had these menu options! * mnuMain.Items.Remove(mniImportHierarchy); * mnuMain.Items.Remove(mniExportHierarchy); */ } }
/// <summary> /// Allows the server or admin console to run a timed processing job now /// </summary> public override void PerformTimedProcessingNow(string AProcessName) { // // Set up 'Timed Processing' // TTimedProcessing.DailyStartTime24Hrs = TAppSettingsManager.GetValue("Server.Processing.DailyStartTime24Hrs", "00:30"); if (TAppSettingsManager.GetBoolean("Server.Processing.PartnerReminders.Enabled", true)) { Assembly PartnerProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MPartner.processing"); Type PartnerReminderClass = PartnerProcessingAssembly.GetType("Ict.Petra.Server.MPartner.Processing.TProcessPartnerReminders"); TTimedProcessing.AddProcessingJob( "TProcessPartnerReminders", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), PartnerReminderClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.DataChecks.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type ProcessDataChecksClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessDataChecks"); TTimedProcessing.AddProcessingJob( "TProcessDataChecks", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), ProcessDataChecksClass, "Process")); } TTimedProcessing.RunJobManually(AProcessName); }
public static void GetServerSmtpSettings(out string ASMTPHost, out int ASMTPPort, out bool AEnableSsl, out string ALoginUsername, out string ALoginPassword) { ASMTPHost = TAppSettingsManager.GetValue("SmtpHost", ""); ASMTPPort = TAppSettingsManager.GetInt32("SmtpPort", 25); AEnableSsl = TAppSettingsManager.GetBoolean("SmtpEnableSsl", false); ALoginUsername = null; ALoginPassword = null; // Validate the host name. It should not be the content of an unmodified config file. if (ASMTPHost.Contains("example.org")) { ASMTPHost = string.Empty; return; } if (TAppSettingsManager.GetBoolean("SmtpRequireCredentials", false) == true) { // We give the client the details of the OP Email user. // The password is converted from a byte array (rather than being compiled into this DLL as plain text). // The username and password are stored in different server DLL's. ALoginUsername = MSysManConstants.EMAIL_USER_LOGIN_NAME; ALoginPassword = Encoding.ASCII.GetString(EmailUserPassword); } }
private void RunOnceOnActivationManual() { FPetraUtilsObject.UnhookControl(txtDetailAccountCode, false); // I don't want changes in this edit box to cause SetChangedFlag - I'll set it myself. FPetraUtilsObject.UnhookControl(txtStatus, false); // This control is not to be spied on! txtDetailAccountCode.TextChanged += new EventHandler(txtDetailAccountCode_TextChanged); txtDetailAccountCode.Validated -= ControlValidatedHandler; // Don't trigger validation on change - I need to do it manually chkDetailForeignCurrencyFlag.CheckedChanged += new EventHandler(chkDetailForeignCurrencyFlag_CheckedChanged); chkDetailIsSummary.CheckedChanged += chkDetailIsSummary_CheckedChanged; FPetraUtilsObject.ControlChanged += new TValueChangedHandler(FPetraUtilsObject_ControlChanged); txtDetailEngAccountCodeLongDesc.Leave += new EventHandler(AutoFillDescriptions); cmbDetailValidCcCombo.SelectedValueChanged += cmbDetailValidCcCombo_SelectedValueChanged; FPetraUtilsObject.DataSaved += OnHierarchySaved; FIAmUpdating = 0; FNameForNewAccounts = Catalog.GetString("NEWACCOUNT"); mniFilePrint.Click += FilePrint; mniFilePrint.Enabled = true; if (TAppSettingsManager.GetBoolean("OmBuild", false)) // In OM, no-one needs to see the import or export functions: { tbrMain.Items.Remove(tbbImportHierarchy); tbrMain.Items.Remove(tbbExportHierarchy); mnuMain.Items.Remove(mniImportHierarchy); mnuMain.Items.Remove(mniExportHierarchy); } }
/// Method to obtain the SMTP email server configuration settings from the configuration file public static TSmtpServerSettings GetSmtpSettingsFromAppSettings() { return(new TSmtpServerSettings( TAppSettingsManager.GetValue("SmtpHost"), TAppSettingsManager.GetInt16("SmtpPort"), TAppSettingsManager.GetBoolean("SmtpEnableSsl", true), TAppSettingsManager.GetValue("SmtpUser"), TAppSettingsManager.GetValue("SmtpPassword"), TAppSettingsManager.GetBoolean("IgnoreServerCertificateValidation", false))); }
private void Form_Load(System.Object sender, System.EventArgs e) { txtErrorDetails.Text = FErrorDetails; if (TAppSettingsManager.GetBoolean("RunAsStandalone", false) == true) { btnShowClientLog.Text = Catalog.GetString("Show Log file"); btnShowServerLog.Enabled = false; } }
/// <summary> /// For development and testing purposes this Method can either execute actions that /// are set up by the program 'PetraMultiStart' (indicated by 'RunAutoTests=true' on /// the command line) OR open a screen with parameters that /// come either from the .config file or Command Line (indicated by 'TestAction="xxx"'). /// The 'Test Action' will not be run if the Control Key is pressed. /// </summary> /// <remarks> /// sample action: TestAction="Namespace=Ict.Petra.Client.MPartner.Gui,ActionOpenScreen=TFrmPartnerEdit2,PartnerKey=0043005002,InitiallySelectedTabPage=petpDetails" ///</remarks> private void RunTestAction() { string DisconnectTimeFromCommandLine = TAppSettingsManager.GetValue("DisconnectTime"); #if TODORemoting if (TAppSettingsManager.GetBoolean("RunAutoTests", false) == true) { // We need to manually 'fix up' the value of DisconnectTime that we get from .NET when we request // the commandline parameters as an array because .NET removes quotation marks in two places where // they were present on the command line. Those two quotation marks need to be there as the call to // TVariant.DecodeFromString() will not succeed if they aren't there in their proper places! DisconnectTimeFromCommandLine = DisconnectTimeFromCommandLine.Substring( 0, DisconnectTimeFromCommandLine.IndexOf(':') + 1) + "\"" + DisconnectTimeFromCommandLine.Substring( DisconnectTimeFromCommandLine.IndexOf(':') + 1) + "\""; TestRunner = new PetraClient_AutomatedAppTest.TAutomatedAppTest( TAppSettingsManager.GetValue("AutoTestConfigFile"), TAppSettingsManager.GetValue("AutoTestParameters"), TVariant.DecodeFromString(DisconnectTimeFromCommandLine).ToDate(), TConnectionManagementBase.GConnectionManagement.ClientName); TestRunner.TestForm = this; TestRunner.ClientID = TConnectionManagementBase.GConnectionManagement.ClientID; TestRunner.Start(this); } else #endif if (System.Windows.Forms.Form.ModifierKeys != Keys.Control) { string testAction = TAppSettingsManager.GetValue("TestAction", false); if (testAction != TAppSettingsManager.UNDEFINEDVALUE) { XmlDocument temp = new XmlDocument(); XmlNode testActionNode = temp.CreateElement("testAction"); temp.AppendChild(testActionNode); testAction = testAction.Trim(new char[] { '"' }); while (testAction.Length > 0) { string[] pair = StringHelper.GetNextCSV(ref testAction, ",").Split(new char[] { '=' }); XmlAttribute attr = temp.CreateAttribute(pair[0]); attr.Value = pair[1]; testActionNode.Attributes.Append(attr); } TLstTasks.ExecuteAction(testActionNode, null); } } }
public CommandLineArgs() { _physicalPath = TAppSettingsManager.GetValue("physicalPath", string.Empty, false); // nant sets this _virtualPath = TAppSettingsManager.GetValue("virtualPath", string.Empty, false); _defaultPage = TAppSettingsManager.GetValue("defaultPage", string.Empty, false); _port = TAppSettingsManager.GetInt16("port", 0); // nant sets this _acceptRemoteConnection = TAppSettingsManager.GetBoolean("acceptRemoteConnection", false); _suppressStartUpMessages = TAppSettingsManager.GetBoolean("quiet", false); // nant usually sets this property to true _maxRuntimeInMinutes = TAppSettingsManager.GetInt16("maxRuntimeInMinutes", 0); _logPageRequests = TAppSettingsManager.GetBoolean("logPageRequests", true); // you can use the config to turn loggin OFF _useFullUI = TAppSettingsManager.GetBoolean("useFullUI", false); // we will only use the full UI if specified or if no port or path _logfilePath = TAppSettingsManager.GetValue("logfilePath", string.Empty, false); // we will use default location }
/// <summary> /// setup the smtp client from the config file or command line parameters /// </summary> public TSmtpSender() { //Set up SMTP client String EmailDirectory = ""; if (TAppSettingsManager.HasValue("OutputEMLToDirectory")) { EmailDirectory = TAppSettingsManager.GetValue("OutputEMLToDirectory"); } Initialise( TAppSettingsManager.GetValue("SmtpHost"), TAppSettingsManager.GetInt16("SmtpPort", 25), TAppSettingsManager.GetBoolean("SmtpEnableSsl", false), TAppSettingsManager.GetValue("SmtpUser", ""), TAppSettingsManager.GetValue("SmtpPassword", ""), EmailDirectory); }
/// get the current session id. if it is not stored in the http context, check the thread private static string FindSessionID() { if (TAppSettingsManager.GetBoolean("RunAsStandalone", false) == true) { return(FStandaloneSessionID); } if ((HttpContext.Current != null) && (HttpContext.Current.Request.Cookies["OpenPetraSessionID"] != null)) { TLogging.LogAtLevel(4, "using session id from HttpContext"); return(HttpContext.Current.Request.Cookies["OpenPetraSessionID"].Value); } // Session ID not found in the http context, checking the thread TLogging.LogAtLevel(4, "(HttpContext.Current != null) : " + (HttpContext.Current != null).ToString()); if (HttpContext.Current != null) { TLogging.LogAtLevel(4, "(HttpContext.Current.Request.Cookies[OpenPetraSessionID] != null) : " + (HttpContext.Current.Request.Cookies["OpenPetraSessionID"] != null).ToString()); } // only look in thread if there is no HttpContext.Current; otherwise Threads are reused. if (HttpContext.Current == null) { string sessionId = FSessionID; if ((sessionId != null) && (sessionId.Length > 0)) { TLogging.LogAtLevel(4, "FindSessionID: Session ID found in thread. SessionID = " + sessionId); return(sessionId); } TLogging.LogAtLevel(1, "FindSessionID: Session ID not found in the thread!!! thread id: " + Thread.CurrentThread.ManagedThreadId.ToString()); } else { TLogging.LogAtLevel(1, "FindSessionID: Session ID not found in the http context"); } return(string.Empty); }
/// <summary> /// Initialises Logging and parses Server settings from different sources. /// </summary> public TServerManager() : base() { // Create SystemDefaults Cache FSystemDefaultsCache = new TSystemDefaultsCache(); TRemoteLoader.CLIENTDOMAIN_DLLNAME = "Ict.Petra.Server.app.Core"; TRemoteLoader.CLIENTDOMAIN_CLASSNAME = "Ict.Petra.Server.App.Core.TClientDomainManager"; TClientAppDomainConnectionBase.ClientAppDomainConnectionType = typeof(TClientAppDomainConnection); TCacheableTablesManager.InitializeUnit(); TCacheableTablesManager.GCacheableTablesManager = new TCacheableTablesManager(new TDelegateSendClientTask(TClientManager.QueueClientTask)); Assembly SysManAssembly = Assembly.Load("Ict.Petra.Server.lib.MSysMan"); Type ImportExportType = SysManAssembly.GetType("Ict.Petra.Server.MSysMan.ImportExport.TImportExportManager"); FImportExportManager = (IImportExportManager)Activator.CreateInstance(ImportExportType, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); Type UserManagement = SysManAssembly.GetType("Ict.Petra.Server.MSysMan.Maintenance.UserManagement.TUserManager"); FUserManager = (IUserManager)Activator.CreateInstance(UserManagement, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); TClientManager.InitializeStaticVariables(FSystemDefaultsCache, TCacheableTablesManager.GCacheableTablesManager, FUserManager, new TErrorLog(), new TMaintenanceLogonMessage(), new TClientAppDomainConnection()); TTimedProcessing.DailyStartTime24Hrs = TAppSettingsManager.GetValue("Server.Processing.DailyStartTime24Hrs", "00:30"); if (TAppSettingsManager.GetBoolean("Server.Processing.PartnerReminders.Enabled", true)) { Assembly PartnerProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MPartner.processing"); Type PartnerReminderClass = PartnerProcessingAssembly.GetType("Ict.Petra.Server.MPartner.Processing.TProcessPartnerReminders"); TTimedProcessing.AddProcessingJob( "TProcessPartnerReminders", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), PartnerReminderClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.AutomatedIntranetExport.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type IntranetExportClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessAutomatedIntranetExport"); TTimedProcessing.AddProcessingJob( "TProcessAutomatedIntranetExport", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), IntranetExportClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.DataChecks.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type ProcessDataChecksClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessDataChecks"); TTimedProcessing.AddProcessingJob( "TProcessDataChecks", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), ProcessDataChecksClass, "Process")); } }
/// <summary> /// Starts the Petra Server. /// /// </summary> /// <returns>void</returns> public void Startup() { try { // // Uncomment the following lines to see which DLL's are loaded into the Default AppDomain at application start. // It can help in identifying which DLL's are loaded later in addition to those that were loaded at application start. /* * Console.WriteLine("Loaded Assemblies in AppDomain " + Thread.GetDomain().FriendlyName + " (at Server start):"); * foreach (Assembly tmpAssembly in Thread.GetDomain().GetAssemblies()) * { * Console.WriteLine(tmpAssembly.FullName); * } */ new TAppSettingsManager(); TLanguageCulture.Init(); TheServerManager = new TServerManager(); // Ensure Logging and an 'ordered cooperative shutdown' in case of an Unhandled Exception TheServerManager.HookupProperShutdownProcessing(); Console.WriteLine(); TLogging.Log(TheServerManager.ServerInfoVersion); TLogging.Log(Catalog.GetString("Configuration file: " + TheServerManager.ConfigurationFileName)); // // Connect to main Database // try { TheServerManager.EstablishDBConnection(); } catch (FileNotFoundException ex) { TLogging.Log(ex.Message); TLogging.Log("Please check your OpenPetra.build.config file ..."); TLogging.Log("Maybe a nant initConfigFile helps ..."); throw new EOPAppException(); } catch (Exception ex) { TLogging.Log(ex.Message); throw; } // Setup Server Timed Processing try { TheServerManager.SetupServerTimedProcessing(); } catch (Exception) { throw; } // // Remote the remoteable objects // try { if (TAppSettingsManager.HasValue("LifetimeServices.LeaseTimeInSeconds")) { TLogging.Log(Catalog.GetString("Reading parameters for server remote configuration from config file...")); BinaryServerFormatterSinkProvider TCPSink = new BinaryServerFormatterSinkProvider(); TCPSink.TypeFilterLevel = TypeFilterLevel.Low; IServerChannelSinkProvider EncryptionSink = TCPSink; if (TAppSettingsManager.GetValue("Server.ChannelEncryption.PrivateKeyfile", "", false).Length > 0) { EncryptionSink = new EncryptionServerSinkProvider(); EncryptionSink.Next = TCPSink; } Hashtable ChannelProperties = new Hashtable(); ChannelProperties.Add("port", TAppSettingsManager.GetValue("Server.Port")); string SpecificIPAddress = TAppSettingsManager.GetValue("ListenOnIPAddress", "", false); if (SpecificIPAddress.Length > 0) { ChannelProperties.Add("machineName", SpecificIPAddress); } TcpChannel Channel = new TcpChannel(ChannelProperties, null, EncryptionSink); ChannelServices.RegisterChannel(Channel, false); RemotingConfiguration.RegisterWellKnownServiceType(typeof(Ict.Petra.Server.App.Core.TServerManager), "Servermanager", WellKnownObjectMode.Singleton); RemotingConfiguration.RegisterWellKnownServiceType(typeof(Ict.Common.Remoting.Server.TClientManager), "Clientmanager", WellKnownObjectMode.Singleton); RemotingConfiguration.RegisterWellKnownServiceType(typeof(TCrossDomainMarshaller), TClientManager.CROSSDOMAINURL, WellKnownObjectMode.Singleton); LifetimeServices.LeaseTime = TimeSpan.FromSeconds(TAppSettingsManager.GetDouble("LifetimeServices.LeaseTimeInSeconds", 5.0f)); LifetimeServices.RenewOnCallTime = TimeSpan.FromSeconds(TAppSettingsManager.GetDouble("LifetimeServices.RenewOnCallTime", 5.0f)); LifetimeServices.LeaseManagerPollTime = TimeSpan.FromSeconds(TAppSettingsManager.GetDouble("LifetimeServices.LeaseManagerPollTime", 1.0f)); } else { TLogging.Log(Catalog.GetString("Reading server remote configuration from config file...")); if (TheServerManager.ConfigurationFileName == "") { RemotingConfiguration.Configure(Environment.GetCommandLineArgs()[0] + ".config", false); } else { RemotingConfiguration.Configure(TheServerManager.ConfigurationFileName, false); } RemotingConfiguration.RegisterWellKnownServiceType(typeof(TCrossDomainMarshaller), TClientManager.CROSSDOMAINURL, WellKnownObjectMode.Singleton); } } catch (RemotingException rex) { if (rex.Message.IndexOf("SocketException") > 1) { TLogging.Log("A SocketException has been thrown."); TLogging.Log("Most probably problem is that the address port is used twice!"); throw new EOPAppException(); } else { throw; } } catch (Exception) { throw; } Thread.Sleep(50); TrackingServices.RegisterTrackingHandler(new TRemotingTracker()); // Display information that the Server is ready to accept .NET Remoting requests TLogging.Log(TheServerManager.ServerInfoState); // // Server startup done. // From now on just listen on .NET Remoting Framework object invocations or on // menu commands... // bool RunWithoutMenu = TAppSettingsManager.GetBoolean("RunWithoutMenu", false); if ((!RunWithoutMenu)) { Console.WriteLine(Environment.NewLine + Catalog.GetString("-> Press \"m\" for menu.")); WriteServerPrompt(); } // All exceptions that are raised from various parts of the Server are handled below. // Note: The Server stops after handling these exceptions!!! if (RunWithoutMenu) { RunInBackground(); } else { RunMenu(); } // THE VERY END OF THE SERVER :( } catch (System.Net.Sockets.SocketException exp) { TLogging.Log( Environment.NewLine + "Unable to start the Server: The IP Port " + TSrvSetting.IPBasePort.ToString() + " is being used by a different instance of the Server or some other application." + Environment.NewLine + exp.ToString()); } catch (System.Runtime.Remoting.RemotingException exp) { System.Diagnostics.Debug.WriteLine(exp.ToString()); TLogging.Log(Environment.NewLine + "Exception occured while setting up Remoting Framework:" + Environment.NewLine + exp.ToString()); } catch (EOPAppException) { // This Exception is used if no more messages shall be done ... } catch (Exception exp) { TLogging.Log(Environment.NewLine + "Exception occured:" + Environment.NewLine + exp.ToString()); } }
/// <summary> /// Initialises Logging and parses Server settings from different sources. /// </summary> public TServerManager() : base() { // Create SystemDefaults Cache TSystemDefaultsCache.GSystemDefaultsCache = new TSystemDefaultsCache(); DomainManager.GetSiteKeyFromSystemDefaultsCacheDelegate = @TSystemDefaultsCache.GSystemDefaultsCache.GetSiteKeyDefault; TCacheableTablesManager.InitializeUnit(); TCacheableTablesManager.GCacheableTablesManager = new TCacheableTablesManager(new TDelegateSendClientTask(TClientManager.QueueClientTask)); Assembly SysManAssembly = Assembly.Load("Ict.Petra.Server.lib.MSysMan"); Type ImportExportType = SysManAssembly.GetType("Ict.Petra.Server.MSysMan.ImportExport.TImportExportManager"); FImportExportManager = (IImportExportManager)Activator.CreateInstance(ImportExportType, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); Assembly DBUpgradesAssembly = Assembly.Load("Ict.Petra.Server.lib.MSysMan.DBUpgrades"); Type DatabaseUpgradeType = DBUpgradesAssembly.GetType("Ict.Petra.Server.MSysMan.DBUpgrades.TDBUpgrades"); FDBUpgrades = (IDBUpgrades)Activator.CreateInstance(DatabaseUpgradeType, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); Type UserManagement = SysManAssembly.GetType("Ict.Petra.Server.MSysMan.Maintenance.UserManagement.TUserManager"); FUserManager = (IUserManager)Activator.CreateInstance(UserManagement, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); TClientManager.InitializeStaticVariables(TSystemDefaultsCache.GSystemDefaultsCache, FUserManager, new TErrorLog(), new TLoginLog(), new TMaintenanceLogonMessage(), ExceptionHandling_DBConnectionBrokenCallback); // Set up the SYSADMIN user (#5650). // (This is required for all SubmitChanges method calls in the server's main AppDomain because // that Method references UserInfo.GUserInfo) // When using this with the Web Services, this does not apply to the threads for each session. TPetraIdentity PetraIdentity = new TPetraIdentity( "SYSADMIN", "", "", "", "", DateTime.MinValue, DateTime.MinValue, DateTime.MinValue, 0, -1, -1, false, false, false); TPetraPrincipal Principal = new TPetraPrincipal(PetraIdentity, null); UserInfo.GUserInfo = Principal; // // Set up 'Timed Processing' // TTimedProcessing.DailyStartTime24Hrs = TAppSettingsManager.GetValue("Server.Processing.DailyStartTime24Hrs", "00:30"); if (TAppSettingsManager.GetBoolean("Server.Processing.PartnerReminders.Enabled", true)) { Assembly PartnerProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MPartner.processing"); Type PartnerReminderClass = PartnerProcessingAssembly.GetType("Ict.Petra.Server.MPartner.Processing.TProcessPartnerReminders"); TTimedProcessing.AddProcessingJob( "TProcessPartnerReminders", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), PartnerReminderClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.AutomatedIntranetExport.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type IntranetExportClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessAutomatedIntranetExport"); TTimedProcessing.AddProcessingJob( "TProcessAutomatedIntranetExport", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), IntranetExportClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.DataChecks.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type ProcessDataChecksClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessDataChecks"); TTimedProcessing.AddProcessingJob( "TProcessDataChecks", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), ProcessDataChecksClass, "Process")); } }
/// <summary> /// If the Email preferences are not already in UserDefaults, this loads them. /// </summary> public static void LoadEmailDefaults() { if (!TUserDefaults.HasDefault("SmtpHost")) { TUserDefaults.SetDefault("SmtpHost", TAppSettingsManager.GetValue("SmtpHost", "")); } if (!TUserDefaults.HasDefault("SmtpPort")) { TUserDefaults.SetDefault("SmtpPort", TAppSettingsManager.GetInt16("SmtpPort", -1)); } if (!TUserDefaults.HasDefault("SmtpUseSsl")) { TUserDefaults.SetDefault("SmtpUseSsl", TAppSettingsManager.GetValue("SmtpEnableSsl", false)); } if (!TUserDefaults.HasDefault("SmtpUser")) { TUserDefaults.SetDefault("SmtpUser", TAppSettingsManager.GetValue("SmtpUser", "")); } if (!TUserDefaults.HasDefault("SmtpPassword")) { TUserDefaults.SetDefault("SmtpPassword", TAppSettingsManager.GetValue("SmtpPassword", "")); } if (!TUserDefaults.HasDefault("SmtpEnableSsl")) { TUserDefaults.SetDefault("SmtpEnableSsl", TAppSettingsManager.GetBoolean("SmtpEnableSsl", false)); } if (!TUserDefaults.HasDefault("SmtpFromAccount")) { TUserDefaults.SetDefault("SmtpFromAccount", TAppSettingsManager.GetValue("SmtpFromAccount", "")); } if (!TUserDefaults.HasDefault("SmtpDisplayName")) { TUserDefaults.SetDefault("SmtpDisplayName", TAppSettingsManager.GetValue("SmtpDisplayName", "")); } if (!TUserDefaults.HasDefault("SmtpReplyTo")) { TUserDefaults.SetDefault("SmtpReplyTo", TAppSettingsManager.GetValue("SmtpReplyTo", "")); } if (!TUserDefaults.HasDefault("SmtpCcTo")) { TUserDefaults.SetDefault("SmtpCcTo", TAppSettingsManager.GetValue("SmtpCcTo", "")); } if (!TUserDefaults.HasDefault("SmtpEmailBody")) { TUserDefaults.SetDefault("SmtpEmailBody", TAppSettingsManager.GetValue("SmtpEmailBody", "")); } if (!TUserDefaults.HasDefault("SmtpSendAsAttachment")) { TUserDefaults.SetDefault("SmtpSendAsAttachment", TAppSettingsManager.GetValue("SmtpSendAsAttachment", "")); } }
/// <summary> /// Initialises Logging and parses Server settings from different sources. /// </summary> public TServerManager() : base() { Assembly SysManAssembly = Assembly.Load("Ict.Petra.Server.lib.MSysMan"); Type ImportExportType = SysManAssembly.GetType("Ict.Petra.Server.MSysMan.ImportExport.TImportExportManager"); FImportExportManager = (IImportExportManager)Activator.CreateInstance(ImportExportType, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); Assembly DBUpgradesAssembly = Assembly.Load("Ict.Petra.Server.lib.MSysMan.DBUpgrades"); Type DatabaseUpgradeType = DBUpgradesAssembly.GetType("Ict.Petra.Server.MSysMan.DBUpgrades.TDBUpgrades"); FDBUpgrades = (IDBUpgrades)Activator.CreateInstance(DatabaseUpgradeType, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); Type UserManagement = SysManAssembly.GetType("Ict.Petra.Server.MSysMan.Maintenance.UserManagement.TUserManager"); FUserManager = (IUserManager)Activator.CreateInstance(UserManagement, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); TClientManager.InitializeStaticVariables( FUserManager, new TErrorLog(), new TLoginLog(), new TMaintenanceLogonMessage()); // // Set up 'Timed Processing' // TTimedProcessing.DailyStartTime24Hrs = TAppSettingsManager.GetValue("Server.Processing.DailyStartTime24Hrs", "00:30"); if (TAppSettingsManager.GetBoolean("Server.Processing.PartnerReminders.Enabled", true)) { Assembly PartnerProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MPartner.processing"); Type PartnerReminderClass = PartnerProcessingAssembly.GetType("Ict.Petra.Server.MPartner.Processing.TProcessPartnerReminders"); TTimedProcessing.AddProcessingJob( "TProcessPartnerReminders", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), PartnerReminderClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.AutomatedIntranetExport.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type IntranetExportClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessAutomatedIntranetExport"); TTimedProcessing.AddProcessingJob( "TProcessAutomatedIntranetExport", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), IntranetExportClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.DataChecks.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type ProcessDataChecksClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessDataChecks"); TTimedProcessing.AddProcessingJob( "TProcessDataChecks", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), ProcessDataChecksClass, "Process")); } }
/// 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); //TLogging.Log("cost centres " + ReportingCostCentres); //TLogging.Log("accounts " + IncludeAccounts); 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>(); 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); } TGDPdUExportWorkers.Export(OutputPathForYear, CSVSeparator, NewLine, LedgerNumber * 1000000, FirstFinancialYear + FinancialYear); TGDPdUExportTransactions.ExportGLTransactions(OutputPathForYear, CSVSeparator, NewLine, LedgerNumber, FinancialYear, ReportingCostCentres, IgnoreAccounts, IgnoreTransactionsByReference, ref CostCentresInvolved, ref AccountsInvolved); TGDPdUExportBalances.ExportGLBalances(OutputPathForYear, CSVSeparator, NewLine, LedgerNumber, FinancialYear, ReportingCostCentres, IgnoreAccounts); } TGDPdUExportAccountsAndCostCentres.ExportCostCentres(OutputPath, CSVSeparator, NewLine, LedgerNumber, CostCentresInvolved); TGDPdUExportAccountsAndCostCentres.ExportAccounts(OutputPath, CSVSeparator, NewLine, LedgerNumber, AccountsInvolved); } catch (Exception e) { TLogging.Log(e.ToString()); } if (TAppSettingsManager.GetValue("interactive", "true") == "true") { Console.WriteLine("Please press Enter to continue..."); Console.ReadLine(); } }
/// <summary> /// Initialises Logging and parses Server settings from different sources. /// </summary> public TServerManager() : base() { // Create SystemDefaults Cache TSystemDefaultsCache.GSystemDefaultsCache = new TSystemDefaultsCache(); DomainManager.GetSiteKeyFromSystemDefaultsCacheDelegate = @TSystemDefaultsCache.GSystemDefaultsCache.GetSiteKeyDefault; TCacheableTablesManager.InitializeUnit(); TCacheableTablesManager.GCacheableTablesManager = new TCacheableTablesManager(new TDelegateSendClientTask(TClientManager.QueueClientTask)); Assembly SysManAssembly = Assembly.Load("Ict.Petra.Server.lib.MSysMan"); Type ImportExportType = SysManAssembly.GetType("Ict.Petra.Server.MSysMan.ImportExport.TImportExportManager"); FImportExportManager = (IImportExportManager)Activator.CreateInstance(ImportExportType, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); Assembly DBUpgradesAssembly = Assembly.Load("Ict.Petra.Server.lib.MSysMan.DBUpgrades"); Type DatabaseUpgradeType = DBUpgradesAssembly.GetType("Ict.Petra.Server.MSysMan.DBUpgrades.TDBUpgrades"); FDBUpgrades = (IDBUpgrades)Activator.CreateInstance(DatabaseUpgradeType, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); Type UserManagement = SysManAssembly.GetType("Ict.Petra.Server.MSysMan.Maintenance.UserManagement.TUserManager"); FUserManager = (IUserManager)Activator.CreateInstance(UserManagement, (BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod), null, null, null); TClientManager.InitializeStaticVariables(TSystemDefaultsCache.GSystemDefaultsCache, FUserManager, new TErrorLog(), new TLoginLog(), new TMaintenanceLogonMessage(), ExceptionHandling_DBConnectionBrokenCallback); // // Set up 'Timed Processing' // TTimedProcessing.DailyStartTime24Hrs = TAppSettingsManager.GetValue("Server.Processing.DailyStartTime24Hrs", "00:30"); if (TAppSettingsManager.GetBoolean("Server.Processing.PartnerReminders.Enabled", true)) { Assembly PartnerProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MPartner.processing"); Type PartnerReminderClass = PartnerProcessingAssembly.GetType("Ict.Petra.Server.MPartner.Processing.TProcessPartnerReminders"); TTimedProcessing.AddProcessingJob( "TProcessPartnerReminders", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), PartnerReminderClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.AutomatedIntranetExport.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type IntranetExportClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessAutomatedIntranetExport"); TTimedProcessing.AddProcessingJob( "TProcessAutomatedIntranetExport", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), IntranetExportClass, "Process")); } if (TAppSettingsManager.GetBoolean("Server.Processing.DataChecks.Enabled", false)) { Assembly CommonProcessingAssembly = Assembly.Load("Ict.Petra.Server.lib.MCommon.processing"); Type ProcessDataChecksClass = CommonProcessingAssembly.GetType("Ict.Petra.Server.MCommon.Processing.TProcessDataChecks"); TTimedProcessing.AddProcessingJob( "TProcessDataChecks", (TTimedProcessing.TProcessDelegate)Delegate.CreateDelegate( typeof(TTimedProcessing.TProcessDelegate), ProcessDataChecksClass, "Process")); } }
/// <summary> /// Loads settings from .NET Configuration File and Command Line. /// /// </summary> /// <returns>void</returns> public TClientSettings() : base() { // // Parse settings from the Application Configuration File // UPathLog = GetPathLog(); UDebugLevel = TAppSettingsManager.GetInt16("Client.DebugLevel", 0); TLogging.DebugLevel = UDebugLevel; UBehaviourSeveralClients = "OnlyOneWithQuestion"; if (TAppSettingsManager.HasValue("BehaviourSeveralClients")) { UBehaviourSeveralClients = TAppSettingsManager.GetValue("BehaviourSeveralClients"); } UDelayedDataLoading = TAppSettingsManager.GetBoolean("DelayedDataLoading", false); UReportingPathReportSettings = GetUserPath("Reporting.PathReportSettings", ""); UReportingPathReportUserSettings = GetUserPath("Reporting.PathReportUserSettings", ""); UServerPollIntervalInSeconds = TAppSettingsManager.GetInt32("ServerPollIntervalInSeconds", 5); UServerObjectKeepAliveIntervalInSeconds = TAppSettingsManager.GetInt32("ServerObjectKeepAliveIntervalInSeconds", 10); URemoteDataDirectory = TAppSettingsManager.GetValue("RemoteDataDirectory"); URemoteTmpDirectory = TAppSettingsManager.GetValue("RemoteTmpDirectory"); URunAsStandalone = TAppSettingsManager.GetBoolean("RunAsStandalone", false); URunAsRemote = TAppSettingsManager.GetBoolean("RunAsRemote", false); UPetra_Path_RemotePatches = ""; UPetra_Path_Dat = ""; UPetra_Path_Patches = ""; UPetraWebsite_Link = TAppSettingsManager.GetValue("OpenPetra.Website", "http://www.openpetra.org"); UPetraPatches_Link = TAppSettingsManager.GetValue("OpenPetra.Path.RemotePatches", "http://www.example.org/index.php?page=OpenPetraPatches"); UPetraSupportTeamEmail = TAppSettingsManager.GetValue("OpenPetra.SupportTeamEmail", String.Empty); if (URunAsStandalone == true) { UPetraServerAdmin_Configfile = TAppSettingsManager.GetValue("PetraServerAdmin.Configfile"); UPetraServer_Configfile = TAppSettingsManager.GetValue("PetraServer.Configfile"); UPetra_Path_Patches = Petra_Path_Bin + Path.DirectorySeparatorChar + "sa-patches"; UPostgreSql_BaseDir = TAppSettingsManager.GetValue("PostgreSQLServer.BaseDirectory"); UPostgreSql_DataDir = TAppSettingsManager.GetValue("PostgreSQLServer.DataDirectory"); } if (URunAsRemote == true) { UPetra_Path_Patches = GetUserPath("OpenPetra.Path.Patches", ""); UPetra_Path_Dat = GetUserPath("OpenPetra.Path.Dat", ""); UPetra_Path_RemotePatches = TAppSettingsManager.GetValue("OpenPetra.Path.RemotePatches"); } if ((!URunAsRemote) && (!URunAsStandalone)) { // network version UPetra_Path_Patches = Petra_Path_Bin + Path.DirectorySeparatorChar + "net-patches"; } if (TAppSettingsManager.HasValue("StartupMessage")) { UCustomStartupMessage = TAppSettingsManager.GetValue("StartupMessage"); } UHTMLHelpBaseURLLocal = TAppSettingsManager.GetValue("HTMLHelpBaseURLLocal", String.Empty); UHTMLHelpBaseURLOnInternet = TAppSettingsManager.GetValue("HTMLHelpBaseURLOnInternet", String.Empty); ULocalHTMLHelp = TAppSettingsManager.GetBoolean("LocalHTMLHelp", true); }
/// <summary> /// Initialises the internal variables that hold the Server Settings, using the current config file. /// /// </summary> /// <returns>void</returns> public TSrvSetting() { if (USingletonSrvSetting == null) { USingletonSrvSetting = this; } FConfigurationFile = TAppSettingsManager.ConfigFileName; FExecutingOS = Utilities.DetermineExecutingOS(); // Server.RDBMSType FRDBMSType = CommonTypes.ParseDBType(TAppSettingsManager.GetValue("Server.RDBMSType", "postgresql")); FDatabaseHostOrFile = TAppSettingsManager.GetValue("Server.DBHostOrFile", "localhost"); FDatabasePort = TAppSettingsManager.GetValue("Server.DBPort", "5432"); FDatabaseName = TAppSettingsManager.GetValue("Server.DBName", "openpetra"); FDBUsername = TAppSettingsManager.GetValue("Server.DBUserName", "petraserver"); FDBPassword = TAppSettingsManager.GetValue("Server.DBPassword", string.Empty, false); if (FDBPassword == "PG_OPENPETRA_DBPWD") { // get the password from the file ~/.pgpass. This currently only works for PostgreSQL on Linux using (StreamReader sr = new StreamReader(Environment.GetFolderPath(Environment.SpecialFolder.Personal) + Path.DirectorySeparatorChar + ".pgpass")) { while (!sr.EndOfStream) { string line = sr.ReadLine(); if (line.StartsWith(FDatabaseHostOrFile + ":" + FDatabasePort + ":" + FDatabaseName + ":" + FDBUsername + ":") || line.StartsWith("*:" + FDatabasePort + ":" + FDatabaseName + ":" + FDBUsername + ":")) { FDBPassword = line.Substring(line.LastIndexOf(':') + 1); break; } } } } if (TAppSettingsManager.HasValue("Server.LogFile")) { FServerLogFile = TAppSettingsManager.GetValue("Server.LogFile", false); } else { // maybe the log file has already been set, eg. by the NUnit Server Test FServerLogFile = TLogging.GetLogFileName(); if (FServerLogFile.Length == 0) { // this is effectively the bin directory (current directory) FServerLogFile = "Server.log"; } } // Server.Port FIPBasePort = TAppSettingsManager.GetInt16("Server.Port", 9000); FRunAsStandalone = TAppSettingsManager.GetBoolean("Server.RunAsStandalone", false); // Server.ClientIdleStatusAfterXMinutes FClientIdleStatusAfterXMinutes = TAppSettingsManager.GetInt32("Server.ClientIdleStatusAfterXMinutes", 5); // Server.ClientKeepAliveCheckIntervalInSeconds FClientKeepAliveCheckIntervalInSeconds = TAppSettingsManager.GetInt32("Server.ClientKeepAliveCheckIntervalInSeconds", 60); // Server.ClientKeepAliveTimeoutAfterXSeconds_LAN FClientKeepAliveTimeoutAfterXSecondsLAN = TAppSettingsManager.GetInt32("Server.ClientKeepAliveTimeoutAfterXSeconds_LAN", 60); // Server.ClientKeepAliveTimeoutAfterXSeconds_Remote FClientKeepAliveTimeoutAfterXSecondsRemote = TAppSettingsManager.GetInt32("Server.ClientKeepAliveTimeoutAfterXSeconds_Remote", (ClientKeepAliveTimeoutAfterXSecondsLAN * 2)); // Server.ClientConnectionTimeoutAfterXSeconds FClientConnectionTimeoutAfterXSeconds = TAppSettingsManager.GetInt32("Server.ClientConnectionTimeoutAfterXSeconds", 20); // Server.ClientAppDomainShutdownAfterKeepAliveTimeout FClientAppDomainShutdownAfterKeepAliveTimeout = TAppSettingsManager.GetBoolean("Server.ClientAppDomainShutdownAfterKeepAliveTimeout", true); FSMTPServer = TAppSettingsManager.GetValue("Server.SMTPServer", "localhost"); // This is disabled in processing at the moment, so we reflect that here. When it works change to true FAutomaticIntranetExportEnabled = TAppSettingsManager.GetBoolean("Server.AutomaticIntranetExportEnabled", false); // The following setting specifies the email address where the Intranet Data emails are sent to when "Server.AutomaticIntranetExportEnabled" is true. FIntranetDataDestinationEmail = TAppSettingsManager.GetValue("Server.IntranetDataDestinationEmail", "???@???.org"); // The following setting is temporary - until we have created a GUI where users can specify the email address for the // responsible Personnel and Finance persons themselves. Those will be stored in SystemDefaults then. FIntranetDataSenderEmail = TAppSettingsManager.GetValue("Server.IntranetDataSenderEmail", "???@???.org"); // Determine network configuration of the Server Networking.DetermineNetworkConfig(out FHostName, out FHostIPAddresses); FApplicationVersion = TFileVersionInfo.GetApplicationVersion(); }
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 = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, 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 "; DBAccess.GDBAccessObj.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 "; DBAccess.GDBAccessObj.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(DBAccess.GDBAccessObj.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 "; DBAccess.GDBAccessObj.Select(WorkingDS, strSQL, WorkingDS.ALedgerInfo.TableName, Transaction); } }); // Accept row changes here so that the Client gets 'unmodified' rows WorkingDS.AcceptChanges(); return(WorkingDS); }