/// <summary> /// train with imported bank statements and existing gift batches /// </summary> public static void Train(AEpStatementTable AStatements) { int stmtCounter = 0; // go through all statements in the dataset, and find gift matches for those days foreach (AEpStatementRow stmt in AStatements.Rows) { TLogging.LogAtLevel(1, "Training Statement " + stmt.StatementKey.ToString() + " " + stmt.Date.ToShortDateString() + " " + stmt.Filename); stmtCounter++; if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); return; } // first stage: collect historic matches from database: // go through each transaction of the statement, // and see if you can find a donation on that date with the same amount from the same bank account // store this as a match TLogging.LogAtLevel(1, "loading data ..."); BankImportTDS MainDS = LoadData(stmt.LedgerNumber, stmt.StatementKey); // Get all gifts at given date TLogging.LogAtLevel(1, "get gifts by date ..."); List <int> GiftBatchNumbers; GetGiftsByDate(stmt.LedgerNumber, MainDS, stmt.Date, stmt.BankAccountCode, out GiftBatchNumbers); int SelectedGiftBatch = -1; if (GiftBatchNumbers.Count > 0) { TLogging.LogAtLevel(1, "Found gift batches: " + GiftBatchNumbers.Count.ToString()); foreach (int i in GiftBatchNumbers) { TLogging.LogAtLevel(1, " " + i.ToString()); } SelectedGiftBatch = FindGiftBatch(MainDS, stmt); TLogging.LogAtLevel(1, " selected gift batch: " + SelectedGiftBatch.ToString()); } if (SelectedGiftBatch == -1) { // cannot find the posted gift batch without any doubt continue; } CreateMatches(MainDS, stmt, SelectedGiftBatch, true); } }
public static bool GetCurrentState(out string ACaption, out string AStatusMessage, out int APercentageDone, out bool AJobFinished) { TProgressTracker.TProgressState state = TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()); ACaption = state.Caption; AStatusMessage = state.StatusMessage; APercentageDone = state.PercentageDone; AJobFinished = state.JobFinished; return(state.PercentageDone != -1 || state.StatusMessage != string.Empty); }
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 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); }
public static TProgressState GetProgress(string AReportID) { return(TProgressTracker.GetCurrentState(AReportID)); }
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(); }
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"]; 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); }
/// <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); }