/// <summary>
/// Executes the query. Always call this method in a separate Thread to execute the query asynchronously!
/// </summary>
/// <param name="ASessionID">the id of the current session</param>
/// <param name="AContext">Context in which this quite generic Method gets called (e.g. 'Partner Find'). This is
/// optional but should be specified to aid in debugging as it gets logged in case Exceptions happen when the
/// DB Transaction is taken out and the Query gets executed.</param>
/// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null
/// gets passed then the Method executes DB commands with a new Database connection</param>
/// <remarks>An instance of TAsyncFindParameters with set up Properties must exist before this procedure can get
/// called!
/// </remarks>
public void ExecuteQuery(string ASessionID, string AContext = null, TDataBase ADataBase = null)
{
// need to initialize the database session
TSession.InitThread(ASessionID);
TDataBase db = DBAccess.Connect("ExecuteQuery", ADataBase);
try
{
FProgressID = Guid.NewGuid().ToString();
TProgressTracker.InitProgressTracker(FProgressID, "Executing Query...", 100.0m);
// Create SQL statement and execute it to return all records
ExecuteFullQuery(AContext, db);
}
catch (Exception exp)
{
TLogging.Log(this.GetType().FullName + ".ExecuteQuery" +
(AContext == null ? "" : " (Context: " + AContext + ")") + ": Exception occured: " + exp.ToString());
// Inform the caller that something has gone wrong...
TProgressTracker.CancelJob(FProgressID);
/*
* WE MUST 'SWALLOW' ANY EXCEPTION HERE, OTHERWISE THE WHOLE
* PETRASERVER WILL GO DOWN!!! (THIS BEHAVIOUR IS NEW WITH .NET 2.0.)
*
* --> ANY EXCEPTION THAT WOULD LEAVE THIS METHOD WOULD BE SEEN AS AN <--
* --> UNHANDLED EXCEPTION IN A THREAD, AND THE .NET/MONO RUNTIME <--
* --> WOULD BRING DOWN THE WHOLE PETRASERVER PROCESS AS A CONSEQUENCE! <--
*
*/
}
}
/// <summary>
/// Calculates the report, which is specified in the parameters table
///
/// </summary>
/// <returns>void</returns>
public void Start(System.Data.DataTable AParameters)
{
TRptUserFunctionsFinance.FlushSqlCache();
FProgressID = "ReportCalculation" + Guid.NewGuid();
TProgressTracker.InitProgressTracker(FProgressID, string.Empty, -1.0m);
FParameterList = new TParameterList();
FParameterList.LoadFromDataTable(AParameters);
FSuccess = false;
String PathStandardReports = TAppSettingsManager.GetValue("Reporting.PathStandardReports");
String PathCustomReports = TAppSettingsManager.GetValue("Reporting.PathCustomReports");
FDatacalculator = new TRptDataCalculator(DBAccess.GDBAccessObj, PathStandardReports, PathCustomReports);
// setup the logging to go to the TProgressTracker
TLogging.SetStatusBarProcedure(new TLogging.TStatusCallbackProcedure(WriteToStatusBar));
string session = TSession.GetSessionID();
ThreadStart myThreadStart = delegate {
Run(session);
};
Thread TheThread = new Thread(myThreadStart);
TheThread.Name = FProgressID;
TheThread.CurrentCulture = Thread.CurrentThread.CurrentCulture;
TheThread.Start();
}
public static Boolean CreateRemittanceAdviceAndChequeFormData(TFormLetterFinanceInfo AFormLetterFinanceInfo,
ref AccountsPayableTDSAApPaymentTable APaymentTable,
Int32 ALedgerNumber,
bool AIncludeChequeFormData,
out List <TFormData> AFormDataList)
{
AFormDataList = new List <TFormData>();
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Creating Remittance Advice"));
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Starting ..."), 10.0m);
// Print in payment number order
APaymentTable.DefaultView.Sort = string.Format("{0} ASC", AccountsPayableTDSAApPaymentTable.GetPaymentNumberDBName());
for (int i = 0; i < APaymentTable.DefaultView.Count; i++)
{
AccountsPayableTDSAApPaymentRow row = (AccountsPayableTDSAApPaymentRow)APaymentTable.DefaultView[i].Row;
int paymentNumber = row.PaymentNumber;
decimal progress = i / APaymentTable.DefaultView.Count * 100.0m;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString(string.Format("Printing payment {0} ...", paymentNumber)),
progress);
CreateFormDataInternal(ALedgerNumber, paymentNumber, AFormLetterFinanceInfo, AFormDataList,
AIncludeChequeFormData, row.ChequeNumber, row.ChequeAmountInWords);
}
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
return(true);
}
/// <summary>
/// Calculates the report, which is specified in the parameters table
///
/// </summary>
/// <returns>void</returns>
public void Start(System.Data.DataTable AParameters)
{
FProgressID = "ReportCalculation" + Guid.NewGuid();
TProgressTracker.InitProgressTracker(FProgressID, string.Empty, -1.0m);
FParameterList = new TParameterList();
FParameterList.LoadFromDataTable(AParameters);
FSuccess = false;
String PathStandardReports = TAppSettingsManager.GetValue("Reporting.PathStandardReports");
String PathCustomReports = TAppSettingsManager.GetValue("Reporting.PathCustomReports");
FDatacalculator = new TRptDataCalculator(PathStandardReports, PathCustomReports);
// setup the logging to go to the TProgressTracker
TLogging.SetStatusBarProcedure(new TLogging.TStatusCallbackProcedure(WriteToStatusBar));
string session = TSession.GetSessionID();
ThreadStart myThreadStart = delegate {
Run(session);
};
Thread TheThread = new Thread(myThreadStart);
TheThread.CurrentCulture = Thread.CurrentThread.CurrentCulture;
TheThread.Name = FProgressID + "_" + UserInfo.GetUserInfo().UserID + "__TReportGeneratorUIConnector.Start_Thread";
TLogging.LogAtLevel(7, TheThread.Name + " starting.");
TheThread.Start();
}
public static Boolean CreateRemittanceAdviceFormData(TFormLetterFinanceInfo AFormLetterFinanceInfo,
List <int> APaymentNumberList,
Int32 ALedgerNumber,
out List <TFormData> AFormDataList)
{
AFormDataList = new List <TFormData>();
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Creating Remittance Advice"));
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Starting ..."), 10.0m);
int counter = 0;
foreach (int paymentNumber in APaymentNumberList)
{
counter++;
decimal progress = counter / APaymentNumberList.Count * 100.0m;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString(string.Format("Printing payment {0} ...", paymentNumber)),
progress);
CreateFormDataInternal(ALedgerNumber, paymentNumber, AFormLetterFinanceInfo, AFormDataList, false);
}
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
return(true);
}
/// <summary>
/// run the report
/// </summary>
private void Run(string ASessionID)
{
// need to initialize the database session
TSession.InitThread(ASessionID);
IsolationLevel Level;
if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "readuncommitted")
{
// for long reports, that should not take out locks;
// the data does not need to be consistent or will most likely not be changed during the generation of the report
Level = IsolationLevel.ReadUncommitted;
}
else if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "repeatableread")
{
// for financial reports: it is important to have consistent data; e.g. for totals
Level = IsolationLevel.RepeatableRead;
}
else if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "serializable")
{
// for creating extracts: we need to write to the database
Level = IsolationLevel.Serializable;
}
else
{
// default behaviour for normal reports
Level = IsolationLevel.ReadCommitted;
}
FSuccess = false;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
try
{
DBAccess.GDBAccessObj.BeginAutoTransaction(Level, ref Transaction,
ref SubmissionOK,
delegate
{
if (FDatacalculator.GenerateResult(ref FParameterList, ref FResultList, ref FErrorMessage))
{
FSuccess = true;
SubmissionOK = true;
}
else
{
TLogging.Log(FErrorMessage);
}
});
}
catch (Exception e)
{
TLogging.Log("problem calculating report: " + e.Message);
TLogging.Log(e.StackTrace, TLoggingType.ToLogfile);
}
TProgressTracker.FinishJob(FProgressID);
}
public static string Create()
{
string session = TSession.GetSessionID();
string ReportID = "ReportCalculation" + session + Guid.NewGuid();
TProgressTracker.InitProgressTracker(ReportID, string.Empty, -1.0m);
return(ReportID);
}
/// <summary>
/// train with imported bank statements and existing gift batches
/// </summary>
public static void Train(AEpStatementTable AStatements)
{
int stmtCounter = 0;
// go through all statements in the dataset, and find gift matches for those days
foreach (AEpStatementRow stmt in AStatements.Rows)
{
TLogging.LogAtLevel(1,
"Training Statement " + stmt.StatementKey.ToString() + " " + stmt.Date.ToShortDateString() + " " + stmt.Filename);
stmtCounter++;
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
return;
}
// first stage: collect historic matches from database:
// go through each transaction of the statement,
// and see if you can find a donation on that date with the same amount from the same bank account
// store this as a match
TLogging.LogAtLevel(1, "loading data ...");
BankImportTDS MainDS = LoadData(stmt.LedgerNumber, stmt.StatementKey);
// Get all gifts at given date
TLogging.LogAtLevel(1, "get gifts by date ...");
List <int> GiftBatchNumbers;
GetGiftsByDate(stmt.LedgerNumber, MainDS, stmt.Date, stmt.BankAccountCode, out GiftBatchNumbers);
int SelectedGiftBatch = -1;
if (GiftBatchNumbers.Count > 0)
{
TLogging.LogAtLevel(1, "Found gift batches: " + GiftBatchNumbers.Count.ToString());
foreach (int i in GiftBatchNumbers)
{
TLogging.LogAtLevel(1, " " + i.ToString());
}
SelectedGiftBatch = FindGiftBatch(MainDS, stmt);
TLogging.LogAtLevel(1, " selected gift batch: " + SelectedGiftBatch.ToString());
}
if (SelectedGiftBatch == -1)
{
// cannot find the posted gift batch without any doubt
continue;
}
CreateMatches(MainDS, stmt, SelectedGiftBatch, true);
}
}
public static bool GetCurrentState(out string ACaption, out string AStatusMessage, out int APercentageDone, out bool AJobFinished)
{
TProgressTracker.TProgressState state = TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString());
ACaption = state.Caption;
AStatusMessage = state.StatusMessage;
APercentageDone = state.PercentageDone;
AJobFinished = state.JobFinished;
return(state.PercentageDone != -1 || state.StatusMessage != string.Empty);
}
private static bool TrainBankStatementsLastMonthThread(String ASessionID, Int32 AClientID, Int32 ALedgerNumber, DateTime AToday)
{
TSession.InitThread(ASessionID);
DomainManager.GClientID = AClientID;
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Training last months bank statements"),
30);
DateTime startDateThisMonth = new DateTime(AToday.Year, AToday.Month, 1);
DateTime endDateLastMonth = startDateThisMonth.AddDays(-1);
DateTime startDateLastMonth = new DateTime(endDateLastMonth.Year, endDateLastMonth.Month, 1);
// get all bank accounts
TCacheable CachePopulator = new TCacheable();
Type typeofTable;
GLSetupTDSAAccountTable accounts = (GLSetupTDSAAccountTable)CachePopulator.GetCacheableTable(
TCacheableFinanceTablesEnum.AccountList,
"",
false,
ALedgerNumber,
out typeofTable);
foreach (GLSetupTDSAAccountRow account in accounts.Rows)
{
// at OM Germany we don't have the bank account flags set
if ((!account.IsBankAccountFlagNull() && (account.BankAccountFlag == true)) ||
(!account.IsCashAccountFlagNull() && (account.CashAccountFlag == true))
)
{
string BankAccountCode = account.AccountCode;
DateTime counter = startDateLastMonth;
while (!counter.Equals(startDateThisMonth))
{
TProgressTracker.SetCurrentState(
MyClientID,
String.Format(Catalog.GetString("Training {0} {1}"), BankAccountCode, counter.ToShortDateString()),
counter.Day);
// TODO: train only one bank statement per date and bank account?
TrainBankStatement(ALedgerNumber, counter, BankAccountCode);
counter = counter.AddDays(1);
}
}
}
TProgressTracker.FinishJob(MyClientID);
return(true);
}
/// <summary>
/// Cancels an asynchronously executing query. This might take some time!
/// </summary>
/// <remarks><em>IMPORTANT:</em> This Method <em>MUST</em> be called on a separate Thread as otherwise the cancellation
/// will not work correctly (this is an implementation detail of ADO.NET!).</remarks>
/// <returns>void</returns>
public void StopQuery()
{
#if TODORemoting
TLogging.LogAtLevel(7,
(this.GetType().FullName + ".StopQuery: ProgressState = " +
Enum.GetName(typeof(TAsyncExecProgressState), FAsyncExecProgress.ProgressState)));
#endif
// TODO this cannot work, since FDataAdapter is always null
// and even if FDataAdapter was implemented, we would have a different thread, and I am not sure how to access the Database object from the other thread?
if (FDataAdapterCanceller == null)
{
return;
}
try
{
// TODORemoting
if (true /* FAsyncExecProgress.ProgressState == TAsyncExecProgressState.Aeps_Stopping */)
{
if (FDataAdapterCanceller != null)
{
// Cancel the executing query.
TLogging.LogAtLevel(7, "TPagedDataSet.StopQuery called...");
FDataAdapterCanceller.CancelFillOperation();
TLogging.LogAtLevel(7, "TPagedDataSet.StopQuery finished.");
}
}
else
{
TLogging.LogAtLevel(7, this.GetType().FullName + ".StopQuery: Query got cancelled after returning records.");
}
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
}
catch (Exception exp)
{
TLogging.Log(this.GetType().FullName + ".StopQuery: Exception occured: " + exp.ToString());
/*
* WE MUST 'SWALLOW' ANY EXCEPTION HERE, OTHERWISE THE WHOLE
* PETRASERVER WILL GO DOWN!!! (THIS BEHAVIOUR IS NEW WITH .NET 2.0.)
*
* --> ANY EXCEPTION THAT WOULD LEAVE THIS METHOD WOULD BE SEEN AS AN <--
* --> UNHANDLED EXCEPTION IN A THREAD, AND THE .NET/MONO RUNTIME <--
* --> WOULD BRING DOWN THE WHOLE PETRASERVER PROCESS AS A CONSEQUENCE! <--
*
*/
}
}
/// <summary>
/// Executes the query. Call this method in a separate Thread to execute the
/// query asynchronously!
/// </summary>
/// <remarks>An instance of TAsyncFindParameters with set up Properties must
/// exist before this procedure can get called!
/// </remarks>
/// <returns>void</returns>
public void ExecuteQuery(string ASessionID)
{
bool ownDatabaseConnection = false;
// need to initialize the database session
TSession.InitThread(ASessionID);
if (!DBAccess.GDBAccessObj.ConnectionOK)
{
// we need a separate database object for this thread, since we cannot access the session object
DBAccess.GDBAccessObj.EstablishDBConnection(TSrvSetting.RDMBSType,
TSrvSetting.PostgreSQLServer,
TSrvSetting.PostgreSQLServerPort,
TSrvSetting.PostgreSQLDatabaseName,
TSrvSetting.DBUsername,
TSrvSetting.DBPassword,
"");
ownDatabaseConnection = true;
}
try
{
FProgressID = Guid.NewGuid().ToString();
TProgressTracker.InitProgressTracker(FProgressID, "Executing Query...", 100.0m);
// Create SQL statement and execute it to return all records
ExecuteFullQuery();
}
catch (Exception exp)
{
TLogging.Log(this.GetType().FullName + ".ExecuteQuery: Exception occured: " + exp.ToString());
// Inform the caller that something has gone wrong...
TProgressTracker.CancelJob(FProgressID);
/*
* WE MUST 'SWALLOW' ANY EXCEPTION HERE, OTHERWISE THE WHOLE
* PETRASERVER WILL GO DOWN!!! (THIS BEHAVIOUR IS NEW WITH .NET 2.0.)
*
* --> ANY EXCEPTION THAT WOULD LEAVE THIS METHOD WOULD BE SEEN AS AN <--
* --> UNHANDLED EXCEPTION IN A THREAD, AND THE .NET/MONO RUNTIME <--
* --> WOULD BRING DOWN THE WHOLE PETRASERVER PROCESS AS A CONSEQUENCE! <--
*
*/
}
if (ownDatabaseConnection)
{
DBAccess.GDBAccessObj.CloseDBConnection();
}
}
public static Boolean FillFormDataFromExtract(Int64 AEventPartnerKey, Int32 AExtractId, TFormLetterInfo AFormLetterInfo,
out List <TFormData> AFormDataList)
{
Boolean ReturnValue = true;
List <TFormData> dataList = new List <TFormData>();
MExtractTable ExtractTable;
Int32 RowCounter = 0;
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Create Attendee Form Letter"));
TDBTransaction ReadTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref ReadTransaction,
delegate
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, ReadTransaction);
RowCounter = 0;
// query all rows of given extract
foreach (MExtractRow ExtractRow in ExtractTable.Rows)
{
RowCounter++;
TFormDataAttendee formDataAttendee = new TFormDataAttendee();
FillFormDataFromAttendee(AEventPartnerKey, ExtractRow.PartnerKey, formDataAttendee, AFormLetterInfo, ExtractRow.SiteKey,
ExtractRow.LocationKey);
dataList.Add(formDataAttendee);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob)
{
dataList.Clear();
ReturnValue = false;
TLogging.Log("Retrieve Conference Form Letter Data - Job cancelled");
break;
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving Attendee Data"),
(RowCounter * 100) / ExtractTable.Rows.Count);
}
});
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
AFormDataList = new List <TFormData>();
AFormDataList = dataList;
return(ReturnValue);
}
public static Boolean FillFormDataForAllAttendees(Int64 AEventPartnerKey, TFormLetterInfo AFormLetterInfo,
out List <TFormData> AFormDataList)
{
Boolean ReturnValue = true;
List <TFormData> dataList = new List <TFormData>();
PcAttendeeTable AttendeeTable;
Int32 RowCounter = 0;
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Create Attendee Form Letter for All Attendee"));
TDBTransaction ReadTransaction = new TDBTransaction();
DBAccess.ReadTransaction(
ref ReadTransaction,
delegate
{
AttendeeTable = PcAttendeeAccess.LoadViaPcConference(AEventPartnerKey, ReadTransaction);
RowCounter = 0;
// query all rows of given extract
foreach (PcAttendeeRow AttendeeRow in AttendeeTable.Rows)
{
RowCounter++;
TFormDataAttendee formDataAttendee = new TFormDataAttendee();
FillFormDataFromAttendee(AEventPartnerKey, AttendeeRow.PartnerKey, formDataAttendee, AFormLetterInfo);
dataList.Add(formDataAttendee);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob)
{
dataList.Clear();
ReturnValue = false;
TLogging.Log("Retrieve Conference Form Letter Data for All Attendees - Job cancelled");
break;
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving Attendee Data"),
(RowCounter * 100) / AttendeeTable.Rows.Count);
}
});
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
AFormDataList = new List <TFormData>();
AFormDataList = dataList;
return(ReturnValue);
}
public static Boolean FillFormDataFromExtract(Int32 AExtractId, TFormLetterInfo AFormLetterInfo,
out List <TFormData> AFormDataList)
{
Boolean ReturnValue = true;
List <TFormData> dataList = new List <TFormData>();
MExtractTable ExtractTable;
Int32 RowCounter = 0;
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Create Personnel Form Letter"));
TDBTransaction ReadTransaction = new TDBTransaction();
DBAccess.ReadTransaction(
ref ReadTransaction,
delegate
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, ReadTransaction);
RowCounter = 0;
// query all rows of given extract
foreach (MExtractRow ExtractRow in ExtractTable.Rows)
{
RowCounter++;
TFormDataPerson FormDataPerson = new TFormDataPerson();
FillFormDataFromPersonnel(ExtractRow.PartnerKey, FormDataPerson, AFormLetterInfo, ExtractRow.SiteKey, ExtractRow.LocationKey);
dataList.Add(FormDataPerson);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob)
{
dataList.Clear();
ReturnValue = false;
TLogging.Log("Retrieve Personnel Form Letter Data - Job cancelled");
break;
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving Personnel Data"),
(RowCounter * 100) / ExtractTable.Rows.Count);
}
});
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
AFormDataList = new List <TFormData>();
AFormDataList = dataList;
return(ReturnValue);
}
/// <summary>
/// Calculates the report, which is specified in the parameters table
///
/// </summary>
/// <returns>void</returns>
public void Start(System.Data.DataTable AParameters)
{
FProgressID = "ReportCalculation" + Guid.NewGuid();
TProgressTracker.InitProgressTracker(FProgressID, string.Empty, -1.0m);
// First check whether the 'globally available' DB Connection isn't busy - we must not allow the start of a
// Report Calculation when that is the case (as this would lead to a nested DB Transaction exception,
// EDBTransactionBusyException).
if (DBAccess.GDBAccessObj.Transaction != null)
{
FErrorMessage = Catalog.GetString(SharedConstants.NO_PARALLEL_EXECUTION_OF_XML_REPORTS_PREFIX +
"The OpenPetra Server is currently too busy to prepare this Report. " +
"Please retry once other running tasks (eg. a Report) are finished!");
TProgressTracker.FinishJob(FProgressID);
FSuccess = false;
// Return to the Client immediately!
return;
}
TRptUserFunctionsFinance.FlushSqlCache();
FParameterList = new TParameterList();
FParameterList.LoadFromDataTable(AParameters);
FSuccess = false;
String PathStandardReports = TAppSettingsManager.GetValue("Reporting.PathStandardReports");
String PathCustomReports = TAppSettingsManager.GetValue("Reporting.PathCustomReports");
FDatacalculator = new TRptDataCalculator(DBAccess.GDBAccessObj, PathStandardReports, PathCustomReports);
// setup the logging to go to the TProgressTracker
TLogging.SetStatusBarProcedure(new TLogging.TStatusCallbackProcedure(WriteToStatusBar));
string session = TSession.GetSessionID();
ThreadStart myThreadStart = delegate {
Run(session);
};
Thread TheThread = new Thread(myThreadStart);
TheThread.CurrentCulture = Thread.CurrentThread.CurrentCulture;
TheThread.Name = FProgressID + "_" + UserInfo.GUserInfo.UserID + "__TReportGeneratorUIConnector.Start_Thread";
TLogging.LogAtLevel(7, TheThread.Name + " starting.");
TheThread.Start();
}
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");
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="AInspectDT"></param>
public void SubmitChangesAsync(PSubscriptionTable AInspectDT)
{
Thread TheThread;
// Cleanup (might be left in a certain state from a possible earlier call)
FSubmitException = null;
FSubmissionDT = null;
FVerificationResult = null;
FResponseDT = null;
FInspectDT = null;
FProgressID = Guid.NewGuid().ToString();
TProgressTracker.InitProgressTracker(FProgressID, string.Empty, 100.0m);
FInspectDT = AInspectDT;
ThreadStart ThreadStartDelegate = new ThreadStart(SubmitChangesInternal);
TheThread = new Thread(ThreadStartDelegate);
TheThread.Name = "ExtractsAddSubscriptionsSubmitChanges" + Guid.NewGuid().ToString();
TheThread.Start();
TLogging.LogAtLevel(6, "TExtractsAddSubscriptionsUIConnector.SubmitChangesAsync thread started.");
}
/// <summary>
/// for displaying the progress
/// </summary>
/// <returns>void</returns>
private void WriteToStatusBar(String s)
{
TProgressTracker.SetCurrentState(FProgressID, s, -1.0m);
}
/// <summary>
/// run the report
/// </summary>
private void Run(string ASessionID)
{
// need to initialize the database session
TSession.InitThread(ASessionID);
IsolationLevel Level;
if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "readuncommitted")
{
// for long reports, that should not take out locks;
// the data does not need to be consistent or will most likely not be changed during the generation of the report
Level = IsolationLevel.ReadUncommitted;
}
else if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "repeatableread")
{
// for financial reports: it is important to have consistent data; e.g. for totals
Level = IsolationLevel.RepeatableRead;
}
else if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "serializable")
{
// for creating extracts: we need to write to the database
Level = IsolationLevel.Serializable;
}
else
{
// default behaviour for normal reports
Level = IsolationLevel.ReadCommitted;
}
FSuccess = false;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
try
{
DBAccess.GDBAccessObj.BeginAutoTransaction(Level, ref Transaction,
ref SubmissionOK,
delegate
{
if (FDatacalculator.GenerateResult(ref FParameterList, ref FResultList, ref FErrorMessage, ref FException))
{
FSuccess = true;
SubmissionOK = true;
}
else
{
TLogging.Log(FErrorMessage);
}
});
}
catch (Exception Exc)
{
TLogging.Log("Problem calculating report: " + Exc.ToString());
TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile);
FSuccess = false;
FErrorMessage = Exc.Message;
FException = Exc;
}
if (TDBExceptionHelper.IsTransactionSerialisationException(FException))
{
// do nothing - we want this exception to bubble up
}
else if (FException is Exception && FException.InnerException is EOPDBException)
{
EOPDBException DbExc = (EOPDBException)FException.InnerException;
if (DbExc.InnerException is Exception)
{
if (DbExc.InnerException is PostgresException)
{
PostgresException PgExc = (PostgresException)DbExc.InnerException;
if (PgExc.SqlState == "57014") // SQL statement timeout problem
{
FErrorMessage = Catalog.GetString(
"Error - Database took too long to respond. Try different parameters to return fewer results.");
}
}
else
{
FErrorMessage = DbExc.InnerException.Message;
}
FException = null;
}
}
TProgressTracker.FinishJob(FProgressID);
}
/// <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);
}
public static bool CancelJob()
{
return(TProgressTracker.CancelJob(DomainManager.GClientID.ToString()));
}
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 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);
}
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);
}
public static Int32 CreateGiftBatch(
Int32 ALedgerNumber,
Int32 AStatementKey,
Int32 AGiftBatchNumber,
out TVerificationResultCollection AVerificationResult)
{
BankImportTDS MainDS = GetBankStatementTransactionsAndMatches(AStatementKey, ALedgerNumber);
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Creating gift batch"),
MainDS.AEpTransaction.DefaultView.Count + 10);
AVerificationResult = new TVerificationResultCollection();
MainDS.AEpTransaction.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpTransactionTable.GetStatementKeyDBName(),
AStatementKey);
MainDS.AEpStatement.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
AStatementKey);
AEpStatementRow stmt = (AEpStatementRow)MainDS.AEpStatement.DefaultView[0].Row;
// TODO: optional: use the preselected gift batch, AGiftBatchNumber
Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber;
DateTime BatchDateEffective = stmt.Date;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
if (!TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref BatchDateEffective, out DateEffectiveYearNumber,
out DateEffectivePeriodNumber,
Transaction, true))
{
// just use the latest possible date
string msg =
String.Format(Catalog.GetString("Date {0} is not in an open period of the ledger, using date {1} instead for the gift batch."),
stmt.Date.ToShortDateString(),
BatchDateEffective.ToShortDateString());
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Info));
}
ACostCentreAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
MainDS.AEpMatch.DefaultView.Sort =
AEpMatchTable.GetActionDBName() + ", " +
AEpMatchTable.GetMatchTextDBName();
if (MainDS.AEpTransaction.DefaultView.Count == 0)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Creating Gift Batch"),
String.Format(Catalog.GetString("There are no transactions for statement #{0}."), AStatementKey),
TResultSeverity.Resv_Info));
return(-1);
}
foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView)
{
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] {
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT,
transactionRow.MatchText
});
if (matches.Length > 0)
{
AEpMatchRow match = (AEpMatchRow)matches[0].Row;
if (match.IsDonorKeyNull() || (match.DonorKey == 0))
{
string msg =
String.Format(Catalog.GetString("Cannot create a gift for transaction {0} since there is no valid donor."),
transactionRow.Description);
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Critical));
DBAccess.GDBAccessObj.RollbackTransaction();
return(-1);
}
}
}
string MatchedGiftReference = stmt.Filename;
if (!stmt.IsBankAccountKeyNull())
{
string sqlGetBankSortCode =
"SELECT bank.p_branch_code_c " +
"FROM PUB_p_banking_details details, PUB_p_bank bank " +
"WHERE details.p_banking_details_key_i = ?" +
"AND details.p_bank_key_n = bank.p_partner_key_n";
OdbcParameter param = new OdbcParameter("detailkey", OdbcType.Int);
param.Value = stmt.BankAccountKey;
PBankTable bankTable = new PBankTable();
DBAccess.GDBAccessObj.SelectDT(bankTable, sqlGetBankSortCode, Transaction, new OdbcParameter[] { param }, 0, 0);
MatchedGiftReference = bankTable[0].BranchCode + " " + stmt.Date.Day.ToString();
}
DBAccess.GDBAccessObj.RollbackTransaction();
GiftBatchTDS GiftDS = TGiftTransactionWebConnector.CreateAGiftBatch(
ALedgerNumber,
BatchDateEffective,
String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString()));
AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0];
giftbatchRow.BankAccountCode = stmt.BankAccountCode;
decimal HashTotal = 0.0M;
MainDS.AEpTransaction.DefaultView.Sort =
AEpTransactionTable.GetNumberOnPaperStatementDBName();
MainDS.AEpMatch.DefaultView.RowFilter = String.Empty;
MainDS.AEpMatch.DefaultView.Sort =
AEpMatchTable.GetActionDBName() + ", " +
AEpMatchTable.GetMatchTextDBName();
int counter = 5;
foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Preparing the gifts"),
counter++);
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] {
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT,
transactionRow.MatchText
});
if (matches.Length > 0)
{
AEpMatchRow match = (AEpMatchRow)matches[0].Row;
AGiftRow gift = GiftDS.AGift.NewRowTyped();
gift.LedgerNumber = giftbatchRow.LedgerNumber;
gift.BatchNumber = giftbatchRow.BatchNumber;
gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1;
gift.DonorKey = match.DonorKey;
gift.DateEntered = transactionRow.DateEffective;
gift.Reference = MatchedGiftReference;
GiftDS.AGift.Rows.Add(gift);
giftbatchRow.LastGiftNumber++;
foreach (DataRowView r in matches)
{
match = (AEpMatchRow)r.Row;
AGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped();
detail.LedgerNumber = gift.LedgerNumber;
detail.BatchNumber = gift.BatchNumber;
detail.GiftTransactionNumber = gift.GiftTransactionNumber;
detail.DetailNumber = gift.LastDetailNumber + 1;
gift.LastDetailNumber++;
detail.GiftTransactionAmount = match.GiftTransactionAmount;
detail.GiftAmount = match.GiftTransactionAmount;
HashTotal += match.GiftTransactionAmount;
detail.MotivationGroupCode = match.MotivationGroupCode;
detail.MotivationDetailCode = match.MotivationDetailCode;
// do not use the description in comment one, because that could show up on the gift receipt???
// detail.GiftCommentOne = transactionRow.Description;
detail.CommentOneType = MFinanceConstants.GIFT_COMMENT_TYPE_BOTH;
detail.CostCentreCode = match.CostCentreCode;
detail.RecipientKey = match.RecipientKey;
detail.RecipientLedgerNumber = match.RecipientLedgerNumber;
AMotivationDetailRow motivation = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find(
new object[] { ALedgerNumber, detail.MotivationGroupCode, detail.MotivationDetailCode });
if (motivation == null)
{
AVerificationResult.Add(new TVerificationResult(
String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description),
String.Format(Catalog.GetString("Cannot find motivation group '{0}' and motivation detail '{1}'"),
detail.MotivationGroupCode, detail.MotivationDetailCode),
TResultSeverity.Resv_Critical));
}
if (detail.CostCentreCode.Length == 0)
{
// try to retrieve the current costcentre for this recipient
if (detail.RecipientKey != 0)
{
detail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(detail.RecipientKey);
detail.CostCentreCode = TGiftTransactionWebConnector.IdentifyPartnerCostCentre(detail.LedgerNumber,
detail.RecipientLedgerNumber);
}
else
{
if (motivation != null)
{
detail.CostCentreCode = motivation.CostCentreCode;
}
}
}
// check for active cost centre
ACostCentreRow costcentre = (ACostCentreRow)MainDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, detail.CostCentreCode });
if ((costcentre == null) || !costcentre.CostCentreActiveFlag)
{
AVerificationResult.Add(new TVerificationResult(
String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description),
Catalog.GetString("Invalid or inactive cost centre"),
TResultSeverity.Resv_Critical));
}
GiftDS.AGiftDetail.Rows.Add(detail);
}
}
}
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Submit to database"),
counter++);
if (AVerificationResult.HasCriticalErrors)
{
return(-1);
}
giftbatchRow.HashTotal = HashTotal;
giftbatchRow.BatchTotal = HashTotal;
// do not overwrite the parameter, because there might be the hint for a different gift batch date
TVerificationResultCollection VerificationResultSubmitChanges;
TSubmitChangesResult result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS,
out VerificationResultSubmitChanges);
TProgressTracker.FinishJob(MyClientID);
if (result == TSubmitChangesResult.scrOK)
{
return(giftbatchRow.BatchNumber);
}
return(-1);
}
public static bool Reset()
{
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), string.Empty, 100.0m);
return(true);
}
public static TProgressState GetProgress(string AReportID)
{
return(TProgressTracker.GetCurrentState(AReportID));
}
