/// <summary>
/// create a new batch with a consecutive batch number in the ledger
/// for call inside a server function
/// for performance reasons submitting (save the data in the database) is done later (not here)
/// </summary>
/// <param name="MainDS"></param>
/// <param name="Transaction"></param>
/// <param name="LedgerTable"></param>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEffective"></param>
/// <param name="AForceEffectiveDateToFit"></param>
/// <returns>the new gift batch row</returns>
public static AGiftBatchRow CreateANewGiftBatchRow(ref GiftBatchTDS MainDS,
ref TDBTransaction Transaction,
ref ALedgerTable LedgerTable,
Int32 ALedgerNumber,
DateTime ADateEffective,
bool AForceEffectiveDateToFit = true)
{
AGiftBatchRow NewRow = MainDS.AGiftBatch.NewRowTyped(true);
NewRow.LedgerNumber = ALedgerNumber;
LedgerTable[0].LastGiftBatchNumber++;
NewRow.BatchNumber = LedgerTable[0].LastGiftBatchNumber;
Int32 BatchYear, BatchPeriod;
// if DateEffective is outside the range of open periods, use the most fitting date
TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber,
ref ADateEffective,
out BatchYear,
out BatchPeriod,
Transaction,
AForceEffectiveDateToFit);
NewRow.BatchYear = BatchYear;
NewRow.BatchPeriod = BatchPeriod;
NewRow.GlEffectiveDate = ADateEffective;
NewRow.ExchangeRateToBase = 1.0M;
NewRow.BatchDescription = "PLEASE ENTER A DESCRIPTION";
NewRow.BankAccountCode = TLedgerInfo.GetDefaultBankAccount(ALedgerNumber);
NewRow.BankCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber);
NewRow.CurrencyCode = LedgerTable[0].BaseCurrency;
MainDS.AGiftBatch.Rows.Add(NewRow);
return NewRow;
}
private DataTable GetFieldApplicationTypeListTable(TDBTransaction AReadTransaction, string ATableName)
{
PtApplicationTypeRow template = new PtApplicationTypeTable().NewRowTyped(false);
template.AppFormType = "LONG FORM";
return PtApplicationTypeAccess.LoadUsingTemplate(template, AReadTransaction);
}
private DataTable GetAccountingPeriodListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName)
{
StringCollection FieldList = new StringCollection();
FieldList.Add(AAccountingPeriodTable.GetLedgerNumberDBName());
FieldList.Add(AAccountingPeriodTable.GetAccountingPeriodNumberDBName());
FieldList.Add(AAccountingPeriodTable.GetAccountingPeriodDescDBName());
FieldList.Add(AAccountingPeriodTable.GetPeriodStartDateDBName());
FieldList.Add(AAccountingPeriodTable.GetPeriodEndDateDBName());
return AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, FieldList, AReadTransaction);
}
private static void LoadAnalysisAttributes(AccountsPayableTDS AMainDS, Int32 ALedgerNumber, TDBTransaction ATransaction)
{
{ // Load via template...
AAnalysisAttributeRow TemplateRow = AMainDS.AAnalysisAttribute.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.Active = true;
AAnalysisAttributeAccess.LoadUsingTemplate(AMainDS, TemplateRow, ATransaction);
}
AFreeformAnalysisAccess.LoadViaALedger(AMainDS, ALedgerNumber, ATransaction);
}
/// find the current best address for the partner
public static bool GetBestAddress(Int64 APartnerKey,
out PLocationTable AAddress,
out string ACountryNameLocal,
TDBTransaction ATransaction)
{
AAddress = null;
ACountryNameLocal = "";
DataSet PartnerLocationsDS = new DataSet();
PartnerLocationsDS.Tables.Add(new PPartnerLocationTable());
PartnerLocationsDS.Tables.Add(new PCountryTable());
DataTable PartnerLocationTable = PartnerLocationsDS.Tables[PPartnerLocationTable.GetTableName()];
PCountryTable CountryTable = (PCountryTable)PartnerLocationsDS.Tables[PCountryTable.GetTableName()];
CountryTable.DefaultView.Sort = PCountryTable.GetCountryCodeDBName();
// add special column BestAddress and Icon
PartnerLocationTable.Columns.Add(new System.Data.DataColumn("BestAddress", typeof(Boolean)));
PartnerLocationTable.Columns.Add(new System.Data.DataColumn("Icon", typeof(Int32)));
// find all locations of the partner, put it into a dataset
PPartnerLocationAccess.LoadViaPPartner(PartnerLocationsDS, APartnerKey, ATransaction);
Ict.Petra.Shared.MPartner.Calculations.DeterminePartnerLocationsDateStatus(PartnerLocationsDS);
Ict.Petra.Shared.MPartner.Calculations.DetermineBestAddress(PartnerLocationsDS);
foreach (PPartnerLocationRow row in PartnerLocationTable.Rows)
{
// find the row with BestAddress = 1
if (Convert.ToInt32(row["BestAddress"]) == 1)
{
// we also want the post address, need to load the p_location table:
AAddress = PLocationAccess.LoadByPrimaryKey(row.SiteKey, row.LocationKey, ATransaction);
// watch out for empty country codes
if (AAddress[0].CountryCode.Trim().Length > 0)
{
if (CountryTable.DefaultView.Find(AAddress[0].CountryCode) == -1)
{
CountryTable.Merge(PCountryAccess.LoadByPrimaryKey(AAddress[0].CountryCode, ATransaction));
}
ACountryNameLocal = CountryTable[CountryTable.DefaultView.Find(AAddress[0].CountryCode)].CountryNameLocal;
}
break;
}
}
return AAddress != null;
}
private DataTable GetUserListTable(TDBTransaction AReadTransaction, string ATableName)
{
DataColumn LastAndFirstNameColumn;
DataTable TmpTable = SUserAccess.LoadAll(AReadTransaction);
// add column to display a combination of last and first name
LastAndFirstNameColumn = new DataColumn();
LastAndFirstNameColumn.DataType = System.Type.GetType("System.String");
LastAndFirstNameColumn.ColumnName = MSysManConstants.USER_LAST_AND_FIRST_NAME_COLUMNNAME;
LastAndFirstNameColumn.Expression = string.Format("{0} + ' ' + {1}", SUserTable.GetFirstNameDBName(), SUserTable.GetLastNameDBName());
TmpTable.Columns.Add(LastAndFirstNameColumn);
return TmpTable;
}
/// <summary>
/// Loads all available Ledgers and their names into a DataTable
///
/// </summary>
/// <returns>void</returns>
public static DataTable GetData(String ATableName, TDBTransaction AReadTransaction)
{
return DBAccess.GDBAccessObj.SelectDT(
String.Format(
"SELECT {0} AS LedgerNumber, {1} AS LedgerName FROM pub_{2}, pub_{3} " +
"WHERE pub_{2}.{4} = pub_{3}.{4} " + "AND pub_{2}.{5} = 1",
ALedgerTable.GetLedgerNumberDBName(),
PPartnerTable.GetPartnerShortNameDBName(),
ALedgerTable.GetTableDBName(),
PPartnerTable.GetTableDBName(),
PPartnerTable.GetPartnerKeyDBName(),
ALedgerTable.GetLedgerStatusDBName()),
ATableName,
AReadTransaction);
}
private DataTable GetOutreachListTable(TDBTransaction AReadTransaction, string ATableName)
{
DataTable Table;
DataColumn[] Key = new DataColumn[1];
// Used eg. Select Event Dialog
Table = DBAccess.GDBAccessObj.SelectDT(
"SELECT DISTINCT " +
PPartnerTable.GetPartnerShortNameDBName() +
", " + PPartnerTable.GetPartnerClassDBName() +
", " + PUnitTable.GetOutreachCodeDBName() +
", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() +
", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
", " + PUnitTable.GetUnitTypeCodeDBName() +
" FROM PUB." + PPartnerTable.GetTableDBName() +
", PUB." + PUnitTable.GetTableDBName() +
", PUB." + PLocationTable.GetTableDBName() +
", PUB." + PPartnerLocationTable.GetTableDBName() +
", PUB." + PCountryTable.GetTableDBName() +
" WHERE " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " +
PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' AND " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " +
PUnitTable.GetOutreachCodeDBName() + " <> '' AND (" +
PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONF%' AND " +
PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONG%')"
,
ATableName, AReadTransaction);
Key[0] = Table.Columns[PPartnerTable.GetPartnerKeyDBName()];
Table.PrimaryKey = Key;
return Table;
}
/// <summary>
/// use the permissions of the user to get all offices that this user has permissions for
/// </summary>
/// <returns></returns>
private static List <Int64>GetRegistrationOfficeKeysOfUser(TDBTransaction ATransaction)
{
List <Int64>AllowedRegistrationOffices = new List <long>();
// get all offices that have registrations for this event
DataTable offices = DBAccess.GDBAccessObj.SelectDT(
String.Format("SELECT DISTINCT {0} FROM PUB_{1}",
PmShortTermApplicationTable.GetRegistrationOfficeDBName(),
PmShortTermApplicationTable.GetTableDBName()),
"registrationoffice", ATransaction);
// if there are no REG-... module permissions for anyone, allow all offices? this would help with a base database for testing?
Int32 CountRegModules =
Convert.ToInt32(DBAccess.GDBAccessObj.ExecuteScalar("SELECT COUNT(*) FROM " + SModuleTable.GetTableDBName() + " WHERE " +
SModuleTable.GetModuleIdDBName() + " LIKE 'REG-%'", ATransaction));
foreach (DataRow officeRow in offices.Rows)
{
Int64 RegistrationOffice = Convert.ToInt64(officeRow[0]);
try
{
if ((CountRegModules == 0) || TModuleAccessManager.CheckUserModulePermissions(String.Format("REG-{0:10}",
StringHelper.PartnerKeyToStr(RegistrationOffice))))
{
AllowedRegistrationOffices.Add(RegistrationOffice);
}
}
catch (ESecurityModuleAccessDeniedException)
{
// no permissions for this registration office
}
}
// the organizer has access to all attendees
if (AllowedRegistrationOffices.Count > MINIMUM_OFFICES_TO_BECOME_ORGANIZER)
{
AllowedRegistrationOffices = new List <long>();
foreach (DataRow officeRow in offices.Rows)
{
Int64 RegistrationOffice = Convert.ToInt64(officeRow[0]);
AllowedRegistrationOffices.Add(RegistrationOffice);
}
}
return AllowedRegistrationOffices;
}
/// <summary>
/// This method needs to be implemented by extracts that can't follow the default processing with just
/// one query.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
{
AExtractId = -1;
if (AParameters.Get("param_sending_receiving").ToString() == "ReceivingField")
{
return ProcessReceivingFields(AParameters, ATransaction, out AExtractId);
}
else if (AParameters.Get("param_sending_receiving").ToString() == "SendingField")
{
return ProcessSendingFields(AParameters, ATransaction, out AExtractId);
}
else
{
return false;
}
}
/// <summary>
/// Loads all costcentres that are linked to a partner, with the partner key and partner class
/// </summary>
public static DataTable GetData(String ATableName, Int32 ALedgerNumber, TDBTransaction AReadTransaction)
{
return DBAccess.GDBAccessObj.SelectDT(
String.Format(
"SELECT {0}, {3}.{1}, {3}.{5}, {2}, {8} FROM pub_{3} " +
"LEFT OUTER JOIN PUB_{7} ON PUB_{7}.{1} = PUB_{3}.{1}, pub_{4} " +
"WHERE pub_{3}.{1} = pub_{4}.{1} AND pub_{3}.{5} = {6}",
AValidLedgerNumberTable.GetCostCentreCodeDBName(),
AValidLedgerNumberTable.GetPartnerKeyDBName(),
PPartnerTable.GetPartnerClassDBName(),
AValidLedgerNumberTable.GetTableDBName(),
PPartnerTable.GetTableDBName(),
AValidLedgerNumberTable.GetLedgerNumberDBName(),
ALedgerNumber.ToString(),
PUnitTable.GetTableDBName(),
PUnitTable.GetUnitTypeCodeDBName()),
ATableName,
AReadTransaction);
}
private DataTable GetFoundationOwnerListTable(TDBTransaction AReadTransaction, string ATableName)
{
// Used in Foundation Details screen.
SUserTable TmpUserTable = new SUserTable();
TmpUserTable = (SUserTable)DBAccess.GDBAccessObj.SelectDT(TmpUserTable, "SELECT " + SUserTable.GetPartnerKeyDBName() + ',' +
SUserTable.GetUserIdDBName() + ',' +
SUserTable.GetFirstNameDBName() + ',' +
SUserTable.GetLastNameDBName() + ' ' +
"FROM PUB_" + SUserTable.GetTableDBName() + ' ' +
"WHERE " + SUserTable.GetPartnerKeyDBName() + " <> 0 " +
"AND " + SUserTable.GetUserIdDBName() +
" IN (SELECT " + SUserModuleAccessPermissionTable.GetUserIdDBName() + ' ' +
"FROM PUB_" + SUserModuleAccessPermissionTable.GetTableDBName() + ' ' +
"WHERE " + SUserModuleAccessPermissionTable.GetModuleIdDBName() +
" = 'DEVUSER')" + "AND " + SUserTable.GetRetiredDBName() +
" = FALSE", AReadTransaction, null, -1, -1);
SUserRow EmptyDR = TmpUserTable.NewRowTyped(false);
EmptyDR.PartnerKey = 0;
EmptyDR.UserId = "";
TmpUserTable.Rows.InsertAt(EmptyDR, 0);
return TmpUserTable;
}
/// <summary>
/// Passes Partner Address data as a DataSet to the caller. Loads all available
/// Addresses for the Partner.
///
/// </summary>
/// <param name="ADataSet">DataSet that holds a DataSet with a DataTable for the
/// Person</param>
/// <param name="APartnerKey">PartnerKey of the Partner for which Address data is to be
/// loaded</param>
/// <param name="AReadTransaction">Transaction for the SELECT statement
/// </param>
/// <returns>void</returns>
public static void LoadAll(DataSet ADataSet, Int64 APartnerKey, TDBTransaction AReadTransaction)
{
PLocationTable LocationDT;
PPartnerLocationTable PartnerLocationDT;
PPartnerLocationAccess.LoadViaPPartner(ADataSet, APartnerKey, AReadTransaction);
PLocationAccess.LoadViaPPartner(ADataSet, APartnerKey, AReadTransaction);
// Apply security
LocationDT = (PLocationTable)ADataSet.Tables[PLocationTable.GetTableName()];
PartnerLocationDT = (PPartnerLocationTable)ADataSet.Tables[PPartnerLocationTable.GetTableName()];
ApplySecurity(ref PartnerLocationDT, ref LocationDT);
// make sure that location specific fields in PartnerLocationDT get initialized
if (ADataSet.GetType() == typeof(PartnerEditTDS))
{
PartnerCodeHelper.SyncPartnerEditTDSPartnerLocation(LocationDT, (PartnerEditTDSPPartnerLocationTable)PartnerLocationDT);
}
if (TLogging.DL >= 9)
{
DebugLoadedDataset(ADataSet);
}
}
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 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 = new TDBTransaction();
bool submissionOK = false;
DBAccess.WriteTransaction(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>
/// Retrieves PartnerLocation information and the rest of the PartnerInfo data, but not the
/// 'Head' data.
/// </summary>
/// <param name="APartnerKey">PartnerKey of Partner to find the PartnerInfo data for</param>
/// <param name="ALocationKey" >Location Key of the Location that the information should be
/// retrieved for.</param>
/// <param name="APartnerInfoDS">Typed DataSet that contains the requested data.</param>
/// <param name="AReadTransaction" >Open DB Transaction.</param>
/// <returns>True if Partner exists, otherwise false.</returns>
public static bool PartnerLocationAndRestOnly(Int64 APartnerKey, TLocationPK ALocationKey,
ref PartnerInfoTDS APartnerInfoDS, TDBTransaction AReadTransaction)
{
return(PartnerLocationInternal(APartnerKey, ALocationKey, ref APartnerInfoDS, true, AReadTransaction));
}
private DataTable GetDataLabelsForPartnerClassesListTable(TDBTransaction AReadTransaction, string ATableName)
{
const string PARTNERCLASSCOL = "PartnerClass";
const string DLAVAILCOL = "DataLabelsAvailable";
DataTable TmpTable;
DataRow NewDR;
TOfficeSpecificDataLabelsUIConnector OfficeSpecificDataLabelsUIConnector;
// Create our custom Cacheable DataTable on-the-fly
TmpTable = new DataTable(ATableName);
DataColumn PKColumn = new DataColumn(PARTNERCLASSCOL, System.Type.GetType("System.String"));
TmpTable.Columns.Add(PKColumn);
TmpTable.Columns.Add(new DataColumn(DLAVAILCOL, System.Type.GetType("System.Boolean")));
TmpTable.PrimaryKey = new DataColumn[] {
PKColumn
};
/*
* Create an Instance of TOfficeSpecificDataLabelsUIConnector - PartnerKey and DataLabelUse are not important here
* because we only call Method 'CountLabelUse', which doesn't rely on any of them.
*/
OfficeSpecificDataLabelsUIConnector = new TOfficeSpecificDataLabelsUIConnector(0,
TOfficeSpecificDataLabelUseEnum.Family);
// DataLabels available for PERSONs?
NewDR = TmpTable.NewRow();
NewDR[PARTNERCLASSCOL] = SharedTypes.PartnerClassEnumToString(TPartnerClass.PERSON);
NewDR[DLAVAILCOL] =
(OfficeSpecificDataLabelsUIConnector.CountLabelUse(NewDR[PARTNERCLASSCOL].ToString(), AReadTransaction) != 0);
TmpTable.Rows.Add(NewDR);
// DataLabels available for FAMILYs?
NewDR = TmpTable.NewRow();
NewDR[PARTNERCLASSCOL] = SharedTypes.PartnerClassEnumToString(TPartnerClass.FAMILY);
NewDR[DLAVAILCOL] =
(OfficeSpecificDataLabelsUIConnector.CountLabelUse(NewDR[PARTNERCLASSCOL].ToString(), AReadTransaction) != 0);
TmpTable.Rows.Add(NewDR);
// DataLabels available for CHURCHes?
NewDR = TmpTable.NewRow();
NewDR[PARTNERCLASSCOL] = SharedTypes.PartnerClassEnumToString(TPartnerClass.CHURCH);
NewDR[DLAVAILCOL] =
(OfficeSpecificDataLabelsUIConnector.CountLabelUse(NewDR[PARTNERCLASSCOL].ToString(), AReadTransaction) != 0);
TmpTable.Rows.Add(NewDR);
// DataLabels available for ORGANISATIONs?
NewDR = TmpTable.NewRow();
NewDR[PARTNERCLASSCOL] = SharedTypes.PartnerClassEnumToString(TPartnerClass.ORGANISATION);
NewDR[DLAVAILCOL] =
(OfficeSpecificDataLabelsUIConnector.CountLabelUse(NewDR[PARTNERCLASSCOL].ToString(), AReadTransaction) != 0);
TmpTable.Rows.Add(NewDR);
// DataLabels available for UNITs?
NewDR = TmpTable.NewRow();
NewDR[PARTNERCLASSCOL] = SharedTypes.PartnerClassEnumToString(TPartnerClass.UNIT);
NewDR[DLAVAILCOL] =
(OfficeSpecificDataLabelsUIConnector.CountLabelUse(NewDR[PARTNERCLASSCOL].ToString(), AReadTransaction) != 0);
TmpTable.Rows.Add(NewDR);
// DataLabels available for BANKs?
NewDR = TmpTable.NewRow();
NewDR[PARTNERCLASSCOL] = SharedTypes.PartnerClassEnumToString(TPartnerClass.BANK);
NewDR[DLAVAILCOL] =
(OfficeSpecificDataLabelsUIConnector.CountLabelUse(NewDR[PARTNERCLASSCOL].ToString(), AReadTransaction) != 0);
TmpTable.Rows.Add(NewDR);
// DataLabels available for VENUEs?
NewDR = TmpTable.NewRow();
NewDR[PARTNERCLASSCOL] = SharedTypes.PartnerClassEnumToString(TPartnerClass.VENUE);
NewDR[DLAVAILCOL] =
(OfficeSpecificDataLabelsUIConnector.CountLabelUse(NewDR[PARTNERCLASSCOL].ToString(), AReadTransaction) != 0);
TmpTable.Rows.Add(NewDR);
return(TmpTable);
}
/// <summary>
/// Set the session id for this current thread.
/// Each request has its own thread.
/// Threads can be reused for different users.
/// </summary>
public static void InitThread(string AThreadDescription, string AConfigFileName, string ASessionID = null)
{
TLogWriter.ResetStaticVariables();
TLogging.ResetStaticVariables();
new TAppSettingsManager(AConfigFileName);
new TLogging(TSrvSetting.ServerLogFile);
TLogging.DebugLevel = TAppSettingsManager.GetInt16("Server.DebugLevel", 0);
string httprequest = "";
if ((HttpContext.Current != null) && (HttpContext.Current.Request != null))
{
httprequest = " for path " + HttpContext.Current.Request.PathInfo;
}
TLogging.LogAtLevel(4, AThreadDescription + ": Running InitThread for thread id " + Thread.CurrentThread.ManagedThreadId.ToString() + httprequest);
FSessionID = ASessionID;
FSessionValues = null;
string sessionID;
if (ASessionID == null)
{
sessionID = FindSessionID();
}
else
{
sessionID = ASessionID;
}
// avoid dead lock on parallel logins
FDeleteSessionMutex.WaitOne();
TDataBase db = ConnectDB("SessionInitThread");
TDBTransaction t = new TDBTransaction();
bool SubmissionOK = false;
bool newSession = false;
db.WriteTransaction(ref t,
ref SubmissionOK,
delegate
{
// get the session ID, or start a new session
// load the session values from the database
// update the session last access in the database
// clean old sessions
newSession = InitSession(sessionID, t);
SubmissionOK = true;
});
if (newSession)
{
// use a separate transaction to clean old sessions
db.WriteTransaction(ref t,
ref SubmissionOK,
delegate
{
CleanOldSessions(t);
SubmissionOK = true;
});
}
db.CloseDBConnection();
FDeleteSessionMutex.ReleaseMutex();
}
public static DataTable GetEventUnits()
{
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
DataColumn[] Key = new DataColumn[3];
DataTable Events = new DataTable();
if (TLogging.DL >= 9)
{
Console.WriteLine("GetEventUnits called!");
}
TDBTransaction Transaction = new TDBTransaction();
DBAccess.ReadTransaction(
ref Transaction,
delegate
{
string SqlStmt =
"SELECT DISTINCT " +
PPartnerTable.GetPartnerShortNameDBName() +
", " + PPartnerTable.GetPartnerClassDBName() +
", " + PUnitTable.GetOutreachCodeDBName() +
", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() +
", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
", " + PUnitTable.GetUnitTypeCodeDBName() +
", " + PUnitTable.GetUnitNameDBName() +
" FROM pub_" + PPartnerTable.GetTableDBName() +
", pub_" + PUnitTable.GetTableDBName() +
", pub_" + PLocationTable.GetTableDBName() +
", pub_" + PPartnerLocationTable.GetTableDBName() +
", pub_" + PCountryTable.GetTableDBName() +
" WHERE " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " +
PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " +
PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' ";
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName();
Events = Transaction.DataBaseObj.SelectDT(SqlStmt, "events",
Transaction, SqlParameterList.ToArray());
Key[0] = Events.Columns[PPartnerTable.GetPartnerKeyDBName()];
Key[1] = Events.Columns[PPartnerLocationTable.GetSiteKeyDBName()];
Key[2] = Events.Columns[PPartnerLocationTable.GetLocationKeyDBName()];
Events.PrimaryKey = Key;
});
return(Events);
}
public static bool IsContactLogAssociatedWithMoreThanOnePartner(long AContactLogId, TDBTransaction ATransaction)
{
return(PPartnerContactAccess.CountViaPContactLog(AContactLogId, ATransaction) > 1);
}
/// <summary>
/// Retrieves Location and PartnerLocation information and the rest of the PartnerInfo data,
/// but not the 'Head' data.
/// </summary>
/// <param name="APartnerKey">PartnerKey of Partner to find the PartnerInfo data for</param>
/// <param name="ALocationKey" >Location Key of the Location that the information should be
/// retrieved for.</param>
/// <param name="APartnerInfoDS">Typed DataSet that contains the requested data.</param>
/// <param name="AReadTransaction" >Open DB Transaction.</param>
/// <returns>True if Partner exists, otherwise false.</returns>
public static bool LocationPartnerLocationOnly(Int64 APartnerKey, TLocationPK ALocationKey,
ref PartnerInfoTDS APartnerInfoDS, TDBTransaction AReadTransaction)
{
return(LocationPartnerLocationInternal(APartnerKey, ALocationKey, ref APartnerInfoDS, false,
AReadTransaction));
}
public static bool SavePartner(PartnerEditTDS AMainDS,
List <string> ASubscriptions,
List <string> APartnerTypes,
List <string> AChanges,
bool ASendMail,
string ADefaultEmailAddress,
string ADefaultPhoneMobile,
string ADefaultPhoneLandline,
out TVerificationResultCollection AVerificationResult)
{
List <string> Dummy1, Dummy2;
string Dummy3, Dummy4, Dummy5;
PartnerEditTDS SaveDS;
AVerificationResult = new TVerificationResultCollection();
if (AMainDS.PPartner[0].ModificationId == DateTime.MinValue)
{
// this is a new partner
SaveDS = AMainDS;
if (SaveDS.PPartner[0].PartnerKey == -1)
{
SaveDS.PPartner[0].PartnerKey = NewPartnerKey();
}
if (SaveDS.PFamily.Count > 0)
{
SaveDS.PFamily[0].PartnerKey = SaveDS.PPartner[0].PartnerKey;
}
else if (SaveDS.PPerson.Count > 0)
{
SaveDS.PPerson[0].PartnerKey = SaveDS.PPartner[0].PartnerKey;
}
else if (SaveDS.PChurch.Count > 0)
{
SaveDS.PChurch[0].PartnerKey = SaveDS.PPartner[0].PartnerKey;
}
else if (SaveDS.POrganisation.Count > 0)
{
SaveDS.POrganisation[0].PartnerKey = SaveDS.PPartner[0].PartnerKey;
}
PPartnerLocationRow partnerlocation = SaveDS.PPartnerLocation.NewRowTyped();
partnerlocation.PartnerKey = SaveDS.PPartner[0].PartnerKey;
partnerlocation.LocationKey = SaveDS.PLocation[0].LocationKey;
partnerlocation.SiteKey = SaveDS.PLocation[0].SiteKey;
partnerlocation.SendMail = ASendMail;
SaveDS.PPartnerLocation.Rows.Add(partnerlocation);
}
else
{
SaveDS = GetPartnerDetails(AMainDS.PPartner[0].PartnerKey, out Dummy1, out Dummy2, out Dummy3, out Dummy4, out Dummy5);
DataUtilities.CopyDataSet(AMainDS, SaveDS);
}
List <string> ExistingPartnerTypes = new List <string>();
foreach (PPartnerTypeRow partnertype in SaveDS.PPartnerType.Rows)
{
if (!APartnerTypes.Contains(partnertype.TypeCode))
{
partnertype.Delete();
}
else
{
ExistingPartnerTypes.Add(partnertype.TypeCode);
}
}
// add new partner types
foreach (string partnertype in APartnerTypes)
{
if (!ExistingPartnerTypes.Contains(partnertype))
{
PPartnerTypeRow partnertypeRow = SaveDS.PPartnerType.NewRowTyped();
partnertypeRow.PartnerKey = AMainDS.PPartner[0].PartnerKey;
partnertypeRow.TypeCode = partnertype;
SaveDS.PPartnerType.Rows.Add(partnertypeRow);
}
}
List <string> ExistingSubscriptions = new List <string>();
foreach (PSubscriptionRow subscription in SaveDS.PSubscription.Rows)
{
if (!ASubscriptions.Contains(subscription.PublicationCode))
{
subscription.Delete();
}
else
{
ExistingSubscriptions.Add(subscription.PublicationCode);
}
}
// add new subscriptions
foreach (string subscription in ASubscriptions)
{
if (!ExistingSubscriptions.Contains(subscription))
{
PSubscriptionRow subscriptionRow = SaveDS.PSubscription.NewRowTyped();
subscriptionRow.PartnerKey = AMainDS.PPartner[0].PartnerKey;
subscriptionRow.PublicationCode = subscription;
subscriptionRow.ReasonSubsGivenCode = "FREE";
SaveDS.PSubscription.Rows.Add(subscriptionRow);
}
}
bool foundDefaultEmailAddress = false;
bool foundDefaultPhoneLandLine = false;
bool foundDefaultMobileLandLine = false;
foreach (PPartnerAttributeRow partnerattr in SaveDS.PPartnerAttribute.Rows)
{
if (partnerattr.AttributeType == MPartnerConstants.ATTR_TYPE_EMAIL)
{
partnerattr.Value = ADefaultEmailAddress;
foundDefaultEmailAddress = true;
}
else if (partnerattr.AttributeType == MPartnerConstants.ATTR_TYPE_PHONE)
{
partnerattr.Value = ADefaultPhoneLandline;
foundDefaultPhoneLandLine = true;
}
else if (partnerattr.AttributeType == MPartnerConstants.ATTR_TYPE_MOBILE_PHONE)
{
partnerattr.Value = ADefaultPhoneMobile;
foundDefaultMobileLandLine = true;
}
}
if (!foundDefaultEmailAddress)
{
PPartnerAttributeRow partnerattr = SaveDS.PPartnerAttribute.NewRowTyped();
partnerattr.PartnerKey = SaveDS.PPartner[0].PartnerKey;
partnerattr.AttributeType = MPartnerConstants.ATTR_TYPE_EMAIL;
partnerattr.Value = ADefaultEmailAddress;
partnerattr.Index = 0;
SaveDS.PPartnerAttribute.Rows.Add(partnerattr);
}
if (!foundDefaultPhoneLandLine)
{
PPartnerAttributeRow partnerattr = SaveDS.PPartnerAttribute.NewRowTyped();
partnerattr.PartnerKey = SaveDS.PPartner[0].PartnerKey;
partnerattr.AttributeType = MPartnerConstants.ATTR_TYPE_PHONE;
partnerattr.Value = ADefaultPhoneLandline;
partnerattr.Index = 0;
SaveDS.PPartnerAttribute.Rows.Add(partnerattr);
}
if (!foundDefaultMobileLandLine)
{
PPartnerAttributeRow partnerattr = SaveDS.PPartnerAttribute.NewRowTyped();
partnerattr.PartnerKey = SaveDS.PPartner[0].PartnerKey;
partnerattr.AttributeType = MPartnerConstants.ATTR_TYPE_MOBILE_PHONE;
partnerattr.Value = ADefaultPhoneMobile;
partnerattr.Index = 0;
SaveDS.PPartnerAttribute.Rows.Add(partnerattr);
}
// TODO: either reuse Partner Edit UIConnector
// or check for changed partner key, or changed Partner Class, etc.
// set Partner Short Name
if (SaveDS.PPartner[0].PartnerClass == MPartnerConstants.PARTNERCLASS_PERSON)
{
SaveDS.PPartner[0].PartnerShortName =
Calculations.DeterminePartnerShortName(
SaveDS.PPerson[0].FamilyName,
SaveDS.PPerson[0].Title,
SaveDS.PPerson[0].FirstName,
SaveDS.PPerson[0].MiddleName1);
}
else if (SaveDS.PPartner[0].PartnerClass == MPartnerConstants.PARTNERCLASS_FAMILY)
{
// check if we have a valid family name
if (SaveDS.PFamily[0].FamilyName.Trim().Length == 0)
{
AVerificationResult.Add(new TVerificationResult("error", "Please specify the family name", "",
"MaintainPartners.ErrMissingFamilyName", TResultSeverity.Resv_Critical));
return(false);
}
// check if we have a valid title
if (SaveDS.PFamily[0].Title.Trim().Length == 0)
{
AVerificationResult.Add(new TVerificationResult("error", "Please specify the title", "",
"MaintainPartners.ErrMissingTitle", TResultSeverity.Resv_Critical));
return(false);
}
SaveDS.PPartner[0].PartnerShortName =
Calculations.DeterminePartnerShortName(
SaveDS.PFamily[0].FamilyName,
SaveDS.PFamily[0].Title,
SaveDS.PFamily[0].FirstName);
}
else if (SaveDS.PPartner[0].PartnerClass == MPartnerConstants.PARTNERCLASS_UNIT)
{
SaveDS.PPartner[0].PartnerShortName = SaveDS.PUnit[0].UnitName;
}
else if (SaveDS.PPartner[0].PartnerClass == MPartnerConstants.PARTNERCLASS_ORGANISATION)
{
// check if we have a valid organisation name
if (SaveDS.POrganisation[0].OrganisationName.Trim().Length == 0)
{
AVerificationResult.Add(new TVerificationResult("error", "Please specify the organisation name", "",
"MaintainPartners.ErrMissingOrganisationName", TResultSeverity.Resv_Critical));
return(false);
}
SaveDS.PPartner[0].PartnerShortName = SaveDS.POrganisation[0].OrganisationName;
}
else if (SaveDS.PPartner[0].PartnerClass == MPartnerConstants.PARTNERCLASS_BANK)
{
SaveDS.PPartner[0].PartnerShortName = SaveDS.PBank[0].BranchName;
}
// change legacy addresses. create a new separate location for each partner
if (SaveDS.PLocation[0].LocationKey == 0)
{
PLocationRow location = SaveDS.PLocation.NewRowTyped();
DataUtilities.CopyAllColumnValues(SaveDS.PLocation[0], location);
location.SiteKey = DomainManager.GSiteKey;
location.LocationKey = -1;
SaveDS.PLocation.Rows.Clear();
SaveDS.PLocation.Rows.Add(location);
PPartnerLocationRow plocation = SaveDS.PPartnerLocation.NewRowTyped();
DataUtilities.CopyAllColumnValues(SaveDS.PPartnerLocation[0], plocation);
plocation.LocationKey = -1;
plocation.SiteKey = DomainManager.GSiteKey;
SaveDS.PPartnerLocation[0].Delete();
SaveDS.PPartnerLocation.Rows.Add(plocation);
}
// check if we have a valid country code
if (SaveDS.PLocation[0].CountryCode.Trim().Length == 0)
{
AVerificationResult.Add(new TVerificationResult("error", "The country code is missing", TResultSeverity.Resv_Critical));
return(false);
}
TDBTransaction Transaction = new TDBTransaction();
bool WrongCountryCode = false;
DBAccess.ReadTransaction(ref Transaction,
delegate
{
WrongCountryCode = !PCountryAccess.Exists(SaveDS.PLocation[0].CountryCode, Transaction);
});
if (WrongCountryCode)
{
AVerificationResult.Add(new TVerificationResult("error", "The country code does not match a country", TResultSeverity.Resv_Critical));
return(false);
}
DataSet ResponseDS = new PartnerEditTDS();
TPartnerEditUIConnector uiconnector = new TPartnerEditUIConnector(SaveDS.PPartner[0].PartnerKey);
// we search in every possible changed list, and added the data_type to NeededChanges
// everything in this list will be valided by TDataHistoryWebConnector.RegisterChanges
// it throws a error if a needed change cant be validated
List <string> NeededChanges = new List <string>();
foreach (PLocationRow Loc in SaveDS.PLocation.Rows)
{
if (Loc.RowState == DataRowState.Modified)
{
NeededChanges.Add("address");
}
}
foreach (PPartnerAttributeRow Attr in SaveDS.PPartnerAttribute.Rows)
{
if (Attr.RowState == DataRowState.Modified)
{
if (Attr.AttributeType == "E-Mail")
{
NeededChanges.Add("email address");
}
if (Attr.AttributeType == "Phone")
{
NeededChanges.Add("phone landline");
}
if (Attr.AttributeType == "Mobile Phone")
{
NeededChanges.Add("phone mobile");
}
}
}
// only run if it's not a new user create call
bool run_after_create = false;
if (AMainDS.PPartner[0].ModificationId != DateTime.MinValue)
{
bool consent_success = TDataHistoryWebConnector.RegisterChanges(AChanges, NeededChanges);
if (consent_success == false)
{
AVerificationResult.Add(new TVerificationResult("error", "consent_error", TResultSeverity.Resv_Critical));
return(false);
}
}
else
{
run_after_create = true;
}
try
{
TSubmitChangesResult result = uiconnector.SubmitChanges(
ref SaveDS,
ref ResponseDS,
out AVerificationResult);
if (run_after_create)
{
// after user entry exists, then enter inital changes
TDataHistoryWebConnector.RegisterChanges(AChanges, new List <string>());
}
return(result == TSubmitChangesResult.scrOK);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
AVerificationResult.Add(new TVerificationResult("error", e.Message, TResultSeverity.Resv_Critical));
return(false);
}
}
public static PartnerEditTDS GetPartnerDetails(Int64 APartnerKey,
out List <string> ASubscriptions,
out List <string> APartnerTypes,
out string ADefaultEmailAddress,
out string ADefaultPhoneMobile,
out string ADefaultPhoneLandline)
{
PartnerEditTDS MainDS = new PartnerEditTDS();
List <string> Subscriptions = new List <string>();
List <string> PartnerTypes = new List <string>();
string DefaultEmailAddress = String.Empty;
string DefaultPhoneMobile = String.Empty;
string DefaultPhoneLandline = String.Empty;
TDBTransaction Transaction = new TDBTransaction();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
PPartnerAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
if (MainDS.PPartner.Rows.Count > 0)
{
switch (MainDS.PPartner[0].PartnerClass)
{
case MPartnerConstants.PARTNERCLASS_FAMILY:
PFamilyAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
break;
case MPartnerConstants.PARTNERCLASS_PERSON:
PPersonAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
break;
case MPartnerConstants.PARTNERCLASS_CHURCH:
PChurchAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
break;
case MPartnerConstants.PARTNERCLASS_ORGANISATION:
POrganisationAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
break;
case MPartnerConstants.PARTNERCLASS_BANK:
PBankAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
break;
case MPartnerConstants.PARTNERCLASS_UNIT:
PUnitAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
break;
}
if (true)
{
PPartnerLocationAccess.LoadViaPPartner(MainDS, APartnerKey, Transaction);
PLocationAccess.LoadViaPPartner(MainDS, APartnerKey, Transaction);
}
if (true)
{
PPartnerRelationshipAccess.LoadViaPPartnerPartnerKey(MainDS, APartnerKey, Transaction);
}
if (true)
{
PCountryAccess.LoadAll(MainDS, Transaction);
}
if (true)
{
PPublicationAccess.LoadAll(MainDS, Transaction);
PSubscriptionAccess.LoadViaPPartnerPartnerKey(MainDS, APartnerKey, Transaction);
foreach (PSubscriptionRow subscription in MainDS.PSubscription.Rows)
{
Subscriptions.Add(subscription.PublicationCode);
}
}
PPartnerStatusAccess.LoadAll(MainDS, Transaction);
PTypeRow templateRow = MainDS.PType.NewRowTyped();
templateRow.SystemType = false;
templateRow.SetTypeDeletableNull();
templateRow.SetDateCreatedNull();
PTypeAccess.LoadUsingTemplate(MainDS, templateRow, Transaction);
PPartnerTypeAccess.LoadViaPPartner(MainDS, APartnerKey, Transaction);
foreach (PPartnerTypeRow partnertype in MainDS.PPartnerType.Rows)
{
PartnerTypes.Add(partnertype.TypeCode);
}
PPartnerAttributeAccess.LoadViaPPartner(MainDS, APartnerKey, Transaction);
foreach (PPartnerAttributeRow partnerattr in MainDS.PPartnerAttribute.Rows)
{
if (partnerattr.AttributeType == MPartnerConstants.ATTR_TYPE_EMAIL)
{
DefaultEmailAddress = partnerattr.Value;
}
else if (partnerattr.AttributeType == MPartnerConstants.ATTR_TYPE_PHONE)
{
DefaultPhoneLandline = partnerattr.Value;
}
else if (partnerattr.AttributeType == MPartnerConstants.ATTR_TYPE_MOBILE_PHONE)
{
DefaultPhoneMobile = partnerattr.Value;
}
}
}
});
APartnerTypes = PartnerTypes;
ASubscriptions = Subscriptions;
ADefaultEmailAddress = DefaultEmailAddress;
ADefaultPhoneMobile = DefaultPhoneMobile;
ADefaultPhoneLandline = DefaultPhoneLandline;
return(MainDS);
}
private void RevaluateAccount(AGeneralLedgerMasterTable AGlmTbl, decimal AExchangeRate, string ACurrencyCode)
{
foreach (AGeneralLedgerMasterRow glmRow in AGlmTbl.Rows)
{
AGeneralLedgerMasterPeriodTable glmpTbl = null;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
glmpTbl = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(glmRow.GlmSequence, F_AccountingPeriod, transaction);
});
try
{
if (glmpTbl.Rows.Count == 0)
{
continue; // I really don't expect this, but if it does happen, this will prevent a crash!
}
AGeneralLedgerMasterPeriodRow glmpRow = glmpTbl[0];
//
// If ActualForeign has not been set, I can't allow the ORM to even attempt to access them:
// (If ActualForeign is NULL, that's probably a fault, but this has occured in historical data.)
if (glmpRow.IsActualBaseNull() || glmpRow.IsActualForeignNull())
{
continue;
}
decimal delta = AccountDelta(glmpRow.ActualBase,
glmpRow.ActualForeign,
AExchangeRate,
F_BaseCurrencyDigits);
if (delta != 0)
{
// Now we have the relevant Cost Centre ...
RevaluateCostCentre(glmRow.AccountCode, glmRow.CostCentreCode, delta, AExchangeRate, ACurrencyCode);
}
else
{
string strMessage = String.Format(
Catalog.GetString("The account {1}:{0} does not require revaluation."),
glmRow.AccountCode,
glmRow.CostCentreCode,
AExchangeRate);
FVerificationCollection.Add(new TVerificationResult(
strStatusContent, strMessage, TResultSeverity.Resv_Status));
}
}
catch (EVerificationException terminate)
{
FVerificationCollection = terminate.ResultCollection();
}
} // foreach
}
/// <summary>
/// generate the partners from a text file that was generated with Benerator
/// </summary>
/// <param name="AInputBeneratorFile"></param>
public static void GenerateFamilyPartners(string AInputBeneratorFile)
{
TDataBase db = DBAccess.Connect("GenerateFamilyPartners");
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
// get a list of banks (all class BANK)
string sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank";
DataTable BankKeys = db.SelectDT(sqlGetBankPartnerKeys, "keys", Transaction);
Transaction.Rollback();
PartnerEditTDS MainDS = new PartnerEditTDS();
// AlanP: May 2016 - We may no longer need the UTF8 because the method now automatically discovers the encoding even with no BOM
XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",", Encoding.UTF8);
XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;
while (RecordNode != null)
{
string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation");
PFamilyRow familyRecord = null;
PPartnerRow FamilyPartnerRow = null;
if (familySituation == "singleMan")
{
familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Male", MainDS);
SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
}
else if (familySituation == "singleWoman")
{
familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Female", MainDS);
SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
}
else if (familySituation == "family")
{
familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Male", MainDS);
SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren"));
if (NumberOfChildren > 0)
{
FamilyPartnerRow = (PPartnerRow)MainDS.PPartner.Rows.Find(familyRecord.PartnerKey);
FamilyPartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_FAMILY;
}
}
FamilyPartnerRow = (PPartnerRow)MainDS.PPartner.Rows.Find(familyRecord.PartnerKey);
FamilyPartnerRow.ReceiptEachGift = false;
FamilyPartnerRow.ReceiptLetterFrequency = "Annual";
SampleDataWorkers.GenerateAddressForFamily(RecordNode, familyRecord, MainDS);
SampleDataWorkers.GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys);
if (MainDS.PFamily.Rows.Count % 100 == 0)
{
TLogging.Log("created donor " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName);
}
RecordNode = RecordNode.NextSibling;
}
// we do not save person records for normal family partners
MainDS.PPerson.Clear();
// need to clear all partner records of the PERSON partners as well
DataView PersonPartners = new DataView(MainDS.PPartner);
PersonPartners.RowFilter =
string.Format("{0} = '{1}'",
PPartnerTable.GetPartnerClassDBName(),
MPartnerConstants.PARTNERCLASS_PERSON);
DataView PartnerLocations = new DataView(MainDS.PPartnerLocation);
foreach (DataRowView rv in PersonPartners)
{
PPartnerRow partnerRow = (PPartnerRow)rv.Row;
PartnerLocations.RowFilter =
string.Format("{0} = {1}",
PPartnerLocationTable.GetPartnerKeyDBName(),
partnerRow.PartnerKey);
PartnerLocations[0].Row.Delete();
partnerRow.Delete();
}
// MainDS.ThrowAwayAfterSubmitChanges = true;
PartnerEditTDSAccess.SubmitChanges(MainDS);
RecordNode = doc.FirstChild.NextSibling.FirstChild;
DataView FamilyPartners = new DataView(MainDS.PFamily);
foreach (DataRowView rv in FamilyPartners)
{
PFamilyRow partnerRow = (PFamilyRow)rv.Row;
string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation");
string setmail;
if (familySituation == "singleWoman")
{
setmail = TXMLParser.GetAttribute(RecordNode, "FemaleEmail");
}
else
{
setmail = TXMLParser.GetAttribute(RecordNode, "MaleEmail");
}
string allowed_contents = TXMLParser.GetAttribute(RecordNode, "AllowedConsents");
string channel_contents = TXMLParser.GetAttribute(RecordNode, "ConsentChannel");
DataHistoryChange change = new DataHistoryChange
{
PartnerKey = partnerRow.PartnerKey,
Type = "email address",
Value = setmail,
ConsentDate = DateTime.Now,
ChannelCode = channel_contents,
Permissions = allowed_contents
};
// Build verification string
List <string> Changes = new List <string>();
List <string> Expected = new List <string>()
{
"email address"
};
Changes.Add(JsonConvert.SerializeObject(change));
// RegisterChanges ()
bool success = TDataHistoryWebConnector.RegisterChanges(Changes, Expected);
if (!success)
{
throw new Exception("saving conents failed");
}
RecordNode = RecordNode.NextSibling;
}
TLogging.Log("after saving donors");
}
/// <summary>
/// Parses certain p_partner_location data columns' content into a data structure that is p_parnter_attribute
/// representation.
/// </summary>
/// <remarks>Similar to code found in \csharp\ICT\BuildTools\DataDumpPetra2\FixData.cs, Method 'FixData',
/// in the code section that starts with the comment 'Process p_partner_location records and migrate certain values
/// of p_partner_location records to 'Contact Detail' records'.</remarks>
/// <param name="AMainDS">Typed DataSet that holds the p_partner_location records that are to be parsed.</param>
/// <param name="ATransaction">Instantiated DB Transaction.</param>
public static void ParsePartnerLocationsForContactDetails(PartnerImportExportTDS AMainDS, TDBTransaction ATransaction)
{
DataTable PartnerLocationsDT;
DataRow NewPartnerLocationDR;
string TelephoneNumber = String.Empty;
string FaxNumber = String.Empty;
string PhoneExtension;
string FaxExtension;
// collect the partner classes
foreach (PPartnerRow PartnerDR in AMainDS.PPartner.Rows)
{
TPartnerContactDetails.PartnerClassInformation[PartnerDR.PartnerKey] = PartnerDR.PartnerClass;
}
SortedList <long, DataTable> PartnerLocationsTables = new SortedList <long, DataTable>();
for (int counter = 0; counter < TPartnerContactDetails.NumberOfTables; counter++)
{
PartnerLocationsTables[counter] = TPartnerContactDetails.BestAddressHelper.GetNewPPartnerLocationTableInstance();
}
TPartnerContactDetails.PartnerLocationRecords = PartnerLocationsTables;
foreach (PPartnerLocationRow PartnerLocationDR in AMainDS.PPartnerLocation.Rows)
{
PartnerLocationsDT = PartnerLocationsTables[Math.Abs(PartnerLocationDR.PartnerKey) % TPartnerContactDetails.NumberOfTables];
DataRow LocationDR = AMainDS.PLocation.Rows.Find(new object[] { PartnerLocationDR.SiteKey, PartnerLocationDR.LocationKey });
// Phone Extension: Ignore if value in the dumped data is either null or 0
if (PartnerLocationDR.IsExtensionNull())
{
PhoneExtension = String.Empty;
}
PhoneExtension = PartnerLocationDR.Extension.ToString();
if (PhoneExtension == "0")
{
PhoneExtension = String.Empty;
}
// Fax Extension: Ignore if value in the dumped data is either null or 0
if (PartnerLocationDR.IsFaxExtensionNull())
{
FaxExtension = String.Empty;
}
FaxExtension = PartnerLocationDR.FaxExtension.ToString();
if (FaxExtension == "0")
{
FaxExtension = String.Empty;
}
if (!PartnerLocationDR.IsTelephoneNumberNull())
{
// Concatenate Phone Number and Phone Extension ONLY if both of them aren't null and Phone Extension isn't 0 either.
TelephoneNumber = PartnerLocationDR.TelephoneNumber + PhoneExtension;
}
if (!PartnerLocationDR.IsFaxNumberNull())
{
// Concatenate Fax Number and Fax Extension ONLY if both of them aren't null and Fax Extension isn't 0 either.
FaxNumber = PartnerLocationDR.FaxNumber + FaxExtension;
}
// Create representation of key data of the p_partner_location row and add it to the TPartnerContactDetails.PartnerLocationRecords Data Structure
NewPartnerLocationDR = PartnerLocationsDT.NewRow();
NewPartnerLocationDR["p_partner_key_n"] = PartnerLocationDR.PartnerKey;
NewPartnerLocationDR["p_site_key_n"] = PartnerLocationDR.SiteKey;
NewPartnerLocationDR["p_location_key_i"] = PartnerLocationDR.LocationKey;
if (!PartnerLocationDR.IsDateEffectiveNull())
{
NewPartnerLocationDR["p_date_effective_d"] = PartnerLocationDR.DateEffective;
}
else
{
PartnerLocationDR.SetDateEffectiveNull();
}
if (!PartnerLocationDR.IsDateGoodUntilNull())
{
NewPartnerLocationDR["p_date_good_until_d"] = PartnerLocationDR.DateGoodUntil;
}
else
{
PartnerLocationDR.SetDateGoodUntilNull();
}
NewPartnerLocationDR["p_location_type_c"] = PartnerLocationDR.LocationType;
NewPartnerLocationDR["p_send_mail_l"] = PartnerLocationDR.SendMail;
NewPartnerLocationDR["p_telephone_number_c"] = TelephoneNumber;
NewPartnerLocationDR["p_fax_number_c"] = FaxNumber;
NewPartnerLocationDR["p_mobile_number_c"] = PartnerLocationDR.MobileNumber;
NewPartnerLocationDR["p_alternate_telephone_c"] = PartnerLocationDR.AlternateTelephone;
NewPartnerLocationDR["p_email_address_c"] = PartnerLocationDR.EmailAddress;
NewPartnerLocationDR["p_url_c"] = PartnerLocationDR.Url;
NewPartnerLocationDR["p_value_country_c"] = LocationDR["p_country_code_c"];
PartnerLocationsDT.Rows.Add(NewPartnerLocationDR);
}
// get data for entire country table
PCountryTable CountryTable = PCountryAccess.LoadAll(ATransaction);
string InternatAccessCode = null;
string SiteCountryCode = TAddressTools.GetCountryCodeFromSiteLedger(ATransaction);
DataRow SiteCountryRow = CountryTable.Rows.Find(SiteCountryCode);
// get InternatAccessCode for site country
if (SiteCountryRow != null)
{
InternatAccessCode = SiteCountryRow[PCountryTable.GetInternatAccessCodeDBName()].ToString();
}
TPartnerContactDetails.CreateContactDetailsRow = CreatePartnerContactDetailRecord;
TPartnerContactDetails.EmptyStringIndicator = String.Empty;
TPartnerContactDetails.PartnerAttributeHoldingDataSet = AMainDS;
TPartnerContactDetails.CountryTable = CountryTable;
TPartnerContactDetails.SiteCountryCode = SiteCountryCode;
TPartnerContactDetails.SiteInternatAccessCode = InternatAccessCode;
TPartnerContactDetails.PopulatePPartnerAttribute();
Ict.Petra.Shared.MPartner.Calculations.DeterminePartnerContactDetailAttributes(AMainDS.PPartnerAttribute);
}
/// <summary>
/// Checks whether Partner Attributes of the same Attribute Type exist in the database.
/// </summary>
/// <param name="AImportedPartnerAttribRow"><see cref="PPartnerAttributeRow" /> that holds the imported data.</param>
/// <param name="AFoundPartnerAttribDR"><see cref="PPartnerAttributeRow" /> that holds the record that was found -
/// if one was found, otherwise this is null.</param>
/// <param name="ATransaction">Instantiated DB Transaction.</param>
/// <returns>False if the row should be imported as found in the import file, otherwise true.</returns>
public static bool ExistingPartnerAttributes(PPartnerAttributeRow AImportedPartnerAttribRow,
out PPartnerAttributeRow AFoundPartnerAttribDR, TDBTransaction ATransaction)
{
PPartnerAttributeTable ExistingPartnerAttributeDT;
AFoundPartnerAttribDR = null;
// Find existing Partner Attribute(s) of the same AttributeType (ignoring sequence!)
PPartnerAttributeRow TmpPartnerAttributeRow = new PPartnerAttributeTable().NewRowTyped(false);
TmpPartnerAttributeRow.PartnerKey = AImportedPartnerAttribRow.PartnerKey;
TmpPartnerAttributeRow.AttributeType = AImportedPartnerAttribRow.AttributeType;
ExistingPartnerAttributeDT = PartnerAttributeLoadUsingTemplate(TmpPartnerAttributeRow, null, ATransaction);
if (ExistingPartnerAttributeDT.Count == 0)
{
// No existing Partner Attribute(s) of the same AttributeType -->
// we want to import the row as found in the import file!
return(false);
}
// Existing Partner Attribute(s) of the same AttributeType --> Check to see whether we want to import
// that Row, or whether we want to overwrite an existing Row with some imported data!
for (int Counter = 0; Counter < ExistingPartnerAttributeDT.Rows.Count; Counter++)
{
if (ExistingPartnerAttributeDT[Counter].Value == AImportedPartnerAttribRow.Value)
{
// This *is* the same Partner Attribute!
AFoundPartnerAttribDR = ExistingPartnerAttributeDT[Counter];
break;
}
else if (AImportedPartnerAttribRow.AttributeType == Ict.Petra.Shared.MPartner.Calculations.ATTR_TYPE_PARTNERS_PRIMARY_CONTACT_METHOD)
{
// This *is* the same Partner Attribute!
AFoundPartnerAttribDR = ExistingPartnerAttributeDT[Counter];
break;
}
}
if (AFoundPartnerAttribDR != null)
{
// Same Partner Attribute --> overwrite the Row that is existing in the DB with some imported data!
return(true);
}
// No existing Partner Attribute(s) of the same AttributeType with the same Value -->
// we want to import the row as found in the import file!
return(false);
}
/// <summary>
/// This method needs to be implemented by extracts that can't follow the default processing with just
/// one query.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
{
Boolean ReturnValue = false;
Boolean NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
out NewTransaction);
DataTable giftdetails;
string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByAmount.sql");
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
bool AddressFilterAdded;
DataTable partnerkeys = new DataTable();
AExtractId = -1;
// call to derived class to retrieve parameters specific for extract
RetrieveParameters(AParameters, ref SqlStmt, ref SqlParameterList);
try
{
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("getting the data from the database", TLoggingType.ToStatusBar);
giftdetails = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", Transaction,
SqlParameterList.ToArray());
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(false);
}
TLogging.Log("preparing the extract", TLoggingType.ToStatusBar);
// With the result of the original query process the data and identify the partner keys for
// the extract.
partnerkeys.Columns.Add("0", typeof(Int64));
partnerkeys.Columns.Add("1", typeof(string));
partnerkeys.Columns.Add("p_site_key_n", typeof(Int64));
partnerkeys.Columns.Add("p_location_key_i", typeof(Int32));
ProcessGiftDetailRecords(giftdetails, AddressFilterAdded, AParameters, ref partnerkeys);
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction);
// create an extract with the given name in the parameters
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out AExtractId,
partnerkeys,
0,
AddressFilterAdded,
true);
return(ReturnValue);
}
/// <summary>
/// Passes data as a Typed DataSet to the caller, containing a DataTable that corresponds with <paramref name="AIndivDataItem"></paramref>.
/// </summary>
/// <param name="APartnerKey">PartnerKey of the Person to load data for.</param>
/// <param name="AIndivDataItem">The Individual Data Item for which data should be returned.</param>
/// <param name="AReadTransaction">Open Database transaction.</param>
/// <returns>A Typed DataSet containing a DataTable that corresponds with <paramref name="AIndivDataItem"></paramref>.</returns>
private static IndividualDataTDS GetData(Int64 APartnerKey, TIndividualDataItemEnum AIndivDataItem, TDBTransaction AReadTransaction)
{
IndividualDataTDS IndividualDataDS = new IndividualDataTDS("IndividualData"); // create the IndividualDataTDS DataSet that will later be passed to the Client
IndividualDataTDSMiscellaneousDataTable MiscellaneousDataDT;
IndividualDataTDSMiscellaneousDataRow MiscellaneousDataDR;
#region Create 'Miscellaneous' DataRow
MiscellaneousDataDT = IndividualDataDS.MiscellaneousData;
MiscellaneousDataDR = MiscellaneousDataDT.NewRowTyped(false);
MiscellaneousDataDR.PartnerKey = APartnerKey;
MiscellaneousDataDT.Rows.Add(MiscellaneousDataDR);
#endregion
switch (AIndivDataItem)
{
case TIndividualDataItemEnum.idiSummary:
BuildSummaryData(APartnerKey, ref IndividualDataDS, AReadTransaction);
DetermineItemCounts(MiscellaneousDataDR, AReadTransaction);
break;
case TIndividualDataItemEnum.idiPersonalLanguages:
PmPersonLanguageAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
PLanguageTable LanguageTable = (PLanguageTable)TSharedDataCache.TMCommon.GetCacheableCommonTable(
TCacheableCommonTablesEnum.LanguageCodeList);
PLanguageRow LanguageRow;
foreach (IndividualDataTDSPmPersonLanguageRow PersonLanguageRow in IndividualDataDS.PmPersonLanguage.Rows)
{
LanguageRow = (PLanguageRow)LanguageTable.Rows.Find(new object[] { PersonLanguageRow.LanguageCode });
if (LanguageRow != null)
{
PersonLanguageRow.LanguageDescription = LanguageRow.LanguageDescription;
}
}
break;
case TIndividualDataItemEnum.idiSpecialNeeds:
PmSpecialNeedAccess.LoadByPrimaryKey(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiPreviousExperiences:
PmPastExperienceAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiPersonalDocuments:
PmDocumentAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiJobAssignments:
PmJobAssignmentAccess.LoadViaPPartner(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiLocalPersonnelData:
// TODO: Fix this so LocalPersonnelData can actually load some data
bool labelsAvailable;
TOfficeSpecificDataLabelsUIConnector OfficeSpecificDataLabelsUIConnector;
OfficeSpecificDataLabelsUIConnector = new TOfficeSpecificDataLabelsUIConnector(APartnerKey,
TOfficeSpecificDataLabelUseEnum.Personnel);
IndividualDataDS.Merge(OfficeSpecificDataLabelsUIConnector.GetDataLocalPartnerDataValues(APartnerKey, out labelsAvailable, false,
AReadTransaction));
break;
case TIndividualDataItemEnum.idiProgressReports:
PmPersonEvaluationAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiCommitmentPeriods:
PmStaffDataAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiPersonSkills:
PmPersonSkillAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiPersonalAbilities:
PmPersonAbilityAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiPassportDetails:
PmPassportDetailsAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
PCountryTable CountryTable = (PCountryTable)TSharedDataCache.TMCommon.GetCacheableCommonTable(
TCacheableCommonTablesEnum.CountryList);
PCountryRow CountryRow;
foreach (IndividualDataTDSPmPassportDetailsRow PassportRow in IndividualDataDS.PmPassportDetails.Rows)
{
CountryRow = (PCountryRow)CountryTable.Rows.Find(new object[] { PassportRow.PassportNationalityCode });
if (CountryRow != null)
{
PassportRow.PassportNationalityName = CountryRow.CountryName;
}
}
break;
case TIndividualDataItemEnum.idiPersonalData:
PmPersonalDataAccess.LoadByPrimaryKey(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiEmergencyData:
PmPersonalDataAccess.LoadByPrimaryKey(IndividualDataDS, APartnerKey, AReadTransaction);
break;
case TIndividualDataItemEnum.idiApplications:
PmGeneralApplicationAccess.LoadViaPPersonPartnerKey(IndividualDataDS, APartnerKey, AReadTransaction);
PmShortTermApplicationAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
PmYearProgramApplicationAccess.LoadViaPPerson(IndividualDataDS, APartnerKey, AReadTransaction);
IndividualDataTDSPmGeneralApplicationRow GenAppRow;
TPartnerClass PartnerClass;
TStdPartnerStatusCode PartnerStatus;
String EventOrFieldName;
//TODO: now go through all short and long term apps and set the
// two fields in general app for EventOrFieldName and ApplicationForEventOrField
foreach (PmShortTermApplicationRow ShortTermRow in IndividualDataDS.PmShortTermApplication.Rows)
{
GenAppRow = (IndividualDataTDSPmGeneralApplicationRow)IndividualDataDS.PmGeneralApplication.Rows.Find
(new object[] { ShortTermRow.PartnerKey,
ShortTermRow.ApplicationKey, ShortTermRow.RegistrationOffice });
GenAppRow.ApplicationForEventOrField = Catalog.GetString("Event");
if (!ShortTermRow.IsStConfirmedOptionNull())
{
Ict.Petra.Server.MCommon.MCommonMain.RetrievePartnerShortName
(ShortTermRow.StConfirmedOption, out EventOrFieldName, out PartnerClass, out PartnerStatus);
GenAppRow.EventOrFieldName = EventOrFieldName;
}
}
foreach (PmYearProgramApplicationRow LongTermRow in IndividualDataDS.PmYearProgramApplication.Rows)
{
GenAppRow = (IndividualDataTDSPmGeneralApplicationRow)IndividualDataDS.PmGeneralApplication.Rows.Find
(new object[] { LongTermRow.PartnerKey,
LongTermRow.ApplicationKey, LongTermRow.RegistrationOffice });
GenAppRow.ApplicationForEventOrField = Catalog.GetString("Field");
if (!GenAppRow.IsGenAppPossSrvUnitKeyNull())
{
Ict.Petra.Server.MCommon.MCommonMain.RetrievePartnerShortName
(GenAppRow.GenAppPossSrvUnitKey, out EventOrFieldName, out PartnerClass, out PartnerStatus);
GenAppRow.EventOrFieldName = EventOrFieldName;
}
}
break;
// TODO: work on all cases/load data for all Individual Data items
}
return IndividualDataDS;
}
/// <summary>
/// Determines the number of DataRows for the Individual Data Items that work on multiple DataRows.
/// </summary>
/// <param name="AMiscellaneousDataDR">Instance of <see cref="IndividualDataTDSMiscellaneousDataRow" />.</param>
/// <param name="AReadTransaction">Open Database transaction.</param>
/// <returns>void</returns>
private static void DetermineItemCounts(IndividualDataTDSMiscellaneousDataRow AMiscellaneousDataDR, TDBTransaction AReadTransaction)
{
Int64 PartnerKey = AMiscellaneousDataDR.PartnerKey;
// Note: Commitment Records are counted already in BuildSummaryData and therefore don't need to be done here.
AMiscellaneousDataDR.ItemsCountPassportDetails = PmPassportDetailsAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountPersonalDocuments = PmDocumentAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountProfessionalAreas = PmPersonQualificationAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountPersonalLanguages = PmPersonLanguageAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountPersonalAbilities = PmPersonAbilityAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountPreviousExperience = PmPastExperienceAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountCommitmentPeriods = PmStaffDataAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountJobAssignments = PmJobAssignmentAccess.CountViaPPartner(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountProgressReports = PmPersonEvaluationAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountPersonSkills = PmPersonSkillAccess.CountViaPPerson(PartnerKey, AReadTransaction);
AMiscellaneousDataDR.ItemsCountApplications = PmGeneralApplicationAccess.CountViaPPersonPartnerKey(PartnerKey, AReadTransaction);
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="APartnerLocationRow"></param>
/// <param name="APartnerKey"></param>
/// <param name="APartnerClass"></param>
/// <param name="AAddressAddedOrChangedPromotionDT"></param>
/// <param name="ALocationPK"></param>
/// <param name="AReadTransaction"></param>
/// <returns></returns>
private static Boolean CheckFamilyMemberPropagation(PPartnerLocationRow APartnerLocationRow,
Int64 APartnerKey,
String APartnerClass,
ref PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable AAddressAddedOrChangedPromotionDT,
TLocationPK ALocationPK,
TDBTransaction AReadTransaction)
{
Boolean ReturnValue;
DataView AddressAddedOrChangedPromotionDV;
PartnerAddressAggregateTDSAddressAddedOrChangedPromotionRow AddressAddedOrChangedRow;
Boolean FoundFamilyMembers;
// TLogging.LogAtLevel(9, "CheckFamilyMemberPropagation for Location " + APartnerLocationRow.LocationKey.ToString() +
// ": AAddressAddedOrChangedPromotionDT.Rows.Count: " + AAddressAddedOrChangedPromotionDT.Rows.Count.ToString());
// TLogging.LogAtLevel(9, "CheckFamilyMemberPropagation: ALocationPK.LocationKey: " + ALocationPK.LocationKey.ToString());
// Check if there is a Parameter Row for the LocationKey we are looking at
AddressAddedOrChangedPromotionDV = new DataView(AAddressAddedOrChangedPromotionDT,
PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable.GetSiteKeyDBName() + " = " + ALocationPK.SiteKey.ToString() + " AND " +
PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable.GetLocationKeyDBName() + " = " + ALocationPK.LocationKey.ToString() +
" AND " + PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable.GetLocationAddedDBName() + " = true AND " +
PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable.GetUserAnswerDBName() + " <> ''''",
"",
DataViewRowState.CurrentRows);
// No, there isn't one: perform DB check
if (AddressAddedOrChangedPromotionDV.Count == 0)
{
if (PPersonAccess.CountViaPFamily(APartnerKey, AReadTransaction) > 0)
{
FoundFamilyMembers = true;
}
else
{
FoundFamilyMembers = false;
}
if (FoundFamilyMembers)
{
// Create a Parameter Row
// TLogging.LogAtLevel(9, "CheckFamilyMemberPropagation: Location " + APartnerLocationRow.LocationKey.ToString() + ": Partner is Family and has Family Members!");
AAddressAddedOrChangedPromotionDT = new PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable(
MPartnerConstants.ADDRESSADDEDORCHANGEDPROMOTION_TABLENAME);
AddressAddedOrChangedRow = AAddressAddedOrChangedPromotionDT.NewRowTyped(false);
AddressAddedOrChangedRow.SiteKey = ALocationPK.SiteKey;
AddressAddedOrChangedRow.LocationKey = ALocationPK.LocationKey;
AddressAddedOrChangedRow.LocationAdded = true;
AddressAddedOrChangedRow.LocationChange = false;
AddressAddedOrChangedRow.AnswerProcessedClientSide = false;
AddressAddedOrChangedRow.AnswerProcessedServerSide = false;
AAddressAddedOrChangedPromotionDT.Rows.Add(AddressAddedOrChangedRow);
ReturnValue = true;
}
else
{
// TLogging.LogAtLevel(9, "CheckFamilyMemberPropagation: Location " + APartnerLocationRow.LocationKey.ToString() + ": found no Family Members.");
ReturnValue = false;
}
}
else
{
// AAddressAddedOrChangedPromotionDT was passed in, holding parameters for the LocationKey we are looking at
AddressAddedOrChangedRow = (PartnerAddressAggregateTDSAddressAddedOrChangedPromotionRow)AddressAddedOrChangedPromotionDV[0].Row;
if (AddressAddedOrChangedRow.UserAnswer == "YES")
{
// TLogging.LogAtLevel(9, "CheckFamilyMemberPropagation: AAddressAddedOrChangedPromotionDT tells me to propagate the new Location to all Family Members.");
AddressAddedOrChangedRow.AnswerProcessedClientSide = true;
AddressAddedOrChangedRow.AcceptChanges();
ReturnValue = true;
}
else
{
// TLogging.LogAtLevel(9, "CheckFamilyMemberPropagation: AAddressAddedOrChangedPromotionDT tells me NOT to propagate the new Location.");
AddressAddedOrChangedRow.AnswerProcessedClientSide = true;
AddressAddedOrChangedRow.AcceptChanges();
ReturnValue = false;
}
}
return ReturnValue;
}
public static ExchangeRateTDS LoadDailyExchangeRateData(bool ADeleteAgedExchangeRatesFirst, DateTime AFromDate, DateTime AToDate)
{
// If relevant, we do a clean of the data table first, purging 'aged' data
if (ADeleteAgedExchangeRatesFirst)
{
// We clean up the DER table unless there is an app setting in the server configuration
// If you want to set this as a developer you create a copy of /inc/template/etc/Server-postgresql.config
// and rename it to Server-postgresql.config.my. Then add a new <add> element with this value set to true.
// Then (re)start the server using nant or OPDA, which will generate the working copy of this file.
if (!TAppSettingsManager.GetBoolean("KeepAgedExchangeRates", false))
{
DoDailyExchangeRateClean();
}
}
ExchangeRateTDS WorkingDS = new ExchangeRateTDS();
WorkingDS.EnforceConstraints = false;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadDailyExchangeRateData");
db.ReadTransaction(
ref Transaction,
delegate
{
// Populate the ExchangeRateTDSADailyExchangeRate table
//-- This is the complete query for the DAILYEXCHANGERATE TABLE
//-- It returns all rows from the Journal and Gift Batch tables
//-- PLUS all the rows from the DailyExchangeRate table that are NOT referenced by the Journal and Gift Batch tables.
string strSQL = "SELECT * FROM ";
strSQL += "( ";
// This returns all the rows in Daily Exchange rate that do NOT match any journal or gift
strSQL += "SELECT ";
strSQL += String.Format(
" 0 AS {0}, 0 AS {1}, 'DER' AS {2}, ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += " der.* ";
strSQL += "FROM PUB_a_daily_exchange_rate AS der ";
// By doing a left join and only selecting the NULL rows we get the rows from DER that are NOT used
strSQL += "LEFT JOIN ";
strSQL += "( ";
// This SELECT returns all the used rows (372 rows in the case of SA-DB)
strSQL += "SELECT ";
strSQL += " j.a_batch_number_i AS a_batch_number_i, ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_journal AS j ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " j.a_batch_number_i AS a_batch_number_i, ";
strSQL += " r.a_revaluation_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM a_journal AS j ";
strSQL += "JOIN a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ON ";
strSQL +=
" r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " gb.a_batch_number_i AS a_batch_number_i, ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_gift_batch AS gb ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS j_and_gb ";
strSQL += "ON ";
strSQL += " der.a_from_currency_code_c = j_and_gb.a_from_currency_code_c ";
strSQL += " AND der.a_to_currency_code_c = j_and_gb.a_to_currency_code_c ";
strSQL += " AND der.a_date_effective_from_d = j_and_gb.a_date_effective_from_d ";
strSQL += " AND der.a_rate_of_exchange_n = j_and_gb.a_rate_of_exchange_n ";
strSQL += "WHERE ";
strSQL += " a_batch_number_i IS NULL ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
// The second half of the UNION returns all the Forex rows from journal and gift
// They are aggregated by from/to/date/rate and the time is the min time.
// We also get the usage count as well as whether the row originated in the DER table or one of gift or batch
strSQL += "SELECT ";
strSQL += String.Format(
" sum(journalUsage) AS {0}, sum(giftBatchUsage) AS {1}, 'GBJ' AS {2}, ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += " a_from_currency_code_c, ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_rate_of_exchange_n, ";
strSQL += " a_date_effective_from_d, ";
strSQL += " min(a_time_effective_from_i), ";
strSQL += " NULL AS s_date_created_d, ";
strSQL += " NULL AS s_created_by_c, ";
strSQL += " NULL AS s_date_modified_d, ";
strSQL += " NULL AS s_modified_by_c, ";
strSQL += " NULL AS s_modification_id_t ";
strSQL += "FROM ";
strSQL += "( ";
// These are all the used rows again (same as part of the query above) but this time we can count the usages from the two tables
strSQL += "SELECT ";
strSQL += " 1 AS journalUsage, ";
strSQL += " 0 AS giftBatchUsage, ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_journal AS j ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " 1 AS journalUsage, ";
strSQL += " 0 AS giftBatchUsage, ";
strSQL += " r.a_revaluation_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM a_journal AS j ";
strSQL += "JOIN a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ON ";
strSQL +=
" r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " 0 AS journalUsage, ";
strSQL += " 1 AS giftBatchUsage, ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " 0 AS a_time_effective_from_i, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_gift_batch AS gb ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS j_and_gb ";
// GROUP the second half of the query (the UNION of used rates)
strSQL += "GROUP BY ";
strSQL += " a_from_currency_code_c, ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_date_effective_from_d, ";
strSQL += " a_rate_of_exchange_n ";
strSQL += ") AS all_rates ";
strSQL += ((AFromDate < DateTime.MaxValue) && (AToDate < DateTime.MaxValue)) ?
String.Format(" WHERE all_rates.{0}>='{1}' AND all_rates.{0}<='{2}' ",
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
AFromDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
AToDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)) :
String.Empty;
// ORDER of the outermost SELECT
strSQL += "ORDER BY ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_from_currency_code_c, ";
strSQL += " a_date_effective_from_d DESC, ";
strSQL += " a_time_effective_from_i DESC ";
db.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRate.TableName, Transaction);
// Now populate the ExchangeRateTDSADailyExchangerateUsage table
//-- COMPLETE QUERY TO RETURN ADailyExchangeRateUsage
//-- Query to return the Daily Exchange Rate Usage details
//-- Only returns rows that are in a foreign currency
//-- Querying this table by from/to/date/time will return one row per use case
//-- If the Journal is 0 the batch refers to a gift batch, otherwise it is a GL batch
strSQL = "SELECT * FROM ( ";
//-- This part of the query returns the use cases from the Journal table
strSQL += "SELECT ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += String.Format(
" j.a_ledger_number_i AS {0}, j.a_batch_number_i AS {1}, j.a_journal_number_i AS {2}, b.a_batch_status_c AS {3}, j.a_journal_description_c AS {4}, b.a_batch_year_i AS {5}, b.a_batch_period_i AS {6}, 'J' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_journal j ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_batch b ";
strSQL += " ON b.a_batch_number_i = j.a_batch_number_i ";
strSQL += " AND b.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
//-- This part of the query returns the revaluation rows
strSQL += "SELECT ";
strSQL += " r.a_revaluation_currency_c as a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += String.Format(
" j.a_ledger_number_i AS {0}, j.a_batch_number_i AS {1}, j.a_journal_number_i AS {2}, b.a_batch_status_c AS {3}, j.a_journal_description_c AS {4}, b.a_batch_year_i AS {5}, b.a_batch_period_i AS {6}, 'J' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_journal j ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_batch b ";
strSQL += " ON b.a_batch_number_i = j.a_batch_number_i ";
strSQL += " AND b.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ";
strSQL +=
" ON r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
//-- This part of the query returns the use cases from the Gift Batch table
strSQL += "SELECT ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " 0 AS a_time_effective_from_i, ";
strSQL += String.Format(
" gb.a_ledger_number_i AS {0}, gb.a_batch_number_i AS {1}, 0 AS {2}, gb.a_batch_status_c AS {3}, gb.a_batch_description_c AS {4}, gb.a_batch_year_i AS {5}, gb.a_batch_period_i AS {6}, 'GB' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_gift_batch gb ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS usage ";
strSQL += ((AFromDate < DateTime.MaxValue) && (AToDate < DateTime.MaxValue)) ?
String.Format(" WHERE usage.{0}>='{1}' AND usage.{0}<='{2}' ",
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
AFromDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
AToDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)) :
String.Empty;
strSQL += "ORDER BY usage.a_date_effective_from_d DESC, usage.a_time_effective_from_i DESC ";
db.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRateUsage.TableName, Transaction);
// Now we start a tricky bit to resolve potential primary key conflicts when the constraints are turned on.
// By combining the Journal and Gift Batch data that is not referenced in the exchange rate table we can easily
// have introduced conflicts where more than one rate has been used for a given currency pair and effective date/time.
// This is because there is no constraint that enforces the batch/journal tables to use a time from the exch rate table.
// So we have to go through all the rows in our data table and potentially change the time to make it possible to get our primary key.
// Start by creating a data view on the whole result set. The ordering is important because we are going to step through the set row by row.
// Within one group of from/to/date it is essential that the first 'source' is the DER table because we don't change the time on that one -
// and of course that must stay the same because the user can modify that one.
// We need to deal with the following possibilities:
// From To Date Time Source Rate
// EUR GBP 2014-01-01 1234 DER 2.11
// EUR GBP 2014-01-01 1234 GBJ 2.115
// EUR GBP 2014-01-01 1234 GBJ 2.22
// EUR GBP 2014-01-01 1234 GBJ 3.11
//
// In the first row we have an entry from the DER table that is not used anywhere, but a (slightly) different rate is actually used
// in a Journal.
// In the other rows we have 3 different rates - all used somewhere. We need to adjust the times so they are different.
DataView dv = new DataView(WorkingDS.ADailyExchangeRate, "",
String.Format("{0}, {1}, {2} DESC, {3} DESC, {4}, {5}",
ADailyExchangeRateTable.GetFromCurrencyCodeDBName(),
ADailyExchangeRateTable.GetToCurrencyCodeDBName(),
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
ADailyExchangeRateTable.GetTimeEffectiveFromDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName(),
ADailyExchangeRateTable.GetRateOfExchangeDBName()), DataViewRowState.CurrentRows);
for (int i = 0; i < dv.Count - 1; i++)
{
// Get the 'current' row and the 'next' one...
ExchangeRateTDSADailyExchangeRateRow drThis = (ExchangeRateTDSADailyExchangeRateRow)dv[i].Row;
ExchangeRateTDSADailyExchangeRateRow drNext = (ExchangeRateTDSADailyExchangeRateRow)dv[i + 1].Row;
if (!drThis.FromCurrencyCode.Equals(drNext.FromCurrencyCode) ||
!drThis.ToCurrencyCode.Equals(drNext.ToCurrencyCode) ||
!drThis.DateEffectiveFrom.Equals(drNext.DateEffectiveFrom) ||
!drThis.TimeEffectiveFrom.Equals(drNext.TimeEffectiveFrom))
{
// Something is different so our primary key will be ok for the current row
continue;
}
// We have got two (or more) rows with the same potential primary key and different rates/usages.
// We need to work out how many rows ahead also have the same time and adjust them all
bool moveForwards = (drThis.TimeEffectiveFrom < 43200);
int timeOffset = 60; // 1 minute
// Start by adjusting our 'next' row we are already working with
drNext.BeginEdit();
int prevTimeEffectiveFrom = drNext.TimeEffectiveFrom;
drNext.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
drNext.EndEdit();
i++; // we can increment our main loop counter now that we have dealt with our 'next' row.
TLogging.LogAtLevel(2, String.Format("Modifying {0} row: From {1}, To {2}, Date {3}, Time {4}, new Time {5}",
drThis.TableSource, drThis.FromCurrencyCode, drThis.ToCurrencyCode, drThis.DateEffectiveFrom.ToString("yyyy-MM-dd"),
prevTimeEffectiveFrom, drNext.TimeEffectiveFrom), TLoggingType.ToLogfile);
// Modify all the rows in the usage table that refer to the previous time
OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drNext.FromCurrencyCode, drNext.ToCurrencyCode,
drNext.DateEffectiveFrom,
prevTimeEffectiveFrom, drNext.TimeEffectiveFrom, drNext.RateOfExchange);
// Now look ahead even further than the 'next' row and modify those times too, adding 1 more minute to each
for (int k = i + 1; k < dv.Count; k++)
{
ExchangeRateTDSADailyExchangeRateRow drLookAhead = (ExchangeRateTDSADailyExchangeRateRow)dv[k].Row;
if (!drThis.FromCurrencyCode.Equals(drLookAhead.FromCurrencyCode) ||
!drThis.ToCurrencyCode.Equals(drLookAhead.ToCurrencyCode) ||
!drThis.DateEffectiveFrom.Equals(drLookAhead.DateEffectiveFrom) ||
!drThis.TimeEffectiveFrom.Equals(drLookAhead.TimeEffectiveFrom))
{
// No more rows match our potential primary key conflict on the 'current' row.
break;
}
// Do exactly the same to this row as we did to the 'next' row above
drLookAhead.BeginEdit();
prevTimeEffectiveFrom = drLookAhead.TimeEffectiveFrom;
drLookAhead.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
drLookAhead.EndEdit();
i++;
TLogging.LogAtLevel(2, String.Format("Modifying additional {0} row: From {1}, To {2}, Date {3}, Time {4}, new Time {5}",
drThis.TableSource, drThis.FromCurrencyCode, drThis.ToCurrencyCode,
drThis.DateEffectiveFrom.ToString("yyyy-MM-dd"),
prevTimeEffectiveFrom, drLookAhead.TimeEffectiveFrom), TLoggingType.ToLogfile);
OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drLookAhead.FromCurrencyCode, drLookAhead.ToCurrencyCode,
drLookAhead.DateEffectiveFrom, prevTimeEffectiveFrom, drLookAhead.TimeEffectiveFrom, drLookAhead.RateOfExchange);
}
} // check the next row in the table so that it becomes the 'current' row.
WorkingDS.EnforceConstraints = true;
// We only load the following data if we are returning ALL exchange rate data
if ((AFromDate == DateTime.MaxValue) && (AToDate == DateTime.MaxValue))
{
// Load the Corporate exchange rate table using the usual method
ACorporateExchangeRateAccess.LoadAll(WorkingDS, Transaction);
// Load the daily exchange rate table as the 'raw' table. The client needs this for adding new rows to check for constraints.
// Note: April 2015. The MissingSchemaAction was added because SQLite gave a mismatched DataType on a_effective_time_i.
// As a result the GUI tests failed on SQLite - as well as the screen not loading(!)
// There should be no difference with PostgreSQL, which worked fine without the parameter.
WorkingDS.ARawDailyExchangeRate.Merge(db.SelectDT("SELECT *, 0 AS Unused FROM PUB_a_daily_exchange_rate",
"a_raw_daily_exchange_rate",
Transaction), false, MissingSchemaAction.Ignore);
strSQL = "SELECT ";
strSQL += " a_ledger_number_i, ";
strSQL += " a_ledger_status_l, ";
strSQL += " max(a_ledger_name_c) AS a_ledger_name_c, ";
strSQL += " max(a_base_currency_c) AS a_base_currency_c, ";
strSQL += " max(a_intl_currency_c) AS a_intl_currency_c, ";
strSQL += " max(a_current_financial_year_i) AS a_current_financial_year_i, ";
strSQL += " max(a_current_period_i) AS a_current_period_i, ";
strSQL += " max(a_number_of_accounting_periods_i) AS a_number_of_accounting_periods_i, ";
strSQL += " max(a_number_fwd_posting_periods_i) AS a_number_fwd_posting_periods_i, ";
strSQL += " min(CurrentPeriodStartDate) AS CurrentPeriodStartDate, ";
strSQL += " max(CurrentPeriodEndDate) AS CurrentPeriodEndDate, ";
strSQL += " max(ForwardPeriodEndDate) AS ForwardPeriodEndDate ";
strSQL += "FROM ";
strSQL += "( ";
strSQL +=
"SELECT ldg.*, pd.a_period_start_date_d AS CurrentPeriodStartDate, pd.a_period_end_date_d AS CurrentPeriodEndDate, NULL AS ForwardPeriodEndDate ";
strSQL += "FROM a_ledger ldg ";
strSQL += "JOIN a_accounting_period pd ";
strSQL += "ON ldg.a_ledger_number_i=pd.a_ledger_number_i and ldg.a_current_period_i=pd.a_accounting_period_number_i ";
strSQL += "UNION ";
strSQL +=
"SELECT ldg.*, pd.a_period_start_date_d AS CurrentPeriodStartDate, NULL AS CurrentPeriodEndDate, pd.a_period_end_date_d AS ForwardPeriodEndDate ";
strSQL += "FROM a_ledger ldg ";
strSQL += "JOIN a_accounting_period pd ";
strSQL +=
"ON ldg.a_ledger_number_i=pd.a_ledger_number_i and (ldg.a_current_period_i + a_number_fwd_posting_periods_i)=pd.a_accounting_period_number_i ";
strSQL += ") AS all_info ";
strSQL += "GROUP BY a_ledger_number_i, a_ledger_status_l ";
db.Select(WorkingDS, strSQL, WorkingDS.ALedgerInfo.TableName, Transaction);
}
});
// Accept row changes here so that the Client gets 'unmodified' rows
WorkingDS.AcceptChanges();
return(WorkingDS);
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="ASiteKey"></param>
/// <param name="ALocationKey"></param>
/// <param name="AReadTransaction"></param>
/// <returns></returns>
public static Boolean CheckHasPartnerLocationOtherPartnerReferences(Int64 APartnerKey,
Int64 ASiteKey,
Int32 ALocationKey,
TDBTransaction AReadTransaction)
{
Boolean ReturnValue;
PPartnerLocationTable TemplateTable;
PPartnerLocationRow TemplateRow;
StringCollection TemplateOperators;
int OtherPartnerLocationReferences;
// TLogging.LogAtLevel(9, "CheckHasPartnerLocationOtherPartnerReferences for Location " + ALocationKey.ToString());
if (ALocationKey != 0)
{
// Look for other Partners that reference the specified PartnerLocation
TemplateTable = new PPartnerLocationTable();
TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.PartnerKey = APartnerKey;
TemplateRow.SiteKey = ASiteKey;
TemplateRow.LocationKey = ALocationKey;
TemplateOperators = new StringCollection();
TemplateOperators.Add("<>");
OtherPartnerLocationReferences = PPartnerLocationAccess.CountUsingTemplate(TemplateRow, TemplateOperators, AReadTransaction);
// TLogging.LogAtLevel(9, "CheckHasPartnerLocationOtherPartnerReferences: Location " + ALocationKey.ToString() + ": is used by " +
// OtherPartnerLocationReferences.ToString() + " other Partners.");
ReturnValue = (OtherPartnerLocationReferences > 0);
}
else
{
/*
* Special case: for Location 0 we always return 'is referenced' - it is a
* dummy record (signalising 'Partner has no address') that is seen as
* beeing referenced all the time (even if no Partner is referencing it).
*/
// TLogging.LogAtLevel(9, "CheckHasPartnerLocationOtherPartnerReferences: Location " + ALocationKey.ToString() +
// ": is Location 0, therefore it is seen as beeing used by other Partners.");
ReturnValue = true;
}
return ReturnValue;
}
public static DataTable FindContacts(string AContactor,
DateTime?AContactDate,
string ACommentContains,
string AMethodOfContact,
string AModuleID,
string AMailingCode,
PPartnerContactAttributeTable AContactAttributes)
{
Boolean NewTransaction;
DataTable Contacts = new DataTable();
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
string Query = "SELECT p_contact_log.*, p_partner_contact.p_partner_key_n, p_partner.p_partner_short_name_c" +
" FROM p_contact_log, p_partner_contact, p_partner" +
" WHERE" +
" p_partner_contact.p_contact_log_id_i = p_contact_log.p_contact_log_id_i" +
" AND p_partner.p_partner_key_n = p_partner_contact.p_partner_key_n";
if (AContactor.Length > 0)
{
Query += " AND p_contact_log.p_contactor_c = '" + AContactor + "'";
}
if (AContactDate.HasValue)
{
Query += " AND p_contact_log.s_contact_date_d = '" + AContactDate + "'";
}
if (AMethodOfContact.Length > 0)
{
Query += " AND p_contact_log.p_contact_code_c = '" + AMethodOfContact + "'";
}
if (AModuleID.Length > 0)
{
Query += " AND p_contact_log.s_module_id_c = '" + AModuleID + "'";
}
if (AMailingCode.Length > 0)
{
Query += " AND p_contact_log.p_mailing_code_c = '" + AMailingCode + "'";
}
if (ACommentContains.Length > 0)
{
Query += " AND p_contact_log.p_contact_comment_c LIKE '%" + ACommentContains + "%'";
}
if ((AContactAttributes != null) && (AContactAttributes.Rows.Count > 0))
{
Query += " AND EXISTS (SELECT * " +
" FROM p_partner_contact_attribute" +
" WHERE" +
" p_partner_contact_attribute.p_contact_id_i = p_contact_log.p_contact_log_id_i" +
" AND (";
foreach (PPartnerContactAttributeRow Row in AContactAttributes.Rows)
{
Query += " (p_partner_contact_attribute.p_contact_attribute_code_c = '" + Row.ContactAttributeCode + "'" +
" AND p_partner_contact_attribute.p_contact_attr_detail_code_c = '" + Row.ContactAttrDetailCode + "') OR";
}
// remove the final " OR"
Query = Query.Substring(0, Query.Length - 3) + "))";
}
DBAccess.GDBAccessObj.SelectDT(Contacts, Query, WriteTransaction);
Contacts.PrimaryKey = new DataColumn[] {
Contacts.Columns["p_partner_key_n"], Contacts.Columns["p_contact_log_id_i"]
};
}
catch (Exception e)
{
TLogging.Log(e.Message);
TLogging.Log(e.StackTrace);
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(Contacts);
}
/// <summary>
/// Determine location keys for partners needed for extract, depending on if location information
/// was retrieved by query or not.
/// </summary>
/// <param name="APartnerKeysTable"></param>
/// <param name="APartnerKeyColumn"></param>
/// <param name="ASiteKeyColumn"></param>
/// <param name="ALocationKeyColumn"></param>
/// <param name="APartnerLocationKeysTable"></param>
/// <param name="ATransaction"></param>
private static void DetermineBestLocationKeys(
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
out PPartnerLocationTable APartnerLocationKeysTable,
TDBTransaction ATransaction)
{
Int64 PartnerKey;
Int64 PreviousPartnerKey;
Int32 NumberOfPartnerRows = APartnerKeysTable.Rows.Count;
DataRow partnerRow;
List <TLocationPK>LocationKeyList = new List <TLocationPK>();
APartnerLocationKeysTable = new PPartnerLocationTable();
// don't go further if table is empty
if (APartnerKeysTable.Rows.Count == 0)
{
return;
}
// If location column exists then check if there is more than one location key for a partner.
// If so then determine the best of the found addresses.
if (ALocationKeyColumn >= 0)
{
PreviousPartnerKey = -1;
// Rows are sorted by partner key. Create a list of location keys per partner and determine
// the best of those addresses.
for (int ii = NumberOfPartnerRows - 1; ii >= 0; ii--)
{
partnerRow = APartnerKeysTable.Rows[ii];
PartnerKey = Convert.ToInt64(partnerRow[APartnerKeyColumn]);
if ((PartnerKey != PreviousPartnerKey)
&& (PreviousPartnerKey != -1))
{
if (!DetermineAndAddBestLocationKey(PreviousPartnerKey, LocationKeyList,
ref APartnerLocationKeysTable, ATransaction))
{
// if no address could be found then remove this partner
APartnerKeysTable.Rows[ii + 1].Delete();
}
else
{
// add first location for next partner
LocationKeyList.Clear();
LocationKeyList.Add(new TLocationPK(Convert.ToInt64(partnerRow[ASiteKeyColumn]),
Convert.ToInt32(partnerRow[ALocationKeyColumn])));
}
}
else
{
// add location for this partner
LocationKeyList.Add(new TLocationPK(Convert.ToInt64(partnerRow[ASiteKeyColumn]),
Convert.ToInt32(partnerRow[ALocationKeyColumn])));
}
// prepare for next round of loop
PreviousPartnerKey = PartnerKey;
}
// process last partner key after loop through all records
if (!DetermineAndAddBestLocationKey(PreviousPartnerKey, LocationKeyList,
ref APartnerLocationKeysTable, ATransaction))
{
// if no address could be found then remove this partner
APartnerKeysTable.Rows[0].Delete();
}
}
else
{
// If no location information was retrieved with earlier query then find best address
// for partner.
for (int ii = NumberOfPartnerRows - 1; ii >= 0; ii--)
{
partnerRow = APartnerKeysTable.Rows[ii];
PartnerKey = Convert.ToInt64(partnerRow[APartnerKeyColumn]);
if (!DetermineAndAddBestLocationKey(PartnerKey, LocationKeyList,
ref APartnerLocationKeysTable, ATransaction))
{
// if no address could be found then remove this partner
partnerRow.Delete();
}
}
}
}
/// <summary>
/// run the report
/// </summary>
private void Run(string ASessionID)
{
// need to initialize the database session
TSession.InitThread(ASessionID);
IsolationLevel Level;
if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "readuncommitted")
{
// for long reports, that should not take out locks;
// the data does not need to be consistent or will most likely not be changed during the generation of the report
Level = IsolationLevel.ReadUncommitted;
}
else if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "repeatableread")
{
// for financial reports: it is important to have consistent data; e.g. for totals
Level = IsolationLevel.RepeatableRead;
}
else if (FParameterList.Get("IsolationLevel").ToString().ToLower() == "serializable")
{
// for creating extracts: we need to write to the database
Level = IsolationLevel.Serializable;
}
else
{
// default behaviour for normal reports
Level = IsolationLevel.ReadCommitted;
}
FSuccess = false;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
try
{
DBAccess.GDBAccessObj.BeginAutoTransaction(Level, ref Transaction,
ref SubmissionOK,
delegate
{
if (FDatacalculator.GenerateResult(ref FParameterList, ref FResultList, ref FErrorMessage, ref FException))
{
FSuccess = true;
SubmissionOK = true;
}
else
{
TLogging.Log(FErrorMessage);
}
});
}
catch (Exception Exc)
{
TLogging.Log("Problem calculating report: " + Exc.ToString());
TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile);
FSuccess = false;
FErrorMessage = Exc.Message;
FException = Exc;
}
if (TDBExceptionHelper.IsTransactionSerialisationException(FException))
{
// do nothing - we want this exception to bubble up
}
else if (FException is Exception && FException.InnerException is EOPDBException)
{
EOPDBException DbExc = (EOPDBException)FException.InnerException;
if (DbExc.InnerException is Exception)
{
if (DbExc.InnerException is NpgsqlException)
{
NpgsqlException PgExc = (NpgsqlException)DbExc.InnerException;
if (PgExc.Code == "57014") // SQL statement timeout problem
{
FErrorMessage = Catalog.GetString(
"Error - Database took too long to respond. Try different parameters to return fewer results.");
}
}
else
{
FErrorMessage = DbExc.InnerException.Message;
}
FException = null;
}
}
TProgressTracker.FinishJob(FProgressID);
}
/// <summary>
/// Format the letter for the donor with all the gifts
///
/// Can also used for a single receipt.
/// </summary>
/// <returns>One or more html documents, each in its own body tag, for printing with the HTML printer</returns>
private static string FormatLetter(Int64 ADonorKey,
string ADonorName,
DataTable ADonations,
string ABaseCurrency,
string AHTMLTemplate,
string ALedgerCountryCode,
TDBTransaction ATransaction)
{
// get details of the donor, and best address
PLocationTable Location;
string CountryName;
string MajorUnitSingular = string.Empty;
string MajorUnitPlural = string.Empty;
string MinorUnitSingular = string.Empty;
string MinorUnitPlural = string.Empty;
if (!TAddressTools.GetBestAddress(ADonorKey, out Location, out CountryName, ATransaction))
{
return "";
}
bool TaxDeductiblePercentageEnabled = Convert.ToBoolean(
TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE"));
string msg = AHTMLTemplate;
if (ADonorName.Contains(","))
{
msg = msg.Replace("#TITLE", Calculations.FormatShortName(ADonorName, eShortNameFormat.eOnlyTitle));
}
else
{
// organisations have no title
msg = msg.Replace("#TITLE", "");
}
msg = msg.Replace("#NAME", Calculations.FormatShortName(ADonorName, eShortNameFormat.eReverseWithoutTitle));
msg = msg.Replace("#STREETNAME", GetStringOrEmpty(Location[0].StreetName));
msg = msg.Replace("#LOCATION", GetStringOrEmpty(Location[0].Locality));
msg = msg.Replace("#ADDRESS3", GetStringOrEmpty(Location[0].Address3));
msg = msg.Replace("#BUILDING1", GetStringOrEmpty(Location[0].Building1));
msg = msg.Replace("#BUILDING2", GetStringOrEmpty(Location[0].Building2));
msg = msg.Replace("#CITY", GetStringOrEmpty(Location[0].City));
msg = msg.Replace("#POSTALCODE", GetStringOrEmpty(Location[0].PostalCode));
msg = msg.Replace("#DATE", DateTime.Now.ToString("d. MMMM yyyy"));
msg = msg.Replace("#DONORKEY", ADonorKey.ToString("0000000000"));
// according to German Post, there is no country code in front of the post code
// if country code is same for the address of the recipient and this office, then COUNTRYNAME is cleared
if (GetStringOrEmpty(Location[0].CountryCode) != ALedgerCountryCode)
{
msg = msg.Replace("#COUNTRYNAME", CountryName);
}
else
{
msg = msg.Replace("#COUNTRYNAME", "");
}
// recognise detail lines automatically
string RowTemplate;
msg = TPrinterHtml.GetTableRow(msg, "#AMOUNT", out RowTemplate);
string OrigRowTemplate = RowTemplate;
string rowTexts = "";
decimal sum = 0;
decimal sumTaxDeduct = 0;
decimal sumNonDeduct = 0;
decimal prevAmount = 0.0M;
decimal prevAmountTaxDeduct = 0.0M;
decimal prevAmountNonDeduct = 0.0M;
string prevCurrency = String.Empty;
string prevgifttype = string.Empty;
DateTime prevDateEntered = DateTime.MaxValue;
foreach (DataRow rowGifts in ADonations.Rows)
{
DateTime dateEntered = Convert.ToDateTime(rowGifts["DateEntered"]);
decimal amount = Convert.ToDecimal(rowGifts["TransactionAmount"]);
decimal taxDeductibleAmount = 0;
decimal nonDeductibleAmount = 0;
string currency = rowGifts["Currency"].ToString();
string commentOne = rowGifts["CommentOne"].ToString();
string commentTwo = rowGifts["CommentTwo"].ToString();
string commentThree = rowGifts["CommentThree"].ToString();
string accountDesc = rowGifts["AccountDesc"].ToString();
string costcentreDesc = rowGifts["CostCentreDesc"].ToString();
string fieldName = rowGifts["FieldName"].ToString();
string recipientName = rowGifts["RecipientName"].ToString();
string gifttype = rowGifts["GiftType"].ToString();
RowTemplate = OrigRowTemplate;
sum += Convert.ToDecimal(rowGifts["AmountInBaseCurrency"]);
if (TaxDeductiblePercentageEnabled)
{
taxDeductibleAmount = Convert.ToDecimal(rowGifts["TaxDeductibleAmount"]);
nonDeductibleAmount = Convert.ToDecimal(rowGifts["NonDeductibleAmount"]);
sumTaxDeduct += Convert.ToDecimal(rowGifts["TaxDeductibleAmountBase"]);
sumNonDeduct += Convert.ToDecimal(rowGifts["NonDeductibleAmountBase"]);
}
/* can we sum up donations on the same date, or do we need to print each detail with the account description? */
// if we are printing every single gift detail
if (RowTemplate.Contains("#ACCOUNTDESC") || RowTemplate.Contains("#COSTCENTREDESC")
|| RowTemplate.Contains("#FIELDNAME") || RowTemplate.Contains("#RECIPIENTNAME")
|| RowTemplate.Contains("#COMMENTONE") || RowTemplate.Contains("#COMMENTTWO") || RowTemplate.Contains("#COMMENTTHREE"))
{
if (gifttype == MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT);
}
else if (gifttype == MFinanceConstants.GIFT_TYPE_GIFT)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND);
}
GetUnitLabels(currency, ref MajorUnitSingular, ref MajorUnitPlural, ref MinorUnitSingular, ref MinorUnitPlural);
rowTexts += RowTemplate.
Replace("#DONATIONDATE", dateEntered.ToString("dd.MM.yyyy")).
Replace("#AMOUNTCURRENCY", currency).
Replace("#AMOUNTINWORDS",
NumberToWords.AmountToWords(amount, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#AMOUNT", StringHelper.FormatUsingCurrencyCode(amount, currency)).
Replace("#TAXDEDUCTAMOUNTINWORDS",
NumberToWords.AmountToWords(taxDeductibleAmount, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#TAXDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(taxDeductibleAmount, currency)).
Replace("#TAXNONDEDUCTAMNTINWORDS",
NumberToWords.AmountToWords(nonDeductibleAmount, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#TAXNONDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(nonDeductibleAmount, currency)).
Replace("#COMMENTONE", commentOne).
Replace("#COMMENTTWO", commentTwo).
Replace("#COMMENTTHREE", commentThree).
Replace("#ACCOUNTDESC", accountDesc).
Replace("#COSTCENTREDESC", costcentreDesc).
Replace("#FIELDNAME", fieldName).
Replace("#RECIPIENTNAME", recipientName);
}
// if we are summing up donations on the same date
else
{
if ((dateEntered != prevDateEntered) && (prevDateEntered != DateTime.MaxValue))
{
if (prevgifttype == MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT);
}
else if (prevgifttype == MFinanceConstants.GIFT_TYPE_GIFT)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND);
}
GetUnitLabels(prevCurrency, ref MajorUnitSingular, ref MajorUnitPlural, ref MinorUnitSingular, ref MinorUnitPlural);
rowTexts += RowTemplate.
Replace("#DONATIONDATE", prevDateEntered.ToString("dd.MM.yyyy")).
Replace("#AMOUNTCURRENCY", prevCurrency).
Replace("#AMOUNTINWORDS",
NumberToWords.AmountToWords(prevAmount, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#AMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmount, prevCurrency)).
Replace("#TAXDEDUCTAMOUNTINWORDS",
NumberToWords.AmountToWords(prevAmountTaxDeduct, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular,
MinorUnitPlural)).
Replace("#TAXDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmountTaxDeduct, prevCurrency)).
Replace("#TAXNONDEDUCTAMNTINWORDS",
NumberToWords.AmountToWords(prevAmountNonDeduct, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular,
MinorUnitPlural)).
Replace("#TAXNONDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmountNonDeduct, prevCurrency));
prevAmount = amount;
if (TaxDeductiblePercentageEnabled)
{
prevAmountTaxDeduct = taxDeductibleAmount;
prevAmountNonDeduct = nonDeductibleAmount;
}
}
else
{
prevAmount += amount;
if (TaxDeductiblePercentageEnabled)
{
prevAmountTaxDeduct += taxDeductibleAmount;
prevAmountNonDeduct += nonDeductibleAmount;
}
}
prevCurrency = currency;
prevDateEntered = dateEntered;
prevgifttype = gifttype;
}
}
// only used when we are summing up donations on the same date
if (prevDateEntered != DateTime.MaxValue)
{
RowTemplate = OrigRowTemplate;
if (prevgifttype == MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT);
}
else if (prevgifttype == MFinanceConstants.GIFT_TYPE_GIFT)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND);
}
GetUnitLabels(prevCurrency, ref MajorUnitSingular, ref MajorUnitPlural, ref MinorUnitSingular, ref MinorUnitPlural);
rowTexts += RowTemplate.
Replace("#DONATIONDATE", prevDateEntered.ToString("dd.MM.yyyy")).
Replace("#AMOUNTCURRENCY", prevCurrency).
Replace("#AMOUNTINWORDS",
NumberToWords.AmountToWords(prevAmount, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#AMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmount, prevCurrency)).
Replace("#TAXDEDUCTAMOUNTINWORDS",
NumberToWords.AmountToWords(prevAmountTaxDeduct, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#TAXDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmountTaxDeduct, prevCurrency)).
Replace("#TAXNONDEDUCTAMNTINWORDS",
NumberToWords.AmountToWords(prevAmountNonDeduct, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#TAXNONDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmountNonDeduct, prevCurrency));
prevAmount = 0.0M;
if (TaxDeductiblePercentageEnabled)
{
prevAmountTaxDeduct = 0.0M;
prevAmountNonDeduct = 0.0M;
}
}
msg = msg.Replace("#OVERALLAMOUNTCURRENCY", ABaseCurrency).
Replace("#OVERALLAMOUNT", StringHelper.FormatUsingCurrencyCode(sum, ABaseCurrency)).
Replace("#OVERALLTAXDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(sumTaxDeduct, ABaseCurrency)).
Replace("#OVERALLTAXNONDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(sumNonDeduct, ABaseCurrency));
if ((ADonations.Rows.Count == 1) && msg.Contains("#DONATIONDATE"))
{
// this is a receipt for just one gift
msg = msg.Replace("#DONATIONDATE", Convert.ToDateTime(ADonations.Rows[0]["DateEntered"]).ToString("dd.MM.yyyy"));
}
GetUnitLabels(ABaseCurrency, ref MajorUnitSingular, ref MajorUnitPlural, ref MinorUnitSingular, ref MinorUnitPlural);
msg =
msg.Replace("#TOTALAMOUNTINWORDS",
NumberToWords.AmountToWords(sum, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#OVERALLTAXDEDUCTAMNTINWORDS",
NumberToWords.AmountToWords(sumTaxDeduct, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural)).
Replace("#OVERALLTAXNONDEDUCTAMNTINWORDS",
NumberToWords.AmountToWords(sumNonDeduct, MajorUnitSingular, MajorUnitPlural, MinorUnitSingular, MinorUnitPlural));
return msg.Replace("#ROWTEMPLATE", rowTexts);
}
private static bool AddFieldToTable(long AFieldKey, ref DataTable AFieldsTable, ref TDBTransaction ATransaction)
{
// First check if the unit is already in the table
foreach (DataRow Row in AFieldsTable.Rows)
{
if ((long)Row[UNIT_KEY] == AFieldKey)
{
return(true);
}
}
bool IsLedger = false;
PUnitTable UnitTable;
PPartnerTypeTable PartnerTypeTable;
StringCollection FieldList = new StringCollection();
FieldList.Add(PUnitTable.GetUnitNameDBName());
UnitTable = PUnitAccess.LoadByPrimaryKey(AFieldKey, FieldList, ATransaction);
PartnerTypeTable = PPartnerTypeAccess.LoadViaPPartner(AFieldKey, ATransaction);
foreach (PPartnerTypeRow PartnerTypeRow in PartnerTypeTable.Rows)
{
if (PartnerTypeRow.TypeCode == "LEDGER")
{
IsLedger = true;
break;
}
}
if (!IsLedger)
{
return(false);
}
if (UnitTable.Rows.Count > 0)
{
DataRow NewRow = AFieldsTable.NewRow();
NewRow[SELECTION] = false;
NewRow[UNIT_KEY] = AFieldKey;
NewRow[UNIT_NAME] = UnitTable.Rows[0][PUnitTable.GetUnitNameDBName()];
NewRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(NewRow);
}
return(true);
}
/// <summary>
/// This array of CostCentres may contain some funds that should not be processed
/// through ICH.
/// I need to process those here, and remove them from the list.
///
/// </summary>
private static StringDictionary GetDestinationAccountCodes(int ALedgerNumber, ACostCentreTable CostCentreTbl, TDBTransaction ATransaction)
{
AAccountPropertyTable AccountPropertyTbl = new AAccountPropertyTable();
AAccountPropertyRow TemplateRow = AccountPropertyTbl.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.PropertyCode = "CLEARING-ACCOUNT-FOR-CC";
AccountPropertyTbl = AAccountPropertyAccess.LoadUsingTemplate(TemplateRow, ATransaction);
StringDictionary ReportingAccountForCC = new StringDictionary();
foreach (AAccountPropertyRow Row in AccountPropertyTbl.Rows)
{
String[] CcList = Row.PropertyValue.Split(',');
foreach (String CC in CcList)
{
ReportingAccountForCC.Add(CC, Row.AccountCode);
}
}
foreach (DataRow UntypedCCRow in CostCentreTbl.Rows)
{
ACostCentreRow CostCentreRow = (ACostCentreRow)UntypedCCRow;
if (!ReportingAccountForCC.ContainsKey(CostCentreRow.CostCentreCode))
{
ReportingAccountForCC.Add(CostCentreRow.CostCentreCode, MFinanceConstants.ICH_ACCT_ICH);
}
}
return ReportingAccountForCC;
}
/// <summary>
/// run the report
/// </summary>
private static void Run(string ASessionID, string AReportID, TRptDataCalculator ADatacalculator, TParameterList AParameterList)
{
// need to initialize the database session
TSession.InitThread(ASessionID);
TDataBase db = DBAccess.Connect("TReportGeneratorWebConnector");
TDBTransaction Transaction = new TDBTransaction();
bool Success = false;
bool Submit = true;
string HTMLOutput = String.Empty;
HtmlDocument HTMLDocument = new HtmlDocument();
string ErrorMessage = String.Empty;
try
{
db.ReadTransaction(ref Transaction,
delegate
{
Exception myException = null;
if (ADatacalculator.GenerateResult(ref AParameterList, ref HTMLOutput, out HTMLDocument, ref ErrorMessage, ref myException, Transaction))
{
Success = true;
}
else
{
TLogging.Log(ErrorMessage);
}
});
}
catch (Exception Exc)
{
TLogging.Log("Problem calculating report: " + Exc.ToString());
TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile);
Success = false;
ErrorMessage = Exc.Message;
}
/*
* if (TDBExceptionHelper.IsTransactionSerialisationException(FException))
* {
* // do nothing - we want this exception to bubble up
* }
* else if (FException is Exception && FException.InnerException is EOPDBException)
* {
* EOPDBException DbExc = (EOPDBException)FException.InnerException;
*
* if (DbExc.InnerException is Exception)
* {
* if (DbExc.InnerException is PostgresException)
* {
* PostgresException PgExc = (PostgresException)DbExc.InnerException;
*
* if (PgExc.SqlState == "57014") // SQL statement timeout problem
* {
* FErrorMessage = Catalog.GetString(
* "Error - Database took too long to respond. Try different parameters to return fewer results.");
* }
* }
* else
* {
* FErrorMessage = DbExc.InnerException.Message;
* }
*
* FException = null;
* }
* }
*/
try
{
// store the report result
db.WriteTransaction(ref Transaction,
ref Submit,
delegate
{
// delete report results that are expired.
string sql = "DELETE FROM PUB_s_report_result WHERE s_valid_until_d < NOW()";
db.ExecuteNonQuery(sql, Transaction);
// TODO: only keep maximum of 10 report results per user (s_created_by_c)
// store success, store parameter list, store html document
SReportResultTable table = new SReportResultTable();
SReportResultRow row = table.NewRowTyped();
row.ReportId = AReportID;
row.SessionId = TSession.GetSessionID();
row.ValidUntil = DateTime.Now.AddHours(12);
row.ParameterList = AParameterList.ToJson();
row.ResultHtml = HTMLOutput;
row.Success = Success;
row.ErrorMessage = ErrorMessage;
table.Rows.Add(row);
SReportResultAccess.SubmitChanges(table, Transaction);
Submit = true;
});
}
catch (Exception Exc)
{
TLogging.Log("Problem storing report result: " + Exc.ToString());
TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile);
Success = false;
ErrorMessage = Exc.Message;
}
db.CloseDBConnection();
TProgressTracker.FinishJob(AReportID);
}
/// <summary>
/// Reads from the table holding all the fees charged for this month and generates a GL batch from it.
/// Relates to gl2150.p
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="APeriodNumber"></param>
/// <param name="APrintReport"></param>
/// <param name="ADBTransaction"></param>
/// <param name="AVerificationResult"></param>
/// <returns></returns>
private static bool GenerateAdminFeeBatch(int ALedgerNumber,
int APeriodNumber,
bool APrintReport,
TDBTransaction ADBTransaction,
ref TVerificationResultCollection AVerificationResult
)
{
bool IsSuccessful = false;
bool CreatedSuccessfully = false;
decimal TransactionAmount;
string DrAccountCode;
string DestCostCentreCode = string.Empty;
string DestAccountCode = string.Empty;
string FeeDescription = string.Empty;
decimal DrFeeTotal = 0;
bool DrCrIndicator = true;
//Error handling
string ErrorContext = String.Empty;
string ErrorMessage = String.Empty;
//Set default type as non-critical
TResultSeverity ErrorType = TResultSeverity.Resv_Noncritical;
/* Make a temporary table to hold totals for gifts going to
* each account. */
GLStewardshipCalculationTDSCreditFeeTotalTable CreditFeeTotalDT = new GLStewardshipCalculationTDSCreditFeeTotalTable();
//int x = CreditFeeTotalDT.Count;
/* Retrieve info on the ledger. */
ALedgerTable AvailableLedgers = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ADBTransaction);
ALedgerRow LedgerRow = (ALedgerRow)AvailableLedgers.Rows[0];
try
{
/* Check that we have not closed all periods for the year yet.
* (Not at the provisional year end point) */
if (LedgerRow.ProvisionalYearEndFlag)
{
//Petra ErrorCode = GL0071
ErrorContext = Catalog.GetString("Generate Admin Fee Batch");
ErrorMessage = String.Format(Catalog.GetString(
"Cannot progress as Ledger {0} is at the provisional year-end point"), ALedgerNumber);
ErrorType = TResultSeverity.Resv_Critical;
throw new System.InvalidOperationException(ErrorMessage);
}
/* 0003 Finds for ledger base currency format, for report currency format */
ACurrencyTable CurrencyInfo = ACurrencyAccess.LoadByPrimaryKey(LedgerRow.BaseCurrency, ADBTransaction);
ACurrencyRow CurrencyRow = (ACurrencyRow)CurrencyInfo.Rows[0];
/* 0001 Extract number of decimal places */
string NumericFormat = CurrencyRow.DisplayFormat;
int NumDecPlaces = THelperNumeric.CalcNumericFormatDecimalPlaces(NumericFormat);
/* Create the journal to create the fee transactions in, if there are
* fees to charge.
* NOTE: if the date in the processed fee table is ? then that fee
* hasn't been processed. */
AProcessedFeeTable ProcessedFeeDataTable = new AProcessedFeeTable();
string sqlStmt = String.Format("SELECT * FROM {0} WHERE {1} = ? AND {2} = ? AND {3} IS NULL AND {4} <> 0 ORDER BY {5}, {6}",
AProcessedFeeTable.GetTableDBName(),
AProcessedFeeTable.GetLedgerNumberDBName(),
AProcessedFeeTable.GetPeriodNumberDBName(),
AProcessedFeeTable.GetProcessedDateDBName(),
AProcessedFeeTable.GetPeriodicAmountDBName(),
AProcessedFeeTable.GetFeeCodeDBName(),
AProcessedFeeTable.GetCostCentreCodeDBName()
);
OdbcParameter[] parameters = new OdbcParameter[2];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("PeriodNumber", OdbcType.Int);
parameters[1].Value = APeriodNumber;
DBAccess.GDBAccessObj.SelectDT(ProcessedFeeDataTable, sqlStmt, ADBTransaction, parameters, -1, -1);
if (ProcessedFeeDataTable.Count == 0)
{
if (TLogging.DebugLevel > 0)
{
TLogging.Log("No fees to charge were found");
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Admin Fee Batch"),
String.Format(Catalog.GetString("No admin fees charged in period {0}."), APeriodNumber),
TResultSeverity.Resv_Status));
}
IsSuccessful = true;
}
else
{
//Post to Ledger - Ln 132
//****************4GL Transaction Starts Here********************
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
APeriodNumber,
ADBTransaction);
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
// Create a Batch. If no fees are to be charged, I'll delete this batch later.
GLBatchTDS AdminFeeDS = TGLPosting.CreateABatch(ALedgerNumber, Catalog.GetString(
"Admin Fees & Grants"), 0, AccountingPeriodRow.PeriodEndDate);
ABatchRow BatchRow = AdminFeeDS.ABatch[0];
AJournalRow JournalRow = AdminFeeDS.AJournal.NewRowTyped();
JournalRow.LedgerNumber = ALedgerNumber;
JournalRow.BatchNumber = BatchRow.BatchNumber;
JournalRow.JournalNumber = ++BatchRow.LastJournal;
JournalRow.JournalDescription = BatchRow.BatchDescription;
JournalRow.SubSystemCode = MFinanceConstants.SUB_SYSTEM_GL;
JournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
JournalRow.TransactionCurrency = LedgerRow.BaseCurrency;
JournalRow.ExchangeRateToBase = 1;
JournalRow.DateEffective = AccountingPeriodRow.PeriodEndDate;
JournalRow.JournalPeriod = APeriodNumber;
AdminFeeDS.AJournal.Rows.Add(JournalRow);
//
// Generate the transactions
//
/* M009 Changed the following loop for Petra 2.1 design changes. a_processed_fee
* now has a record for each gift detail so it is necessary to sum up all the
* totals for each fee code/cost centre so that only one transaction is posted
* for each */
int GLJournalNumber = JournalRow.JournalNumber;
int GLTransactionNumber = 0;
string CurrentFeeCode = string.Empty;
string CostCentreCode = string.Empty;
string CostCentreCodeDBName = AProcessedFeeTable.GetCostCentreCodeDBName();
for (int i = 0; i < ProcessedFeeDataTable.Count; i++)
{
AProcessedFeeRow pFR = (AProcessedFeeRow)ProcessedFeeDataTable.Rows[i];
if (CurrentFeeCode != pFR.FeeCode)
{
CurrentFeeCode = pFR.FeeCode;
// Find first
AFeesPayableTable FeesPayableTable = AFeesPayableAccess.LoadByPrimaryKey(ALedgerNumber, CurrentFeeCode, ADBTransaction);
if (FeesPayableTable.Count > 0) //if null try receivables instead
{
AFeesPayableRow FeesPayableRow = (AFeesPayableRow)FeesPayableTable.Rows[0];
DrAccountCode = FeesPayableRow.DrAccountCode;
DestCostCentreCode = FeesPayableRow.CostCentreCode;
DestAccountCode = FeesPayableRow.AccountCode;
FeeDescription = FeesPayableRow.FeeDescription;
}
else
{
AFeesReceivableTable FeesReceivableTable = AFeesReceivableAccess.LoadByPrimaryKey(ALedgerNumber,
CurrentFeeCode,
ADBTransaction);
if (FeesReceivableTable.Count > 0)
{
AFeesReceivableRow FeesReceivableRow = (AFeesReceivableRow)FeesReceivableTable.Rows[0];
DrAccountCode = FeesReceivableRow.DrAccountCode;
DestCostCentreCode = FeesReceivableRow.CostCentreCode;
DestAccountCode = FeesReceivableRow.AccountCode;
FeeDescription = FeesReceivableRow.FeeDescription;
}
else
{
//Petra error: X_0007
ErrorContext = Catalog.GetString("Generate Transactions");
ErrorMessage =
String.Format(Catalog.GetString(
"Unable to access information for Fee Code '{1}' in either the Fees Payable & Receivable Tables for Ledger {0}"),
ALedgerNumber, CurrentFeeCode);
ErrorType = TResultSeverity.Resv_Critical;
throw new System.InvalidOperationException(ErrorMessage);
}
}
DrFeeTotal = 0;
//Get all the distinct CostCentres
DataView CostCentreView = ProcessedFeeDataTable.DefaultView;
CostCentreView.Sort = CostCentreCodeDBName;
CostCentreView.RowFilter = string.Format("{0} = '{1}'", AProcessedFeeTable.GetFeeCodeDBName(), CurrentFeeCode);
DataTable ProcessedFeeCostCentresTable = CostCentreView.ToTable(true, CostCentreCodeDBName);
foreach (DataRow r in ProcessedFeeCostCentresTable.Rows)
{
CostCentreCode = r[0].ToString();
DataView view = ProcessedFeeDataTable.DefaultView;
view.Sort = CostCentreCodeDBName;
view.RowFilter = string.Format("{0} = '{1}' AND {2} = '{3}'",
AProcessedFeeTable.GetFeeCodeDBName(),
CurrentFeeCode,
CostCentreCodeDBName,
CostCentreCode);
//ProcessedFeeDataTable2 = ProcessedFeeDataTable2.Clone();
DataTable ProcessedFeeDataTable2 = view.ToTable();
Int32 FeeCodeRowCount = ProcessedFeeDataTable2.Rows.Count;
for (int j = 0; j < FeeCodeRowCount; j++)
{
DataRow pFR2 = ProcessedFeeDataTable2.Rows[j];
DrFeeTotal = DrFeeTotal + Math.Round(Convert.ToDecimal(
pFR2[AProcessedFeeTable.GetPeriodicAmountDBName()]), NumDecPlaces); //pFR2.PeriodicAmount; //ROUND(pFR2.PeriodicAmount,
// lv_dp)
if (j == (FeeCodeRowCount - 1)) //implies last of the CostCentre rows for this feecode
{
if (DrFeeTotal != 0)
{
if (DrFeeTotal < 0)
{
DrCrIndicator = false; //Credit
DrFeeTotal = -DrFeeTotal;
}
else
{
DrCrIndicator = true; //Debit
//lv_dr_fee_total remains unchanged
}
/*
* Generate the transaction to deduct the fee amount from the source cost centre. (Expense leg)
*/
//RUN gl1130o.p -> gl1130.i
//Create a transaction
if (!TGLPosting.CreateATransaction(AdminFeeDS,
ALedgerNumber,
BatchRow.BatchNumber,
GLJournalNumber,
"Fee: " + FeeDescription + " (" + CurrentFeeCode + ")",
DrAccountCode,
CostCentreCode,
DrFeeTotal,
AccountingPeriodRow.PeriodEndDate,
DrCrIndicator,
"AG",
true,
DrFeeTotal,
out GLTransactionNumber))
{
ErrorContext = Catalog.GetString("Generating the Admin Fee batch");
ErrorMessage =
String.Format(Catalog.GetString(
"Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."),
ALedgerNumber,
BatchRow.BatchNumber,
GLJournalNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
DrFeeTotal = 0;
}
}
}
}
}
/* Mark each fee entry as processed. */
pFR.ProcessedDate = DateTime.Today.Date;
pFR.Timestamp =
(DateTime.Today.TimeOfDay.Hours * 3600 + DateTime.Today.TimeOfDay.Minutes * 60 + DateTime.Today.TimeOfDay.Seconds);
/* Add the charges on this account to the fee total,
* creating an entry if necessary. (This is for the income total)
*/
GLStewardshipCalculationTDSCreditFeeTotalRow CreditFeeTotalRow = (GLStewardshipCalculationTDSCreditFeeTotalRow)
CreditFeeTotalDT.Rows.Find(new object[] { DestCostCentreCode,
DestAccountCode });
if (CreditFeeTotalRow != null)
{
CreditFeeTotalRow.TransactionAmount += Math.Round(pFR.PeriodicAmount, NumDecPlaces);
}
else
{
CreditFeeTotalRow = CreditFeeTotalDT.NewRowTyped();
CreditFeeTotalRow.CostCentreCode = DestCostCentreCode;
CreditFeeTotalRow.AccountCode = DestAccountCode;
CreditFeeTotalRow.TransactionAmount = Math.Round(pFR.PeriodicAmount, NumDecPlaces);
CreditFeeTotalDT.Rows.Add(CreditFeeTotalRow);
}
}
/* Generate the transaction to credit the fee amounts to
* the destination accounts. (Income leg)
*/
for (int k = 0; k < CreditFeeTotalDT.Count; k++)
{
GLStewardshipCalculationTDSCreditFeeTotalRow cFT = (GLStewardshipCalculationTDSCreditFeeTotalRow)
CreditFeeTotalDT.Rows[k];
if (cFT.TransactionAmount < 0)
{
/* The case of a negative gift total should be very rare.
* It would only happen if, for instance, the was only
* a reversal but no new gifts for a certain ledger.
*/
DrCrIndicator = true; //Debit
TransactionAmount = -cFT.TransactionAmount;
}
else
{
DrCrIndicator = false; //Credit
TransactionAmount = cFT.TransactionAmount;
}
/* 0002 - Ok for it to be 0 as just a correction */
if (cFT.TransactionAmount != 0)
{
GLTransactionNumber = 0;
CreatedSuccessfully = TGLPosting.CreateATransaction(AdminFeeDS,
ALedgerNumber,
BatchRow.BatchNumber,
JournalRow.JournalNumber,
"Collected admin charges",
cFT.AccountCode,
cFT.CostCentreCode,
TransactionAmount,
AccountingPeriodRow.PeriodEndDate,
DrCrIndicator,
"AG",
true,
TransactionAmount,
out GLTransactionNumber);
}
}
TVerificationResultCollection Verification = null;
/* check that something has been posted - we know this if the IsSuccessful flag is still false */
if (!CreatedSuccessfully)
{
IsSuccessful = true;
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Admin Fee Batch"),
String.Format(Catalog.GetString("No admin fees charged in period ({0})."), APeriodNumber),
TResultSeverity.Resv_Status));
// An empty GL Batch now exists, which I need to delete.
//
TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection();
TGLPosting.DeleteGLBatch(
ALedgerNumber,
BatchRow.BatchNumber,
out BatchCancelResult);
AVerificationResult.AddCollection(BatchCancelResult);
}
else
{
//Post the batch just created
GLBatchTDSAccess.SubmitChanges(AdminFeeDS);
IsSuccessful = TGLPosting.PostGLBatch(ALedgerNumber, BatchRow.BatchNumber, out Verification);
if (IsSuccessful)
{
AProcessedFeeAccess.SubmitChanges(ProcessedFeeDataTable, ADBTransaction);
}
}
if (!TVerificationHelper.IsNullOrOnlyNonCritical(Verification))
{
//Petra error: GL0067
ErrorContext = Catalog.GetString("Posting Admin Fee Batch");
ErrorMessage = String.Format(Catalog.GetString("The posting of the admin fee batch failed."));
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
//End of Transaction block in 4GL
/* Print the Admin Fee Calculations report, if requested */
if (APrintReport && IsSuccessful)
{
//TODO
}
}
}
catch (InvalidOperationException ex)
{
AVerificationResult.Add(new TVerificationResult(ErrorContext, ex.Message, ErrorType));
IsSuccessful = false;
}
catch (Exception ex)
{
ErrorContext = Catalog.GetString("Generate Admin Fee Batch");
ErrorMessage = String.Format(Catalog.GetString("Error while generating admin fee batch for Ledger {0}:" +
Environment.NewLine + Environment.NewLine + ex.ToString()),
ALedgerNumber
);
ErrorType = TResultSeverity.Resv_Critical;
AVerificationResult.Add(new TVerificationResult(ErrorContext, ErrorMessage, ErrorType));
IsSuccessful = false;
}
return IsSuccessful;
}
public static TSubmitChangesResult SaveData(string ATablename,
ref TTypedDataTable ASubmitTable, out TVerificationResultCollection AVerificationResult,
TDBTransaction AWriteTransaction)
{
AVerificationResult = null;
// TODO: check write permissions
string context = string.Format("SaveData {0}", SharedConstants.MODULE_ACCESS_MANAGER);
if (ASubmitTable != null)
{
AVerificationResult = new TVerificationResultCollection();
try
{
if (ATablename == AAccountingPeriodTable.GetTableDBName())
{
AAccountingPeriodAccess.SubmitChanges((AAccountingPeriodTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
ACurrencyAccess.SubmitChanges((ACurrencyTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
TSecurityChecks.CheckUserModulePermissions(
string.Format("AND({0},{1})", SharedConstants.PETRAGROUP_FINANCE1, SharedConstants.PETRAMODULE_FINEXRATE),
context);
ADailyExchangeRateAccess.SubmitChanges((ADailyExchangeRateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
// AlanP: I don't think this is used any more. There is a TDS Save method instead
ACorporateExchangeRateAccess.SubmitChanges((ACorporateExchangeRateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
ACurrencyLanguageAccess.SubmitChanges((ACurrencyLanguageTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
AFeesPayableAccess.SubmitChanges((AFeesPayableTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesPayableList.ToString());
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
AFeesReceivableAccess.SubmitChanges((AFeesReceivableTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesReceivableList.ToString());
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the gift batch screens, which use a TDS
AGiftBatchAccess.SubmitChanges((AGiftBatchTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
AJournalAccess.SubmitChanges((AJournalTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ARecurringJournalTable.GetTableDBName())
{
// This method is called from Submit Recurring GL Batch form - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
ARecurringJournalAccess.SubmitChanges((ARecurringJournalTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Testing - please do not remove
ALedgerAccess.SubmitChanges((ALedgerTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
AAnalysisTypeAccess.SubmitChanges((AAnalysisTypeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ASuspenseAccountTable.GetTableDBName())
{
ASuspenseAccountAccess.SubmitChanges((ASuspenseAccountTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.SuspenseAccountList.ToString());
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
PcAttendeeAccess.SubmitChanges((PcAttendeeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcConferenceTable.GetTableDBName())
{
PcConferenceAccess.SubmitChanges((PcConferenceTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
PcConferenceCostAccess.SubmitChanges((PcConferenceCostTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
PcEarlyLateAccess.SubmitChanges((PcEarlyLateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
PcSupplementAccess.SubmitChanges((PcSupplementTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
PcDiscountAccess.SubmitChanges((PcDiscountTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
ValidateInternationalPostalType(ref AVerificationResult, ASubmitTable);
ValidateInternationalPostalTypeManual(ref AVerificationResult, ASubmitTable);
if (TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult))
{
PInternationalPostalTypeAccess.SubmitChanges((PInternationalPostalTypeTable)ASubmitTable, AWriteTransaction);
}
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
PtApplicationTypeAccess.SubmitChanges((PtApplicationTypeTable)ASubmitTable, AWriteTransaction);
// mark dependent lists for needing to be refreshed since there was a change in base list
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.EventApplicationTypeList.ToString());
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.FieldApplicationTypeList.ToString());
}
else if (ATablename == PFormTable.GetTableDBName())
{
PFormAccess.SubmitChanges((PFormTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PFormalityTable.GetTableDBName())
{
PFormalityAccess.SubmitChanges((PFormalityTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
PMailingAccess.SubmitChanges((PMailingTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
PPartnerGiftDestinationAccess.SubmitChanges((PPartnerGiftDestinationTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
PmDocumentTypeAccess.SubmitChanges((PmDocumentTypeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
SGroupAccess.SubmitChanges((SGroupTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SSystemDefaultsTable.GetTableDBName())
{
SSystemDefaultsAccess.SubmitChanges((SSystemDefaultsTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName())
{
SSystemDefaultsGuiAccess.SubmitChanges((SSystemDefaultsGuiTable)ASubmitTable, AWriteTransaction);
}
else
{
throw new EOPAppException("TCommonDataReader.SaveData: unknown table '" + ATablename + "'");
}
}
catch (Exception Exc)
{
AVerificationResult.Add(
new TVerificationResult(null, "Cannot SubmitChanges:" + Environment.NewLine +
Exc.Message, "UNDEFINED", TResultSeverity.Resv_Critical));
}
}
if ((AVerificationResult != null) &&
(AVerificationResult.Count > 0))
{
// Downgrade TScreenVerificationResults to TVerificationResults in order to allow
// Serialisation (needed for .NET Remoting).
TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);
return(AVerificationResult.HasCriticalErrors ? TSubmitChangesResult.scrError : TSubmitChangesResult.scrOK);
}
return(TSubmitChangesResult.scrOK);
}
/// <summary>
/// This method is called when clients access the Daily Exchange Rate data.
/// The Daily Exchange Rate table is unusual in that it doesn't really need to hold any data because the DataSet that the client receives
/// contains all the used rates from the GL/Gift tables whether or not those rates are in the DER table itself. Any rates in the DER table
/// that are NOT used are also returned, but, of course, because they are not used anywhere they are not very inetresting!
/// Additionally, because the GL/Gift tables do not necessarily hold a time or a time that matches the same rate in the DER table, it is possible
/// for the DER table to have a rate that is used on the date but at a different time. As a result the client sometimes does not see all rows
/// from the DER table - and so has no way of deleting them.
///
/// That is the reason why we need to automatically clean the table.
///
/// But there is some value in having some 'unused' rows that are work-in-progress. So we delete everything in the DER table that
/// applies to dates older than 30 days. In the future this might become a configurable server option.
/// </summary>
private static void DoDailyExchangeRateClean()
{
DateTime PreviousDailyExchangeRateAccessTime = DateTime.UtcNow.AddHours(-24);
if (TSession.HasVariable("PreviousDailyExchangeRateAccessTime"))
{
PreviousDailyExchangeRateAccessTime = (DateTime)TSession.GetVariable("PreviousDailyExchangeRateAccessTime");
}
DateTime PreviousDailyExchangeRateCleanTime = DateTime.UtcNow.AddDays(-30);
if (TSession.HasVariable("PreviousDailyExchangeRateCleanTime"))
{
PreviousDailyExchangeRateCleanTime = (DateTime)TSession.GetVariable("PreviousDailyExchangeRateCleanTime");
}
if ((DateTime.UtcNow - PreviousDailyExchangeRateAccessTime).TotalHours > 8)
{
// Nobody has opened a DailyExchangeRate screen for 8 hours
if ((DateTime.UtcNow - PreviousDailyExchangeRateCleanTime).TotalHours > 24)
{
// It is more than 24 hours since our last clean
TDBTransaction t = new TDBTransaction();
TDataBase db = DBAccess.Connect("DoDailyExchangeRateClean");
bool bSubmissionOk = false;
db.WriteTransaction(ref t, ref bSubmissionOk,
delegate
{
string logMsg = String.Empty;
int affectedRowCount = 0;
// Standard is that we delete rows applicable to dates more than 60 days old
string criticalDate = DateTime.Now.AddDays(-60).ToString("yyyy-MM-dd");
try
{
// Our deletion rule is to delete rows where
// either the effective date is too old and we have no info about creation or modification
// or the creation date is too old and we have no info about any modification
// or the modification date is too old
// These rules ensure that if rates are added to a DB that is past its last accounting period (like SA-DB)
// we can still continue to use the DER screen to add unused rates because they will have create/modify times
// that can be long past the final accounting period because we will keep
// any row that has been modified recently, whatever the effective date or creation date
// any row that was created recently but not subsequently modified, whatever the effective date
// any row where we don't have info about create/modify but where the effective date is recent
string sql = String.Format(
"DELETE FROM PUB_{0} WHERE (({1}<'{2}') and {3} is NULL and {4} is NULL) or (({3}<'{2}') and {4} is NULL) or ({4}<'{2}')",
ADailyExchangeRateTable.GetTableDBName(),
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
criticalDate,
ADailyExchangeRateTable.GetDateCreatedDBName(),
ADailyExchangeRateTable.GetDateModifiedDBName());
affectedRowCount = db.ExecuteNonQuery(sql, t);
bSubmissionOk = true;
TSession.SetVariable("PreviousDailyExchangeRateCleanTime", DateTime.UtcNow);
}
catch (Exception ex)
{
logMsg = "An error occurred while trying to purge the Daily Exchange Rate table of 'aged' rows.";
logMsg += String.Format(" The exception message was: {0}", ex.Message);
}
if ((affectedRowCount > 0) && (logMsg == String.Empty))
{
logMsg =
String.Format("The Daily Exchange Rate table was purged of {0} entries applicable prior to ",
affectedRowCount) + criticalDate;
}
if (logMsg != String.Empty)
{
TLogging.Log(logMsg);
}
});
}
}
TSession.SetVariable("PreviousDailyExchangeRateAccessTime", DateTime.UtcNow);
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="ALocationRow"></param>
/// <param name="APartnerKey"></param>
/// <param name="AAddressAddedOrChangedPromotionDT"></param>
/// <param name="AReadTransaction"></param>
/// <param name="AUpdateLocation"></param>
/// <param name="ACreateLocation"></param>
/// <param name="ACreateLocationOtherPartnerKeys"></param>
/// <param name="AChangePromotionParametersDT"></param>
/// <returns></returns>
private static Boolean CheckLocationChange(PLocationRow ALocationRow,
Int64 APartnerKey,
ref PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable AAddressAddedOrChangedPromotionDT,
TDBTransaction AReadTransaction,
out Boolean AUpdateLocation,
out Boolean ACreateLocation,
out Int64[] ACreateLocationOtherPartnerKeys,
out PartnerAddressAggregateTDSChangePromotionParametersTable AChangePromotionParametersDT)
{
Boolean ReturnValue;
PartnerAddressAggregateTDSChangePromotionParametersRow AddressAddedPromotionRow;
ACreateLocation = false;
AUpdateLocation = false;
ACreateLocationOtherPartnerKeys = null;
AChangePromotionParametersDT = null;
// TLogging.LogAtLevel(9, "CheckLocationChange for Location " + ALocationRow.LocationKey.ToString() + ": AAddressAddedOrChangedPromotionDT.Rows.Count: " +
// AAddressAddedOrChangedPromotionDT.Rows.Count.ToString());
// Check if there is a Parameter Row for the LocationKey we are looking at
DataView AddressAddedOrChangedPromotionDV = new DataView(AAddressAddedOrChangedPromotionDT,
PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable.GetSiteKeyDBName() + " = " + ALocationRow.SiteKey.ToString() +
" AND " +
PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable.GetLocationKeyDBName() + " = " + ALocationRow.LocationKey.ToString() +
" AND " + PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable.GetLocationChangeDBName() + " = true",
"",
DataViewRowState.CurrentRows);
// No, there isn't one therefore create one
if (AddressAddedOrChangedPromotionDV.Count == 0)
{
// TLogging.LogAtLevel(9, "CheckLocationChange: Location " + ALocationRow.LocationKey.ToString() +
// ": Location has been changed and is referenced by other Partners!");
AAddressAddedOrChangedPromotionDT = new PartnerAddressAggregateTDSAddressAddedOrChangedPromotionTable(
MPartnerConstants.ADDRESSADDEDORCHANGEDPROMOTION_TABLENAME);
PartnerAddressAggregateTDSAddressAddedOrChangedPromotionRow AddressAddedOrChangedRow =
AAddressAddedOrChangedPromotionDT.NewRowTyped(false);
AddressAddedOrChangedRow.SiteKey = ALocationRow.SiteKey;
AddressAddedOrChangedRow.LocationKey = ALocationRow.LocationKey;
AddressAddedOrChangedRow.PartnerKey = APartnerKey;
AddressAddedOrChangedRow.LocationChange = true;
AddressAddedOrChangedRow.LocationAdded = false;
AddressAddedOrChangedRow.AnswerProcessedClientSide = false;
AddressAddedOrChangedRow.AnswerProcessedServerSide = false;
AAddressAddedOrChangedPromotionDT.Rows.Add(AddressAddedOrChangedRow);
#region Build ChangePromotionParameters DataTable
AChangePromotionParametersDT = new PartnerAddressAggregateTDSChangePromotionParametersTable(
MPartnerConstants.ADDRESSCHANGEPROMOTIONPARAMETERS_TABLENAME);
// Load data for all other Partners that reference the PartnerLocation
OdbcParameter[] ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Decimal, 10);
ParametersArray[0].Value = (System.Object)(APartnerKey);
ParametersArray[1] = new OdbcParameter("", OdbcType.Decimal, 10);
ParametersArray[1].Value = (System.Object)(ALocationRow.SiteKey);
ParametersArray[2] = new OdbcParameter("", OdbcType.Int);
ParametersArray[2].Value = (System.Object)(ALocationRow.LocationKey);
DataTable OtherPartnerLocationReferencesDT = DBAccess.GDBAccessObj.SelectDT(
"SELECT PUB_" + PPartnerLocationTable.GetTableDBName() + '.' + PPartnerLocationTable.GetPartnerKeyDBName() + ", " +
PPartnerTable.GetPartnerShortNameDBName() + ", " +
PPartnerTable.GetPartnerClassDBName() + ", " + PPartnerLocationTable.GetTelephoneNumberDBName() + ", " +
PPartnerLocationTable.GetLocationTypeDBName() + ' ' + "FROM PUB_" + PPartnerTable.GetTableDBName() + " INNER JOIN PUB_" +
PPartnerLocationTable.GetTableDBName() + " ON PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() +
" = PUB_" + PPartnerLocationTable.GetTableDBName() + '.' + PPartnerLocationTable.GetPartnerKeyDBName() + ' ' + "WHERE PUB_" +
PPartnerLocationTable.GetTableDBName() + '.' + PPartnerLocationTable.GetPartnerKeyDBName() + " <> ? " + "AND " +
PPartnerLocationTable.GetSiteKeyDBName() + " = ? " + "AND " + PPartnerLocationTable.GetLocationKeyDBName() + " = ?",
"OtherPartnerLocationReferencesDT", AReadTransaction, ParametersArray);
// Don't need these columns for the moment, but it would be nice to have them later on
// PPartnerLocationTable.GetSendMailDBName + ', ' +
// PPartnerLocationTable.GetDateEffectiveDBName + ', ' +
// PPartnerLocationTable.GetDateGoodUntilDBName + ', ' +
// Insert data into the ChangePromotionParameters DataTable
for (int Counter = 0; Counter <= OtherPartnerLocationReferencesDT.Rows.Count - 1; Counter += 1)
{
DataRow OtherPartnerLocationReferenceRow = OtherPartnerLocationReferencesDT.Rows[Counter];
AddressAddedPromotionRow = AChangePromotionParametersDT.NewRowTyped(false);
AddressAddedPromotionRow.SiteKey = ALocationRow.SiteKey;
AddressAddedPromotionRow.LocationKey = (Int32)ALocationRow.LocationKey;
AddressAddedPromotionRow.PartnerKey = Convert.ToInt64(
OtherPartnerLocationReferenceRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
AddressAddedPromotionRow.PartnerShortName = OtherPartnerLocationReferenceRow[PPartnerTable.GetPartnerShortNameDBName()].ToString();
AddressAddedPromotionRow.PartnerClass = OtherPartnerLocationReferenceRow[PPartnerTable.GetPartnerClassDBName()].ToString();
// AddressAddedPromotionRow.SendMail :=
// Convert.ToBoolean(OtherPartnerLocationReferenceRow[PPartnerLocationTable.GetSendMailDBName]);
// if not OtherPartnerLocationReferenceRow.IsNull(
// PPartnerLocationTable.GetDateEffectiveDBName) then
// begin
// AddressAddedPromotionRow.DateEffective :=
// Convert.ToDateTime(OtherPartnerLocationReferenceRow[PPartnerLocationTable.GetDateEffectiveDBName]);
// end;
// if not OtherPartnerLocationReferenceRow.IsNull(
// PPartnerLocationTable.GetDateGoodUntilDBName) then
// begin
// AddressAddedPromotionRow.DateGoodUntil :=
// Convert.ToDateTime(OtherPartnerLocationReferenceRow[PPartnerLocationTable.GetDateGoodUntilDBName]);
// end;
AddressAddedPromotionRow.LocationType = OtherPartnerLocationReferenceRow[PPartnerLocationTable.GetLocationTypeDBName()].ToString();
AChangePromotionParametersDT.Rows.Add(AddressAddedPromotionRow);
}
// TLogging.LogAtLevel(9, "CheckLocationChange: Location " + ALocationRow.LocationKey.ToString() + ": inserted PartnerLocation data of " +
// AChangePromotionParametersDT.Rows.Count.ToString() + " other Partners that reference this Location into AChangePromotionParametersDT!");
#endregion
ReturnValue = true;
}
else
{
// AAddressAddedOrChangedPromotionDT was passed in, holding parameters for the LocationKey we are looking at
PartnerAddressAggregateTDSAddressAddedOrChangedPromotionRow AddressAddedOrChangedRow =
(PartnerAddressAggregateTDSAddressAddedOrChangedPromotionRow)AddressAddedOrChangedPromotionDV[0].Row;
if (AddressAddedOrChangedRow.UserAnswer == "CHANGE-NONE")
{
// TLogging.LogAtLevel(9, "CheckLocationChange: AAddressAddedOrChangedPromotionDT tells me to CREATE the Location.");
ACreateLocation = true;
// Signal to calling procedure that the created location should not be
// assigned to any Partner other than the one we are currently processing.
ACreateLocationOtherPartnerKeys = new Int64[0];
AddressAddedOrChangedRow.AnswerProcessedClientSide = true;
AddressAddedOrChangedRow.AcceptChanges();
ReturnValue = true;
}
else if (AddressAddedOrChangedRow.UserAnswer.StartsWith("CHANGE-SOME"))
{
// TLogging.LogAtLevel(9, "CheckLocationChange: AAddressAddedOrChangedPromotionDT tells me to CREATE the Location and assign it to selected partners.");
ACreateLocation = true;
// Parse the UserAnswer. It's format is 'CHANGESOME:PartnerKey1;PartnerKey2;PartnerKeyN'
string[] ChangeSomeArray = AddressAddedOrChangedRow.UserAnswer.Split(":,".ToCharArray());
// Build the ACreateLocationOtherPartnerKeys array from it to
// signal to calling procedure that the created location should be
// assigned to all the Partners contained in the Array.
ACreateLocationOtherPartnerKeys = new Int64[ChangeSomeArray.Length - 1];
for (int Counter = 1; Counter <= ChangeSomeArray.Length - 1; Counter += 1)
{
ACreateLocationOtherPartnerKeys[Counter - 1] = Convert.ToInt64(ChangeSomeArray[Counter]);
}
AddressAddedOrChangedRow.AnswerProcessedClientSide = true;
AddressAddedOrChangedRow.AcceptChanges();
ReturnValue = true;
}
else if (AddressAddedOrChangedRow.UserAnswer == "CHANGE-ALL")
{
// TLogging.LogAtLevel(9, "CheckLocationChange: AAddressAddedOrChangedPromotionDT tells me to UPDATE the Location.");
AUpdateLocation = true;
AddressAddedOrChangedRow.AnswerProcessedClientSide = true;
AddressAddedOrChangedRow.AcceptChanges();
ReturnValue = true;
}
else
{
// TLogging.LogAtLevel(9, "CheckLocationChange: AAddressAddedOrChangedPromotionDT tells me to CANCEL the changing of the Location.");
AddressAddedOrChangedRow.AnswerProcessedClientSide = true;
AddressAddedOrChangedRow.AcceptChanges();
ReturnValue = false;
}
}
return ReturnValue;
}
public static void GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable,
TDBTransaction AReadTransaction)
{
AResultTable = null;
string context = string.Format("GetData {0}", SharedConstants.MODULE_ACCESS_MANAGER);
// TODO: check access permissions for the current user
// TODO: auto generate
if (ATablename == AApSupplierTable.GetTableDBName())
{
AResultTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == AApDocumentTable.GetTableDBName())
{
AResultTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == ATransactionTypeTable.GetTableDBName())
{
AResultTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
AResultTable = ACurrencyAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
AResultTable = ADailyExchangeRateAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
AResultTable = ACorporateExchangeRateAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
AResultTable = ACurrencyLanguageAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
AResultTable = AFeesPayableAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
AResultTable = AFeesReceivableAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
AResultTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
AResultTable = AGiftBatchAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
AResultTable = AJournalAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
AResultTable = ALedgerAccess.LoadAll(AReadTransaction);
}
else if (ATablename == MExtractMasterTable.GetTableDBName())
{
if (ASearchCriteria == null)
{
AResultTable = MExtractMasterAccess.LoadAll(AReadTransaction);
}
else
{
AResultTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
}
else if (ATablename == MExtractTable.GetTableDBName())
{
// it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
if (ASearchCriteria != null)
{
AResultTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
AResultTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
AResultTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
AResultTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
AResultTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
AResultTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PFormTable.GetTableDBName())
{
string[] columns = TTypedDataTable.GetColumnStringList(PFormTable.TableId);
StringCollection fieldList = new StringCollection();
for (int i = 0; i < columns.Length; i++)
{
// Do not load the template document - we don't display it and it is big!
if (columns[i] != PFormTable.GetTemplateDocumentDBName())
{
fieldList.Add(columns[i]);
}
}
AResultTable = PFormAccess.LoadAll(fieldList, AReadTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
AResultTable = PInternationalPostalTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
AResultTable = PtApplicationTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PFormalityTable.GetTableDBName())
{
AResultTable = PFormalityAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
AResultTable = PMailingAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
AResultTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
AResultTable = PmDocumentTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context);
AResultTable = SGroupAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SSystemDefaultsTable.GetTableDBName())
{
TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context);
AResultTable = SSystemDefaultsAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName())
{
AResultTable = SSystemDefaultsGuiAccess.LoadAll(AReadTransaction);
}
else
{
throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
}
// Accept row changes here so that the Client gets 'unmodified' rows
AResultTable.AcceptChanges();
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="ADataRow"></param>
/// <param name="APartnerKey"></param>
/// <param name="AReadTransaction"></param>
/// <returns></returns>
public static Boolean CheckHasPartnerLocationOtherPartnerReferences(DataRow ADataRow, Int64 APartnerKey, TDBTransaction AReadTransaction)
{
Int64 SiteKey;
Int32 LocationKey;
// Determine LocationKey
if (ADataRow.RowState != DataRowState.Deleted)
{
if (ADataRow is PLocationRow)
{
SiteKey = Convert.ToInt64(ADataRow[PLocationTable.GetSiteKeyDBName(), DataRowVersion.Original]);
LocationKey = Convert.ToInt32(ADataRow[PLocationTable.GetLocationKeyDBName(), DataRowVersion.Original]);
// SiteKey := (ADataRow as PLocationRow).SiteKey;
// LocationKey := (ADataRow as PLocationRow).LocationKey;
}
else if (ADataRow is PPartnerLocationRow)
{
SiteKey = ((PPartnerLocationRow)ADataRow).SiteKey;
LocationKey = ((PPartnerLocationRow)ADataRow).LocationKey;
}
else
{
throw new EOPAppException(
"Expected a PLocationRow or a PPartnerLocationRow for ADataRow parameter, but received other DataRow");
}
}
else
{
if (ADataRow is PLocationRow)
{
SiteKey = Convert.ToInt64(ADataRow[PLocationTable.GetSiteKeyDBName(), DataRowVersion.Original]);
LocationKey = Convert.ToInt32(ADataRow[PLocationTable.GetLocationKeyDBName(), DataRowVersion.Original]);
}
else if (ADataRow is PPartnerLocationRow)
{
SiteKey = Convert.ToInt64(ADataRow[PPartnerLocationTable.GetSiteKeyDBName(), DataRowVersion.Original]);
LocationKey = Convert.ToInt32(ADataRow[PPartnerLocationTable.GetLocationKeyDBName(), DataRowVersion.Original]);
}
else
{
throw new EOPAppException(
"Expected a PLocationRow or a PPartnerLocationRow for ADataRow parameter, but received other DataRow");
}
}
return CheckHasPartnerLocationOtherPartnerReferences(APartnerKey, SiteKey, LocationKey, AReadTransaction);
}
/// <summary>
/// return a table with gift details for the given date with donor partner keys and bank account numbers
/// </summary>
private static bool GetGiftsByDate(Int32 ALedgerNumber,
BankImportTDS AMainDS,
DateTime ADateEffective,
string ABankAccountCode,
out List <int> AGiftBatchNumbers)
{
TDataBase db = DBAccess.Connect("GetGiftsByDate");
TDBTransaction transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
// first get all gifts, even those that have no bank account associated
string stmt = TDataBase.ReadSqlFile("BankImport.GetDonationsByDate.sql");
OdbcParameter[] parameters = new OdbcParameter[3];
parameters[0] = new OdbcParameter("ALedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date);
parameters[1].Value = ADateEffective;
parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
parameters[2].Value = ABankAccountCode;
db.SelectDT(AMainDS.AGiftDetail, stmt, transaction, parameters, 0, 0);
// calculate the totals of gifts
AMainDS.AGift.Clear();
AGiftBatchNumbers = new List <int>();
foreach (BankImportTDSAGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
{
BankImportTDSAGiftRow giftRow =
(BankImportTDSAGiftRow)AMainDS.AGift.Rows.Find(new object[] { giftdetail.LedgerNumber, giftdetail.BatchNumber,
giftdetail.GiftTransactionNumber });
if (giftRow == null)
{
giftRow = AMainDS.AGift.NewRowTyped(true);
giftRow.LedgerNumber = giftdetail.LedgerNumber;
giftRow.BatchNumber = giftdetail.BatchNumber;
giftRow.GiftTransactionNumber = giftdetail.GiftTransactionNumber;
giftRow.TotalAmount = 0;
giftRow.DonorKey = giftdetail.DonorKey;
AMainDS.AGift.Rows.Add(giftRow);
}
giftRow.TotalAmount += giftdetail.GiftTransactionAmount;
if (!AGiftBatchNumbers.Contains(giftRow.BatchNumber))
{
AGiftBatchNumbers.Add(giftRow.BatchNumber);
}
}
// get PartnerKey and banking details (most important BankAccountNumber) for all donations on the given date
stmt = TDataBase.ReadSqlFile("BankImport.GetBankAccountByDate.sql");
parameters = new OdbcParameter[2];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date);
parameters[1].Value = ADateEffective;
// TODO ? parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
//parameters[2].Value = ABankAccountCode;
// There can be several donors with the same banking details
AMainDS.PBankingDetails.Constraints.Clear();
db.Select(AMainDS, stmt, AMainDS.PBankingDetails.TableName, transaction, parameters);
transaction.Rollback();
db.CloseDBConnection();
return(true);
}
/// <summary>
/// This method needs to be implemented by extracts that can't follow the default processing with just one query.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
{
Boolean ReturnValue = false;
Int32 ExtractId = -1;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref Transaction,
delegate
{
DataTable giftdetails;
string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByAmount.sql");
List <OdbcParameter>SqlParameterList = new List <OdbcParameter>();
bool AddressFilterAdded;
DataTable partnerkeys = new DataTable();
// call to derived class to retrieve parameters specific for extract
RetrieveParameters(AParameters, ref SqlStmt, ref SqlParameterList);
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
giftdetails = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return;
}
TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);
// With the result of the original query process the data and identify the partner keys for
// the extract.
partnerkeys.Columns.Add("0", typeof(Int64));
partnerkeys.Columns.Add("1", typeof(string));
partnerkeys.Columns.Add("p_site_key_n", typeof(Int64));
partnerkeys.Columns.Add("p_location_key_i", typeof(Int32));
ProcessGiftDetailRecords(giftdetails, AddressFilterAdded, AParameters, ref partnerkeys);
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction);
// create an extract with the given name in the parameters
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out ExtractId,
partnerkeys,
0,
AddressFilterAdded);
});
AExtractId = ExtractId;
return ReturnValue;
} // Run Special Treatment
private static void FindDonorKeysByBankAccount(BankImportTDS AMainDS)
{
if (AMainDS.PBankingDetails.Rows.Count == 0)
{
// First collect all IBANs in this statement
List <string> IBANs = new List <string>();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
if (transaction.Iban.Length > 0)
{
IBANs.Add(transaction.Iban);
}
}
// load all banking details by IBAN into AMainDS
if (IBANs.Count > 0)
{
string sql = "SELECT " + PPartnerBankingDetailsTable.GetPartnerKeyDBName() + " as PartnerKey, " +
"bd.* " +
"FROM " + PBankingDetailsTable.GetTableDBName() + " bd, " + PPartnerBankingDetailsTable.GetTableDBName() + " pbd " +
"WHERE pbd." + PPartnerBankingDetailsTable.GetBankingDetailsKeyDBName() + " = bd." + PBankingDetailsTable.GetBankingDetailsKeyDBName() + " " +
"AND " + BankImportTDSPBankingDetailsTable.GetIbanDBName() + " IN (";
List <OdbcParameter> parameters = new List <OdbcParameter>();
foreach (string iban in IBANs)
{
if (parameters.Count > 0)
{
sql += ",";
}
sql += "?";
OdbcParameter p = new OdbcParameter("IBAN" + parameters.Count.ToString(), OdbcType.VarChar);
p.Value = iban;
parameters.Add(p);
}
sql += ")";
TDataBase db = DBAccess.Connect("FindDonorKeysByBankAccount");
TDBTransaction transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
db.SelectDT(AMainDS.PBankingDetails, sql, transaction, parameters.ToArray());
transaction.Rollback();
db.CloseDBConnection();
}
}
AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetIbanDBName();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
// find the donor for this transaction, by his IBAN number
Int64 DonorKey = GetDonorByIBAN(AMainDS, transaction.Iban);
if (transaction.Iban.Length == 0)
{
// useful for NUnit testing for csv import: partnerkey in description
try
{
DonorKey = Convert.ToInt64(transaction.Description);
}
catch (Exception)
{
DonorKey = -1;
}
}
transaction.DonorKey = DonorKey;
}
}
/// <summary>
/// Determine best location for partner out of a list of possible locations. Or simply find best one
/// if no suggestion is made.
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="ALocationKeyList"></param>
/// <param name="APartnerLocationKeysTable"></param>
/// <param name="ATransaction"></param>
/// <returns>True if the address was found and added, otherwise false.</returns>
private static Boolean DetermineAndAddBestLocationKey(
Int64 APartnerKey,
List <TLocationPK>ALocationKeyList,
ref PPartnerLocationTable APartnerLocationKeysTable,
TDBTransaction ATransaction)
{
PPartnerLocationTable AllPartnerLocationTable;
PPartnerLocationTable FilteredPartnerLocationTable = new PPartnerLocationTable();
TLocationPK LocationPK = new TLocationPK();
PPartnerLocationRow PartnerLocationKeyRow;
PPartnerLocationRow PartnerLocationRowCopy;
TLocationPK BestLocationPK;
if (ALocationKeyList.Count == 0)
{
// no list suggested: find best address in db for this partner
BestLocationPK = TMailing.GetPartnersBestLocation(APartnerKey);
}
else if (ALocationKeyList.Count == 1)
{
// only one location suggested: take this one
BestLocationPK = ALocationKeyList[0];
}
else
{
// Process location key list related to partner.
// In order to use Calculations.DetermineBestAddress we need to first retrieve full data
// for all suggested records from the db. Therefore load all locations for this partner
// and then create a table of the ones that are suggested.
AllPartnerLocationTable = PPartnerLocationAccess.LoadViaPPartner(APartnerKey, ATransaction);
foreach (PPartnerLocationRow PartnerLocationRow in AllPartnerLocationTable.Rows)
{
LocationPK.SiteKey = PartnerLocationRow.SiteKey;
LocationPK.LocationKey = PartnerLocationRow.LocationKey;
if (ALocationKeyList.Contains(LocationPK))
{
PartnerLocationRowCopy = (PPartnerLocationRow)FilteredPartnerLocationTable.NewRow();
DataUtilities.CopyAllColumnValues(PartnerLocationRow, PartnerLocationRowCopy);
FilteredPartnerLocationTable.Rows.Add(PartnerLocationRowCopy);
}
}
BestLocationPK = Calculations.DetermineBestAddress(FilteredPartnerLocationTable);
}
// create new row, initialize it and add it to the table
if (BestLocationPK.LocationKey != -1)
{
PartnerLocationKeyRow = (PPartnerLocationRow)APartnerLocationKeysTable.NewRow();
PartnerLocationKeyRow[PPartnerLocationTable.GetPartnerKeyDBName()] = APartnerKey;
PartnerLocationKeyRow[PPartnerLocationTable.GetSiteKeyDBName()] = BestLocationPK.SiteKey;
PartnerLocationKeyRow[PPartnerLocationTable.GetLocationKeyDBName()] = BestLocationPK.LocationKey;
// only add row if it does not already exist
if (!APartnerLocationKeysTable.Rows.Contains(
new object[] { PartnerLocationKeyRow.PartnerKey, PartnerLocationKeyRow.SiteKey, PartnerLocationKeyRow.LocationKey }))
{
APartnerLocationKeysTable.Rows.Add(PartnerLocationKeyRow);
}
return true;
}
else
{
return false;
}
}
/// <summary>
/// Run extract in case the user wants to analyze receiving fields.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
private bool ProcessReceivingFields(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
{
/*Approach:
* In case of a specified "Period" only find persons
* that have a commitment record.
* In case of "Now" or "Ever" also find partners
* (persons & families) without such a commitment
* record, that match the specified criteria.
* In case of "Now" only find partners with a "Worker" type.
* (This check is dropped in case of "Ever")
* When interested in families only, also find families
* for which a member matches the specified criteria.*/
bool ReturnValue = false;
// for receiving fields first look at commitments
ReturnValue = ProcessCommitments(true, AParameters, ATransaction, out AExtractId);
if (ReturnValue == false)
{
// if result was false then rollback transaction
TExtractsHandling.FinishExtractFromListOfPartnerKeys(false);
return(ReturnValue);
}
// if only commitments need to be considered then no need to continue here
if (AParameters.Get("param_commitments_and_worker_field").IsZeroOrNull() ||
(AParameters.Get("param_commitments_and_worker_field").ToString() == "CommitmentsOnly"))
{
return(ReturnValue);
}
bool AddressFilterAdded;
string SqlStmtWorkerFieldOriginal = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByField.WorkerField.sql");
string SqlStmt;
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
string TypeCodeParameter;
// If date range was specified then only look at staff data. Otherwise look for persons and families seperately.
if (AParameters.Get("param_field_dates").ToString() == "DateRange")
{
return(ReturnValue);
}
// prepare parameter field for partner type code.
if (AParameters.Get("param_field_dates").ToString() == "DateEver")
{
TypeCodeParameter = "";
}
else
{
TypeCodeParameter = "OMER%";
}
// prepare list of selected fields
List <String> param_fields = new List <String>();
foreach (TVariant choice in AParameters.Get("param_fields").ToComposite())
{
param_fields.Add(choice.ToString());
}
if (param_fields.Count == 0)
{
throw new NoNullAllowedException("At least one option must be checked.");
}
// now add parameters to sql parameter list
SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));
SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
{
Value = AParameters.Get("param_active").ToBool()
});
SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
{
Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
});
// ----------------------------------------------------------------------------------------
// now start retrieving either families or persons whose worker field is set to given value
// ----------------------------------------------------------------------------------------
SqlStmt = SqlStmtWorkerFieldOriginal;
SqlStmt = SqlStmt.Replace("##person_or_family_table##", ", pub_p_person");
SqlStmt = SqlStmt.Replace("##person_or_family_table_name##", "pub_p_person");
SqlStmt = SqlStmt.Replace("##exclude_familiy_members_existing_in_extract##", "");
SqlStmt = SqlStmt.Replace("##worker_type##", TypeCodeParameter);
if (AParameters.Get("param_families_only").ToBool())
{
/* In case that only family records are wanted a join via family key of a person is needed
* to find families of persons. */
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_partner.p_partner_key_n = pub_p_person.p_family_key_n");
}
else
{
// in this case there will be person records in the extract
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_partner.p_partner_key_n = pub_p_person.p_partner_key_n");
}
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("getting the data from the database", TLoggingType.ToStatusBar);
DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(false);
}
TLogging.Log("preparing the extract", TLoggingType.ToStatusBar);
// create an extract with the given name in the parameters
TExtractsHandling.ExtendExtractFromListOfPartnerKeys(
AExtractId,
partnerkeys,
0,
AddressFilterAdded,
false,
false);
// ----------------------------------------------------------------------------------------
// Now start retrieving families whose worker field is set to given value and that are not
// already contained in the created extract.
// ----------------------------------------------------------------------------------------
SqlStmt = SqlStmtWorkerFieldOriginal;
// need to rebuild parameter list as statement is also loaded again and filled
SqlParameterList.Clear();
SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));
SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
{
Value = AParameters.Get("param_active").ToBool()
});
SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
{
Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
});
SqlStmt = SqlStmt.Replace("##person_or_family_table##", ", pub_p_family");
SqlStmt = SqlStmt.Replace("##person_or_family_table_name##", "pub_p_family");
SqlStmt = SqlStmt.Replace("##worker_type##", TypeCodeParameter);
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_partner.p_partner_key_n = pub_p_family.p_partner_key_n");
SqlStmt = SqlStmt.Replace("##exclude_familiy_members_existing_in_extract##",
"AND NOT EXISTS (SELECT pub_p_family.p_partner_key_n " +
" FROM pub_p_family, pub_p_person, pub_m_extract " +
" WHERE pub_p_person.p_family_key_n = pub_p_family.p_partner_key_n " +
" AND pub_m_extract.m_extract_id_i = " + AExtractId.ToString() +
" AND pub_m_extract.p_partner_key_n = pub_p_person.p_partner_key_n)");
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("getting the data from the database", TLoggingType.ToStatusBar);
partnerkeys.Clear();
partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(false);
}
TLogging.Log("preparing the extract", TLoggingType.ToStatusBar);
// create an extract with the given name in the parameters
TExtractsHandling.ExtendExtractFromListOfPartnerKeys(
AExtractId,
partnerkeys,
0,
AddressFilterAdded,
false,
false);
ReturnValue = true;
// if result was true then commit transaction, otherwise rollback
TExtractsHandling.FinishExtractFromListOfPartnerKeys(ReturnValue);
return(ReturnValue);
}
public static string FormatHtmlReceipt(
String ADonorShortName,
Int64 ADonorKey,
TPartnerClass ADonorClass,
String AGiftCurrency,
string ALocalCountryCode,
AGiftTable AGiftsThisDonor,
string AHTMLTemplateFilename,
TDBTransaction ATransaction)
{
SortedList <string, List <string>>FormValues = new SortedList <string, List <string>>();
// These are the fields that can be printed in the letter:
FormValues.Add("AdresseeShortName", new List <string>());
FormValues.Add("AdresseeTitle", new List <string>());
FormValues.Add("AdresseeFirstName", new List <string>());
FormValues.Add("AdresseeFamilyName", new List <string>());
FormValues.Add("AdresseeStreetAddress", new List <string>());
FormValues.Add("AdresseeAddress3", new List <string>());
FormValues.Add("AdresseeCity", new List <string>());
FormValues.Add("AdresseePostCode", new List <string>());
FormValues.Add("AdresseeCountry", new List <string>());
FormValues.Add("FormattedAddress", new List <string>());
FormValues.Add("DateToday", new List <string>());
FormValues.Add("DateEntered", new List <string>());
FormValues.Add("GiftAmount", new List <string>());
FormValues.Add("GiftCurrency", new List <string>());
FormValues.Add("GiftTxd", new List <string>());
FormValues.Add("RecipientShortName", new List <string>());
FormValues.Add("MotivationDetail", new List <string>());
FormValues.Add("Reference", new List <string>());
FormValues.Add("DonorComment", new List <string>());
FormValues.Add("GiftTotalAmount", new List <string>());
FormValues.Add("GiftTotalCurrency", new List <string>());
FormValues.Add("TxdTotal", new List <string>());
FormValues.Add("NonTxdTotal", new List <string>());
// Donor Name:
FormValues["AdresseeShortName"].Add(ADonorShortName);
if (ADonorClass == TPartnerClass.PERSON)
{
PPersonTable Tbl = PPersonAccess.LoadByPrimaryKey(ADonorKey, ATransaction);
if (Tbl.Rows.Count > 0)
{
FormValues["AdresseeTitle"].Add(Tbl[0].Title);
FormValues["AdresseeFirstName"].Add(Tbl[0].FirstName);
FormValues["AdresseeFamilyName"].Add(Tbl[0].FamilyName);
}
}
else if (ADonorClass == TPartnerClass.FAMILY)
{
PFamilyTable Tbl = PFamilyAccess.LoadByPrimaryKey(ADonorKey, ATransaction);
if (Tbl.Rows.Count > 0)
{
FormValues["AdresseeTitle"].Add(Tbl[0].Title);
FormValues["AdresseeFirstName"].Add(Tbl[0].FirstName);
FormValues["AdresseeFamilyName"].Add(Tbl[0].FamilyName);
}
}
else
{
FormValues["AdresseeFamilyName"].Add(ADonorShortName);
}
FormValues["DateToday"].Add(DateTime.Now.ToString("dd MMMM yyyy"));
// Donor Adress:
PLocationTable Location;
string CountryName;
if (TAddressTools.GetBestAddress(ADonorKey, out Location, out CountryName, ATransaction))
{
PLocationRow LocRow = Location[0];
FormValues["AdresseeStreetAddress"].Add(LocRow.StreetName);
FormValues["AdresseeAddress3"].Add(LocRow.Address3);
FormValues["AdresseeCity"].Add(LocRow.City);
FormValues["AdresseePostCode"].Add(LocRow.PostalCode);
if (LocRow.CountryCode != ALocalCountryCode) // Don't add the Donor's country if it's also my country:
{
FormValues["AdresseeCountry"].Add(CountryName);
}
else
{
LocRow.CountryCode = "";
}
FormValues["FormattedAddress"].Add(Calculations.DetermineLocationString(LocRow,
Calculations.TPartnerLocationFormatEnum.plfHtmlLineBreak));
}
decimal GiftTotal = 0;
decimal TxdTotal = 0;
decimal NonTxdTotal = 0;
// Details per gift:
foreach (AGiftRow GiftRow in AGiftsThisDonor.Rows)
{
String DateEntered = GiftRow.DateEntered.ToString("dd MMM yyyy");
String GiftReference = GiftRow.Reference;
AGiftDetailTable DetailTbl = AGiftDetailAccess.LoadViaAGift(
GiftRow.LedgerNumber, GiftRow.BatchNumber, GiftRow.GiftTransactionNumber, ATransaction);
foreach (AGiftDetailRow DetailRow in DetailTbl.Rows)
{
FormValues["Reference"].Add(GiftReference);
FormValues["DateEntered"].Add(DateEntered);
GiftReference = ""; // Date and Reference are one-per-gift, not per detail
DateEntered = ""; // so if this gift has several details, I'll blank the subsequent lines.
string DonorComment = "";
FormValues["GiftAmount"].Add(StringHelper.FormatUsingCurrencyCode(DetailRow.GiftTransactionAmount, AGiftCurrency));
FormValues["GiftCurrency"].Add(AGiftCurrency);
FormValues["MotivationDetail"].Add(DetailRow.MotivationDetailCode);
GiftTotal += DetailRow.GiftTransactionAmount;
if (DetailRow.TaxDeductible)
{
FormValues["GiftTxd"].Add("Y");
TxdTotal += DetailRow.GiftTransactionAmount;
}
else
{
FormValues["GiftTxd"].Add(" ");
NonTxdTotal += DetailRow.GiftTransactionAmount;
}
// Recipient Short Name:
PPartnerTable RecipientTbl = PPartnerAccess.LoadByPrimaryKey(DetailRow.RecipientKey, ATransaction);
if (RecipientTbl.Rows.Count > 0)
{
String ShortName = Calculations.FormatShortName(RecipientTbl[0].PartnerShortName, eShortNameFormat.eReverseShortname);
FormValues["RecipientShortName"].Add(ShortName);
}
if (DetailRow.CommentOneType == "Donor")
{
DonorComment += DetailRow.GiftCommentOne;
}
if (DetailRow.CommentTwoType == "Donor")
{
if (DonorComment != "")
{
DonorComment += "\r\n";
}
DonorComment += DetailRow.GiftCommentTwo;
}
if (DetailRow.CommentThreeType == "Donor")
{
if (DonorComment != "")
{
DonorComment += "\r\n";
}
DonorComment += DetailRow.GiftCommentThree;
}
if (DonorComment != "")
{
DonorComment = "Comment: " + DonorComment;
}
FormValues["DonorComment"].Add(DonorComment);
} // foreach GiftDetail
} // foreach Gift
FormValues["GiftTotalAmount"].Add(StringHelper.FormatUsingCurrencyCode(GiftTotal, AGiftCurrency));
FormValues["GiftTotalCurrency"].Add(AGiftCurrency);
FormValues["TxdTotal"].Add(StringHelper.FormatUsingCurrencyCode(TxdTotal, AGiftCurrency));
FormValues["NonTxdTotal"].Add(StringHelper.FormatUsingCurrencyCode(NonTxdTotal, AGiftCurrency));
return TFormLettersTools.PrintSimpleHTMLLetter(AHTMLTemplateFilename, FormValues);
}
/// <summary>
/// Add persons or families to extract that have commitments to specified receiving/sending fields
/// </summary>
/// <param name="AProcessReceivingFields"></param>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
private bool ProcessCommitments(bool AProcessReceivingFields, TParameterList AParameters,
TDBTransaction ATransaction, out int AExtractId)
{
bool ReturnValue = false;
bool AddressFilterAdded;
string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByField.Commitment.sql");
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
// need to set initial value here in case method needs to return before value is set
AExtractId = -1;
// prepare list of selected fields
List <String> param_fields = new List <String>();
foreach (TVariant choice in AParameters.Get("param_fields").ToComposite())
{
param_fields.Add(choice.ToString());
}
if (param_fields.Count == 0)
{
throw new NoNullAllowedException("At least one option must be checked.");
}
// now add parameters to sql parameter list
SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));
SqlParameterList.Add(new OdbcParameter("param_from_date_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_from_date").IsZeroOrNull()
});
SqlParameterList.Add(new OdbcParameter("param_from_date", OdbcType.Date)
{
Value = AParameters.Get("param_from_date").ToDate()
});
SqlParameterList.Add(new OdbcParameter("param_until_date_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_until_date").IsZeroOrNull()
});
SqlParameterList.Add(new OdbcParameter("param_until_date", OdbcType.Date)
{
Value = AParameters.Get("param_until_date").ToDate()
});
SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
{
Value = AParameters.Get("param_active").ToBool()
});
SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
{
Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
});
if (AProcessReceivingFields)
{
// for receiving fields target field table field needs to be used
SqlStmt = SqlStmt.Replace("##sending_or_receiving_field##", "pm_receiving_field_n");
}
else
{
// for sending fields home office table field needs to be used
SqlStmt = SqlStmt.Replace("##sending_or_receiving_field##", "pm_home_office_n");
}
if (AParameters.Get("param_families_only").ToBool())
{
/* In case that only family records are wanted a join via family key of a person is needed
* to find families of persons. */
SqlStmt = SqlStmt.Replace("##person_table##", ", pub_p_person");
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_person.p_partner_key_n = pub_pm_staff_data.p_partner_key_n" +
" AND pub_p_partner.p_partner_key_n = pub_p_person.p_family_key_n ");
}
else
{
// in this case there will be person records in the extract
SqlStmt = SqlStmt.Replace("##person_table##", "");
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_partner.p_partner_key_n = pub_pm_staff_data.p_partner_key_n");
}
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("getting the data from the database", TLoggingType.ToStatusBar);
DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(false);
}
TLogging.Log("preparing the extract", TLoggingType.ToStatusBar);
// create an extract with the given name in the parameters
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out AExtractId,
partnerkeys,
0,
AddressFilterAdded,
false);
return(ReturnValue);
}
/// <summary>
/// To build a CSV list of accounts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="AAccountRowFirst"></param>
/// <param name="DBTransaction"></param>
/// <param name="AChildAccounts"></param>
/// <param name="AVerificationResult"></param>
private static void BuildChildAccountList(int ALedgerNumber,
AAccountRow AAccountRowFirst,
TDBTransaction DBTransaction,
ref string AChildAccounts,
ref TVerificationResultCollection AVerificationResult)
{
//Return value
string AccountCode = AAccountRowFirst.AccountCode;
//Error handling
string ErrorContext = Catalog.GetString("List Child Accounts");
string ErrorMessage = String.Empty;
//Set default type as non-critical
TResultSeverity ErrorType = TResultSeverity.Resv_Noncritical;
try
{
if (AAccountRowFirst.PostingStatus)
{
AChildAccounts += AccountCode + ",";
}
else
{
AAccountHierarchyDetailTable AccountHierarchyDetailTable1 = new AAccountHierarchyDetailTable();
AAccountHierarchyDetailRow TemplateRow = (AAccountHierarchyDetailRow)AccountHierarchyDetailTable1.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.AccountHierarchyCode = MFinanceConstants.ACCOUNT_HIERARCHY_CODE;
TemplateRow.AccountCodeToReportTo = AAccountRowFirst.AccountCode;
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "=", "=" });
AAccountHierarchyDetailTable AccountHierarchyDetailTable2 = AAccountHierarchyDetailAccess.LoadUsingTemplate(TemplateRow,
operators,
null,
DBTransaction);
if (AccountHierarchyDetailTable2 != null)
{
for (int m = 0; m < AccountHierarchyDetailTable2.Count; m++)
{
AAccountHierarchyDetailRow AccountHierarchyDetailRow = (AAccountHierarchyDetailRow)AccountHierarchyDetailTable2.Rows[m];
AAccountTable AccountTable = AAccountAccess.LoadByPrimaryKey(ALedgerNumber,
AccountHierarchyDetailRow.ReportingAccountCode,
DBTransaction);
if (AccountTable != null)
{
AAccountRow AccountRow = (AAccountRow)AccountTable.Rows[0];
BuildChildAccountList(ALedgerNumber,
AccountRow,
DBTransaction,
ref AChildAccounts,
ref AVerificationResult);
}
else
{
break;
}
}
}
}
}
catch (ArgumentException ex)
{
AVerificationResult.Add(new TVerificationResult(ErrorContext, ex.Message, ErrorType));
}
catch (InvalidOperationException ex)
{
AVerificationResult.Add(new TVerificationResult(ErrorContext, ex.Message, ErrorType));
}
catch (Exception ex)
{
ErrorMessage =
String.Format(Catalog.GetString("Unknown error while building list of Child Accounts for Ledger: {0} and Account code: {1}" +
Environment.NewLine + Environment.NewLine + ex.ToString()),
ALedgerNumber,
AAccountRowFirst.AccountCode
);
ErrorType = TResultSeverity.Resv_Critical;
AVerificationResult.Add(new TVerificationResult(ErrorContext, ErrorMessage, ErrorType));
}
}
private static GiftBatchTDS CreateGiftBatches(SortedList <DateTime, List <XmlNode> > AGiftsPerDate, int APeriodNumber)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
ALedgerTable LedgerTable = null;
TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
// get a list of potential donors (all class FAMILY)
string sqlGetFamilyPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_family";
DataTable FamilyKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetFamilyPartnerKeys, "keys", ReadTransaction);
// get a list of workers (all class FAMILY, with special type WORKER)
string sqlGetWorkerPartnerKeys =
"SELECT PUB_p_family.p_partner_key_n FROM PUB_p_family, PUB_p_partner_type WHERE PUB_p_partner_type.p_partner_key_n = PUB_p_family.p_partner_key_n AND p_type_code_c = 'WORKER'";
DataTable WorkerKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetWorkerPartnerKeys, "keys", ReadTransaction);
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys =
String.Format(
"SELECT U.p_partner_key_n FROM PUB_p_unit U WHERE u_unit_type_code_c = 'F' AND EXISTS (SELECT * FROM PUB_a_valid_ledger_number V WHERE V.a_ledger_number_i = {0} AND V.p_partner_key_n = U.p_partner_key_n)",
FLedgerNumber);
DataTable FieldKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetFieldPartnerKeys, "keys", ReadTransaction);
// get a list of key ministries (all class UNIT, with unit type KEY-MIN), and their field ledger number and cost centre code
string sqlGetKeyMinPartnerKeys =
"SELECT u.p_partner_key_n, us.um_parent_unit_key_n, vl.a_cost_centre_code_c " +
"FROM PUB_p_unit u, PUB_um_unit_structure us, PUB_a_valid_ledger_number vl " +
"WHERE u.u_unit_type_code_c = 'KEY-MIN' " +
"AND us.um_child_unit_key_n = u.p_partner_key_n " +
"AND vl.p_partner_key_n = us.um_parent_unit_key_n " +
"AND vl.a_ledger_number_i = " + FLedgerNumber.ToString();
DataTable KeyMinistries = DBAccess.GDBAccessObj.SelectDT(sqlGetKeyMinPartnerKeys, "keys", ReadTransaction);
LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, ReadTransaction);
AAccountingPeriodRow AccountingPeriodRow = AAccountingPeriodAccess.LoadByPrimaryKey(FLedgerNumber, APeriodNumber, ReadTransaction)[0];
// create a gift batch for each day.
// TODO: could create one batch per month, if there are not so many gifts (less than 100 per month)
foreach (DateTime GlEffectiveDate in AGiftsPerDate.Keys)
{
if ((GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodStartDate) < 0) ||
(GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodEndDate) > 0))
{
// only create gifts in that period
continue;
}
AGiftBatchRow giftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref MainDS,
ref ReadTransaction,
ref LedgerTable,
FLedgerNumber,
GlEffectiveDate);
giftBatch.BatchDescription = "Benerator Batch for " + GlEffectiveDate.ToShortDateString();
giftBatch.BatchTotal = 0.0m;
foreach (XmlNode RecordNode in AGiftsPerDate[GlEffectiveDate])
{
AGiftRow gift = MainDS.AGift.NewRowTyped();
gift.LedgerNumber = giftBatch.LedgerNumber;
gift.BatchNumber = giftBatch.BatchNumber;
gift.GiftTransactionNumber = giftBatch.LastGiftNumber + 1;
gift.DateEntered = GlEffectiveDate;
// set donorKey
int donorID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "donor")) % FamilyKeys.Rows.Count;
gift.DonorKey = Convert.ToInt64(FamilyKeys.Rows[donorID].ItemArray[0]);
// calculate gift detail information
int countDetails = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "splitgift"));
for (int counter = 1; counter <= countDetails; counter++)
{
AGiftDetailRow giftDetail = MainDS.AGiftDetail.NewRowTyped();
giftDetail.LedgerNumber = gift.LedgerNumber;
giftDetail.BatchNumber = gift.BatchNumber;
giftDetail.GiftTransactionNumber = gift.GiftTransactionNumber;
giftDetail.MotivationGroupCode = "GIFT";
giftDetail.GiftTransactionAmount = Convert.ToDecimal(TXMLParser.GetAttribute(RecordNode, "amount_" + counter.ToString()));
giftDetail.GiftAmount = giftDetail.GiftTransactionAmount;
giftBatch.BatchTotal += giftDetail.GiftAmount;
string motivation = TXMLParser.GetAttribute(RecordNode, "motivation_" + counter.ToString());
if (motivation == "SUPPORT")
{
if (WorkerKeys.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "SUPPORT";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_support_" +
counter.ToString())) % WorkerKeys.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(WorkerKeys.Rows[recipientID].ItemArray[0]);
// TODO: ignore this gift detail, if there is no valid commitment period for the worker
// if (InvalidCommitment(giftBatch.GlEffectiveDate)) continue;
// TODO giftDetail.RecipientLedgerNumber =
}
else if (motivation == "FIELD")
{
if (FieldKeys.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "FIELD";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_field_" +
counter.ToString())) % FieldKeys.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(FieldKeys.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = giftDetail.RecipientKey;
giftDetail.CostCentreCode = (giftDetail.RecipientKey / 10000).ToString("0000");
}
else if (motivation == "KEYMIN")
{
if (KeyMinistries.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "KEYMIN";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_keymin_" +
counter.ToString())) % KeyMinistries.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[1]);
// TTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey);
giftDetail.CostCentreCode = KeyMinistries.Rows[recipientID].ItemArray[2].ToString();
// TTransactionWebConnector.IdentifyPartnerCostCentre(FLedgerNumber, giftDetail.RecipientLedgerNumber);
}
giftDetail.DetailNumber = gift.LastDetailNumber + 1;
MainDS.AGiftDetail.Rows.Add(giftDetail);
gift.LastDetailNumber = giftDetail.DetailNumber;
}
if (gift.LastDetailNumber > 0)
{
MainDS.AGift.Rows.Add(gift);
giftBatch.LastGiftNumber = gift.GiftTransactionNumber;
}
}
if (TLogging.DebugLevel > 0)
{
TLogging.Log(
GlEffectiveDate.ToShortDateString() + " " + giftBatch.LastGiftNumber.ToString());
}
}
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
// need to save the last gift batch number in a_ledger
if (LedgerTable != null)
{
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
ALedgerAccess.SubmitChanges(LedgerTable, WriteTransaction);
DBAccess.GDBAccessObj.CommitTransaction();
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the creation of Gift Batches:" + Environment.NewLine + Exc.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
}
return(MainDS);
}
// check if a person has on going commitments
private static bool PersonHasCommitments(PPersonTable ATable, TDBTransaction ATransaction)
{
foreach (PPersonRow Row in ATable.Rows)
{
PmStaffDataTable StaffDataTable = PmStaffDataAccess.LoadViaPPerson(Row.PartnerKey, ATransaction);
foreach (PmStaffDataRow StaffRow in StaffDataTable.Rows)
{
if (((StaffRow.EndOfCommitment >= DateTime.Now) || (StaffRow.EndOfCommitment == null))
&& (StaffRow.StartOfCommitment <= DateTime.Now))
{
return true;
}
}
}
return false;
}
/// <summary>
/// Stores a System Default in the DB. If it was already there it gets updated, if it wasn't there it gets added.
/// </summary>
/// <remarks>The change gets reflected in the System Defaults Cache the next time the System Defaults Cache
/// gets accessed.</remarks>
/// <param name="AKey">Name of the System Default.</param>
/// <param name="AValue">Value of the System Default.</param>
/// <param name="AAdded">True if the System Default got added, false if it already existed.</param>
/// <param name="ADataBase"></param>
/// <remarks>SystemDefault Names are not case sensitive.</remarks>
public void SetSystemDefault(String AKey, String AValue, out bool AAdded, TDataBase ADataBase = null)
{
TDataBase DBConnectionObj = null;
TDBTransaction WriteTransaction = new TDBTransaction();
bool SubmissionOK = false;
SSystemDefaultsTable SystemDefaultsDT;
Boolean Added = false;
try
{
// Open a separate DB Connection...
DBConnectionObj = DBAccess.Connect("SetSystemDefault", ADataBase);
// ...and start a DB Transaction on that separate DB Connection
DBConnectionObj.WriteTransaction(ref WriteTransaction, ref SubmissionOK,
delegate
{
SystemDefaultsDT = SSystemDefaultsAccess.LoadAll(WriteTransaction);
// This will find the row that matches a case-insensitive search of the table primary keys
SystemDefaultsDT.CaseSensitive = false; // It is anyway
SSystemDefaultsRow match = (SSystemDefaultsRow)SystemDefaultsDT.Rows.Find(AKey);
if (match != null)
{
// I already have this System Default in the DB --> simply update the Value in the DB.
// (This will often be the case!)
match.DefaultValue = AValue;
Added = false;
}
else
{
// The System Default isn't in the DB yet --> store it in the DB.
var SystemDefaultsDR = SystemDefaultsDT.NewRowTyped(true);
SystemDefaultsDR.DefaultCode = AKey;
SystemDefaultsDR.DefaultDescription = "Created in OpenPetra";
SystemDefaultsDR.DefaultValue = AValue;
SystemDefaultsDT.Rows.Add(SystemDefaultsDR);
Added = true;
}
SSystemDefaultsAccess.SubmitChanges(SystemDefaultsDT, WriteTransaction);
SubmissionOK = true;
});
AAdded = Added;
}
catch (Exception Exc)
{
TLogging.Log(
"TSystemDefaultCache.SetSystemDefault: An Exception occured during the saving of the System Default '" + AKey +
"'. Value to be saved: + '" + AValue + "'" +
Environment.NewLine + Exc.ToString());
throw;
}
finally
{
if (ADataBase == null)
{
DBConnectionObj.CloseDBConnection();
}
if (SubmissionOK && (FSystemDefaultsDT != null))
{
// We need to ensure that the next time the System Defaults Caches gets accessed it is refreshed from the DB!!!
FSystemDefaultsDT.Clear();
FSystemDefaultsDT = null;
}
}
}
/// <summary>
/// Retrieves data that will be shown on the 'Overview' UserControl and adds it to <paramref name="AIndividualDataDS" />.
/// </summary>
/// <param name="APartnerKey">PartnerKey of the Person to load data for.</param>
/// <param name="AIndividualDataDS">Typed DataSet of Type <see cref="IndividualDataTDS" />. Needs to be instantiated already!</param>
/// <param name="AReadTransaction">Open Database transaction.</param>
/// <returns>void</returns>
private static void BuildSummaryData(Int64 APartnerKey, ref IndividualDataTDS AIndividualDataDS, TDBTransaction AReadTransaction)
{
string StrNotAvailable = Catalog.GetString("Not Available");
IndividualDataTDSSummaryDataTable SummaryDT;
IndividualDataTDSSummaryDataRow SummaryDR;
IndividualDataTDSMiscellaneousDataRow MiscellaneousDataDR = AIndividualDataDS.MiscellaneousData[0];
PPersonTable PPersonDT;
PPersonRow PersonDR = null;
PmPassportDetailsTable PassportDetailsDT;
PmStaffDataTable PmStaffDataDT;
PmStaffDataRow PmStaffDataDR = null;
PmJobAssignmentTable PmJobAssignmentDT = null;
PUnitTable PUnitDT = null;
PmJobAssignmentRow PmJobAssignmentDR;
IndividualDataTDSJobAssignmentStaffDataCombinedRow JobAssiStaffDataCombDR;
int JobAssiStaffDataCombKey = 0;
TCacheable CommonCacheable = new TCacheable();
TPartnerCacheable PartnerCacheable = new TPartnerCacheable();
string MaritalStatusDescr;
StringCollection PassportColumns;
string Nationalities;
PPartnerRelationshipTable PartnerRelationshipDT;
PPartnerTable PartnerDT;
PPartnerRow PartnerDR = null;
PLocationRow LocationDR;
PPartnerLocationRow PartnerLocationDR;
string PhoneNumber;
string PhoneExtension = String.Empty;
Int64 ChurchPartnerKey;
SummaryDT = new IndividualDataTDSSummaryDataTable();
SummaryDR = SummaryDT.NewRowTyped(false);
SummaryDR.PartnerKey = APartnerKey;
#region Person Info
PPersonDT = PPersonAccess.LoadByPrimaryKey(APartnerKey, AReadTransaction);
if (PPersonDT.Rows.Count == 1)
{
PersonDR = (PPersonRow)PPersonDT.Rows[0];
}
if (PersonDR != null)
{
SummaryDR.DateOfBirth = PersonDR.DateOfBirth;
SummaryDR.Gender = PersonDR.Gender;
MaritalStatusDescr = PartnerCodeHelper.GetMaritalStatusDescription(
@PartnerCacheable.GetCacheableTable, PersonDR.MaritalStatus);
if (MaritalStatusDescr != String.Empty)
{
MaritalStatusDescr = " - " + MaritalStatusDescr;
}
SummaryDR.MaritalStatus = PersonDR.MaritalStatus + MaritalStatusDescr;
}
else
{
SummaryDR.SetDateOfBirthNull();
SummaryDR.Gender = StrNotAvailable;
SummaryDR.MaritalStatus = StrNotAvailable;
}
#region Nationalities
PassportColumns = StringHelper.StrSplit(
PmPassportDetailsTable.GetDateOfIssueDBName() + "," +
PmPassportDetailsTable.GetDateOfExpirationDBName() + "," +
PmPassportDetailsTable.GetPassportNationalityCodeDBName() + "," +
PmPassportDetailsTable.GetMainPassportDBName(), ",");
PassportDetailsDT = PmPassportDetailsAccess.LoadViaPPerson(APartnerKey,
PassportColumns, AReadTransaction, null, 0, 0);
Nationalities = Ict.Petra.Shared.MPersonnel.Calculations.DeterminePersonsNationalities(
@CommonCacheable.GetCacheableTable, PassportDetailsDT);
if (Nationalities != String.Empty)
{
SummaryDR.Nationalities = Nationalities;
}
else
{
SummaryDR.Nationalities = StrNotAvailable;
}
#endregion
#region Phone and Email (from 'Best Address')
ServerCalculations.DetermineBestAddress(APartnerKey, out PartnerLocationDR, out LocationDR);
if (LocationDR != null)
{
SummaryDR.EmailAddress = PartnerLocationDR.EmailAddress;
if (PartnerLocationDR.TelephoneNumber != String.Empty)
{
PhoneNumber = PartnerLocationDR.TelephoneNumber;
if (!PartnerLocationDR.IsExtensionNull())
{
PhoneExtension = PartnerLocationDR.Extension.ToString();
}
SummaryDR.TelephoneNumber = Calculations.FormatIntlPhoneNumber(PhoneNumber, PhoneExtension, LocationDR.CountryCode,
@CommonCacheable.GetCacheableTable);
}
else if (PartnerLocationDR.MobileNumber != String.Empty)
{
SummaryDR.TelephoneNumber = Calculations.FormatIntlPhoneNumber(PartnerLocationDR.MobileNumber,
String.Empty, LocationDR.CountryCode, @CommonCacheable.GetCacheableTable) + " " +
Catalog.GetString("(Mobile)");
}
else
{
SummaryDR.TelephoneNumber = StrNotAvailable;
}
}
else
{
SummaryDR.TelephoneNumber = StrNotAvailable;
SummaryDR.EmailAddress = StrNotAvailable;
}
#endregion
#endregion
#region Commitments/Jobs
PmStaffDataDT = PmStaffDataAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
MiscellaneousDataDR.ItemsCountCommitmentPeriods = PmStaffDataDT.Rows.Count;
// First check if the PERSON has got any Commitments
if (PmStaffDataDT.Rows.Count > 0)
{
foreach (DataRow DR in PmStaffDataDT.Rows)
{
JobAssiStaffDataCombDR = AIndividualDataDS.JobAssignmentStaffDataCombined.NewRowTyped(false);
JobAssiStaffDataCombDR.Key = JobAssiStaffDataCombKey++;
JobAssiStaffDataCombDR.PartnerKey = APartnerKey;
PmStaffDataDR = (PmStaffDataRow)DR;
if (!(PmStaffDataDR.IsReceivingFieldNull())
&& (PmStaffDataDR.ReceivingField != 0))
{
PUnitDT = PUnitAccess.LoadByPrimaryKey(PmStaffDataDR.ReceivingField, AReadTransaction);
JobAssiStaffDataCombDR.FieldKey = PmStaffDataDR.ReceivingField;
JobAssiStaffDataCombDR.FieldName = PUnitDT[0].UnitName;
}
else
{
JobAssiStaffDataCombDR.FieldKey = 0;
JobAssiStaffDataCombDR.FieldName = "[None]";
}
JobAssiStaffDataCombDR.Position = PmStaffDataDR.JobTitle;
JobAssiStaffDataCombDR.FromDate = PmStaffDataDR.StartOfCommitment;
JobAssiStaffDataCombDR.ToDate = PmStaffDataDR.EndOfCommitment;
AIndividualDataDS.JobAssignmentStaffDataCombined.Rows.Add(JobAssiStaffDataCombDR);
}
}
else
{
// The PERSON hasn't got any Commitments, therefore check if the PERSON has any Job Assignments
PmJobAssignmentDT = PmJobAssignmentAccess.LoadViaPPartner(APartnerKey, AReadTransaction);
if (PmJobAssignmentDT.Rows.Count > 0)
{
foreach (DataRow DR in PmJobAssignmentDT.Rows)
{
JobAssiStaffDataCombDR = AIndividualDataDS.JobAssignmentStaffDataCombined.NewRowTyped(false);
JobAssiStaffDataCombDR.Key = JobAssiStaffDataCombKey++;
JobAssiStaffDataCombDR.PartnerKey = APartnerKey;
PmJobAssignmentDR = (PmJobAssignmentRow)DR;
if (PmJobAssignmentDR.UnitKey != 0)
{
PUnitDT = PUnitAccess.LoadByPrimaryKey(PmJobAssignmentDR.UnitKey, AReadTransaction);
JobAssiStaffDataCombDR.FieldKey = PmJobAssignmentDR.UnitKey;
JobAssiStaffDataCombDR.FieldName = PUnitDT[0].UnitName;
}
else
{
JobAssiStaffDataCombDR.FieldKey = 0;
JobAssiStaffDataCombDR.FieldName = "[None]";
}
JobAssiStaffDataCombDR.Position = PmJobAssignmentDR.PositionName;
JobAssiStaffDataCombDR.FromDate = PmJobAssignmentDR.FromDate;
JobAssiStaffDataCombDR.ToDate = PmJobAssignmentDR.ToDate;
AIndividualDataDS.JobAssignmentStaffDataCombined.Rows.Add(JobAssiStaffDataCombDR);
}
}
}
#endregion
#region Church Info
SummaryDR.ChurchName = StrNotAvailable;
SummaryDR.ChurchAddress = StrNotAvailable;
SummaryDR.ChurchPhone = StrNotAvailable;
SummaryDR.ChurchPastor = StrNotAvailable;
SummaryDR.ChurchPastorsPhone = StrNotAvailable;
SummaryDR.NumberOfShownSupportingChurchPastors = 0;
// Find SUPPCHURCH Relationship
PartnerRelationshipDT = PPartnerRelationshipAccess.LoadUsingTemplate(new TSearchCriteria[] {
new TSearchCriteria(PPartnerRelationshipTable.GetRelationKeyDBName(), APartnerKey),
new TSearchCriteria(PPartnerRelationshipTable.GetRelationNameDBName(), "SUPPCHURCH")
},
AReadTransaction);
SummaryDR.NumberOfShownSupportingChurches = PartnerRelationshipDT.Rows.Count;
if (PartnerRelationshipDT.Rows.Count > 0)
{
ChurchPartnerKey = PartnerRelationshipDT[0].PartnerKey;
// Load Church Partner
PartnerDT = PPartnerAccess.LoadByPrimaryKey(ChurchPartnerKey, AReadTransaction);
if (PartnerDT.Rows.Count > 0)
{
PartnerDR = PartnerDT[0];
// Church Name
if (PartnerDR.PartnerShortName != String.Empty)
{
SummaryDR.ChurchName = PartnerDR.PartnerShortName;
}
#region Church Address and Phone
ServerCalculations.DetermineBestAddress(PartnerRelationshipDT[0].PartnerKey, out PartnerLocationDR, out LocationDR);
if (LocationDR != null)
{
SummaryDR.ChurchAddress = Calculations.DetermineLocationString(LocationDR,
Calculations.TPartnerLocationFormatEnum.plfCommaSeparated);
// Church Phone
if (PartnerLocationDR.TelephoneNumber != String.Empty)
{
PhoneNumber = PartnerLocationDR.TelephoneNumber;
if (!PartnerLocationDR.IsExtensionNull())
{
PhoneExtension = PartnerLocationDR.Extension.ToString();
}
SummaryDR.ChurchPhone = Calculations.FormatIntlPhoneNumber(PhoneNumber, PhoneExtension, LocationDR.CountryCode,
@CommonCacheable.GetCacheableTable);
}
else if (PartnerLocationDR.MobileNumber != String.Empty)
{
SummaryDR.ChurchPhone = Calculations.FormatIntlPhoneNumber(PartnerLocationDR.MobileNumber,
String.Empty, LocationDR.CountryCode, @CommonCacheable.GetCacheableTable) + " " +
Catalog.GetString("(Mobile)");
}
}
#endregion
#region Pastor
// Find PASTOR Relationship
PartnerRelationshipDT.Rows.Clear();
PartnerRelationshipDT = PPartnerRelationshipAccess.LoadUsingTemplate(new TSearchCriteria[] {
new TSearchCriteria(PPartnerRelationshipTable.GetPartnerKeyDBName(), ChurchPartnerKey),
new TSearchCriteria(PPartnerRelationshipTable.GetRelationNameDBName(), "PASTOR")
},
AReadTransaction);
SummaryDR.NumberOfShownSupportingChurchPastors = PartnerRelationshipDT.Rows.Count;
if (PartnerRelationshipDT.Rows.Count > 0)
{
// Load PASTOR Partner
PartnerDT = PPartnerAccess.LoadByPrimaryKey(PartnerRelationshipDT[0].RelationKey, AReadTransaction);
if (PartnerDT.Rows.Count > 0)
{
PartnerDR = PartnerDT[0];
// Pastor's Name
if (PartnerDR.PartnerShortName != String.Empty)
{
SummaryDR.ChurchPastor = PartnerDR.PartnerShortName;
}
#region Pastor's Phone
ServerCalculations.DetermineBestAddress(PartnerRelationshipDT[0].RelationKey,
out PartnerLocationDR, out LocationDR);
if (LocationDR != null)
{
// Pastor's Phone
if (PartnerLocationDR.TelephoneNumber != String.Empty)
{
PhoneNumber = PartnerLocationDR.TelephoneNumber;
if (!PartnerLocationDR.IsExtensionNull())
{
PhoneExtension = PartnerLocationDR.Extension.ToString();
}
SummaryDR.ChurchPastorsPhone = Calculations.FormatIntlPhoneNumber(PhoneNumber,
PhoneExtension, LocationDR.CountryCode, @CommonCacheable.GetCacheableTable);
}
else if (PartnerLocationDR.MobileNumber != String.Empty)
{
SummaryDR.ChurchPastorsPhone = Calculations.FormatIntlPhoneNumber(PartnerLocationDR.MobileNumber,
String.Empty, LocationDR.CountryCode, @CommonCacheable.GetCacheableTable) + " " +
Catalog.GetString("(Mobile)");
}
}
#endregion
}
}
#endregion
}
}
#endregion
// Add Summary DataRow to Summary DataTable
SummaryDT.Rows.Add(SummaryDR);
// Add Row to 'SummaryData' DataTable in Typed DataSet 'IndividualDataTDS'
AIndividualDataDS.Merge(SummaryDT);
}
public static void FillFormDataFromPersonnel(Int64 APartnerKey,
TFormDataPerson AFormDataPerson,
TFormLetterInfo AFormLetterInfo,
TDBTransaction AReadTransaction,
Int64 ASiteKey = 0,
Int32 ALocationKey = 0)
{
TPartnerClass PartnerClass;
String ShortName;
TStdPartnerStatusCode PartnerStatusCode;
if (AFormDataPerson == null)
{
return;
}
if (MCommonMain.RetrievePartnerShortName(APartnerKey, out ShortName, out PartnerClass, out PartnerStatusCode, AReadTransaction))
{
// first retrieve all partner information
TFormLettersWebConnector.FillFormDataFromPerson(APartnerKey,
AFormDataPerson,
AFormLetterInfo,
AReadTransaction,
ASiteKey,
ALocationKey);
// retrieve Special Needs information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eSpecialNeeds))
{
PmSpecialNeedTable SpecialNeedTable;
PmSpecialNeedRow SpecialNeedRow;
SpecialNeedTable = PmSpecialNeedAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
if (SpecialNeedTable.Count > 0)
{
SpecialNeedRow = (PmSpecialNeedRow)SpecialNeedTable.Rows[0];
AFormDataPerson.DietaryNeeds = SpecialNeedRow.DietaryComment;
AFormDataPerson.MedicalNeeds = SpecialNeedRow.MedicalComment;
AFormDataPerson.OtherNeeds = SpecialNeedRow.OtherSpecialNeed;
AFormDataPerson.Vegetarian = SpecialNeedRow.VegetarianFlag;
}
}
// retrieve Personal Data information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.ePersonalData))
{
PmPersonalDataTable PersonalDataTable;
PmPersonalDataRow PersonalDataRow;
PersonalDataTable = PmPersonalDataAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
if (PersonalDataTable.Count > 0)
{
PersonalDataRow = (PmPersonalDataRow)PersonalDataTable.Rows[0];
if (!PersonalDataRow.IsBelieverSinceYearNull() &&
(PersonalDataRow.BelieverSinceYear != 0))
{
AFormDataPerson.YearsBeliever = (DateTime.Today.Year - PersonalDataRow.BelieverSinceYear).ToString();
}
AFormDataPerson.CommentBeliever = PersonalDataRow.BelieverSinceComment;
}
}
// retrieve Passport information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.ePassport))
{
PmPassportDetailsTable PassportTable;
TFormDataPassport PassportRecord;
PassportTable = PmPassportDetailsAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
Boolean MainPassportFound = false;
foreach (PmPassportDetailsRow PassportRow in PassportTable.Rows)
{
// only list "full" passports that have not expired yet
if ((PassportRow.IsDateOfExpirationNull() ||
(PassportRow.DateOfExpiration >= DateTime.Today)) &&
(PassportRow.PassportDetailsType == "P"))
{
PassportRecord = new TFormDataPassport();
PassportRecord.IsMainPassport = PassportRow.MainPassport;
PassportRecord.Number = PassportRow.PassportNumber;
PassportRecord.PassportName = PassportRow.FullPassportName;
PassportRecord.NationalityCode = PassportRow.PassportNationalityCode;
// retrieve country name from country table
TCacheable CachePopulator = new TCacheable();
PCountryTable CountryTable =
(PCountryTable)CachePopulator.GetCacheableTable(TCacheableCommonTablesEnum.CountryList);
PCountryRow CountryRow =
(PCountryRow)CountryTable.Rows.Find(new object[] { PassportRow.PassportNationalityCode });
if (CountryRow != null)
{
PassportRecord.NationalityName = CountryRow.CountryName;
}
PassportRecord.TypeCode = PassportRow.PassportDetailsType;
// retrieve passport type name from type table
TPersonnelCacheable PersonnelCachePopulator = new TPersonnelCacheable();
PtPassportTypeTable TypeTable =
(PtPassportTypeTable)PersonnelCachePopulator.GetCacheableTable(TCacheablePersonTablesEnum.
PassportTypeList);
PtPassportTypeRow TypeRow =
(PtPassportTypeRow)TypeTable.Rows.Find(new object[] { PassportRow.PassportDetailsType });
if (TypeRow != null)
{
PassportRecord.TypeDescription = TypeRow.Description;
}
PassportRecord.DateOfIssue = PassportRow.DateOfIssue;
PassportRecord.PlaceOfIssue = PassportRow.PlaceOfIssue;
PassportRecord.DateOfExpiry = PassportRow.DateOfExpiration;
PassportRecord.PlaceOfBirth = PassportRow.PlaceOfBirth;
// set number and nationality in main record (only for main passport or if there is just one)
if (PassportRow.MainPassport || (PassportTable.Count == 1))
{
AFormDataPerson.PassportNumber = PassportRecord.Number;
AFormDataPerson.PassportNationality = PassportRecord.NationalityName;
AFormDataPerson.PassportNationalityCode = PassportRecord.NationalityCode;
AFormDataPerson.PassportName = PassportRecord.PassportName;
AFormDataPerson.PassportDateOfIssue = PassportRecord.DateOfIssue;
AFormDataPerson.PassportPlaceOfIssue = PassportRecord.PlaceOfIssue;
AFormDataPerson.PassportDateOfExpiry = PassportRecord.DateOfExpiry;
AFormDataPerson.PassportPlaceOfBirth = PassportRecord.PlaceOfBirth;
MainPassportFound = true;
}
// If the PassportName has not been set yet then make sure it is set on Person level from at least one passport
// (this will not be necessary any longer once the tick box for "Main Passport" is implemented)
if (!MainPassportFound)
{
AFormDataPerson.PassportName = PassportRecord.PassportName;
}
AFormDataPerson.AddPassport(PassportRecord);
}
}
}
// retrieve Language information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eLanguage))
{
PmPersonLanguageTable PersonLanguageTable;
TFormDataLanguage PersonLanguageRecord;
PersonLanguageTable = PmPersonLanguageAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
foreach (PmPersonLanguageRow PersonLanguageRow in PersonLanguageTable.Rows)
{
PersonLanguageRecord = new TFormDataLanguage();
PersonLanguageRecord.Code = PersonLanguageRow.LanguageCode;
// retrieve language name from language table
TCacheable CachePopulator = new TCacheable();
PLanguageTable LanguageTable =
(PLanguageTable)CachePopulator.GetCacheableTable(TCacheableCommonTablesEnum.LanguageCodeList);
PLanguageRow LanguageRow = (PLanguageRow)LanguageTable.Rows.Find(new object[] { PersonLanguageRow.LanguageCode });
if (LanguageRow != null)
{
PersonLanguageRecord.Name = LanguageRow.LanguageDescription;
}
PersonLanguageRecord.Level = PersonLanguageRow.LanguageLevel.ToString();
// retrieve language level name from language level table
TPersonnelCacheable CachePopulatorPersonnel = new TPersonnelCacheable();
PtLanguageLevelTable LanguageLevelTable =
(PtLanguageLevelTable)CachePopulatorPersonnel.GetCacheableTable(TCacheablePersonTablesEnum.LanguageLevelList);
PtLanguageLevelRow LanguageLevelRow =
(PtLanguageLevelRow)LanguageLevelTable.Rows.Find(new object[] { PersonLanguageRow.LanguageLevel });
if (LanguageLevelRow != null)
{
PersonLanguageRecord.LevelDesc = LanguageLevelRow.LanguageLevelDescr;
}
PersonLanguageRecord.Comment = PersonLanguageRow.Comment;
AFormDataPerson.AddLanguage(PersonLanguageRecord);
}
}
// retrieve Skill information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eSkill))
{
PmPersonSkillTable PersonSkillTable;
TFormDataSkill PersonSkillRecord;
TFormDataDegree PersonDegreeRecord;
PersonSkillTable = PmPersonSkillAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
foreach (PmPersonSkillRow PersonSkillRow in PersonSkillTable.Rows)
{
PersonSkillRecord = new TFormDataSkill();
PersonSkillRecord.Category = PersonSkillRow.SkillCategoryCode;
PersonSkillRecord.Description = PersonSkillRow.DescriptionEnglish;
// if no description in local language then use english
PersonSkillRecord.DescriptionLocalOrDefault = PersonSkillRow.DescriptionLocal;
if (PersonSkillRow.DescriptionLocal != "")
{
PersonSkillRecord.DescriptionLocalOrDefault = PersonSkillRow.DescriptionEnglish;
}
PersonSkillRecord.Level = PersonSkillRow.SkillLevel;
// retrieve skill level name from skill level table
TPersonnelCacheable CachePopulatorPersonnel = new TPersonnelCacheable();
PtSkillLevelTable SkillLevelTable =
(PtSkillLevelTable)CachePopulatorPersonnel.GetCacheableTable(TCacheablePersonTablesEnum.SkillLevelList);
PtSkillLevelRow SkillLevelRow =
(PtSkillLevelRow)SkillLevelTable.Rows.Find(new object[] { PersonSkillRow.SkillLevel });
if (SkillLevelRow != null)
{
PersonSkillRecord.LevelDesc = SkillLevelRow.Description;
}
PersonSkillRecord.YearsExp = PersonSkillRow.YearsOfExperience;
PersonSkillRecord.Professional = PersonSkillRow.ProfessionalSkill;
PersonSkillRecord.Degree = PersonSkillRow.Degree;
PersonSkillRecord.Comment = PersonSkillRow.Comment;
AFormDataPerson.AddSkill(PersonSkillRecord);
// now add a degree record if a degree is mentioned
if (!PersonSkillRow.IsDegreeNull() &&
(PersonSkillRow.Degree.Length > 0))
{
PersonDegreeRecord = new TFormDataDegree();
PersonDegreeRecord.Name = PersonSkillRow.Degree;
PersonDegreeRecord.Year = PersonSkillRow.YearOfDegree.ToString();
AFormDataPerson.AddDegree(PersonDegreeRecord);
}
}
}
// retrieve past work experience information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eWorkExperience))
{
TFormDataWorkExperience PersonExpRecord;
/*
* currently we don't include application records in the work experience data
*
* String UnitShortName;
* TPartnerClass UnitClass;
*
* // retrieve applications for short term events
* String SqlStmt = TDataBase.ReadSqlFile("Personnel.FormLetters.GetAppTravelDates.sql");
*
* OdbcParameter[] parameters = new OdbcParameter[1];
* parameters[0] = new OdbcParameter("PartnerKey", OdbcType.BigInt);
* parameters[0].Value = APartnerKey;
*
* DataTable travelData = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "TravelDates", ReadTransaction, parameters);
*
* for (int i = 0; i < travelData.Rows.Count; i++)
* {
* PersonExpRecord = new TFormDataWorkExperience();
*
* if ((travelData.Rows[i][0]).GetType() == typeof(DateTime))
* {
* PersonExpRecord.StartDate = (DateTime?)travelData.Rows[i][0];
* }
*
* if ((travelData.Rows[i][1]).GetType() == typeof(DateTime))
* {
* PersonExpRecord.EndDate = (DateTime?)travelData.Rows[i][1];
* }
*
* PersonExpRecord.Organisation = "";
* PersonExpRecord.Role = "";
* PersonExpRecord.Category = "";
* PersonExpRecord.SameOrg = true;
* PersonExpRecord.SimilarOrg = true;
* PersonExpRecord.Comment = "";
*
* // check if unit exists and use unit name as location
* if (TPartnerServerLookups.GetPartnerShortName((Int64)travelData.Rows[i][2], out UnitShortName, out UnitClass))
* {
* PersonExpRecord.Location = UnitShortName;
* }
* else
* {
* PersonExpRecord.Location = travelData.Rows[i][3].ToString();
* }
*
* AFormDataPerson.AddWorkExperience(PersonExpRecord);
* }
*/
// retrieve actual past experience records
PmPastExperienceTable PersonExpTable;
PersonExpTable = PmPastExperienceAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
PUnitTable UnitTable;
PUnitRow UnitRow;
foreach (PmPastExperienceRow PersonExpRow in PersonExpTable.Rows)
{
PersonExpRecord = new TFormDataWorkExperience();
PersonExpRecord.StartDate = PersonExpRow.StartDate;
PersonExpRecord.EndDate = PersonExpRow.EndDate;
PersonExpRecord.Location = PersonExpRow.PrevLocation;
PersonExpRecord.Organisation = PersonExpRow.OtherOrganisation;
PersonExpRecord.Role = PersonExpRow.PrevRole;
PersonExpRecord.Category = PersonExpRow.Category;
PersonExpRecord.SameOrg = PersonExpRow.PrevWorkHere;
PersonExpRecord.SimilarOrg = PersonExpRow.PrevWork;
PersonExpRecord.Comment = PersonExpRow.PastExpComments;
// check if previous experience came from an event with location set to event code
// -> in this case don't set event code as location but rather the event name
PUnitRow template = new PUnitTable().NewRowTyped(false);
template.OutreachCode = PersonExpRow.PrevLocation;
UnitTable = PUnitAccess.LoadUsingTemplate(template, AReadTransaction);
if (UnitTable.Rows.Count > 0)
{
UnitRow = (PUnitRow)UnitTable.Rows[0];
PersonExpRecord.Location = UnitRow.UnitName;
}
AFormDataPerson.AddWorkExperience(PersonExpRecord);
}
}
// retrieve Commitment information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eCommitment) ||
AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eFutureCommitment))
{
String FieldName;
TPartnerClass FieldPartnerClass;
PmStaffDataTable PersonCommitmentTable;
PmStaffDataRow PersonCommitmentRow;
TFormDataCommitment PersonCommitmentRecord;
PersonCommitmentTable = PmStaffDataAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
PersonCommitmentTable.DefaultView.Sort = PmStaffDataTable.GetStartOfCommitmentDBName() + " DESC";
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eCommitment))
{
foreach (DataRowView rv in PersonCommitmentTable.DefaultView)
{
PersonCommitmentRow = (PmStaffDataRow)rv.Row;
PersonCommitmentRecord = new TFormDataCommitment();
PersonCommitmentRecord.StartDate = PersonCommitmentRow.StartOfCommitment;
PersonCommitmentRecord.EndDate = PersonCommitmentRow.EndOfCommitment;
PersonCommitmentRecord.Status = PersonCommitmentRow.StatusCode;
PersonCommitmentRecord.ReceivingFieldKey = PersonCommitmentRow.ReceivingField.ToString("0000000000");
TPartnerServerLookups.GetPartnerShortName(PersonCommitmentRow.ReceivingField, out FieldName,
out FieldPartnerClass);
PersonCommitmentRecord.ReceivingFieldName = FieldName;
PersonCommitmentRecord.SendingFieldKey = PersonCommitmentRow.HomeOffice.ToString("0000000000");
TPartnerServerLookups.GetPartnerShortName(PersonCommitmentRow.HomeOffice, out FieldName,
out FieldPartnerClass);
PersonCommitmentRecord.SendingFieldName = FieldName;
PersonCommitmentRecord.RecruitingFieldKey = PersonCommitmentRow.OfficeRecruitedBy.ToString("0000000000");
TPartnerServerLookups.GetPartnerShortName(PersonCommitmentRow.OfficeRecruitedBy, out FieldName,
out FieldPartnerClass);
PersonCommitmentRecord.RecruitingFieldName = FieldName;
PersonCommitmentRecord.Comment = PersonCommitmentRow.StaffDataComments;
AFormDataPerson.AddCommitment(PersonCommitmentRecord);
}
}
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eFutureCommitment))
{
foreach (DataRowView rv in PersonCommitmentTable.DefaultView)
{
PersonCommitmentRow = (PmStaffDataRow)rv.Row;
if (PersonCommitmentRow.StartOfCommitment >= DateTime.Today)
{
TPartnerServerLookups.GetPartnerShortName(PersonCommitmentRow.ReceivingField, out FieldName,
out FieldPartnerClass);
AFormDataPerson.FutureFieldName = FieldName;
AFormDataPerson.FutureCommitStartDate = PersonCommitmentRow.StartOfCommitment;
AFormDataPerson.FutureCommitEndDate = PersonCommitmentRow.EndOfCommitment;
// only use the first commitment (list is sorted by start date)
break;
}
}
}
}
}
}
public static TSubmitChangesResult SubmitChangesServerSide(ref IndividualDataTDS AInspectDS,
ref PartnerEditTDS APartnerEditInspectDS,
TDBTransaction ASubmitChangesTransaction,
out TVerificationResultCollection AVerificationResult)
{
TSubmitChangesResult SubmissionResult;
PmJobAssignmentTable PmJobAssignmentTableSubmit;
AVerificationResult = new TVerificationResultCollection();
if (AInspectDS != null)
{
SubmissionResult = TSubmitChangesResult.scrOK;
// Job Assignments: make sure that jobs exist for assignments
if (AInspectDS.Tables.Contains(PmJobAssignmentTable.GetTableName())
&& (AInspectDS.PmJobAssignment.Rows.Count > 0))
{
UmJobTable JobTableTemp = new UmJobTable();
UmJobTable JobTableSubmit = new UmJobTable();
UmJobRow JobRow;
PmJobAssignmentTableSubmit = AInspectDS.PmJobAssignment;
// every job_assignment_row needs to have a row that it references in um_job
foreach (PmJobAssignmentRow JobAssignmentRow in PmJobAssignmentTableSubmit.Rows)
{
if (JobAssignmentRow.RowState != DataRowState.Deleted)
{
JobTableTemp = UmJobAccess.LoadByPrimaryKey(JobAssignmentRow.UnitKey,
JobAssignmentRow.PositionName,
JobAssignmentRow.PositionScope,
JobAssignmentRow.JobKey,
ASubmitChangesTransaction);
// if no corresponding job record found then we need to create one
// (job key was already set on client side to new value so merging back to the
// client does not cause problems because of primary key change)
if (JobTableTemp.Count == 0)
{
JobRow = (UmJobRow)JobTableSubmit.NewRow();
JobRow.UnitKey = JobAssignmentRow.UnitKey;
JobRow.PositionName = JobAssignmentRow.PositionName;
JobRow.PositionScope = JobAssignmentRow.PositionScope;
JobRow.JobKey = JobAssignmentRow.JobKey;
JobRow.FromDate = JobAssignmentRow.FromDate;
JobRow.ToDate = JobAssignmentRow.ToDate;
JobRow.CommitmentPeriod = "None";
JobRow.TrainingPeriod = "None";
// Need to update the JobKey field in job assignment table record from job record
JobAssignmentRow.JobKey = JobRow.JobKey;
JobTableSubmit.Rows.Add(JobRow);
}
else
{
// job record exists: in this case we need to update JobKey in
// the Job Assignment Record from Job Row
JobAssignmentRow.JobKey = ((UmJobRow)JobTableTemp.Rows[0]).JobKey;
}
}
}
// submit table with newly created jobs
if (JobTableSubmit.Rows.Count > 0)
{
UmJobAccess.SubmitChanges(JobTableSubmit, ASubmitChangesTransaction);
}
}
// now submit the whole dataset at once
IndividualDataTDSAccess.SubmitChanges(AInspectDS);
// Need to merge tables back into APartnerEditInspectDS so the updated s_modification_id_t is returned
// correctly to the Partner Edit.
// Unfortunately this can't be done simply by using merge method of the dataset since they are two different
// types but has to be done per table.
if (AInspectDS.Tables.Contains(PmSpecialNeedTable.GetTableName())
&& (AInspectDS.PmSpecialNeed.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmSpecialNeedTable.GetTableName()].Merge(AInspectDS.PmSpecialNeed);
}
if (AInspectDS.Tables.Contains(PmPersonAbilityTable.GetTableName())
&& (AInspectDS.PmPersonAbility.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonAbilityTable.GetTableName()].Merge(AInspectDS.PmPersonAbility);
}
if (AInspectDS.Tables.Contains(PmPassportDetailsTable.GetTableName())
&& (AInspectDS.PmPassportDetails.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPassportDetailsTable.GetTableName()].Merge(AInspectDS.PmPassportDetails);
}
if (AInspectDS.Tables.Contains(PmPersonalDataTable.GetTableName())
&& (AInspectDS.PmPersonalData.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonalDataTable.GetTableName()].Merge(AInspectDS.PmPersonalData);
}
if (AInspectDS.Tables.Contains(PmPersonLanguageTable.GetTableName())
&& (AInspectDS.PmPersonLanguage.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonLanguageTable.GetTableName()].Merge(AInspectDS.PmPersonLanguage);
}
if (AInspectDS.Tables.Contains(PmPersonEvaluationTable.GetTableName())
&& (AInspectDS.PmPersonEvaluation.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonEvaluationTable.GetTableName()].Merge(AInspectDS.PmPersonEvaluation);
}
if (AInspectDS.Tables.Contains(PmStaffDataTable.GetTableName())
&& (AInspectDS.PmStaffData.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmStaffDataTable.GetTableName()].Merge(AInspectDS.PmStaffData);
}
if (AInspectDS.Tables.Contains(PmPersonSkillTable.GetTableName())
&& (AInspectDS.PmPersonSkill.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonSkillTable.GetTableName()].Merge(AInspectDS.PmPersonSkill);
}
if (AInspectDS.Tables.Contains(PmPastExperienceTable.GetTableName())
&& (AInspectDS.PmPastExperience.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPastExperienceTable.GetTableName()].Merge(AInspectDS.PmPastExperience);
}
if (AInspectDS.Tables.Contains(PmDocumentTable.GetTableName())
&& (AInspectDS.PmDocument.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmDocumentTable.GetTableName()].Merge(AInspectDS.PmDocument);
}
if (AInspectDS.Tables.Contains(PmJobAssignmentTable.GetTableName())
&& (AInspectDS.PmJobAssignment.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmJobAssignmentTable.GetTableName()].Merge(AInspectDS.PmJobAssignment);
}
if (AInspectDS.Tables.Contains(PmGeneralApplicationTable.GetTableName())
&& (AInspectDS.PmGeneralApplication.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmGeneralApplicationTable.GetTableName()].Merge(AInspectDS.PmGeneralApplication);
}
if (AInspectDS.Tables.Contains(PmShortTermApplicationTable.GetTableName())
&& (AInspectDS.PmShortTermApplication.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmShortTermApplicationTable.GetTableName()].Merge(AInspectDS.PmShortTermApplication);
}
if (AInspectDS.Tables.Contains(PmYearProgramApplicationTable.GetTableName())
&& (AInspectDS.PmYearProgramApplication.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmYearProgramApplicationTable.GetTableName()].Merge(AInspectDS.PmYearProgramApplication);
}
}
else
{
TLogging.LogAtLevel(8, "TIndividualDataWebConnector.SubmitChangesServerSide: AInspectDS = nil!");
SubmissionResult = TSubmitChangesResult.scrNothingToBeSaved;
}
return SubmissionResult;
}
public static void FillFormDataFromApplicant(Int64 AEventPartnerKey, Int64 APartnerKey,
TFormDataApplicant AFormDataApplicant,
TFormLetterInfo AFormLetterInfo,
TDBTransaction AReadTransaction,
Int64 ASiteKey = 0,
Int32 ALocationKey = 0)
{
TPartnerClass PartnerClass;
String ShortName;
TStdPartnerStatusCode PartnerStatusCode;
if (AFormDataApplicant == null)
{
return;
}
if (MCommonMain.RetrievePartnerShortName(APartnerKey, out ShortName, out PartnerClass, out PartnerStatusCode, AReadTransaction))
{
// first retrieve all personnel information
TFormLettersPersonnelWebConnector.FillFormDataFromPersonnel(APartnerKey,
AFormDataApplicant,
AFormLetterInfo,
AReadTransaction,
ASiteKey,
ALocationKey);
// retrieve Applicant information
PmShortTermApplicationTable ShortTermAppTable;
PmShortTermApplicationRow ShortTermAppRow;
PmShortTermApplicationRow template = new PmShortTermApplicationTable().NewRowTyped(false);
template.PartnerKey = APartnerKey;
template.StConfirmedOption = AEventPartnerKey;
ShortTermAppTable = PmShortTermApplicationAccess.LoadUsingTemplate(template, AReadTransaction);
if (ShortTermAppTable.Count > 0)
{
ShortTermAppRow = (PmShortTermApplicationRow)ShortTermAppTable.Rows[0];
AFormDataApplicant.EventPartnerKey = AEventPartnerKey.ToString("0000000000");
AFormDataApplicant.ArrivalDate = ShortTermAppRow.Arrival;
AFormDataApplicant.ArrivalTime = new DateTime(ShortTermAppRow.Arrival == null ? 0 : ((DateTime)ShortTermAppRow.Arrival).Year,
ShortTermAppRow.Arrival == null ? 0 : ((DateTime)ShortTermAppRow.Arrival).Month,
ShortTermAppRow.Arrival == null ? 0 : ((DateTime)ShortTermAppRow.Arrival).Day,
ShortTermAppRow.ArrivalHour, ShortTermAppRow.ArrivalMinute, 0);
AFormDataApplicant.ArrivalFlightNumber = ShortTermAppRow.FromCongTravelInfo;
AFormDataApplicant.ArrivalComment = ShortTermAppRow.ArrivalComments;
AFormDataApplicant.DepartureDate = ShortTermAppRow.Departure;
AFormDataApplicant.DepartureTime = new DateTime(ShortTermAppRow.Departure == null ? 0 : ((DateTime)ShortTermAppRow.Arrival).Year,
ShortTermAppRow.Departure == null ? 0 : ((DateTime)ShortTermAppRow.Arrival).Month,
ShortTermAppRow.Departure == null ? 0 : ((DateTime)ShortTermAppRow.Arrival).Day,
ShortTermAppRow.DepartureHour, ShortTermAppRow.DepartureMinute, 0);
AFormDataApplicant.DepartureFlightNumber = ShortTermAppRow.ToCongTravelInfo;
AFormDataApplicant.DepartureComment = ShortTermAppRow.DepartureComments;
AFormDataApplicant.EventRole = ShortTermAppRow.StCongressCode;
AFormDataApplicant.CampaignType = ShortTermAppRow.ConfirmedOptionCode.Substring(5, 6);
AFormDataApplicant.FellowshipGroup = ShortTermAppRow.StFgCode;
TPartnerClass PartnerClassDummy = new TPartnerClass();
AFormDataApplicant.ChargedFieldKey = ShortTermAppRow.StFieldCharged.ToString("0000000000");
string chargedFieldName = "";
if (!ShortTermAppRow.IsStCurrentFieldNull())
{
TPartnerServerLookups.GetPartnerShortName(ShortTermAppRow.StCurrentField, out chargedFieldName, out PartnerClassDummy);
}
AFormDataApplicant.SendingFieldName = chargedFieldName;
/*ApplicantFormData.SendingFieldKey = ShortTermAppRow.StCurrentField.ToString("0000000000");
*
*
* string sendingFieldName;
* TPartnerServerLookups.GetPartnerShortName(ShortTermAppRow.StCurrentField, out sendingFieldName, out PartnerClassDummy);
* ApplicantFormData.SendingFieldName = sendingFieldName;*/
//TODO: SendingFieldKey & -name; ReceivingFieldKey & -name
PmGeneralApplicationTable GeneralAppTable;
PmGeneralApplicationRow GeneralAppRow;
GeneralAppTable = PmGeneralApplicationAccess.LoadByPrimaryKey(APartnerKey,
ShortTermAppRow.ApplicationKey,
ShortTermAppRow.RegistrationOffice,
AReadTransaction);
if (GeneralAppTable.Count > 0)
{
GeneralAppRow = (PmGeneralApplicationRow)GeneralAppTable.Rows[0];
AFormDataApplicant.RegistrationDate = GeneralAppRow.GenAppDate;
AFormDataApplicant.ApplicationComment = GeneralAppRow.Comment;
}
}
}
}
