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