/// <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);
}
