/// <summary> /// Calculates the report, which is specified in the parameters table /// /// </summary> /// <returns>void</returns> public void Start(System.Data.DataTable AParameters) { FProgressID = "ReportCalculation" + Guid.NewGuid(); TProgressTracker.InitProgressTracker(FProgressID, string.Empty, -1.0m); FParameterList = new TParameterList(); FParameterList.LoadFromDataTable(AParameters); FSuccess = false; String PathStandardReports = TAppSettingsManager.GetValue("Reporting.PathStandardReports"); String PathCustomReports = TAppSettingsManager.GetValue("Reporting.PathCustomReports"); FDatacalculator = new TRptDataCalculator(PathStandardReports, PathCustomReports); // setup the logging to go to the TProgressTracker TLogging.SetStatusBarProcedure(new TLogging.TStatusCallbackProcedure(WriteToStatusBar)); string session = TSession.GetSessionID(); ThreadStart myThreadStart = delegate { Run(session); }; Thread TheThread = new Thread(myThreadStart); TheThread.CurrentCulture = Thread.CurrentThread.CurrentCulture; TheThread.Name = FProgressID + "_" + UserInfo.GetUserInfo().UserID + "__TReportGeneratorUIConnector.Start_Thread"; TLogging.LogAtLevel(7, TheThread.Name + " starting."); TheThread.Start(); }
/// <summary> /// Executes the query. Always call this method in a separate Thread to execute the query asynchronously! /// </summary> /// <param name="ASessionID">the id of the current session</param> /// <param name="AContext">Context in which this quite generic Method gets called (e.g. 'Partner Find'). This is /// optional but should be specified to aid in debugging as it gets logged in case Exceptions happen when the /// DB Transaction is taken out and the Query gets executed.</param> /// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null /// gets passed then the Method executes DB commands with a new Database connection</param> /// <remarks>An instance of TAsyncFindParameters with set up Properties must exist before this procedure can get /// called! /// </remarks> public void ExecuteQuery(string ASessionID, string AContext = null, TDataBase ADataBase = null) { // need to initialize the database session TSession.InitThread(ASessionID); TDataBase db = DBAccess.Connect("ExecuteQuery", ADataBase); try { FProgressID = Guid.NewGuid().ToString(); TProgressTracker.InitProgressTracker(FProgressID, "Executing Query...", 100.0m); // Create SQL statement and execute it to return all records ExecuteFullQuery(AContext, db); } catch (Exception exp) { TLogging.Log(this.GetType().FullName + ".ExecuteQuery" + (AContext == null ? "" : " (Context: " + AContext + ")") + ": Exception occured: " + exp.ToString()); // Inform the caller that something has gone wrong... TProgressTracker.CancelJob(FProgressID); /* * WE MUST 'SWALLOW' ANY EXCEPTION HERE, OTHERWISE THE WHOLE * PETRASERVER WILL GO DOWN!!! (THIS BEHAVIOUR IS NEW WITH .NET 2.0.) * * --> ANY EXCEPTION THAT WOULD LEAVE THIS METHOD WOULD BE SEEN AS AN <-- * --> UNHANDLED EXCEPTION IN A THREAD, AND THE .NET/MONO RUNTIME <-- * --> WOULD BRING DOWN THE WHOLE PETRASERVER PROCESS AS A CONSEQUENCE! <-- * */ } }
public static Boolean CreateRemittanceAdviceAndChequeFormData(TFormLetterFinanceInfo AFormLetterFinanceInfo, ref AccountsPayableTDSAApPaymentTable APaymentTable, Int32 ALedgerNumber, bool AIncludeChequeFormData, out List <TFormData> AFormDataList) { AFormDataList = new List <TFormData>(); TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Creating Remittance Advice")); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Starting ..."), 10.0m); // Print in payment number order APaymentTable.DefaultView.Sort = string.Format("{0} ASC", AccountsPayableTDSAApPaymentTable.GetPaymentNumberDBName()); for (int i = 0; i < APaymentTable.DefaultView.Count; i++) { AccountsPayableTDSAApPaymentRow row = (AccountsPayableTDSAApPaymentRow)APaymentTable.DefaultView[i].Row; int paymentNumber = row.PaymentNumber; decimal progress = i / APaymentTable.DefaultView.Count * 100.0m; TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString(string.Format("Printing payment {0} ...", paymentNumber)), progress); CreateFormDataInternal(ALedgerNumber, paymentNumber, AFormLetterFinanceInfo, AFormDataList, AIncludeChequeFormData, row.ChequeNumber, row.ChequeAmountInWords); } TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); return(true); }
public static Boolean CreateRemittanceAdviceFormData(TFormLetterFinanceInfo AFormLetterFinanceInfo, List <int> APaymentNumberList, Int32 ALedgerNumber, out List <TFormData> AFormDataList) { AFormDataList = new List <TFormData>(); TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Creating Remittance Advice")); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Starting ..."), 10.0m); int counter = 0; foreach (int paymentNumber in APaymentNumberList) { counter++; decimal progress = counter / APaymentNumberList.Count * 100.0m; TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString(string.Format("Printing payment {0} ...", paymentNumber)), progress); CreateFormDataInternal(ALedgerNumber, paymentNumber, AFormLetterFinanceInfo, AFormDataList, false); } TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); return(true); }
/// <summary> /// Calculates the report, which is specified in the parameters table /// /// </summary> /// <returns>void</returns> public void Start(System.Data.DataTable AParameters) { TRptUserFunctionsFinance.FlushSqlCache(); FProgressID = "ReportCalculation" + Guid.NewGuid(); TProgressTracker.InitProgressTracker(FProgressID, string.Empty, -1.0m); FParameterList = new TParameterList(); FParameterList.LoadFromDataTable(AParameters); FSuccess = false; String PathStandardReports = TAppSettingsManager.GetValue("Reporting.PathStandardReports"); String PathCustomReports = TAppSettingsManager.GetValue("Reporting.PathCustomReports"); FDatacalculator = new TRptDataCalculator(DBAccess.GDBAccessObj, PathStandardReports, PathCustomReports); // setup the logging to go to the TProgressTracker TLogging.SetStatusBarProcedure(new TLogging.TStatusCallbackProcedure(WriteToStatusBar)); string session = TSession.GetSessionID(); ThreadStart myThreadStart = delegate { Run(session); }; Thread TheThread = new Thread(myThreadStart); TheThread.Name = FProgressID; TheThread.CurrentCulture = Thread.CurrentThread.CurrentCulture; TheThread.Start(); }
public static string Create() { string session = TSession.GetSessionID(); string ReportID = "ReportCalculation" + session + Guid.NewGuid(); TProgressTracker.InitProgressTracker(ReportID, string.Empty, -1.0m); return(ReportID); }
private static bool TrainBankStatementsLastMonthThread(String ASessionID, Int32 AClientID, Int32 ALedgerNumber, DateTime AToday) { TSession.InitThread(ASessionID); DomainManager.GClientID = AClientID; string MyClientID = DomainManager.GClientID.ToString(); TProgressTracker.InitProgressTracker(MyClientID, Catalog.GetString("Training last months bank statements"), 30); DateTime startDateThisMonth = new DateTime(AToday.Year, AToday.Month, 1); DateTime endDateLastMonth = startDateThisMonth.AddDays(-1); DateTime startDateLastMonth = new DateTime(endDateLastMonth.Year, endDateLastMonth.Month, 1); // get all bank accounts TCacheable CachePopulator = new TCacheable(); Type typeofTable; GLSetupTDSAAccountTable accounts = (GLSetupTDSAAccountTable)CachePopulator.GetCacheableTable( TCacheableFinanceTablesEnum.AccountList, "", false, ALedgerNumber, out typeofTable); foreach (GLSetupTDSAAccountRow account in accounts.Rows) { // at OM Germany we don't have the bank account flags set if ((!account.IsBankAccountFlagNull() && (account.BankAccountFlag == true)) || (!account.IsCashAccountFlagNull() && (account.CashAccountFlag == true)) ) { string BankAccountCode = account.AccountCode; DateTime counter = startDateLastMonth; while (!counter.Equals(startDateThisMonth)) { TProgressTracker.SetCurrentState( MyClientID, String.Format(Catalog.GetString("Training {0} {1}"), BankAccountCode, counter.ToShortDateString()), counter.Day); // TODO: train only one bank statement per date and bank account? TrainBankStatement(ALedgerNumber, counter, BankAccountCode); counter = counter.AddDays(1); } } } TProgressTracker.FinishJob(MyClientID); return(true); }
/// <summary> /// Executes the query. Call this method in a separate Thread to execute the /// query asynchronously! /// </summary> /// <remarks>An instance of TAsyncFindParameters with set up Properties must /// exist before this procedure can get called! /// </remarks> /// <returns>void</returns> public void ExecuteQuery(string ASessionID) { bool ownDatabaseConnection = false; // need to initialize the database session TSession.InitThread(ASessionID); if (!DBAccess.GDBAccessObj.ConnectionOK) { // we need a separate database object for this thread, since we cannot access the session object DBAccess.GDBAccessObj.EstablishDBConnection(TSrvSetting.RDMBSType, TSrvSetting.PostgreSQLServer, TSrvSetting.PostgreSQLServerPort, TSrvSetting.PostgreSQLDatabaseName, TSrvSetting.DBUsername, TSrvSetting.DBPassword, ""); ownDatabaseConnection = true; } try { FProgressID = Guid.NewGuid().ToString(); TProgressTracker.InitProgressTracker(FProgressID, "Executing Query...", 100.0m); // Create SQL statement and execute it to return all records ExecuteFullQuery(); } catch (Exception exp) { TLogging.Log(this.GetType().FullName + ".ExecuteQuery: Exception occured: " + exp.ToString()); // Inform the caller that something has gone wrong... TProgressTracker.CancelJob(FProgressID); /* * WE MUST 'SWALLOW' ANY EXCEPTION HERE, OTHERWISE THE WHOLE * PETRASERVER WILL GO DOWN!!! (THIS BEHAVIOUR IS NEW WITH .NET 2.0.) * * --> ANY EXCEPTION THAT WOULD LEAVE THIS METHOD WOULD BE SEEN AS AN <-- * --> UNHANDLED EXCEPTION IN A THREAD, AND THE .NET/MONO RUNTIME <-- * --> WOULD BRING DOWN THE WHOLE PETRASERVER PROCESS AS A CONSEQUENCE! <-- * */ } if (ownDatabaseConnection) { DBAccess.GDBAccessObj.CloseDBConnection(); } }
public static Boolean FillFormDataFromExtract(Int64 AEventPartnerKey, Int32 AExtractId, TFormLetterInfo AFormLetterInfo, out List <TFormData> AFormDataList) { Boolean ReturnValue = true; List <TFormData> dataList = new List <TFormData>(); MExtractTable ExtractTable; Int32 RowCounter = 0; TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Create Attendee Form Letter")); TDBTransaction ReadTransaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref ReadTransaction, delegate { ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, ReadTransaction); RowCounter = 0; // query all rows of given extract foreach (MExtractRow ExtractRow in ExtractTable.Rows) { RowCounter++; TFormDataAttendee formDataAttendee = new TFormDataAttendee(); FillFormDataFromAttendee(AEventPartnerKey, ExtractRow.PartnerKey, formDataAttendee, AFormLetterInfo, ExtractRow.SiteKey, ExtractRow.LocationKey); dataList.Add(formDataAttendee); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob) { dataList.Clear(); ReturnValue = false; TLogging.Log("Retrieve Conference Form Letter Data - Job cancelled"); break; } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving Attendee Data"), (RowCounter * 100) / ExtractTable.Rows.Count); } }); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); AFormDataList = new List <TFormData>(); AFormDataList = dataList; return(ReturnValue); }
public static Boolean FillFormDataForAllAttendees(Int64 AEventPartnerKey, TFormLetterInfo AFormLetterInfo, out List <TFormData> AFormDataList) { Boolean ReturnValue = true; List <TFormData> dataList = new List <TFormData>(); PcAttendeeTable AttendeeTable; Int32 RowCounter = 0; TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Create Attendee Form Letter for All Attendee")); TDBTransaction ReadTransaction = new TDBTransaction(); DBAccess.ReadTransaction( ref ReadTransaction, delegate { AttendeeTable = PcAttendeeAccess.LoadViaPcConference(AEventPartnerKey, ReadTransaction); RowCounter = 0; // query all rows of given extract foreach (PcAttendeeRow AttendeeRow in AttendeeTable.Rows) { RowCounter++; TFormDataAttendee formDataAttendee = new TFormDataAttendee(); FillFormDataFromAttendee(AEventPartnerKey, AttendeeRow.PartnerKey, formDataAttendee, AFormLetterInfo); dataList.Add(formDataAttendee); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob) { dataList.Clear(); ReturnValue = false; TLogging.Log("Retrieve Conference Form Letter Data for All Attendees - Job cancelled"); break; } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving Attendee Data"), (RowCounter * 100) / AttendeeTable.Rows.Count); } }); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); AFormDataList = new List <TFormData>(); AFormDataList = dataList; return(ReturnValue); }
public static Boolean FillFormDataFromExtract(Int32 AExtractId, TFormLetterInfo AFormLetterInfo, out List <TFormData> AFormDataList) { Boolean ReturnValue = true; List <TFormData> dataList = new List <TFormData>(); MExtractTable ExtractTable; Int32 RowCounter = 0; TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Create Personnel Form Letter")); TDBTransaction ReadTransaction = new TDBTransaction(); DBAccess.ReadTransaction( ref ReadTransaction, delegate { ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, ReadTransaction); RowCounter = 0; // query all rows of given extract foreach (MExtractRow ExtractRow in ExtractTable.Rows) { RowCounter++; TFormDataPerson FormDataPerson = new TFormDataPerson(); FillFormDataFromPersonnel(ExtractRow.PartnerKey, FormDataPerson, AFormLetterInfo, ExtractRow.SiteKey, ExtractRow.LocationKey); dataList.Add(FormDataPerson); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob) { dataList.Clear(); ReturnValue = false; TLogging.Log("Retrieve Personnel Form Letter Data - Job cancelled"); break; } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving Personnel Data"), (RowCounter * 100) / ExtractTable.Rows.Count); } }); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); AFormDataList = new List <TFormData>(); AFormDataList = dataList; return(ReturnValue); }
public static TSubmitChangesResult StoreNewBankStatement(BankImportTDS AStatementAndTransactionsDS, out Int32 AFirstStatementKey) { string MyClientID = DomainManager.GClientID.ToString(); AFirstStatementKey = -1; TProgressTracker.InitProgressTracker(MyClientID, Catalog.GetString("Processing new bank statements"), AStatementAndTransactionsDS.AEpStatement.Rows.Count + 1); TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("Saving to database"), 0); try { // Must not throw away the changes because we need the correct statement keys AStatementAndTransactionsDS.DontThrowAwayAfterSubmitChanges = true; BankImportTDSAccess.SubmitChanges(AStatementAndTransactionsDS); AFirstStatementKey = -1; if (AStatementAndTransactionsDS != null) { TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("starting to train"), 1); AFirstStatementKey = AStatementAndTransactionsDS.AEpStatement[0].StatementKey; // search for already posted gift batches, and do the matching for these imported statements TBankImportMatching.Train(AStatementAndTransactionsDS.AEpStatement); } TProgressTracker.FinishJob(MyClientID); } catch (Exception ex) { TLogging.Log(ex.ToString()); TProgressTracker.CancelJob(MyClientID); return(TSubmitChangesResult.scrError); } return(TSubmitChangesResult.scrOK); }
/// <summary> /// Calculates the report, which is specified in the parameters table /// /// </summary> /// <returns>void</returns> public void Start(System.Data.DataTable AParameters) { FProgressID = "ReportCalculation" + Guid.NewGuid(); TProgressTracker.InitProgressTracker(FProgressID, string.Empty, -1.0m); // First check whether the 'globally available' DB Connection isn't busy - we must not allow the start of a // Report Calculation when that is the case (as this would lead to a nested DB Transaction exception, // EDBTransactionBusyException). if (DBAccess.GDBAccessObj.Transaction != null) { FErrorMessage = Catalog.GetString(SharedConstants.NO_PARALLEL_EXECUTION_OF_XML_REPORTS_PREFIX + "The OpenPetra Server is currently too busy to prepare this Report. " + "Please retry once other running tasks (eg. a Report) are finished!"); TProgressTracker.FinishJob(FProgressID); FSuccess = false; // Return to the Client immediately! return; } TRptUserFunctionsFinance.FlushSqlCache(); FParameterList = new TParameterList(); FParameterList.LoadFromDataTable(AParameters); FSuccess = false; String PathStandardReports = TAppSettingsManager.GetValue("Reporting.PathStandardReports"); String PathCustomReports = TAppSettingsManager.GetValue("Reporting.PathCustomReports"); FDatacalculator = new TRptDataCalculator(DBAccess.GDBAccessObj, PathStandardReports, PathCustomReports); // setup the logging to go to the TProgressTracker TLogging.SetStatusBarProcedure(new TLogging.TStatusCallbackProcedure(WriteToStatusBar)); string session = TSession.GetSessionID(); ThreadStart myThreadStart = delegate { Run(session); }; Thread TheThread = new Thread(myThreadStart); TheThread.CurrentCulture = Thread.CurrentThread.CurrentCulture; TheThread.Name = FProgressID + "_" + UserInfo.GUserInfo.UserID + "__TReportGeneratorUIConnector.Start_Thread"; TLogging.LogAtLevel(7, TheThread.Name + " starting."); TheThread.Start(); }
/// <summary> /// sample webconnector method that takes a long time and uses the ProgressTracker /// </summary> static public string LongRunningJob() { string ClientID = DomainManager.GClientID.ToString(); // enable DebugLevel to show progress state on console TLogging.DebugLevel = 1; TProgressTracker.InitProgressTracker(ClientID, "LongRunningJob", 100); for (int i = 0; i < 10; i++) { Thread.Sleep(500); TProgressTracker.SetCurrentState(ClientID, "working", Convert.ToDecimal(i * 10)); } TProgressTracker.FinishJob(ClientID); return("done"); }
/// <summary> /// todoComment /// </summary> /// <param name="AInspectDT"></param> public void SubmitChangesAsync(PSubscriptionTable AInspectDT) { Thread TheThread; // Cleanup (might be left in a certain state from a possible earlier call) FSubmitException = null; FSubmissionDT = null; FVerificationResult = null; FResponseDT = null; FInspectDT = null; FProgressID = Guid.NewGuid().ToString(); TProgressTracker.InitProgressTracker(FProgressID, string.Empty, 100.0m); FInspectDT = AInspectDT; ThreadStart ThreadStartDelegate = new ThreadStart(SubmitChangesInternal); TheThread = new Thread(ThreadStartDelegate); TheThread.Name = "ExtractsAddSubscriptionsSubmitChanges" + Guid.NewGuid().ToString(); TheThread.Start(); TLogging.LogAtLevel(6, "TExtractsAddSubscriptionsUIConnector.SubmitChangesAsync thread started."); }
public static bool ImportPartnerTaxCodes( Hashtable AParameters, String AImportString, out TVerificationResultCollection AErrorMessages, out List <string> AOutputLines, out bool AExtractCreated, out int AExtractId, out int AExtractKeyCount, out int AImportedCodeCount, out int ADeletedCodeCount, out int ATaxCodeMismatchCount) { TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Importing Partner Tax Codes"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Initialising"), 5); TVerificationResultCollection errorMessages = new TVerificationResultCollection(); List <string> outputLines = new List <string>(); List <long> partnerTaxCodesImported = new List <long>(); List <long> partnerTaxCodesDeleted = new List <long>(); int taxCodeMismatchCount = 0; bool extractCreated = false; int extractId = -1; int extractKeyCount = 0; string delimiter = Convert.ToString(AParameters["Delimiter"]); bool firstRowIsHeader = Convert.ToBoolean(AParameters["FirstRowIsHeader"]); bool failIfNotPerson = Convert.ToBoolean(AParameters["FailIfNotPerson"]); bool failIfInvalidPartner = Convert.ToBoolean(AParameters["FailIfInvalidPartner"]); bool overwriteExistingTaxCode = Convert.ToBoolean(AParameters["OverwriteExistingTaxCode"]); bool createExtract = Convert.ToBoolean(AParameters["CreateExtract"]); bool createOutputFile = Convert.ToBoolean(AParameters["CreateOutFile"]); bool includePartnerDetails = Convert.ToBoolean(AParameters["IncludePartnerDetails"]); int emptyTaxCodeAction = Convert.ToInt32(AParameters["EmptyTaxCode"]); string taxCodeType = Convert.ToString(AParameters["TaxCodeType"]); string extractName = Convert.ToString(AParameters["ExtractName"]); string extractDescription = Convert.ToString(AParameters["ExtractDescription"]); StringReader sr = new StringReader(AImportString); int rowNumber = 0; bool cancelledByUser = false; bool doneHeaderRow = (firstRowIsHeader == false); TDBTransaction transaction = null; bool submissionOK = false; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, 3, ref transaction, ref submissionOK, delegate { try { PTaxTable taxTable = PTaxAccess.LoadAll(transaction); string importLine = sr.ReadLine(); int percentDone = 10; int previousPercentDone = 0; int initialTextLength = AImportString.Length; int totalRows = AImportString.Split('\n').Length; if (delimiter.Length < 1) { // should not happen delimiter = ","; } while (importLine != null) { rowNumber++; percentDone = 10 + ((rowNumber * 80) / totalRows); // skip empty lines and commented lines if ((importLine.Trim().Length == 0) || importLine.StartsWith("/*") || importLine.StartsWith("#")) { importLine = sr.ReadLine(); continue; } int numberOfElements = StringHelper.GetCSVList(importLine, delimiter).Count; if (numberOfElements < 2) { // That is a critical error TVerificationResult errorMsg = new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, rowNumber), Catalog.GetString("Wrong number of columns. There must be at least 2"), TResultSeverity.Resv_Critical); errorMessages.Add(errorMsg); outputLines.Add(string.Format("\"Wrong number of columns in Line {0}\"", rowNumber)); importLine = sr.ReadLine(); continue; } if (!doneHeaderRow) { // Skip a header row doneHeaderRow = true; importLine = sr.ReadLine(); continue; } int preParseMessageCount = errorMessages.Count; importLine = importLine.TrimStart(' '); // TCommonImport.ImportInt64 does not remove leading spaces before optional quotes Int64 partnerKey = TCommonImport.ImportInt64(ref importLine, delimiter, null, null, rowNumber, errorMessages, null); importLine = importLine.TrimStart(' '); // TCommonImport.ImportString does not remove leading spaces before optional quotes string taxCode = TCommonImport.ImportString(ref importLine, delimiter, null, null, rowNumber, errorMessages, false); if (errorMessages.Count > preParseMessageCount) { outputLines.Add(string.Format("\"Error reading Line {0}\"", rowNumber)); importLine = sr.ReadLine(); continue; } // Ok so we have a good key and a code, which might be null // See if the partner exists PartnerFindTDS partner = TSimplePartnerFindWebConnector.FindPartners(partnerKey, true); PartnerFindTDSSearchResultTable result = partner.SearchResult; if (result.DefaultView.Count == 0) { // We don't have the partner if (failIfInvalidPartner) { TVerificationResult errorMsg = new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, rowNumber), Catalog.GetString("Could not find the specified partner"), TResultSeverity.Resv_Critical); errorMessages.Add(errorMsg); outputLines.Add(string.Format("\"Error\"{0}{1}{0}{0}{0}{0}\"Partner not found\"", delimiter, partnerKey)); } else { outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}{0}{0}{0}\"Partner not found\"", delimiter, partnerKey)); } importLine = sr.ReadLine(); continue; } // We already have this partner PartnerFindTDSSearchResultRow row = (PartnerFindTDSSearchResultRow)result.DefaultView[0].Row; string partnerClass = row.PartnerClass; string partnerDetails = string.Empty; if (includePartnerDetails) { string shortName = row.PartnerShortName; string bestAddress = string.Empty; PLocationTable locationTable; string country; if (TAddressTools.GetBestAddress(partnerKey, out locationTable, out country, transaction)) { if ((locationTable != null) && (locationTable.Rows.Count > 0)) { bestAddress = Calculations.DetermineLocationString((PLocationRow)locationTable.Rows[0], Calculations.TPartnerLocationFormatEnum.plfCommaSeparated); } } partnerDetails = string.Format("{0}\"{1}\"{0}\"{2}\"", delimiter, shortName, bestAddress); } if (partnerClass != "PERSON") { if (failIfNotPerson) { TVerificationResult errorMsg = new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, rowNumber), Catalog.GetString("Partner is not a PERSON"), TResultSeverity.Resv_Critical); errorMessages.Add(errorMsg); outputLines.Add(string.Format("\"Error\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"", delimiter, partnerKey, taxCode, partnerDetails, "Partner is not a PERSON")); } else { outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"", delimiter, partnerKey, taxCode, partnerDetails, "Partner is not a PERSON")); } importLine = sr.ReadLine(); continue; } bool deleteExistingRow = false; if (taxCode == null) { // what to do if the code is not supplied? if ((emptyTaxCodeAction == 0) || (emptyTaxCodeAction == 1)) { if (emptyTaxCodeAction == 0) { // Fail TVerificationResult errorMsg = new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, rowNumber), Catalog.GetString("No tax code specified"), TResultSeverity.Resv_Critical); errorMessages.Add(errorMsg); outputLines.Add(string.Format("\"Error\"{0}{1}{0}{2}{0}\"{3}\"", delimiter, partnerKey, partnerDetails, "No tax code specified")); } else { outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}{2}{0}\"{3}\"", delimiter, partnerKey, partnerDetails, "No tax code specified")); } importLine = sr.ReadLine(); continue; } else if (emptyTaxCodeAction == 2) { deleteExistingRow = true; } else { throw new Exception("Developer error! Unknown action for an empty tax code..."); } } // See if there is a tax code already for this partner PTaxRow taxRow = null; taxTable.DefaultView.RowFilter = string.Format("{0}={1} AND {2}='{3}'", PTaxTable.GetPartnerKeyDBName(), partnerKey, PTaxTable.GetTaxTypeDBName(), taxCodeType); if (taxTable.DefaultView.Count == 0) { if (deleteExistingRow) { // The tax code is null and we can only delete if the row already exists in the tax table outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"", delimiter, partnerKey, taxCode, partnerDetails, "Cannot delete. The partner is not in the tax table.")); } else { // Add a new row taxRow = taxTable.NewRowTyped(true); taxRow.PartnerKey = partnerKey; taxRow.TaxType = taxCodeType; taxRow.TaxRef = taxCode; taxTable.Rows.Add(taxRow); outputLines.Add(string.Format("\"Added\"{0}{1}{0}\"{2}\"{3}", delimiter, partnerKey, taxCode, partnerDetails)); partnerTaxCodesImported.Add(partnerKey); } } else { // the row exists already. We expect there to only be one row per tax code type for a given partner // If partners can have multiple codes of a specified type the code below will need to change! taxRow = (PTaxRow)taxTable.DefaultView[0].Row; string currentCode = taxRow.TaxRef; if (deleteExistingRow) { if ((taxRow.RowState != DataRowState.Detached) && (taxRow.RowState != DataRowState.Deleted)) { taxRow.Delete(); } outputLines.Add(string.Format("\"Deleted\"{0}{1}{0}\"{2}\"{3}", delimiter, partnerKey, currentCode, partnerDetails)); partnerTaxCodesDeleted.Add(partnerKey); } else { // Check if it is the same if (string.Compare(currentCode, taxCode, false) == 0) { // they are the same outputLines.Add(string.Format("\"Unchanged\"{0}{1}{0}\"{2}\"{3}", delimiter, partnerKey, taxCode, partnerDetails)); partnerTaxCodesImported.Add(partnerKey); // this counts as an import } else { // They are different if (overwriteExistingTaxCode) { // Overwrite the old value taxRow.TaxRef = taxCode; outputLines.Add(string.Format("\"Modified\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"", delimiter, partnerKey, taxCode, partnerDetails, "Previous code was: " + currentCode)); partnerTaxCodesImported.Add(partnerKey); } else { // Do nothing on this row outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"", delimiter, partnerKey, currentCode, partnerDetails, "WARNING!! File and Database codes differ. New code is: " + taxCode)); taxCodeMismatchCount++; } } } if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { cancelledByUser = true; break; } if (errorMessages.HasCriticalErrors && (errorMessages.Count > 100)) { // This probably means that it is a big file and the user has made the same mistake many times over break; } // Update progress tracker every few percent if ((percentDone - previousPercentDone) > 3) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString("Importing row {0}"), rowNumber), (percentDone > 90) ? 90 : percentDone); previousPercentDone = percentDone; } } importLine = sr.ReadLine(); } // No more lines in the file - or cancelled by the user if (cancelledByUser) { errorMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, "The import was cancelled by the user.", TResultSeverity.Resv_Critical)); outputLines.Add("\"Import cancelled by user\""); } if (errorMessages.HasCriticalErrors) { submissionOK = false; partnerTaxCodesImported.Clear(); } else { PTaxAccess.SubmitChanges(taxTable, transaction); submissionOK = true; } // Now create the optional extract if (submissionOK && (partnerTaxCodesImported.Count > 0) && createExtract) { // we have more server work to do so we reset submission OK to false submissionOK = false; TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Creating new Extract ..."), 97); DataTable table = new DataTable(); table.Columns.Add(new DataColumn()); for (int i = 0; i < partnerTaxCodesImported.Count; i++) { DataRow row = table.NewRow(); row[0] = partnerTaxCodesImported[i]; table.Rows.Add(row); } List <long> ignoredKeys; extractCreated = TExtractsHandling.CreateExtractFromListOfPartnerKeys(extractName, extractDescription, out extractId, table, 0, false, out extractKeyCount, out ignoredKeys); submissionOK = true; } } catch (Exception ex) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Exception Occurred"), 0); if (TDBExceptionHelper.IsTransactionSerialisationException(ex)) { errorMessages.Add(new TVerificationResult("ImportPartnerTaxCodes", ErrorCodeInventory.RetrieveErrCodeInfo(PetraErrorCodes.ERR_DB_SERIALIZATION_EXCEPTION))); } else { errorMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, rowNumber), ex.Message, TResultSeverity.Resv_Critical)); } partnerTaxCodesImported.Clear(); } finally { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } }); AErrorMessages = errorMessages; AOutputLines = outputLines; AExtractCreated = extractCreated; AExtractId = extractId; AExtractKeyCount = extractKeyCount; AImportedCodeCount = partnerTaxCodesImported.Count; ADeletedCodeCount = partnerTaxCodesDeleted.Count; ATaxCodeMismatchCount = taxCodeMismatchCount; return(submissionOK); }
/// <summary> /// export all the Data of the batches matching the parameters to a String /// </summary> /// <param name="ARequestParams">Hashtable containing the given params </param> /// <param name="AExportString">Big parts of the export file as a simple String</param> /// <param name="AVerificationMessages">Additional messages to display in a messagebox</param> /// <returns>number of exported batches, -1 if cancelled, -2 if error</returns> public Int32 ExportAllGiftBatchData( Hashtable ARequestParams, out String AExportString, out TVerificationResultCollection AVerificationMessages) { //Return number of exported batches, -1 if cancelled, -2 if error int ReturnGiftBatchCount = 0; FStringWriter = new StringWriter(); FMainDS = new GiftBatchTDS(); FDelimiter = (String)ARequestParams["Delimiter"]; FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"]; FDateFormatString = (String)ARequestParams["DateFormatString"]; Boolean Summary = (Boolean)ARequestParams["Summary"]; FUseBaseCurrency = (Boolean)ARequestParams["bUseBaseCurrency"]; FDateForSummary = (DateTime)ARequestParams["DateForSummary"]; String NumberFormat = (String)ARequestParams["NumberFormat"]; FCultureInfo = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); FTransactionsOnly = (Boolean)ARequestParams["TransactionsOnly"]; FExtraColumns = (Boolean)ARequestParams["ExtraColumns"]; Int64 recipientNumber = (Int64)ARequestParams["RecipientNumber"]; String RecipientFilter = (recipientNumber != 0) ? " AND PUB_a_gift_detail.p_recipient_key_n = " + recipientNumber : ""; Int64 fieldNumber = (Int64)ARequestParams["FieldNumber"]; String FieldFilter = (fieldNumber != 0) ? " AND PUB_a_gift_detail.a_recipient_ledger_number_n = " + fieldNumber : ""; Boolean IncludeUnposted = (Boolean)ARequestParams["IncludeUnposted"]; String StatusFilter = (IncludeUnposted) ? " AND (PUB_a_gift_batch.a_batch_status_c = 'Posted' OR PUB_a_gift_batch.a_batch_status_c = 'Unposted')" : " AND PUB_a_gift_batch.a_batch_status_c = 'Posted'"; try { DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref FTransaction, delegate { try { ALedgerAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, FTransaction); String BatchRangeFilter = (ARequestParams.ContainsKey( "BatchNumberStart")) ? " AND (PUB_a_gift_batch.a_batch_number_i >= " + (Int32)ARequestParams["BatchNumberStart"] + " AND PUB_a_gift_batch.a_batch_number_i <= " + (Int32)ARequestParams["BatchNumberEnd"] + ")" : ""; // If I've specified a BatchRange, I can't also have a DateRange: String DateRangeFilter = (BatchRangeFilter == "") ? " AND (PUB_a_gift_batch.a_gl_effective_date_d >= '" + ((DateTime)ARequestParams["BatchDateFrom"]).ToString( "yyyy-MM-dd") + "' AND PUB_a_gift_batch.a_gl_effective_date_d <= '" + ((DateTime)ARequestParams["BatchDateTo"]).ToString("yyyy-MM-dd") + "')" : ""; string StatementCore = " FROM PUB_a_gift_batch, PUB_a_gift, PUB_a_gift_detail" + " WHERE PUB_a_gift_batch.a_ledger_number_i = " + FLedgerNumber + RecipientFilter + FieldFilter + DateRangeFilter + BatchRangeFilter + StatusFilter + " AND PUB_a_gift.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i" + " AND PUB_a_gift.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i" + " AND PUB_a_gift_detail.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i" + " AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i" + " AND PUB_a_gift_detail.a_gift_transaction_number_i = PUB_a_gift.a_gift_transaction_number_i"; string sqlStatement = "SELECT DISTINCT PUB_a_gift_batch.* " + StatementCore + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName(); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift batch records"), 5); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } DBAccess.GDBAccessObj.Select(FMainDS, sqlStatement, FMainDS.AGiftBatch.TableName, FTransaction ); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift records"), 10); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } sqlStatement = "SELECT DISTINCT PUB_a_gift.* " + StatementCore + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName() + ", " + AGiftTable.GetGiftTransactionNumberDBName(); DBAccess.GDBAccessObj.Select(FMainDS, sqlStatement, FMainDS.AGift.TableName, FTransaction); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift detail records"), 15); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } sqlStatement = "SELECT DISTINCT PUB_a_gift_detail.* " + StatementCore; DBAccess.GDBAccessObj.Select(FMainDS, sqlStatement, FMainDS.AGiftDetail.TableName, FTransaction); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } }); TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Exporting Gift Batches"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving records"), 5); string BaseCurrency = FMainDS.ALedger[0].BaseCurrency; FCurrencyCode = BaseCurrency; // Depending on FUseBaseCurrency, this will be overwritten for each gift. SortedDictionary <String, AGiftSummaryRow> sdSummary = new SortedDictionary <String, AGiftSummaryRow>(); UInt32 counter = 0; // TProgressTracker Variables UInt32 GiftCounter = 0; AGiftSummaryRow giftSummary = null; FMainDS.AGiftDetail.DefaultView.Sort = AGiftDetailTable.GetLedgerNumberDBName() + "," + AGiftDetailTable.GetBatchNumberDBName() + "," + AGiftDetailTable.GetGiftTransactionNumberDBName(); foreach (AGiftBatchRow giftBatch in FMainDS.AGiftBatch.Rows) { if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } ReturnGiftBatchCount++; TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), string.Format(Catalog.GetString("Batch {0}"), giftBatch.BatchNumber), 20); GiftCounter = 0; if (!FTransactionsOnly & !Summary) { WriteGiftBatchLine(giftBatch); } foreach (AGiftRow gift in FMainDS.AGift.Rows) { if (gift.BatchNumber.Equals(giftBatch.BatchNumber) && gift.LedgerNumber.Equals(giftBatch.LedgerNumber)) { if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } // Update progress tracker every 25 records if (++GiftCounter % 25 == 0) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), string.Format(Catalog.GetString("Batch {0} - Exporting gifts"), giftBatch.BatchNumber), (GiftCounter / 25 + 4) * 5 > 90 ? 90 : (GiftCounter / 25 + 4) * 5); } DataRowView[] selectedRowViews = FMainDS.AGiftDetail.DefaultView.FindRows( new object[] { gift.LedgerNumber, gift.BatchNumber, gift.GiftTransactionNumber }); foreach (DataRowView rv in selectedRowViews) { AGiftDetailRow giftDetail = (AGiftDetailRow)rv.Row; if (Summary) { FCurrencyCode = FUseBaseCurrency ? BaseCurrency : giftBatch.CurrencyCode; decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : giftBatch.ExchangeRateToBase; counter++; String DictionaryKey = FCurrencyCode + ";" + giftBatch.BankCostCentre + ";" + giftBatch.BankAccountCode + ";" + giftDetail.RecipientKey + ";" + giftDetail.MotivationGroupCode + ";" + giftDetail.MotivationDetailCode; if (sdSummary.TryGetValue(DictionaryKey, out giftSummary)) { giftSummary.GiftTransactionAmount += giftDetail.GiftTransactionAmount; giftSummary.GiftAmount += giftDetail.GiftAmount; } else { giftSummary = new AGiftSummaryRow(); /* * summary_data.a_transaction_currency_c = lv_stored_currency_c * summary_data.a_bank_cost_centre_c = a_gift_batch.a_bank_cost_centre_c * summary_data.a_bank_account_code_c = a_gift_batch.a_bank_account_code_c * summary_data.a_recipient_key_n = a_gift_detail.p_recipient_key_n * summary_data.a_motivation_group_code_c = a_gift_detail.a_motivation_group_code_c * summary_data.a_motivation_detail_code_c = a_gift_detail.a_motivation_detail_code_c * summary_data.a_exchange_rate_to_base_n = lv_exchange_rate_n * summary_data.a_gift_type_c = a_gift_batch.a_gift_type_c */ giftSummary.CurrencyCode = FCurrencyCode; giftSummary.BankCostCentre = giftBatch.BankCostCentre; giftSummary.BankAccountCode = giftBatch.BankAccountCode; giftSummary.RecipientKey = giftDetail.RecipientKey; giftSummary.MotivationGroupCode = giftDetail.MotivationGroupCode; giftSummary.MotivationDetailCode = giftDetail.MotivationDetailCode; giftSummary.GiftTransactionAmount = giftDetail.GiftTransactionAmount; giftSummary.GiftAmount = giftDetail.GiftAmount; sdSummary.Add(DictionaryKey, giftSummary); } //overwrite always because we want to have the last giftSummary.ExchangeRateToBase = mapExchangeRateToBase; } else // not summary { WriteGiftLine(gift, giftDetail); } } } } } if (Summary) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Export Summary"), 95); bool first = true; foreach (KeyValuePair <string, AGiftSummaryRow> kvp in sdSummary) { if (!FTransactionsOnly && first) { WriteGiftBatchSummaryLine(kvp.Value); first = false; } WriteGiftSummaryLine(kvp.Value); } } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Gift batch export successful"), 100); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } catch (ApplicationException) { //Show cancel condition ReturnGiftBatchCount = -1; TProgressTracker.CancelJob(DomainManager.GClientID.ToString()); } catch (Exception ex) { TLogging.Log(ex.ToString()); //Show error condition ReturnGiftBatchCount = -2; FMessages.Add(new TVerificationResult( "Exporting Gift Batches Terminated Unexpectedly", ex.Message, "An unexpected error occurred during the export of gift batches", string.Empty, TResultSeverity.Resv_Critical, Guid.Empty)); TProgressTracker.CancelJob(DomainManager.GClientID.ToString()); } if (ReturnGiftBatchCount > 0) { AExportString = FStringWriter.ToString(); } else { AExportString = string.Empty; } AVerificationMessages = FMessages; return(ReturnGiftBatchCount); } // ExportAllGiftBatchData
public static bool ResetDatabase(string AZippedNewDatabaseData) { TDataBase DBConnectionObj = null; List <string> tables = TTableList.GetDBNames(); bool SubmissionResult = false; TDBTransaction ReadWriteTransaction = new TDBTransaction(); SortedList <int, string> CurrencyPerLedger = new SortedList <int, string>(); string ClientID = "ClientID"; try { ClientID = DomainManager.GClientID.ToString(); } catch (Exception) { } try { // Open a separate DB Connection for the importing of the data... DBConnectionObj = DBAccess.Connect("ExportAllTables"); // ...and start a DB Transaction on that separate DB Connection DBConnectionObj.WriteTransaction(ref ReadWriteTransaction, ref SubmissionResult, delegate { try { TProgressTracker.InitProgressTracker(ClientID, Catalog.GetString("Restoring Database..."), tables.Count + 3); tables.Reverse(); // ignore s_session table, to avoid locking during the restore tables.Remove("s_session"); TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Deleting current data..."), 0); // need to reset connection between s_user and p_partner to avoid broken foreign key ReadWriteTransaction.DataBaseObj.ExecuteNonQuery("UPDATE pub_s_user SET p_partner_key_n = NULL", ReadWriteTransaction); foreach (string table in tables) { ReadWriteTransaction.DataBaseObj.ExecuteNonQuery("DELETE FROM pub_" + table, ReadWriteTransaction); } if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true) { TProgressTracker.FinishJob(ClientID); // As SubmissionResult is still false, a DB Transaction Rollback will get // executed automatically and the Method will be exited with return value 'false'! return; } TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData)); ymlParser.ParseCaptions(); tables.Reverse(); TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Loading initial tables..."), 1); // one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails? bool success = true; success = success && LoadTable("s_user", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger); success = success && LoadTable("s_module", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger); success = success && LoadTable("s_user_module_access_permission", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger); success = success && LoadTable("s_system_defaults", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger); success = success && LoadTable("s_system_status", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger); if (!success) { // As SubmissionResult is still TSubmitChangesResult.scrError, a DB Transaction Rollback will get // executed automatically and the Method will be exited with return value 'false'! return; } if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true) { TProgressTracker.FinishJob(ClientID); // As SubmissionResult is still false, a DB Transaction Rollback will get // executed automatically and the Method will be exited with return value 'false'! return; } tables.Remove("s_user"); tables.Remove("s_module"); tables.Remove("s_user_module_access_permission"); tables.Remove("s_system_defaults"); tables.Remove("s_system_status"); int tableCounter = 2; foreach (string table in tables) { TProgressTracker.SetCurrentState(ClientID, String.Format(Catalog.GetString("Loading Table {0}..."), table), tableCounter); tableCounter++; if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true) { TProgressTracker.FinishJob(ClientID); // As SubmissionResult is still false, a DB Transaction Rollback will get // executed automatically and the Method will be exited with return value 'false'! return; } LoadTable(table, ymlParser, ReadWriteTransaction, ref CurrencyPerLedger); } TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Loading Sequences..."), tables.Count + 5 + 3); // set sequences appropriately, not lagging behind the imported data foreach (string seq in TTableList.GetDBSequenceNames()) { LoadSequence(seq, ymlParser, ReadWriteTransaction); } TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Finishing Restore..."), tables.Count + 5 + 4); SubmissionResult = true; // reset all cached tables TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing(); } catch (Exception e) { TLogging.Log("Problem in ResetDatabase: " + e.ToString()); TLogging.LogStackTrace(TLoggingType.ToLogfile); throw; } }); } finally { if (DBConnectionObj != null) { DBConnectionObj.CloseDBConnection(); } } // if we import an older database, we need to upgrade it. // this happens in a separate database connection and database transactions. TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Updating the database..."), tables.Count + 5 + 5); TDBUpgrades upgrades = new TDBUpgrades(); upgrades.UpgradeDatabase(); TProgressTracker.FinishJob(ClientID); return(SubmissionResult); }
public static BankImportTDS GetBankStatementTransactionsAndMatches(Int32 AStatementKey, Int32 ALedgerNumber) { TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); BankImportTDS ResultDataset = new BankImportTDS(); string MyClientID = DomainManager.GClientID.ToString(); TProgressTracker.InitProgressTracker(MyClientID, Catalog.GetString("Load Bank Statement"), 100.0m); TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("loading statement"), 0); try { AEpStatementAccess.LoadByPrimaryKey(ResultDataset, AStatementKey, Transaction); if (ResultDataset.AEpStatement[0].BankAccountCode.Length == 0) { throw new Exception("Loading Bank Statement: Bank Account must not be empty"); } ACostCentreAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction); AMotivationDetailAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction); AEpTransactionAccess.LoadViaAEpStatement(ResultDataset, AStatementKey, Transaction); BankImportTDS TempDataset = new BankImportTDS(); AEpTransactionAccess.LoadViaAEpStatement(TempDataset, AStatementKey, Transaction); AEpMatchAccess.LoadViaALedger(TempDataset, ResultDataset.AEpStatement[0].LedgerNumber, Transaction); // load all bankingdetails and partner shortnames related to this statement string sqlLoadPartnerByBankAccount = "SELECT DISTINCT p.p_partner_key_n AS PartnerKey, " + "p.p_partner_short_name_c AS ShortName, " + "t.p_branch_code_c AS BranchCode, " + "t.a_bank_account_number_c AS BankAccountNumber " + "FROM PUB_a_ep_transaction t, PUB_p_banking_details bd, PUB_p_bank b, PUB_p_partner_banking_details pbd, PUB_p_partner p " + "WHERE t.a_statement_key_i = " + AStatementKey.ToString() + " " + "AND bd.p_bank_account_number_c = t.a_bank_account_number_c " + "AND b.p_partner_key_n = bd.p_bank_key_n " + "AND b.p_branch_code_c = t.p_branch_code_c " + "AND pbd.p_banking_details_key_i = bd.p_banking_details_key_i " + "AND p.p_partner_key_n = pbd.p_partner_key_n"; DataTable PartnerByBankAccount = DBAccess.GDBAccessObj.SelectDT(sqlLoadPartnerByBankAccount, "partnerByBankAccount", Transaction); PartnerByBankAccount.DefaultView.Sort = "BranchCode, BankAccountNumber"; // get all recipients that have been merged string sqlGetMergedRecipients = string.Format( "SELECT DISTINCT p.p_partner_key_n AS PartnerKey, p.p_status_code_c AS StatusCode FROM PUB_a_ep_match m, PUB_p_partner p " + "WHERE (m.p_recipient_key_n = p.p_partner_key_n OR m.p_donor_key_n = p.p_partner_key_n) AND p.p_status_code_c = '{0}'", MPartnerConstants.PARTNERSTATUS_MERGED); DataTable MergedPartners = DBAccess.GDBAccessObj.SelectDT(sqlGetMergedRecipients, "mergedPartners", Transaction); MergedPartners.DefaultView.Sort = "PartnerKey"; DBAccess.GDBAccessObj.RollbackTransaction(); string BankAccountCode = ResultDataset.AEpStatement[0].BankAccountCode; TempDataset.AEpMatch.DefaultView.Sort = AEpMatchTable.GetMatchTextDBName(); SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>(); int count = 0; // load the matches or create new matches foreach (BankImportTDSAEpTransactionRow row in ResultDataset.AEpTransaction.Rows) { TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("finding matches") + " " + count + "/" + ResultDataset.AEpTransaction.Rows.Count.ToString(), 10.0m + (count * 80.0m / ResultDataset.AEpTransaction.Rows.Count)); count++; BankImportTDSAEpTransactionRow tempTransactionRow = (BankImportTDSAEpTransactionRow)TempDataset.AEpTransaction.Rows.Find( new object[] { row.StatementKey, row.Order, row.DetailKey }); // find a match with the same match text, or create a new one if (row.IsMatchTextNull() || (row.MatchText.Length == 0) || !row.MatchText.StartsWith(BankAccountCode)) { row.MatchText = TBankImportMatching.CalculateMatchText(BankAccountCode, row); tempTransactionRow.MatchText = row.MatchText; } DataRowView[] matches = TempDataset.AEpMatch.DefaultView.FindRows(row.MatchText); if (matches.Length > 0) { Decimal sum = 0.0m; // update the recent date foreach (DataRowView rv in matches) { AEpMatchRow r = (AEpMatchRow)rv.Row; sum += r.GiftTransactionAmount; // check if the recipient key is still valid. could be that they have married, and merged into another family record if ((r.RecipientKey != 0) && (MergedPartners.DefaultView.FindRows(r.RecipientKey).Length > 0)) { TLogging.LogAtLevel(1, "partner has been merged: " + r.RecipientKey.ToString()); r.RecipientKey = 0; r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; } // check if the donor key is still valid. could be that they have married, and merged into another family record if ((r.DonorKey != 0) && (MergedPartners.DefaultView.FindRows(r.DonorKey).Length > 0)) { TLogging.LogAtLevel(1, "partner has been merged: " + r.DonorKey.ToString()); r.DonorKey = 0; r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; } if (r.RecentMatch < row.DateEffective) { r.RecentMatch = row.DateEffective; } // do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time row.MatchAction = r.Action; if (r.IsDonorKeyNull() || (r.DonorKey <= 0)) { FindDonorByAccountNumber(r, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber); } } if (sum != row.TransactionAmount) { TLogging.Log("we should drop this match since the total is wrong: " + row.Description); row.MatchAction = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; foreach (DataRowView rv in matches) { AEpMatchRow r = (AEpMatchRow)rv.Row; r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; } } } else if (!MatchesToAddLater.ContainsKey(row.MatchText)) { // create new match AEpMatchRow tempRow = TempDataset.AEpMatch.NewRowTyped(true); tempRow.EpMatchKey = (TempDataset.AEpMatch.Count + MatchesToAddLater.Count + 1) * -1; tempRow.Detail = 0; tempRow.MatchText = row.MatchText; tempRow.LedgerNumber = ALedgerNumber; tempRow.GiftTransactionAmount = row.TransactionAmount; tempRow.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; FindDonorByAccountNumber(tempRow, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber); #if disabled // fuzzy search for the partner. only return if unique result string sql = "SELECT p_partner_key_n, p_partner_short_name_c FROM p_partner WHERE p_partner_short_name_c LIKE '{0}%' OR p_partner_short_name_c LIKE '{1}%'"; string[] names = row.AccountName.Split(new char[] { ' ' }); if (names.Length > 1) { string optionShortName1 = names[0] + ", " + names[1]; string optionShortName2 = names[1] + ", " + names[0]; DataTable partner = DBAccess.GDBAccessObj.SelectDT(String.Format(sql, optionShortName1, optionShortName2), "partner", Transaction); if (partner.Rows.Count == 1) { tempRow.DonorKey = Convert.ToInt64(partner.Rows[0][0]); } } #endif MatchesToAddLater.Add(tempRow.MatchText, tempRow); // do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time row.MatchAction = tempRow.Action; } } // for speed reasons, add the new rows after clearing the sort on the view TempDataset.AEpMatch.DefaultView.Sort = string.Empty; foreach (AEpMatchRow m in MatchesToAddLater.Values) { TempDataset.AEpMatch.Rows.Add(m); } TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("save matches"), 90.0m); TempDataset.ThrowAwayAfterSubmitChanges = true; // only store a_ep_transactions and a_ep_matches, but without additional typed fields (ie MatchAction) BankImportTDSAccess.SubmitChanges(TempDataset.GetChangesTyped(true)); } catch (Exception e) { TLogging.Log(e.GetType().ToString() + " in BankImport, GetBankStatementTransactionsAndMatches; " + e.Message); TLogging.Log(e.StackTrace); DBAccess.GDBAccessObj.RollbackTransaction(); throw; } // drop all matches that do not occur on this statement ResultDataset.AEpMatch.Clear(); // reloading is faster than deleting all matches that are not needed string sqlLoadMatchesOfStatement = "SELECT DISTINCT m.* FROM PUB_a_ep_match m, PUB_a_ep_transaction t WHERE t.a_statement_key_i = ? AND m.a_ledger_number_i = ? AND m.a_match_text_c = t.a_match_text_c"; OdbcParameter param = new OdbcParameter("statementkey", OdbcType.Int); param.Value = AStatementKey; OdbcParameter paramLedgerNumber = new OdbcParameter("ledgerNumber", OdbcType.Int); paramLedgerNumber.Value = ALedgerNumber; DBAccess.GDBAccessObj.SelectDT(ResultDataset.AEpMatch, sqlLoadMatchesOfStatement, null, new OdbcParameter[] { param, paramLedgerNumber }, -1, -1); // update the custom field for cost centre name for each match foreach (BankImportTDSAEpMatchRow row in ResultDataset.AEpMatch.Rows) { ACostCentreRow ccRow = (ACostCentreRow)ResultDataset.ACostCentre.Rows.Find(new object[] { row.LedgerNumber, row.CostCentreCode }); if (ccRow != null) { row.CostCentreName = ccRow.CostCentreName; } } // remove all rows that we do not need on the client side ResultDataset.AGiftDetail.Clear(); ResultDataset.AMotivationDetail.Clear(); ResultDataset.ACostCentre.Clear(); ResultDataset.AcceptChanges(); if (TLogging.DebugLevel > 0) { int CountMatched = 0; foreach (BankImportTDSAEpTransactionRow transaction in ResultDataset.AEpTransaction.Rows) { if (!transaction.IsMatchActionNull() && (transaction.MatchAction != MFinanceConstants.BANK_STMT_STATUS_UNMATCHED)) { CountMatched++; } } TLogging.Log( "Loading bank statement: matched: " + CountMatched.ToString() + " of " + ResultDataset.AEpTransaction.Rows.Count.ToString()); } TProgressTracker.FinishJob(MyClientID); return(ResultDataset); }
public static Int32 CreateGiftBatch( Int32 ALedgerNumber, Int32 AStatementKey, Int32 AGiftBatchNumber, out TVerificationResultCollection AVerificationResult) { BankImportTDS MainDS = GetBankStatementTransactionsAndMatches(AStatementKey, ALedgerNumber); string MyClientID = DomainManager.GClientID.ToString(); TProgressTracker.InitProgressTracker(MyClientID, Catalog.GetString("Creating gift batch"), MainDS.AEpTransaction.DefaultView.Count + 10); AVerificationResult = new TVerificationResultCollection(); MainDS.AEpTransaction.DefaultView.RowFilter = String.Format("{0}={1}", AEpTransactionTable.GetStatementKeyDBName(), AStatementKey); MainDS.AEpStatement.DefaultView.RowFilter = String.Format("{0}={1}", AEpStatementTable.GetStatementKeyDBName(), AStatementKey); AEpStatementRow stmt = (AEpStatementRow)MainDS.AEpStatement.DefaultView[0].Row; // TODO: optional: use the preselected gift batch, AGiftBatchNumber Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber; DateTime BatchDateEffective = stmt.Date; TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); if (!TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref BatchDateEffective, out DateEffectiveYearNumber, out DateEffectivePeriodNumber, Transaction, true)) { // just use the latest possible date string msg = String.Format(Catalog.GetString("Date {0} is not in an open period of the ledger, using date {1} instead for the gift batch."), stmt.Date.ToShortDateString(), BatchDateEffective.ToShortDateString()); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Info)); } ACostCentreAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); MainDS.AEpMatch.DefaultView.Sort = AEpMatchTable.GetActionDBName() + ", " + AEpMatchTable.GetMatchTextDBName(); if (MainDS.AEpTransaction.DefaultView.Count == 0) { AVerificationResult.Add(new TVerificationResult( Catalog.GetString("Creating Gift Batch"), String.Format(Catalog.GetString("There are no transactions for statement #{0}."), AStatementKey), TResultSeverity.Resv_Info)); return(-1); } foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView) { AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row; DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] { MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT, transactionRow.MatchText }); if (matches.Length > 0) { AEpMatchRow match = (AEpMatchRow)matches[0].Row; if (match.IsDonorKeyNull() || (match.DonorKey == 0)) { string msg = String.Format(Catalog.GetString("Cannot create a gift for transaction {0} since there is no valid donor."), transactionRow.Description); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Critical)); DBAccess.GDBAccessObj.RollbackTransaction(); return(-1); } } } string MatchedGiftReference = stmt.Filename; if (!stmt.IsBankAccountKeyNull()) { string sqlGetBankSortCode = "SELECT bank.p_branch_code_c " + "FROM PUB_p_banking_details details, PUB_p_bank bank " + "WHERE details.p_banking_details_key_i = ?" + "AND details.p_bank_key_n = bank.p_partner_key_n"; OdbcParameter param = new OdbcParameter("detailkey", OdbcType.Int); param.Value = stmt.BankAccountKey; PBankTable bankTable = new PBankTable(); DBAccess.GDBAccessObj.SelectDT(bankTable, sqlGetBankSortCode, Transaction, new OdbcParameter[] { param }, 0, 0); MatchedGiftReference = bankTable[0].BranchCode + " " + stmt.Date.Day.ToString(); } DBAccess.GDBAccessObj.RollbackTransaction(); GiftBatchTDS GiftDS = TGiftTransactionWebConnector.CreateAGiftBatch( ALedgerNumber, BatchDateEffective, String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString())); AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0]; giftbatchRow.BankAccountCode = stmt.BankAccountCode; decimal HashTotal = 0.0M; MainDS.AEpTransaction.DefaultView.Sort = AEpTransactionTable.GetNumberOnPaperStatementDBName(); MainDS.AEpMatch.DefaultView.RowFilter = String.Empty; MainDS.AEpMatch.DefaultView.Sort = AEpMatchTable.GetActionDBName() + ", " + AEpMatchTable.GetMatchTextDBName(); int counter = 5; foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView) { TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("Preparing the gifts"), counter++); AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row; DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] { MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT, transactionRow.MatchText }); if (matches.Length > 0) { AEpMatchRow match = (AEpMatchRow)matches[0].Row; AGiftRow gift = GiftDS.AGift.NewRowTyped(); gift.LedgerNumber = giftbatchRow.LedgerNumber; gift.BatchNumber = giftbatchRow.BatchNumber; gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1; gift.DonorKey = match.DonorKey; gift.DateEntered = transactionRow.DateEffective; gift.Reference = MatchedGiftReference; GiftDS.AGift.Rows.Add(gift); giftbatchRow.LastGiftNumber++; foreach (DataRowView r in matches) { match = (AEpMatchRow)r.Row; AGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped(); detail.LedgerNumber = gift.LedgerNumber; detail.BatchNumber = gift.BatchNumber; detail.GiftTransactionNumber = gift.GiftTransactionNumber; detail.DetailNumber = gift.LastDetailNumber + 1; gift.LastDetailNumber++; detail.GiftTransactionAmount = match.GiftTransactionAmount; detail.GiftAmount = match.GiftTransactionAmount; HashTotal += match.GiftTransactionAmount; detail.MotivationGroupCode = match.MotivationGroupCode; detail.MotivationDetailCode = match.MotivationDetailCode; // do not use the description in comment one, because that could show up on the gift receipt??? // detail.GiftCommentOne = transactionRow.Description; detail.CommentOneType = MFinanceConstants.GIFT_COMMENT_TYPE_BOTH; detail.CostCentreCode = match.CostCentreCode; detail.RecipientKey = match.RecipientKey; detail.RecipientLedgerNumber = match.RecipientLedgerNumber; AMotivationDetailRow motivation = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find( new object[] { ALedgerNumber, detail.MotivationGroupCode, detail.MotivationDetailCode }); if (motivation == null) { AVerificationResult.Add(new TVerificationResult( String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description), String.Format(Catalog.GetString("Cannot find motivation group '{0}' and motivation detail '{1}'"), detail.MotivationGroupCode, detail.MotivationDetailCode), TResultSeverity.Resv_Critical)); } if (detail.CostCentreCode.Length == 0) { // try to retrieve the current costcentre for this recipient if (detail.RecipientKey != 0) { detail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(detail.RecipientKey); detail.CostCentreCode = TGiftTransactionWebConnector.IdentifyPartnerCostCentre(detail.LedgerNumber, detail.RecipientLedgerNumber); } else { if (motivation != null) { detail.CostCentreCode = motivation.CostCentreCode; } } } // check for active cost centre ACostCentreRow costcentre = (ACostCentreRow)MainDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, detail.CostCentreCode }); if ((costcentre == null) || !costcentre.CostCentreActiveFlag) { AVerificationResult.Add(new TVerificationResult( String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description), Catalog.GetString("Invalid or inactive cost centre"), TResultSeverity.Resv_Critical)); } GiftDS.AGiftDetail.Rows.Add(detail); } } } TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("Submit to database"), counter++); if (AVerificationResult.HasCriticalErrors) { return(-1); } giftbatchRow.HashTotal = HashTotal; giftbatchRow.BatchTotal = HashTotal; // do not overwrite the parameter, because there might be the hint for a different gift batch date TVerificationResultCollection VerificationResultSubmitChanges; TSubmitChangesResult result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS, out VerificationResultSubmitChanges); TProgressTracker.FinishJob(MyClientID); if (result == TSubmitChangesResult.scrOK) { return(giftbatchRow.BatchNumber); } return(-1); }
public static bool Reset() { TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), string.Empty, 100.0m); return(true); }
/// <summary> /// export all the Data of the batches matching the parameters to a String /// </summary> /// <param name="ARequestParams">Hashtable containing the given params </param> /// <param name="AExportString">Big parts of the export file as a simple String</param> /// <param name="AVerificationMessages">Additional messages to display in a messagebox</param> /// <returns>number of exported batches, -1 if cancelled, -2 if error</returns> public Int32 ExportAllGiftBatchData( Hashtable ARequestParams, out String AExportString, out TVerificationResultCollection AVerificationMessages) { //Return number of exported batches, -1 if cancelled, -2 if error int ReturnGiftBatchCount = 0; FStringWriter = new StringWriter(); FMainDS = new GiftBatchTDS(); FDelimiter = (String)ARequestParams["Delimiter"]; FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"]; FDateFormatString = (String)ARequestParams["DateFormatString"]; bool Summary = (bool)ARequestParams["Summary"]; FUseBaseCurrency = (bool)ARequestParams["bUseBaseCurrency"]; FDateForSummary = (DateTime)ARequestParams["DateForSummary"]; String NumberFormat = (String)ARequestParams["NumberFormat"]; FCultureInfo = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); FTransactionsOnly = (bool)ARequestParams["TransactionsOnly"]; FExtraColumns = (bool)ARequestParams["ExtraColumns"]; try { DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref FTransaction, delegate { try { ALedgerAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, FTransaction); List <OdbcParameter> parameters = new List <OdbcParameter>(); SortedList <String, String> SQLCommandDefines = new SortedList <string, string>(); if ((bool)ARequestParams["IncludeUnposted"]) { SQLCommandDefines.Add("INCLUDEUNPOSTED", string.Empty); } OdbcParameter param = new OdbcParameter("LedgerNumber", OdbcType.Int); param.Value = FLedgerNumber; parameters.Add(param); Int64 recipientNumber = (Int64)ARequestParams["RecipientNumber"]; Int64 fieldNumber = (Int64)ARequestParams["FieldNumber"]; if (recipientNumber != 0) { SQLCommandDefines.Add("BYRECIPIENT", string.Empty); param = new OdbcParameter("RecipientNumber", OdbcType.Int); param.Value = recipientNumber; parameters.Add(param); } if (fieldNumber != 0) { SQLCommandDefines.Add("BYFIELD", string.Empty); param = new OdbcParameter("fieldNumber", OdbcType.Int); param.Value = fieldNumber; parameters.Add(param); } if (ARequestParams.ContainsKey("BatchNumberStart")) { SQLCommandDefines.Add("BYBATCHNUMBER", string.Empty); param = new OdbcParameter("BatchNumberStart", OdbcType.Int); param.Value = (Int32)ARequestParams["BatchNumberStart"]; parameters.Add(param); param = new OdbcParameter("BatchNumberEnd", OdbcType.Int); param.Value = (Int32)ARequestParams["BatchNumberEnd"]; parameters.Add(param); } else { SQLCommandDefines.Add("BYDATERANGE", string.Empty); param = new OdbcParameter("BatchDateFrom", OdbcType.DateTime); param.Value = (DateTime)ARequestParams["BatchDateFrom"]; parameters.Add(param); param = new OdbcParameter("BatchDateTo", OdbcType.DateTime); param.Value = (DateTime)ARequestParams["BatchDateTo"]; parameters.Add(param); } string sqlStatement = TDataBase.ReadSqlFile("Gift.GetGiftsToExport.sql", SQLCommandDefines); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift batch records"), 5); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } DBAccess.GDBAccessObj.Select(FMainDS, "SELECT DISTINCT PUB_a_gift_batch.* " + sqlStatement + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName(), FMainDS.AGiftBatch.TableName, FTransaction, parameters.ToArray()); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift records"), 10); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } DBAccess.GDBAccessObj.Select(FMainDS, "SELECT DISTINCT PUB_a_gift.* " + sqlStatement + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName() + ", " + AGiftTable.GetGiftTransactionNumberDBName(), FMainDS.AGift.TableName, FTransaction, parameters.ToArray()); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift detail records"), 15); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } DBAccess.GDBAccessObj.Select(FMainDS, "SELECT DISTINCT PUB_a_gift_detail.* " + sqlStatement, FMainDS.AGiftDetail.TableName, FTransaction, parameters.ToArray()); } catch (ApplicationException ex) { throw ex; } catch (Exception ex) { TLogging.Log("Error in ExportAllGiftBatchData: " + ex.Message); throw ex; } }); TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Exporting Gift Batches"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving records"), 5); string BaseCurrency = FMainDS.ALedger[0].BaseCurrency; FCurrencyCode = BaseCurrency; // Depending on FUseBaseCurrency, this will be overwritten for each gift. SortedDictionary <String, AGiftSummaryRow> sdSummary = new SortedDictionary <String, AGiftSummaryRow>(); UInt32 counter = 0; // TProgressTracker Variables UInt32 GiftCounter = 0; AGiftSummaryRow giftSummary = null; FMainDS.AGiftDetail.DefaultView.Sort = AGiftDetailTable.GetLedgerNumberDBName() + "," + AGiftDetailTable.GetBatchNumberDBName() + "," + AGiftDetailTable.GetGiftTransactionNumberDBName(); foreach (AGiftBatchRow giftBatch in FMainDS.AGiftBatch.Rows) { if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } ReturnGiftBatchCount++; TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), string.Format(Catalog.GetString("Batch {0}"), giftBatch.BatchNumber), 20); GiftCounter = 0; if (!FTransactionsOnly & !Summary) { WriteGiftBatchLine(giftBatch); } foreach (AGiftRow gift in FMainDS.AGift.Rows) { if (gift.BatchNumber.Equals(giftBatch.BatchNumber) && gift.LedgerNumber.Equals(giftBatch.LedgerNumber)) { if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } // Update progress tracker every 25 records if (++GiftCounter % 25 == 0) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), string.Format(Catalog.GetString("Batch {0} - Exporting gifts"), giftBatch.BatchNumber), (GiftCounter / 25 + 4) * 5 > 90 ? 90 : (GiftCounter / 25 + 4) * 5); } DataRowView[] selectedRowViews = FMainDS.AGiftDetail.DefaultView.FindRows( new object[] { gift.LedgerNumber, gift.BatchNumber, gift.GiftTransactionNumber }); foreach (DataRowView rv in selectedRowViews) { AGiftDetailRow giftDetail = (AGiftDetailRow)rv.Row; if (Summary) { FCurrencyCode = FUseBaseCurrency ? BaseCurrency : giftBatch.CurrencyCode; decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : giftBatch.ExchangeRateToBase; counter++; String DictionaryKey = FCurrencyCode + ";" + giftBatch.BankCostCentre + ";" + giftBatch.BankAccountCode + ";" + giftDetail.RecipientKey + ";" + giftDetail.MotivationGroupCode + ";" + giftDetail.MotivationDetailCode; if (sdSummary.TryGetValue(DictionaryKey, out giftSummary)) { giftSummary.GiftTransactionAmount += giftDetail.GiftTransactionAmount; giftSummary.GiftAmount += giftDetail.GiftAmount; } else { giftSummary = new AGiftSummaryRow(); /* * summary_data.a_transaction_currency_c = lv_stored_currency_c * summary_data.a_bank_cost_centre_c = a_gift_batch.a_bank_cost_centre_c * summary_data.a_bank_account_code_c = a_gift_batch.a_bank_account_code_c * summary_data.a_recipient_key_n = a_gift_detail.p_recipient_key_n * summary_data.a_motivation_group_code_c = a_gift_detail.a_motivation_group_code_c * summary_data.a_motivation_detail_code_c = a_gift_detail.a_motivation_detail_code_c * summary_data.a_exchange_rate_to_base_n = lv_exchange_rate_n * summary_data.a_gift_type_c = a_gift_batch.a_gift_type_c */ giftSummary.CurrencyCode = FCurrencyCode; giftSummary.BankCostCentre = giftBatch.BankCostCentre; giftSummary.BankAccountCode = giftBatch.BankAccountCode; giftSummary.RecipientKey = giftDetail.RecipientKey; giftSummary.MotivationGroupCode = giftDetail.MotivationGroupCode; giftSummary.MotivationDetailCode = giftDetail.MotivationDetailCode; giftSummary.GiftTransactionAmount = giftDetail.GiftTransactionAmount; giftSummary.GiftAmount = giftDetail.GiftAmount; sdSummary.Add(DictionaryKey, giftSummary); } //overwrite always because we want to have the last giftSummary.ExchangeRateToBase = mapExchangeRateToBase; } else // not summary { WriteGiftLine(gift, giftDetail); } } } } } if (Summary) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Export Summary"), 95); bool first = true; foreach (KeyValuePair <string, AGiftSummaryRow> kvp in sdSummary) { if (!FTransactionsOnly && first) { WriteGiftBatchSummaryLine(kvp.Value); first = false; } WriteGiftSummaryLine(kvp.Value); } } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Gift batch export successful"), 100); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } catch (ApplicationException) { //Show cancel condition ReturnGiftBatchCount = -1; TProgressTracker.CancelJob(DomainManager.GClientID.ToString()); } catch (Exception ex) { TLogging.Log(ex.ToString()); //Show error condition ReturnGiftBatchCount = -2; FMessages.Add(new TVerificationResult( "Exporting Gift Batches Terminated Unexpectedly", ex.Message, "An unexpected error occurred during the export of gift batches", string.Empty, TResultSeverity.Resv_Critical, Guid.Empty)); TProgressTracker.CancelJob(DomainManager.GClientID.ToString()); } if (ReturnGiftBatchCount > 0) { AExportString = FStringWriter.ToString(); } else { AExportString = string.Empty; } AVerificationMessages = FMessages; return(ReturnGiftBatchCount); }
public static bool ResetDatabase(string AZippedNewDatabaseData) { List <string> tables = TTableList.GetDBNames(); TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Importing database"), tables.Count + 3); TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); try { tables.Reverse(); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("deleting current data"), 0); foreach (string table in tables) { DBAccess.GDBAccessObj.ExecuteNonQuery("DELETE FROM pub_" + table, Transaction); } if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); DBAccess.GDBAccessObj.RollbackTransaction(); return(false); } TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData)); ymlParser.ParseCaptions(); tables.Reverse(); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("loading initial tables"), 1); // one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails? bool success = true; success = success && LoadTable("s_user", ymlParser, Transaction); success = success && LoadTable("s_module", ymlParser, Transaction); success = success && LoadTable("s_user_module_access_permission", ymlParser, Transaction); success = success && LoadTable("s_system_defaults", ymlParser, Transaction); success = success && LoadTable("s_system_status", ymlParser, Transaction); // make sure we have the correct database version TFileVersionInfo serverExeInfo = new TFileVersionInfo(TSrvSetting.ApplicationVersion); DBAccess.GDBAccessObj.ExecuteNonQuery(String.Format( "UPDATE PUB_s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion'", serverExeInfo.ToString()), Transaction); if (!success) { DBAccess.GDBAccessObj.RollbackTransaction(); return(false); } if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); DBAccess.GDBAccessObj.RollbackTransaction(); return(false); } DBAccess.GDBAccessObj.CommitTransaction(); tables.Remove("s_user"); tables.Remove("s_module"); tables.Remove("s_user_module_access_permission"); tables.Remove("s_system_defaults"); tables.Remove("s_system_status"); FCurrencyPerLedger = new SortedList <int, string>(); Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); int tableCounter = 2; foreach (string table in tables) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString("loading table {0}"), table), tableCounter); tableCounter++; if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); DBAccess.GDBAccessObj.RollbackTransaction(); return(false); } LoadTable(table, ymlParser, Transaction); } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("loading sequences"), tables.Count + 5 + 3); // set sequences appropriately, not lagging behind the imported data foreach (string seq in TTableList.GetDBSequenceNames()) { LoadSequence(seq, ymlParser, Transaction); } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("finish import"), tables.Count + 5 + 4); DBAccess.GDBAccessObj.CommitTransaction(); // reset all cached tables TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing(); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } catch (Exception e) { TLogging.Log("Problem in ResetDatabase: " + e.Message); TLogging.Log(e.StackTrace); DBAccess.GDBAccessObj.RollbackTransaction(); return(false); } return(true); }
public static bool ResetDatabase(string AZippedNewDatabaseData) { List <string> tables = TTableList.GetDBNames(); bool SubmissionResult = false; TDBTransaction Transaction = null; string ClientID = "ClientID"; try { ClientID = DomainManager.GClientID.ToString(); } catch (Exception) { } TProgressTracker.InitProgressTracker(ClientID, Catalog.GetString("Restoring Database..."), tables.Count + 3); DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionResult, delegate { try { tables.Reverse(); TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Deleting current data..."), 0); foreach (string table in tables) { DBAccess.GDBAccessObj.ExecuteNonQuery("DELETE FROM pub_" + table, Transaction); } if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true) { TProgressTracker.FinishJob(ClientID); // As SubmissionResult is still false, a DB Transaction Rollback will get // executed automatically and the Method will be exited with return value 'false'! return; } TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData)); ymlParser.ParseCaptions(); tables.Reverse(); TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Loading initial tables..."), 1); // one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails? bool success = true; success = success && LoadTable("s_user", ymlParser, Transaction); success = success && LoadTable("s_module", ymlParser, Transaction); success = success && LoadTable("s_user_module_access_permission", ymlParser, Transaction); success = success && LoadTable("s_system_defaults", ymlParser, Transaction); success = success && LoadTable("s_system_status", ymlParser, Transaction); // make sure we have the correct database version TFileVersionInfo serverExeInfo = new TFileVersionInfo(TSrvSetting.ApplicationVersion); DBAccess.GDBAccessObj.ExecuteNonQuery(String.Format( "UPDATE PUB_s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion'", serverExeInfo.ToString()), Transaction); if (!success) { // As SubmissionResult is still TSubmitChangesResult.scrError, a DB Transaction Rollback will get // executed automatically and the Method will be exited with return value 'false'! return; } if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true) { TProgressTracker.FinishJob(ClientID); // As SubmissionResult is still false, a DB Transaction Rollback will get // executed automatically and the Method will be exited with return value 'false'! return; } tables.Remove("s_user"); tables.Remove("s_module"); tables.Remove("s_user_module_access_permission"); tables.Remove("s_system_defaults"); tables.Remove("s_system_status"); FCurrencyPerLedger = new SortedList <int, string>(); int tableCounter = 2; foreach (string table in tables) { TProgressTracker.SetCurrentState(ClientID, String.Format(Catalog.GetString("Loading Table {0}..."), table), tableCounter); tableCounter++; if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true) { TProgressTracker.FinishJob(ClientID); // As SubmissionResult is still false, a DB Transaction Rollback will get // executed automatically and the Method will be exited with return value 'false'! return; } LoadTable(table, ymlParser, Transaction); } TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Loading Sequences..."), tables.Count + 5 + 3); // set sequences appropriately, not lagging behind the imported data foreach (string seq in TTableList.GetDBSequenceNames()) { LoadSequence(seq, ymlParser, Transaction); } TProgressTracker.SetCurrentState(ClientID, Catalog.GetString("Finishing Restore..."), tables.Count + 5 + 4); SubmissionResult = true; // reset all cached tables TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing(); TProgressTracker.FinishJob(ClientID); } catch (Exception e) { TLogging.Log("Problem in ResetDatabase: " + e.ToString()); TLogging.LogStackTrace(TLoggingType.ToLogfile); throw; } }); return(SubmissionResult); }
/// <summary> /// Export all the Data of the batches array list to a String /// </summary> /// <param name="Abatches"></param> /// <param name="ArequestParams"></param> /// <param name="AexportString"></param> /// <returns>false if batch does not exist at all</returns> public bool ExportAllGLBatchData(ArrayList Abatches, Hashtable ArequestParams, out String AexportString) { FStringWriter = new StringWriter(); FMainDS = new GLBatchTDS(); FDelimiter = (String)ArequestParams["Delimiter"]; FLedgerNumber = (Int32)ArequestParams["ALedgerNumber"]; FDateFormatString = (String)ArequestParams["DateFormatString"]; FSummary = (bool)ArequestParams["Summary"]; FUseBaseCurrency = (bool)ArequestParams["bUseBaseCurrency"]; FBaseCurrency = (String)ArequestParams["BaseCurrency"]; FDateForSummary = (DateTime)ArequestParams["DateForSummary"]; String NumberFormat = (String)ArequestParams["NumberFormat"]; FCultureInfo = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); FTransactionsOnly = (bool)ArequestParams["TransactionsOnly"]; FDontSummarize = (bool)ArequestParams["bDontSummarize"]; FDontSummarizeAccount = (String)ArequestParams["DontSummarizeAccount"]; SortedDictionary <String, AJournalSummaryRow> sdSummary = new SortedDictionary <String, AJournalSummaryRow>(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("ExportAllGLBatchData"); db.ReadTransaction(ref Transaction, delegate { UInt32 progressCounter = 0; UInt32 progressJournalCounter = 0; TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Exporting GL Batches"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving records"), 10); while (Abatches.Count > 0) { Int32 ABatchNumber = (Int32)Abatches[0]; ABatchAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, ABatchNumber, Transaction); AJournalAccess.LoadViaABatch(FMainDS, FLedgerNumber, ABatchNumber, Transaction); foreach (AJournalRow journal in FMainDS.AJournal.Rows) { if (journal.BatchNumber.Equals(ABatchNumber) && journal.LedgerNumber.Equals(FLedgerNumber)) { ATransactionAccess.LoadViaAJournal(FMainDS, journal.LedgerNumber, journal.BatchNumber, journal.JournalNumber, Transaction); } } foreach (ATransactionRow trans in FMainDS.ATransaction.Rows) { if (trans.BatchNumber.Equals(ABatchNumber) && trans.LedgerNumber.Equals(FLedgerNumber)) { ATransAnalAttribAccess.LoadViaATransaction(FMainDS, trans.LedgerNumber, trans.BatchNumber, trans.JournalNumber, trans.TransactionNumber, Transaction); } } Abatches.RemoveAt(0); } UInt32 counter = 0; AJournalSummaryRow journalSummary = null; foreach (ABatchRow batch in FMainDS.ABatch.Rows) { progressCounter = 0; TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString("Batch {0}"), batch.BatchNumber), 20); if (!FTransactionsOnly & !FSummary) { WriteBatchLine(batch); } //foreach (AJournalRow journal in journalDS.AJournal.Rows) foreach (AJournalRow journal in FMainDS.AJournal.Rows) { if (journal.BatchNumber.Equals(batch.BatchNumber) && journal.LedgerNumber.Equals(batch.LedgerNumber)) { progressJournalCounter = 0; TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString("Batch {0}, Journal {1}"), batch.BatchNumber, journal.JournalNumber), (progressCounter / 25 + 4) * 5 > 90 ? 90 : (progressCounter / 25 + 4) * 5); if (FSummary) { String mapCurrency = FUseBaseCurrency ? FBaseCurrency : journal.TransactionCurrency; decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : journal.ExchangeRateToBase; if (!sdSummary.TryGetValue(mapCurrency, out journalSummary)) { journalSummary = new AJournalSummaryRow(); sdSummary.Add(mapCurrency, journalSummary); } //overwrite always because we want to have the last journalSummary.ExchangeRateToBase = mapExchangeRateToBase; journalSummary.TransactionCurrency = mapCurrency; } else { if (!FTransactionsOnly) { WriteJournalLine(journal); } } FMainDS.ATransaction.DefaultView.Sort = ATransactionTable.GetTransactionNumberDBName(); FMainDS.ATransaction.DefaultView.RowFilter = String.Format("{0}={1} and {2}={3} and {4}={5}", ATransactionTable.GetLedgerNumberDBName(), journal.LedgerNumber, ATransactionTable.GetBatchNumberDBName(), journal.BatchNumber, ATransactionTable.GetJournalNumberDBName(), journal.JournalNumber); foreach (DataRowView dv in FMainDS.ATransaction.DefaultView) { progressJournalCounter++; if (++progressCounter % 25 == 0) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString( "Batch {0}, Journal {1} - {2}"), batch.BatchNumber, journal.JournalNumber, progressJournalCounter), (progressCounter / 25 + 4) * 5 > 90 ? 90 : (progressCounter / 25 + 4) * 5); } ATransactionRow transactionRow = (ATransactionRow)dv.Row; if (FSummary) { ATransactionSummaryRow transactionSummary; counter++; String DictionaryKey = transactionRow.CostCentreCode + ";" + transactionRow.AccountCode; int signum = transactionRow.DebitCreditIndicator ? 1 : -1; bool bDontSummarizeAccount = FDontSummarize && FDontSummarizeAccount != null && FDontSummarizeAccount.Length > 0 && transactionRow.AccountCode.Equals(FDontSummarizeAccount); if (bDontSummarizeAccount) { DictionaryKey += ";" + counter.ToString("X"); } if (journalSummary.TransactionSummaries.TryGetValue(DictionaryKey, out transactionSummary)) { transactionSummary.TransactionAmount += signum * transactionRow.TransactionAmount; transactionSummary.AmountInBaseCurrency += signum * transactionRow.AmountInBaseCurrency; } else { transactionSummary = new ATransactionSummaryRow(); transactionSummary.CostCentreCode = transactionRow.CostCentreCode; transactionSummary.AccountCode = transactionRow.AccountCode; transactionSummary.TransactionAmount = signum * transactionRow.TransactionAmount; transactionSummary.AmountInBaseCurrency = signum * transactionRow.AmountInBaseCurrency; if (bDontSummarizeAccount) { transactionSummary.Narrative = transactionRow.Narrative; transactionSummary.Reference = transactionRow.Reference; } else { transactionSummary.Narrative = summarizedData; transactionSummary.Reference = ""; } journalSummary.TransactionSummaries.Add(DictionaryKey, transactionSummary); } } else { WriteTransactionLine(transactionRow); } } } } } if (FSummary) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Summarising"), 95); //To simplify matters this is always written even if there are no batches if (!FTransactionsOnly) { // no batch summary line if only transactions are to be exported WriteBatchSummaryLine(); } foreach (KeyValuePair <string, AJournalSummaryRow> kvp in sdSummary) { if (!FTransactionsOnly) { // no journal summary line if only transactions are to be exported WriteJournalSummaryLine(kvp.Value); } foreach (KeyValuePair <string, ATransactionSummaryRow> kvpt in kvp.Value.TransactionSummaries) { WriteTransactionSummaryLine(kvpt.Value); } } } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("GL batch export successful"), 100); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); }); AexportString = FStringWriter.ToString(); return(true); } // Export All GLBatch Data
public static bool DeleteConference(Int64 AConferenceKey, out TVerificationResultCollection AVerificationResult) { TVerificationResultCollection VerificationResult = null; TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Deleting conference"), 100); TDBTransaction Transaction = null; bool SubmissionOK = false; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { try { string[] TableNames = new string[] { PcAttendeeTable.GetTableDBName(), PcConferenceCostTable.GetTableDBName(), PcConferenceOptionTable.GetTableDBName(), PcConferenceVenueTable.GetTableDBName(), PcDiscountTable.GetTableDBName(), PcEarlyLateTable.GetTableDBName(), PcExtraCostTable.GetTableDBName(), PcGroupTable.GetTableDBName(), PcSupplementTable.GetTableDBName() }; OdbcParameter[] ConferenceParameter = new OdbcParameter[] { new OdbcParameter("conferencekey", OdbcType.BigInt) }; ConferenceParameter[0].Value = AConferenceKey; int Progress = 0; foreach (string Table in TableNames) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Deleting: ") + Table, 10 * Progress); DBAccess.GDBAccessObj.ExecuteNonQuery( String.Format("DELETE FROM PUB_{0} WHERE pc_conference_key_n = ?", Table), Transaction, ConferenceParameter); Progress++; } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Deleting: Conference"), 90); PcConferenceAccess.DeleteByPrimaryKey(AConferenceKey, Transaction); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == false) { SubmissionOK = true; } TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } catch (Exception e) { TLogging.Log(e.ToString()); VerificationResult = new TVerificationResultCollection(); VerificationResult.Add(new TVerificationResult( "Problems deleting conference " + AConferenceKey.ToString("0000000000"), e.Message, "Cannot delete conference", string.Empty, TResultSeverity.Resv_Critical, Guid.Empty)); TProgressTracker.CancelJob(DomainManager.GClientID.ToString()); } }); AVerificationResult = VerificationResult; return(SubmissionOK); }
public static void FindAddressDuplicates(ref DataTable ADuplicateAddresses, bool AExactMatchNumber) { TDBTransaction Transaction = null; DataTable ReturnTable = ADuplicateAddresses.Clone(); PLocationTable Locations = new PLocationTable(); TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Checking for duplicate addresses")); DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { // get all locations from database, // except for those without a Locality, Street Name and Address as these are too vague to make a match string Query = "SELECT p_location.* FROM p_location" + " WHERE (p_location.p_locality_c is NOT NULL AND p_location.p_locality_c <> '')" + " OR (p_location.p_street_name_c is NOT NULL AND p_location.p_street_name_c <> '')" + " OR (p_location.p_address_3_c is NOT NULL AND p_location.p_address_3_c <> '')"; DBAccess.GDBAccessObj.SelectDT(Locations, Query, Transaction); // create a list of tables grouped by country codes List <DataTable> LocationDataTables = Locations.AsEnumerable() .GroupBy(row => row[PLocationTable.GetCountryCodeDBName()]) .Select(g => g.CopyToDataTable()) .ToList(); DataTable BlankCountryLocations = Locations.Clone(); // create another table that contains all locations without a valid country code for (int i = 0; i < LocationDataTables.Count; i++) { // this helps the time left feature to be more accurate from the start LocationDataTables[i].DefaultView.Sort = PLocationTable.GetPostalCodeDBName() + " DESC"; LocationDataTables[i] = LocationDataTables[i].DefaultView.ToTable(); if (string.IsNullOrEmpty(LocationDataTables[i].Rows[0]["p_country_code_c"].ToString()) || (LocationDataTables[i].Rows[0]["p_country_code_c"].ToString() == "99")) { foreach (DataRow Row in LocationDataTables[i].Rows) { BlankCountryLocations.Rows.Add((object[])Row.ItemArray.Clone()); } } } Int64 TotalCalculations = 0; Int64 CompletedCalculations = 0; decimal PercentageCompleted = 0; // calculate number of calculations required for this check for (int i = 0; i < LocationDataTables.Count; i++) { if (LocationDataTables[i].Rows.Count > 0) { TotalCalculations += ((Int64)LocationDataTables[i].Rows.Count) * ((Int64)LocationDataTables[i].Rows.Count - 1) / 2; // if not table containing invalid country codes if (!string.IsNullOrEmpty(LocationDataTables[i].Rows[0]["p_country_code_c"].ToString()) && (LocationDataTables[i].Rows[0]["p_country_code_c"].ToString() != "99")) { TotalCalculations += BlankCountryLocations.Rows.Count; } } } Int64 TimeLeft; int MinutesLeft; int SecondsLeft; Stopwatch time = Stopwatch.StartNew(); // begin search for possible duplicates foreach (DataTable LocationCountry in LocationDataTables) { if (LocationCountry.Rows.Count <= 0) { continue; } for (int i = 0; i < LocationCountry.Rows.Count && ReturnTable.Rows.Count < 500; i++) { string AAddress = null; string[] AAddressArray = null; for (int j = i + 1; j < LocationCountry.Rows.Count; j++) { // check if two rows are a possible duplicate if (PossibleMatch(LocationCountry.Rows[i], ref AAddress, ref AAddressArray, LocationCountry.Rows[j], AExactMatchNumber)) { ReturnTable.Rows.Add(new object[] { LocationCountry.Rows[i][PLocationTable.GetSiteKeyDBName()], LocationCountry.Rows[i][PLocationTable.GetLocationKeyDBName()], LocationCountry.Rows[i][PLocationTable.GetLocalityDBName()], LocationCountry.Rows[i][PLocationTable.GetStreetNameDBName()], LocationCountry.Rows[i][PLocationTable.GetAddress3DBName()], LocationCountry.Rows[i][PLocationTable.GetCityDBName()], LocationCountry.Rows[i][PLocationTable.GetCountyDBName()], LocationCountry.Rows[i][PLocationTable.GetPostalCodeDBName()], LocationCountry.Rows[i][PLocationTable.GetCountryCodeDBName()], LocationCountry.Rows[j][PLocationTable.GetSiteKeyDBName()], LocationCountry.Rows[j][PLocationTable.GetLocationKeyDBName()], LocationCountry.Rows[j][PLocationTable.GetLocalityDBName()], LocationCountry.Rows[j][PLocationTable.GetStreetNameDBName()], LocationCountry.Rows[j][PLocationTable.GetAddress3DBName()], LocationCountry.Rows[j][PLocationTable.GetCityDBName()], LocationCountry.Rows[j][PLocationTable.GetCountyDBName()], LocationCountry.Rows[j][PLocationTable.GetPostalCodeDBName()], LocationCountry.Rows[j][PLocationTable.GetCountryCodeDBName()] }); } CompletedCalculations++; } // if not table containing invalid country codes if (!string.IsNullOrEmpty(LocationCountry.Rows[0]["p_country_code_c"].ToString()) && (LocationCountry.Rows[0]["p_country_code_c"].ToString() != "99")) { // compare with locations with invalid country codes for (int j = 0; j < BlankCountryLocations.Rows.Count; j++) { if (PossibleMatch(LocationCountry.Rows[i], ref AAddress, ref AAddressArray, BlankCountryLocations.Rows[j], AExactMatchNumber)) { ReturnTable.Rows.Add(new object[] { LocationCountry.Rows[i][PLocationTable.GetSiteKeyDBName()], LocationCountry.Rows[i][PLocationTable.GetLocationKeyDBName()], LocationCountry.Rows[i][PLocationTable.GetLocalityDBName()], LocationCountry.Rows[i][PLocationTable.GetStreetNameDBName()], LocationCountry.Rows[i][PLocationTable.GetAddress3DBName()], LocationCountry.Rows[i][PLocationTable.GetCityDBName()], LocationCountry.Rows[i][PLocationTable.GetCountyDBName()], LocationCountry.Rows[i][PLocationTable.GetPostalCodeDBName()], LocationCountry.Rows[i][PLocationTable.GetCountryCodeDBName()], BlankCountryLocations.Rows[j][PLocationTable.GetSiteKeyDBName()], BlankCountryLocations.Rows[j][PLocationTable.GetLocationKeyDBName()], BlankCountryLocations.Rows[j][PLocationTable.GetLocalityDBName()], BlankCountryLocations.Rows[j][PLocationTable.GetStreetNameDBName()], BlankCountryLocations.Rows[j][PLocationTable.GetAddress3DBName()], BlankCountryLocations.Rows[j][PLocationTable.GetCityDBName()], BlankCountryLocations.Rows[j][PLocationTable.GetCountyDBName()], BlankCountryLocations.Rows[j][PLocationTable.GetPostalCodeDBName()], BlankCountryLocations.Rows[j][PLocationTable.GetCountryCodeDBName()] }); } CompletedCalculations++; } } if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob) { break; } // estimate the remaining time PercentageCompleted = decimal.Divide(CompletedCalculations * 100, TotalCalculations); TimeLeft = (Int64)(time.ElapsedMilliseconds * ((100 / PercentageCompleted) - 1)); MinutesLeft = (int)TimeLeft / 60000; string OutputMessage = string.Format(Catalog.GetString("Completed: {0}%"), Math.Round(PercentageCompleted, 1)); // only show estimated time left if at least 0.1% complete if (PercentageCompleted >= (decimal)0.1) { // only show seconds if less than 10 minutes remaining if (MinutesLeft < 10) { SecondsLeft = (int)(TimeLeft % 60000) / 1000; OutputMessage += string.Format(Catalog.GetPluralString(" (approx. {0} minute and {1} seconds remaining)", " (approx. {0} minutes and {1} seconds remaining)", MinutesLeft, true), MinutesLeft, SecondsLeft); } else { OutputMessage += string.Format(Catalog.GetString(" (approx. {0} minutes remaining)"), MinutesLeft); } } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), OutputMessage, PercentageCompleted); } } }); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); ADuplicateAddresses = ReturnTable.Copy(); }