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 = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
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(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 = 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++;
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 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 = 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++;
TFormDataPerson FormDataPerson = new TFormDataPerson();
FillFormDataFromPersonnel(ExtractRow.PartnerKey, FormDataPerson, AFormLetterInfo, ExtractRow.SiteKey, ExtractRow.LocationKey);
dataList.Add(FormDataPerson);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob)
{
dataList.Clear();
ReturnValue = false;
TLogging.Log("Retrieve Personnel Form Letter Data - Job cancelled");
break;
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving Personnel Data"),
(RowCounter * 100) / ExtractTable.Rows.Count);
}
});
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
AFormDataList = new List <TFormData>();
AFormDataList = dataList;
return(ReturnValue);
}
public static TSubmitChangesResult StoreNewBankStatement(BankImportTDS AStatementAndTransactionsDS,
out Int32 AFirstStatementKey)
{
string MyClientID = DomainManager.GClientID.ToString();
AFirstStatementKey = -1;
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Processing new bank statements"),
AStatementAndTransactionsDS.AEpStatement.Rows.Count + 1);
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Saving to database"),
0);
try
{
// Must not throw away the changes because we need the correct statement keys
AStatementAndTransactionsDS.DontThrowAwayAfterSubmitChanges = true;
BankImportTDSAccess.SubmitChanges(AStatementAndTransactionsDS);
AFirstStatementKey = -1;
if (AStatementAndTransactionsDS != null)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("starting to train"),
1);
AFirstStatementKey = AStatementAndTransactionsDS.AEpStatement[0].StatementKey;
// search for already posted gift batches, and do the matching for these imported statements
TBankImportMatching.Train(AStatementAndTransactionsDS.AEpStatement);
}
TProgressTracker.FinishJob(MyClientID);
}
catch (Exception ex)
{
TLogging.Log(ex.ToString());
TProgressTracker.CancelJob(MyClientID);
return(TSubmitChangesResult.scrError);
}
return(TSubmitChangesResult.scrOK);
}
/// <summary>
/// sample webconnector method that takes a long time and uses the ProgressTracker
/// </summary>
static public string LongRunningJob()
{
string ClientID = DomainManager.GClientID.ToString();
// enable DebugLevel to show progress state on console
TLogging.DebugLevel = 1;
TProgressTracker.InitProgressTracker(ClientID,
"LongRunningJob",
100);
for (int i = 0; i < 10; i++)
{
Thread.Sleep(500);
TProgressTracker.SetCurrentState(ClientID, "working", Convert.ToDecimal(i * 10));
}
TProgressTracker.FinishJob(ClientID);
return("done");
}
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 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);
}
private void ExecuteFullQuery(string AContext = null, TDataBase ADataBase = null)
{
TDataBase DBConnectionObj = null;
TDBTransaction ReadTransaction = new TDBTransaction();
bool SeparateDBConnectionEstablished = false;
if (FFindParameters.FParametersGivenSeparately)
{
string SQLOrderBy = "";
string SQLWhereCriteria = "";
if (FFindParameters.FPagedTableWhereCriteria != "")
{
SQLWhereCriteria = "WHERE " + FFindParameters.FPagedTableWhereCriteria;
}
if (FFindParameters.FPagedTableOrderBy != "")
{
SQLOrderBy = " ORDER BY " + FFindParameters.FPagedTableOrderBy;
}
FSelectSQL = "SELECT " + FFindParameters.FPagedTableColumns + " FROM " + FFindParameters.FPagedTable +
' ' +
SQLWhereCriteria + SQLOrderBy;
}
else
{
FSelectSQL = FFindParameters.FSqlQuery;
}
TLogging.LogAtLevel(9, (this.GetType().FullName + ".ExecuteFullQuery SQL:" + FSelectSQL));
// clear temp table. do not recreate because it may be typed
FTmpDataTable.Clear();
try
{
if (ADataBase == null)
{
ADataBase = new TDataBase();
ADataBase.EstablishDBConnection(AContext + " Connection");
SeparateDBConnectionEstablished = true;
}
ReadTransaction = ADataBase.BeginTransaction(IsolationLevel.ReadCommitted,
-1, AContext + " Transaction");
// Fill temporary table with query results (all records)
FTotalRecords = ADataBase.SelectUsingDataAdapter(FSelectSQL, ReadTransaction,
ref FTmpDataTable, out FDataAdapterCanceller,
delegate(ref IDictionaryEnumerator AEnumerator)
{
if (FFindParameters.FColumNameMapping != null)
{
AEnumerator = FFindParameters.FColumNameMapping.GetEnumerator();
return(FFindParameters.FPagedTable + "_for_paging");
}
else
{
return(String.Empty);
}
}, 60, FFindParameters.FParametersArray);
}
catch (PostgresException Exp)
{
if (Exp.SqlState == "57014") // Exception with Code 57014 is what Npgsql raises as a response to a Cancel request of a Command
{
TLogging.LogAtLevel(7, this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; proper reply from Npgsql!");
}
else
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general PostgresException occured: " + Exp.ToString());
}
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
return;
}
catch (Exception Exp)
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general Exception occured: " + Exp.ToString());
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
return;
}
finally
{
ReadTransaction.Rollback();
// Close separate DB Connection if we opened one earlier
if (SeparateDBConnectionEstablished)
{
DBConnectionObj.CloseDBConnection();
}
}
TLogging.LogAtLevel(7,
(this.GetType().FullName + ".ExecuteFullQuery: FDataAdapter.Fill finished. FTotalRecords: " + FTotalRecords.ToString()));
FPageDataTable = FTmpDataTable.Clone();
FPageDataTable.TableName = FFindParameters.FSearchName;
TProgressTracker.SetCurrentState(FProgressID, "Query executed.", 100.0m);
TProgressTracker.FinishJob(FProgressID);
}
/// <summary>
/// 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
private void ExecuteFullQuery()
{
// TDBTransaction ReadTransaction;
// Boolean NewTransaction = false;
if (FFindParameters.FParametersGivenSeparately)
{
string SQLOrderBy = "";
string SQLWhereCriteria = "";
if (FFindParameters.FPagedTableWhereCriteria != "")
{
SQLWhereCriteria = "WHERE " + FFindParameters.FPagedTableWhereCriteria;
}
if (FFindParameters.FPagedTableOrderBy != "")
{
SQLOrderBy = " ORDER BY " + FFindParameters.FPagedTableOrderBy;
}
FSelectSQL = "SELECT " + FFindParameters.FPagedTableColumns + " FROM " + FFindParameters.FPagedTable +
' ' +
SQLWhereCriteria + SQLOrderBy;
}
else
{
FSelectSQL = FFindParameters.FSqlQuery;
}
TLogging.LogAtLevel(9, (this.GetType().FullName + ".ExecuteFullQuery SQL:" + FSelectSQL));
// clear temp table. do not recreate because it may be typed
FTmpDataTable.Clear();
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
try
{
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// Fill temporary table with query results (all records)
FTotalRecords = DBAccess.GDBAccessObj.SelectUsingDataAdapter(FSelectSQL, ReadTransaction,
ref FTmpDataTable, out FDataAdapterCanceller,
delegate(ref IDictionaryEnumerator AEnumerator)
{
if (FFindParameters.FColumNameMapping != null)
{
AEnumerator = FFindParameters.FColumNameMapping.GetEnumerator();
return(FFindParameters.FPagedTable + "_for_paging");
}
else
{
return(String.Empty);
}
}, true, 60, FFindParameters.FParametersArray);
}
catch (NpgsqlException Exp)
{
if (Exp.Code == "57014") // Exception with Code 57014 is what Npgsql raises as a response to a Cancel request of a Command
{
TLogging.LogAtLevel(7, this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; proper reply from Npgsql!");
}
else
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general NpgsqlException occured: " + Exp.ToString());
}
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
return;
}
catch (Exception Exp)
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general Exception occured: " + Exp.ToString());
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
return;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
TLogging.LogAtLevel(7,
(this.GetType().FullName + ".ExecuteFullQuery: FDataAdapter.Fill finished. FTotalRecords: " + FTotalRecords.ToString()));
FPageDataTable = FTmpDataTable.Clone();
FPageDataTable.TableName = FFindParameters.FSearchName;
TProgressTracker.SetCurrentState(FProgressID, "Query executed.", 100.0m);
TProgressTracker.FinishJob(FProgressID);
}
public static bool ResetDatabase(string AZippedNewDatabaseData)
{
TDataBase DBConnectionObj = null;
List <string> tables = TTableList.GetDBNames();
bool SubmissionResult = false;
TDBTransaction ReadWriteTransaction = new TDBTransaction();
SortedList <int, string> CurrencyPerLedger = new SortedList <int, string>();
string ClientID = "ClientID";
try
{
ClientID = DomainManager.GClientID.ToString();
}
catch (Exception)
{
}
try
{
// Open a separate DB Connection for the importing of the data...
DBConnectionObj = DBAccess.Connect("ExportAllTables");
// ...and start a DB Transaction on that separate DB Connection
DBConnectionObj.WriteTransaction(ref ReadWriteTransaction, ref SubmissionResult, delegate
{
try
{
TProgressTracker.InitProgressTracker(ClientID,
Catalog.GetString("Restoring Database..."),
tables.Count + 3);
tables.Reverse();
// ignore s_session table, to avoid locking during the restore
tables.Remove("s_session");
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Deleting current data..."),
0);
// need to reset connection between s_user and p_partner to avoid broken foreign key
ReadWriteTransaction.DataBaseObj.ExecuteNonQuery("UPDATE pub_s_user SET p_partner_key_n = NULL", ReadWriteTransaction);
foreach (string table in tables)
{
ReadWriteTransaction.DataBaseObj.ExecuteNonQuery("DELETE FROM pub_" + table, ReadWriteTransaction);
}
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData));
ymlParser.ParseCaptions();
tables.Reverse();
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Loading initial tables..."),
1);
// one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails?
bool success = true;
success = success && LoadTable("s_user", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
success = success && LoadTable("s_module", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
success = success && LoadTable("s_user_module_access_permission", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
success = success && LoadTable("s_system_defaults", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
success = success && LoadTable("s_system_status", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
if (!success)
{
// As SubmissionResult is still TSubmitChangesResult.scrError, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
tables.Remove("s_user");
tables.Remove("s_module");
tables.Remove("s_user_module_access_permission");
tables.Remove("s_system_defaults");
tables.Remove("s_system_status");
int tableCounter = 2;
foreach (string table in tables)
{
TProgressTracker.SetCurrentState(ClientID,
String.Format(Catalog.GetString("Loading Table {0}..."), table),
tableCounter);
tableCounter++;
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
LoadTable(table, ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
}
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Loading Sequences..."),
tables.Count + 5 + 3);
// set sequences appropriately, not lagging behind the imported data
foreach (string seq in TTableList.GetDBSequenceNames())
{
LoadSequence(seq, ymlParser, ReadWriteTransaction);
}
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Finishing Restore..."),
tables.Count + 5 + 4);
SubmissionResult = true;
// reset all cached tables
TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing();
}
catch (Exception e)
{
TLogging.Log("Problem in ResetDatabase: " + e.ToString());
TLogging.LogStackTrace(TLoggingType.ToLogfile);
throw;
}
});
}
finally
{
if (DBConnectionObj != null)
{
DBConnectionObj.CloseDBConnection();
}
}
// if we import an older database, we need to upgrade it.
// this happens in a separate database connection and database transactions.
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Updating the database..."),
tables.Count + 5 + 5);
TDBUpgrades upgrades = new TDBUpgrades();
upgrades.UpgradeDatabase();
TProgressTracker.FinishJob(ClientID);
return(SubmissionResult);
}
public static BankImportTDS GetBankStatementTransactionsAndMatches(Int32 AStatementKey, Int32 ALedgerNumber)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
BankImportTDS ResultDataset = new BankImportTDS();
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Load Bank Statement"),
100.0m);
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("loading statement"),
0);
try
{
AEpStatementAccess.LoadByPrimaryKey(ResultDataset, AStatementKey, Transaction);
if (ResultDataset.AEpStatement[0].BankAccountCode.Length == 0)
{
throw new Exception("Loading Bank Statement: Bank Account must not be empty");
}
ACostCentreAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction);
AEpTransactionAccess.LoadViaAEpStatement(ResultDataset, AStatementKey, Transaction);
BankImportTDS TempDataset = new BankImportTDS();
AEpTransactionAccess.LoadViaAEpStatement(TempDataset, AStatementKey, Transaction);
AEpMatchAccess.LoadViaALedger(TempDataset, ResultDataset.AEpStatement[0].LedgerNumber, Transaction);
// load all bankingdetails and partner shortnames related to this statement
string sqlLoadPartnerByBankAccount =
"SELECT DISTINCT p.p_partner_key_n AS PartnerKey, " +
"p.p_partner_short_name_c AS ShortName, " +
"t.p_branch_code_c AS BranchCode, " +
"t.a_bank_account_number_c AS BankAccountNumber " +
"FROM PUB_a_ep_transaction t, PUB_p_banking_details bd, PUB_p_bank b, PUB_p_partner_banking_details pbd, PUB_p_partner p " +
"WHERE t.a_statement_key_i = " + AStatementKey.ToString() + " " +
"AND bd.p_bank_account_number_c = t.a_bank_account_number_c " +
"AND b.p_partner_key_n = bd.p_bank_key_n " +
"AND b.p_branch_code_c = t.p_branch_code_c " +
"AND pbd.p_banking_details_key_i = bd.p_banking_details_key_i " +
"AND p.p_partner_key_n = pbd.p_partner_key_n";
DataTable PartnerByBankAccount = DBAccess.GDBAccessObj.SelectDT(sqlLoadPartnerByBankAccount, "partnerByBankAccount", Transaction);
PartnerByBankAccount.DefaultView.Sort = "BranchCode, BankAccountNumber";
// get all recipients that have been merged
string sqlGetMergedRecipients =
string.Format(
"SELECT DISTINCT p.p_partner_key_n AS PartnerKey, p.p_status_code_c AS StatusCode FROM PUB_a_ep_match m, PUB_p_partner p " +
"WHERE (m.p_recipient_key_n = p.p_partner_key_n OR m.p_donor_key_n = p.p_partner_key_n) AND p.p_status_code_c = '{0}'",
MPartnerConstants.PARTNERSTATUS_MERGED);
DataTable MergedPartners = DBAccess.GDBAccessObj.SelectDT(sqlGetMergedRecipients, "mergedPartners", Transaction);
MergedPartners.DefaultView.Sort = "PartnerKey";
DBAccess.GDBAccessObj.RollbackTransaction();
string BankAccountCode = ResultDataset.AEpStatement[0].BankAccountCode;
TempDataset.AEpMatch.DefaultView.Sort = AEpMatchTable.GetMatchTextDBName();
SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>();
int count = 0;
// load the matches or create new matches
foreach (BankImportTDSAEpTransactionRow row in ResultDataset.AEpTransaction.Rows)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("finding matches") +
" " + count + "/" + ResultDataset.AEpTransaction.Rows.Count.ToString(),
10.0m + (count * 80.0m / ResultDataset.AEpTransaction.Rows.Count));
count++;
BankImportTDSAEpTransactionRow tempTransactionRow =
(BankImportTDSAEpTransactionRow)TempDataset.AEpTransaction.Rows.Find(
new object[] {
row.StatementKey,
row.Order,
row.DetailKey
});
// find a match with the same match text, or create a new one
if (row.IsMatchTextNull() || (row.MatchText.Length == 0) || !row.MatchText.StartsWith(BankAccountCode))
{
row.MatchText = TBankImportMatching.CalculateMatchText(BankAccountCode, row);
tempTransactionRow.MatchText = row.MatchText;
}
DataRowView[] matches = TempDataset.AEpMatch.DefaultView.FindRows(row.MatchText);
if (matches.Length > 0)
{
Decimal sum = 0.0m;
// update the recent date
foreach (DataRowView rv in matches)
{
AEpMatchRow r = (AEpMatchRow)rv.Row;
sum += r.GiftTransactionAmount;
// check if the recipient key is still valid. could be that they have married, and merged into another family record
if ((r.RecipientKey != 0) &&
(MergedPartners.DefaultView.FindRows(r.RecipientKey).Length > 0))
{
TLogging.LogAtLevel(1, "partner has been merged: " + r.RecipientKey.ToString());
r.RecipientKey = 0;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
// check if the donor key is still valid. could be that they have married, and merged into another family record
if ((r.DonorKey != 0) &&
(MergedPartners.DefaultView.FindRows(r.DonorKey).Length > 0))
{
TLogging.LogAtLevel(1, "partner has been merged: " + r.DonorKey.ToString());
r.DonorKey = 0;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
if (r.RecentMatch < row.DateEffective)
{
r.RecentMatch = row.DateEffective;
}
// do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time
row.MatchAction = r.Action;
if (r.IsDonorKeyNull() || (r.DonorKey <= 0))
{
FindDonorByAccountNumber(r, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber);
}
}
if (sum != row.TransactionAmount)
{
TLogging.Log("we should drop this match since the total is wrong: " + row.Description);
row.MatchAction = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
foreach (DataRowView rv in matches)
{
AEpMatchRow r = (AEpMatchRow)rv.Row;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
}
}
else if (!MatchesToAddLater.ContainsKey(row.MatchText))
{
// create new match
AEpMatchRow tempRow = TempDataset.AEpMatch.NewRowTyped(true);
tempRow.EpMatchKey = (TempDataset.AEpMatch.Count + MatchesToAddLater.Count + 1) * -1;
tempRow.Detail = 0;
tempRow.MatchText = row.MatchText;
tempRow.LedgerNumber = ALedgerNumber;
tempRow.GiftTransactionAmount = row.TransactionAmount;
tempRow.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
FindDonorByAccountNumber(tempRow, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber);
#if disabled
// fuzzy search for the partner. only return if unique result
string sql =
"SELECT p_partner_key_n, p_partner_short_name_c FROM p_partner WHERE p_partner_short_name_c LIKE '{0}%' OR p_partner_short_name_c LIKE '{1}%'";
string[] names = row.AccountName.Split(new char[] { ' ' });
if (names.Length > 1)
{
string optionShortName1 = names[0] + ", " + names[1];
string optionShortName2 = names[1] + ", " + names[0];
DataTable partner = DBAccess.GDBAccessObj.SelectDT(String.Format(sql,
optionShortName1,
optionShortName2), "partner", Transaction);
if (partner.Rows.Count == 1)
{
tempRow.DonorKey = Convert.ToInt64(partner.Rows[0][0]);
}
}
#endif
MatchesToAddLater.Add(tempRow.MatchText, tempRow);
// do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time
row.MatchAction = tempRow.Action;
}
}
// for speed reasons, add the new rows after clearing the sort on the view
TempDataset.AEpMatch.DefaultView.Sort = string.Empty;
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
TempDataset.AEpMatch.Rows.Add(m);
}
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("save matches"),
90.0m);
TempDataset.ThrowAwayAfterSubmitChanges = true;
// only store a_ep_transactions and a_ep_matches, but without additional typed fields (ie MatchAction)
BankImportTDSAccess.SubmitChanges(TempDataset.GetChangesTyped(true));
}
catch (Exception e)
{
TLogging.Log(e.GetType().ToString() + " in BankImport, GetBankStatementTransactionsAndMatches; " + e.Message);
TLogging.Log(e.StackTrace);
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
// drop all matches that do not occur on this statement
ResultDataset.AEpMatch.Clear();
// reloading is faster than deleting all matches that are not needed
string sqlLoadMatchesOfStatement =
"SELECT DISTINCT m.* FROM PUB_a_ep_match m, PUB_a_ep_transaction t WHERE t.a_statement_key_i = ? AND m.a_ledger_number_i = ? AND m.a_match_text_c = t.a_match_text_c";
OdbcParameter param = new OdbcParameter("statementkey", OdbcType.Int);
param.Value = AStatementKey;
OdbcParameter paramLedgerNumber = new OdbcParameter("ledgerNumber", OdbcType.Int);
paramLedgerNumber.Value = ALedgerNumber;
DBAccess.GDBAccessObj.SelectDT(ResultDataset.AEpMatch,
sqlLoadMatchesOfStatement,
null,
new OdbcParameter[] { param, paramLedgerNumber }, -1, -1);
// update the custom field for cost centre name for each match
foreach (BankImportTDSAEpMatchRow row in ResultDataset.AEpMatch.Rows)
{
ACostCentreRow ccRow = (ACostCentreRow)ResultDataset.ACostCentre.Rows.Find(new object[] { row.LedgerNumber, row.CostCentreCode });
if (ccRow != null)
{
row.CostCentreName = ccRow.CostCentreName;
}
}
// remove all rows that we do not need on the client side
ResultDataset.AGiftDetail.Clear();
ResultDataset.AMotivationDetail.Clear();
ResultDataset.ACostCentre.Clear();
ResultDataset.AcceptChanges();
if (TLogging.DebugLevel > 0)
{
int CountMatched = 0;
foreach (BankImportTDSAEpTransactionRow transaction in ResultDataset.AEpTransaction.Rows)
{
if (!transaction.IsMatchActionNull() && (transaction.MatchAction != MFinanceConstants.BANK_STMT_STATUS_UNMATCHED))
{
CountMatched++;
}
}
TLogging.Log(
"Loading bank statement: matched: " + CountMatched.ToString() + " of " + ResultDataset.AEpTransaction.Rows.Count.ToString());
}
TProgressTracker.FinishJob(MyClientID);
return(ResultDataset);
}
/// <summary>
/// Export all the Data of the batches array list to a String
/// </summary>
/// <param name="Abatches"></param>
/// <param name="ArequestParams"></param>
/// <param name="AexportString"></param>
/// <returns>false if batch does not exist at all</returns>
public bool ExportAllGLBatchData(ArrayList Abatches, Hashtable ArequestParams, out String AexportString)
{
FStringWriter = new StringWriter();
FMainDS = new GLBatchTDS();
FDelimiter = (String)ArequestParams["Delimiter"];
FLedgerNumber = (Int32)ArequestParams["ALedgerNumber"];
FDateFormatString = (String)ArequestParams["DateFormatString"];
FSummary = (bool)ArequestParams["Summary"];
FUseBaseCurrency = (bool)ArequestParams["bUseBaseCurrency"];
FBaseCurrency = (String)ArequestParams["BaseCurrency"];
FDateForSummary = (DateTime)ArequestParams["DateForSummary"];
String NumberFormat = (String)ArequestParams["NumberFormat"];
FCultureInfo = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE");
FTransactionsOnly = (bool)ArequestParams["TransactionsOnly"];
FDontSummarize = (bool)ArequestParams["bDontSummarize"];
FDontSummarizeAccount = (String)ArequestParams["DontSummarizeAccount"];
SortedDictionary <String, AJournalSummaryRow> sdSummary = new SortedDictionary <String, AJournalSummaryRow>();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("ExportAllGLBatchData");
db.ReadTransaction(ref Transaction,
delegate
{
UInt32 progressCounter = 0;
UInt32 progressJournalCounter = 0;
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Exporting GL Batches"),
100);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving records"),
10);
while (Abatches.Count > 0)
{
Int32 ABatchNumber = (Int32)Abatches[0];
ABatchAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, ABatchNumber, Transaction);
AJournalAccess.LoadViaABatch(FMainDS, FLedgerNumber, ABatchNumber, Transaction);
foreach (AJournalRow journal in FMainDS.AJournal.Rows)
{
if (journal.BatchNumber.Equals(ABatchNumber) && journal.LedgerNumber.Equals(FLedgerNumber))
{
ATransactionAccess.LoadViaAJournal(FMainDS, journal.LedgerNumber,
journal.BatchNumber,
journal.JournalNumber,
Transaction);
}
}
foreach (ATransactionRow trans in FMainDS.ATransaction.Rows)
{
if (trans.BatchNumber.Equals(ABatchNumber) && trans.LedgerNumber.Equals(FLedgerNumber))
{
ATransAnalAttribAccess.LoadViaATransaction(FMainDS, trans.LedgerNumber,
trans.BatchNumber,
trans.JournalNumber,
trans.TransactionNumber,
Transaction);
}
}
Abatches.RemoveAt(0);
}
UInt32 counter = 0;
AJournalSummaryRow journalSummary = null;
foreach (ABatchRow batch in FMainDS.ABatch.Rows)
{
progressCounter = 0;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString("Batch {0}"), batch.BatchNumber),
20);
if (!FTransactionsOnly & !FSummary)
{
WriteBatchLine(batch);
}
//foreach (AJournalRow journal in journalDS.AJournal.Rows)
foreach (AJournalRow journal in FMainDS.AJournal.Rows)
{
if (journal.BatchNumber.Equals(batch.BatchNumber) && journal.LedgerNumber.Equals(batch.LedgerNumber))
{
progressJournalCounter = 0;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString("Batch {0}, Journal {1}"), batch.BatchNumber, journal.JournalNumber),
(progressCounter / 25 + 4) * 5 > 90 ? 90 : (progressCounter / 25 + 4) * 5);
if (FSummary)
{
String mapCurrency = FUseBaseCurrency ? FBaseCurrency : journal.TransactionCurrency;
decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : journal.ExchangeRateToBase;
if (!sdSummary.TryGetValue(mapCurrency, out journalSummary))
{
journalSummary = new AJournalSummaryRow();
sdSummary.Add(mapCurrency, journalSummary);
}
//overwrite always because we want to have the last
journalSummary.ExchangeRateToBase = mapExchangeRateToBase;
journalSummary.TransactionCurrency = mapCurrency;
}
else
{
if (!FTransactionsOnly)
{
WriteJournalLine(journal);
}
}
FMainDS.ATransaction.DefaultView.Sort = ATransactionTable.GetTransactionNumberDBName();
FMainDS.ATransaction.DefaultView.RowFilter =
String.Format("{0}={1} and {2}={3} and {4}={5}",
ATransactionTable.GetLedgerNumberDBName(),
journal.LedgerNumber,
ATransactionTable.GetBatchNumberDBName(),
journal.BatchNumber,
ATransactionTable.GetJournalNumberDBName(),
journal.JournalNumber);
foreach (DataRowView dv in FMainDS.ATransaction.DefaultView)
{
progressJournalCounter++;
if (++progressCounter % 25 == 0)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString(
"Batch {0}, Journal {1} - {2}"), batch.BatchNumber, journal.JournalNumber,
progressJournalCounter),
(progressCounter / 25 + 4) * 5 > 90 ? 90 : (progressCounter / 25 + 4) * 5);
}
ATransactionRow transactionRow = (ATransactionRow)dv.Row;
if (FSummary)
{
ATransactionSummaryRow transactionSummary;
counter++;
String DictionaryKey = transactionRow.CostCentreCode + ";" + transactionRow.AccountCode;
int signum = transactionRow.DebitCreditIndicator ? 1 : -1;
bool bDontSummarizeAccount = FDontSummarize && FDontSummarizeAccount != null &&
FDontSummarizeAccount.Length > 0 &&
transactionRow.AccountCode.Equals(FDontSummarizeAccount);
if (bDontSummarizeAccount)
{
DictionaryKey += ";" + counter.ToString("X");
}
if (journalSummary.TransactionSummaries.TryGetValue(DictionaryKey, out transactionSummary))
{
transactionSummary.TransactionAmount += signum * transactionRow.TransactionAmount;
transactionSummary.AmountInBaseCurrency += signum * transactionRow.AmountInBaseCurrency;
}
else
{
transactionSummary = new ATransactionSummaryRow();
transactionSummary.CostCentreCode = transactionRow.CostCentreCode;
transactionSummary.AccountCode = transactionRow.AccountCode;
transactionSummary.TransactionAmount = signum * transactionRow.TransactionAmount;
transactionSummary.AmountInBaseCurrency = signum * transactionRow.AmountInBaseCurrency;
if (bDontSummarizeAccount)
{
transactionSummary.Narrative = transactionRow.Narrative;
transactionSummary.Reference = transactionRow.Reference;
}
else
{
transactionSummary.Narrative = summarizedData;
transactionSummary.Reference = "";
}
journalSummary.TransactionSummaries.Add(DictionaryKey, transactionSummary);
}
}
else
{
WriteTransactionLine(transactionRow);
}
}
}
}
}
if (FSummary)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Summarising"),
95);
//To simplify matters this is always written even if there are no batches
if (!FTransactionsOnly)
{
// no batch summary line if only transactions are to be exported
WriteBatchSummaryLine();
}
foreach (KeyValuePair <string, AJournalSummaryRow> kvp in sdSummary)
{
if (!FTransactionsOnly)
{
// no journal summary line if only transactions are to be exported
WriteJournalSummaryLine(kvp.Value);
}
foreach (KeyValuePair <string, ATransactionSummaryRow> kvpt in kvp.Value.TransactionSummaries)
{
WriteTransactionSummaryLine(kvpt.Value);
}
}
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("GL batch export successful"),
100);
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
});
AexportString = FStringWriter.ToString();
return(true);
} // Export All GLBatch Data
public static Int32 CreateGiftBatch(
Int32 ALedgerNumber,
Int32 AStatementKey,
Int32 AGiftBatchNumber,
out TVerificationResultCollection AVerificationResult)
{
BankImportTDS MainDS = GetBankStatementTransactionsAndMatches(AStatementKey, ALedgerNumber);
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Creating gift batch"),
MainDS.AEpTransaction.DefaultView.Count + 10);
AVerificationResult = new TVerificationResultCollection();
MainDS.AEpTransaction.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpTransactionTable.GetStatementKeyDBName(),
AStatementKey);
MainDS.AEpStatement.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
AStatementKey);
AEpStatementRow stmt = (AEpStatementRow)MainDS.AEpStatement.DefaultView[0].Row;
// TODO: optional: use the preselected gift batch, AGiftBatchNumber
Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber;
DateTime BatchDateEffective = stmt.Date;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
if (!TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref BatchDateEffective, out DateEffectiveYearNumber,
out DateEffectivePeriodNumber,
Transaction, true))
{
// just use the latest possible date
string msg =
String.Format(Catalog.GetString("Date {0} is not in an open period of the ledger, using date {1} instead for the gift batch."),
stmt.Date.ToShortDateString(),
BatchDateEffective.ToShortDateString());
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Info));
}
ACostCentreAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
MainDS.AEpMatch.DefaultView.Sort =
AEpMatchTable.GetActionDBName() + ", " +
AEpMatchTable.GetMatchTextDBName();
if (MainDS.AEpTransaction.DefaultView.Count == 0)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Creating Gift Batch"),
String.Format(Catalog.GetString("There are no transactions for statement #{0}."), AStatementKey),
TResultSeverity.Resv_Info));
return(-1);
}
foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView)
{
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] {
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT,
transactionRow.MatchText
});
if (matches.Length > 0)
{
AEpMatchRow match = (AEpMatchRow)matches[0].Row;
if (match.IsDonorKeyNull() || (match.DonorKey == 0))
{
string msg =
String.Format(Catalog.GetString("Cannot create a gift for transaction {0} since there is no valid donor."),
transactionRow.Description);
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Critical));
DBAccess.GDBAccessObj.RollbackTransaction();
return(-1);
}
}
}
string MatchedGiftReference = stmt.Filename;
if (!stmt.IsBankAccountKeyNull())
{
string sqlGetBankSortCode =
"SELECT bank.p_branch_code_c " +
"FROM PUB_p_banking_details details, PUB_p_bank bank " +
"WHERE details.p_banking_details_key_i = ?" +
"AND details.p_bank_key_n = bank.p_partner_key_n";
OdbcParameter param = new OdbcParameter("detailkey", OdbcType.Int);
param.Value = stmt.BankAccountKey;
PBankTable bankTable = new PBankTable();
DBAccess.GDBAccessObj.SelectDT(bankTable, sqlGetBankSortCode, Transaction, new OdbcParameter[] { param }, 0, 0);
MatchedGiftReference = bankTable[0].BranchCode + " " + stmt.Date.Day.ToString();
}
DBAccess.GDBAccessObj.RollbackTransaction();
GiftBatchTDS GiftDS = TGiftTransactionWebConnector.CreateAGiftBatch(
ALedgerNumber,
BatchDateEffective,
String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString()));
AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0];
giftbatchRow.BankAccountCode = stmt.BankAccountCode;
decimal HashTotal = 0.0M;
MainDS.AEpTransaction.DefaultView.Sort =
AEpTransactionTable.GetNumberOnPaperStatementDBName();
MainDS.AEpMatch.DefaultView.RowFilter = String.Empty;
MainDS.AEpMatch.DefaultView.Sort =
AEpMatchTable.GetActionDBName() + ", " +
AEpMatchTable.GetMatchTextDBName();
int counter = 5;
foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Preparing the gifts"),
counter++);
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] {
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT,
transactionRow.MatchText
});
if (matches.Length > 0)
{
AEpMatchRow match = (AEpMatchRow)matches[0].Row;
AGiftRow gift = GiftDS.AGift.NewRowTyped();
gift.LedgerNumber = giftbatchRow.LedgerNumber;
gift.BatchNumber = giftbatchRow.BatchNumber;
gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1;
gift.DonorKey = match.DonorKey;
gift.DateEntered = transactionRow.DateEffective;
gift.Reference = MatchedGiftReference;
GiftDS.AGift.Rows.Add(gift);
giftbatchRow.LastGiftNumber++;
foreach (DataRowView r in matches)
{
match = (AEpMatchRow)r.Row;
AGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped();
detail.LedgerNumber = gift.LedgerNumber;
detail.BatchNumber = gift.BatchNumber;
detail.GiftTransactionNumber = gift.GiftTransactionNumber;
detail.DetailNumber = gift.LastDetailNumber + 1;
gift.LastDetailNumber++;
detail.GiftTransactionAmount = match.GiftTransactionAmount;
detail.GiftAmount = match.GiftTransactionAmount;
HashTotal += match.GiftTransactionAmount;
detail.MotivationGroupCode = match.MotivationGroupCode;
detail.MotivationDetailCode = match.MotivationDetailCode;
// do not use the description in comment one, because that could show up on the gift receipt???
// detail.GiftCommentOne = transactionRow.Description;
detail.CommentOneType = MFinanceConstants.GIFT_COMMENT_TYPE_BOTH;
detail.CostCentreCode = match.CostCentreCode;
detail.RecipientKey = match.RecipientKey;
detail.RecipientLedgerNumber = match.RecipientLedgerNumber;
AMotivationDetailRow motivation = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find(
new object[] { ALedgerNumber, detail.MotivationGroupCode, detail.MotivationDetailCode });
if (motivation == null)
{
AVerificationResult.Add(new TVerificationResult(
String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description),
String.Format(Catalog.GetString("Cannot find motivation group '{0}' and motivation detail '{1}'"),
detail.MotivationGroupCode, detail.MotivationDetailCode),
TResultSeverity.Resv_Critical));
}
if (detail.CostCentreCode.Length == 0)
{
// try to retrieve the current costcentre for this recipient
if (detail.RecipientKey != 0)
{
detail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(detail.RecipientKey);
detail.CostCentreCode = TGiftTransactionWebConnector.IdentifyPartnerCostCentre(detail.LedgerNumber,
detail.RecipientLedgerNumber);
}
else
{
if (motivation != null)
{
detail.CostCentreCode = motivation.CostCentreCode;
}
}
}
// check for active cost centre
ACostCentreRow costcentre = (ACostCentreRow)MainDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, detail.CostCentreCode });
if ((costcentre == null) || !costcentre.CostCentreActiveFlag)
{
AVerificationResult.Add(new TVerificationResult(
String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description),
Catalog.GetString("Invalid or inactive cost centre"),
TResultSeverity.Resv_Critical));
}
GiftDS.AGiftDetail.Rows.Add(detail);
}
}
}
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Submit to database"),
counter++);
if (AVerificationResult.HasCriticalErrors)
{
return(-1);
}
giftbatchRow.HashTotal = HashTotal;
giftbatchRow.BatchTotal = HashTotal;
// do not overwrite the parameter, because there might be the hint for a different gift batch date
TVerificationResultCollection VerificationResultSubmitChanges;
TSubmitChangesResult result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS,
out VerificationResultSubmitChanges);
TProgressTracker.FinishJob(MyClientID);
if (result == TSubmitChangesResult.scrOK)
{
return(giftbatchRow.BatchNumber);
}
return(-1);
}
public static bool 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>
/// for displaying the progress
/// </summary>
/// <returns>void</returns>
private void WriteToStatusBar(String s)
{
TProgressTracker.SetCurrentState(FProgressID, s, -1.0m);
}
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();
}
/// <summary>
/// Returns a DataTable for the requested 'Page'.
/// </summary>
/// <remarks><see cref="ExecuteQuery" /> (or <see cref="ExecuteFullQuery" />)
/// needs to be called before to execute the SQL SELECT Statement to be able to return data.</remarks>
/// <param name="APage">The 'Page' that should be returned</param>
/// <param name="APageSize">Number of DataRows that a 'Page' should contain.</param>
/// <returns>DataTable containing 'PageSize' (or less if the 'TotalRecords' are
/// less than that value) DataRows.
/// </returns>
public DataTable GetData(Int16 APage, Int16 APageSize)
{
TLogging.LogAtLevel(7, String.Format("TPagedDataSet.GetData called (APage: {0}, APageSize={1})", APage, APageSize));
// wait until the query has been run in the other thread
while (FTotalRecords == -1)
{
System.Threading.Thread.Sleep(500);
}
if (APage != FLastRetrievedPage)
{
FLastRetrievedPage = APage;
if (APage == 0)
{
FTotalPages = Convert.ToInt16(Math.Ceiling(((double)FTotalRecords) / ((double)APageSize)));
TLogging.LogAtLevel(7, "FTotalPages: " + FTotalPages.ToString());
if (FTotalRecords > 0)
{
// Build FPageDataTable
CopyRowsInPage(0, APageSize);
}
}
else
{
// page > 0
if (FTotalRecords > 0)
{
if (APage <= FTotalPages)
{
FPageDataTable.Rows.Clear();
// Build FPageDataTable
CopyRowsInPage(APage, APageSize);
}
// FRecordsAffected > 0
else
{
throw new EPagedTableNoSuchPageException(
"Tried to retrieve page " + APage.ToString() + ", but there are only " + FTotalPages.ToString() +
" pages in the paged table!");
}
}
else
{
throw new EPagedTableNoRecordsException();
}
}
}
// (APage > FLastRetrievedPage) or (APage = 0)
else
{
if (APage >= 0)
{
// return empty DataTable
FPageDataTable.Rows.Clear();
}
else
{
throw new EPagedTableNoSuchPageException("Invalid page " + APage.ToString() + " was requested");
}
}
TProgressTracker.SetCurrentState(FProgressID, "Query executed.", 100.0m);
TProgressTracker.FinishJob(FProgressID);
return(FPageDataTable);
}
/// <summary>
/// First loads the extractTable needed. Goes throught all the Partners in the extract, If the partner already has the subscription, returns all those partners back to client. If the partner doesn't have the subscription, saves this
/// subscription to those partners.
/// </summary>
/// <returns>void</returns>
private void SubmitChangesInternal()
{
TDBTransaction SubmitChangesTransaction = null;
TSubmitChangesResult SubmissionResult = TSubmitChangesResult.scrNothingToBeSaved;
MExtractTable ExtractDT;
PSubscriptionTable SubscriptionTable;
PPartnerTable PartnerTable;
Int32 RowCounter;
Int32 PartnersInExtract;
StringCollection RequiredColumns;
StringCollection RequiredColumns2;
if (FInspectDT != null)
{
// Initialisations
FVerificationResult = new TVerificationResultCollection();
ExtractDT = new MExtractTable();
SubscriptionTable = new PSubscriptionTable();
FSubmissionDT = SubscriptionTable.Clone();
((TTypedDataTable)FSubmissionDT).InitVars();
PartnerTable = new PPartnerTable();
RequiredColumns = new StringCollection();
RequiredColumns.Add(MExtractTable.GetPartnerKeyDBName());
RequiredColumns2 = new StringCollection();
RequiredColumns2.Add(PPartnerTable.GetPartnerKeyDBName());
RequiredColumns2.Add(PPartnerTable.GetPartnerShortNameDBName());
RowCounter = 0;
// Set up asynchronous execution
TProgressTracker.SetCurrentState(FProgressID, "Checking Partners' Subscriptions...", 0.0m);
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref SubmitChangesTransaction,
ref SubmissionResult,
delegate
{
try
{
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: loading Subscriptions for ExtractID " + FExtractID.ToString() + "...");
ExtractDT = MExtractAccess.LoadViaMExtractMaster(FExtractID, RequiredColumns, SubmitChangesTransaction);
PartnersInExtract = ExtractDT.Rows.Count;
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: ExtractID has " + PartnersInExtract.ToString() + " Partners.");
// Go throught all the Partners in the extract
foreach (MExtractRow ExtractRow in ExtractDT.Rows)
{
RowCounter = RowCounter + 1;
// Calculate how much Partners we have checked. Let all Partners be a maximum of 70%.
TProgressTracker.SetCurrentState(
FProgressID,
string.Empty,
Convert.ToInt16((((double)RowCounter / (double)PartnersInExtract) * 100) * (MAX_PERCENTAGE_CHECKS / 100.0)));
TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: loadbyPrimaryKey");
SubscriptionTable = PSubscriptionAccess.LoadByPrimaryKey(
FInspectDT[0].PublicationCode,
ExtractRow.PartnerKey,
SubmitChangesTransaction);
// if the Partner does not yet have the subscription, add the subscription to this partner.
if (SubscriptionTable.Rows.Count == 0)
{
TLogging.LogAtLevel(
7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: will add Subscription to Partner with PartnerKey "
+
ExtractRow.PartnerKey.ToString());
FInspectDT[0].PartnerKey = ExtractRow.PartnerKey;
TLogging.LogAtLevel(7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: importing Row into FSubmissionDT...");
FSubmissionDT.ImportRow(FInspectDT[0]);
}
else
{
// The partner already has this Subscription: add the partner to the ResponseTable
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: won't add Subscription to Partner with PartnerKey " + ExtractRow.PartnerKey.ToString());
PartnerTable = PPartnerAccess.LoadByPrimaryKey(ExtractRow.PartnerKey, RequiredColumns2, SubmitChangesTransaction);
if (FResponseDT == null)
{
FResponseDT = PartnerTable.Clone();
}
FResponseDT.ImportRow(PartnerTable[0]);
}
}
TLogging.LogAtLevel(7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: Finished checking Partner's Subscriptions.");
if (FSubmissionDT.Rows.Count > 0)
{
// Submit the Partners with new Subscriptions to the PSubscription Table.
TProgressTracker.SetCurrentState(
FProgressID,
"Adding Subscriptions to " + FSubmissionDT.Rows.Count.ToString() + " Partners...",
MAX_PERCENTAGE_CHECKS);
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: " + FAsyncExecProgress.ProgressInformation);
PSubscriptionAccess.SubmitChanges((PSubscriptionTable)FSubmissionDT, SubmitChangesTransaction);
}
else
{
TLogging.LogAtLevel(
7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: no Subscriptions were added to Partners because all the Partners in the Extract already had this Subscription.");
}
SubmissionResult = TSubmitChangesResult.scrOK;
}
catch (Exception Exp)
{
TLogging.LogAtLevel(7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: Exception occured, Transaction will get ROLLED BACK. Exception: "
+
Exp.ToString());
SubmissionResult = TSubmitChangesResult.scrError;
FSubmitResult = SubmissionResult;
FSubmitException = Exp;
TProgressTracker.CancelJob(FProgressID);
return;
}
});
}
// if no values at response table, it needs to be created. If not creates, will raise exeption at client side.
if (FResponseDT == null)
{
FResponseDT = new DataTable();
}
TProgressTracker.FinishJob(FProgressID);
FSubmitResult = SubmissionResult;
}
