private DataTable GetConferenceListTable(TDBTransaction AReadTransaction, string ATableName)
{
DataTable Table;
DataColumn[] Key = new DataColumn[1];
// Used eg. Select Event Dialog
Table = DBAccess.GetDBAccessObj(AReadTransaction).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.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' AND (" +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')"
,
ATableName, AReadTransaction);
Key[0] = Table.Columns[PPartnerTable.GetPartnerKeyDBName()];
Table.PrimaryKey = Key;
return(Table);
}
private void SelectAllRecords(System.Object sender, EventArgs e)
{
string SelectString = "";
foreach (DataRowView rowView in FPartnerSharingLocationDV)
{
DataRow row = rowView.Row;
if (SelectString.Length > 0)
{
SelectString = SelectString + ",";
}
SelectString = SelectString + row[PPartnerLocationTable.GetPartnerKeyDBName()].ToString();
}
clbAddress.SetCheckedStringList(SelectString);
}
/// <summary>
/// The screen has been shown
/// </summary>
private void RunOnceOnActivationManual()
{
// enable autofind in list for first character (so the user can press character to find list entry)
this.clbAddress.AutoFindColumn = ((Int16)(1));
this.clbAddress.AutoFindMode = Ict.Common.Controls.TAutoFindModeEnum.FirstCharacter;
clbAddress.SpecialKeys =
((SourceGrid.GridSpecialKeys)((((((SourceGrid.GridSpecialKeys.Arrows |
SourceGrid.GridSpecialKeys.PageDownUp) |
SourceGrid.GridSpecialKeys.Enter) |
SourceGrid.GridSpecialKeys.Escape) |
SourceGrid.GridSpecialKeys.Control) | SourceGrid.GridSpecialKeys.Shift)));
string CheckedColumn = "CHECKED";
string ShortNameColumn = PPartnerTable.GetPartnerShortNameDBName();
string PartnerKeyColumn = PPartnerLocationTable.GetPartnerKeyDBName();
string PartnerClassColumn = PPartnerTable.GetPartnerClassDBName();
string TelephoneColumn = PPartnerLocationTable.GetTelephoneNumberDBName();
string LocationTypeColumn = PPartnerLocationTable.GetLocationTypeDBName();
DataTable NewTable = FPartnerSharingLocationDV.ToTable(true,
new string[] { ShortNameColumn, PartnerKeyColumn, PartnerClassColumn, TelephoneColumn, LocationTypeColumn });
NewTable.Columns.Add(new DataColumn(CheckedColumn, typeof(bool)));
clbAddress.Columns.Clear();
clbAddress.AddCheckBoxColumn("", NewTable.Columns[CheckedColumn], 17, false);
clbAddress.AddTextColumn(Catalog.GetString("Name"), NewTable.Columns[ShortNameColumn], 240);
clbAddress.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), NewTable.Columns[PartnerKeyColumn], 90);
clbAddress.AddTextColumn(Catalog.GetString("Partner Class"), NewTable.Columns[PartnerClassColumn], 90);
clbAddress.AddTextColumn(Catalog.GetString("Telephone"), NewTable.Columns[TelephoneColumn], 130);
clbAddress.AddTextColumn(Catalog.GetString("Location Type"), NewTable.Columns[LocationTypeColumn], 90);
clbAddress.DataBindGrid(NewTable, ShortNameColumn, CheckedColumn, PartnerKeyColumn, false, true, false);
// initialize list of checked items
clbAddress.SetCheckedStringList("");
// this is just to avoid compiler warning from FMainDS being initialized but not used
if (FMainDS.IsInitialized)
{
}
}
/// <summary>
/// Private method used by ExportPartners().
/// For the given partnerKey, updates given references countryCode and siteKey.
/// If there is no location for the given partnerKey, leaves them as they are.
/// </summary>
/// <param name="MainDS">Reference to the already filled Datastructure</param>
/// <param name="partnerKey"></param>
/// <param name="countryCode"></param>
/// <param name="siteKey"></param>
/// <returns>
/// True: if a location was found for given key and the countryCode
/// and siteKey were updated. False otherwise.
/// </returns>
private static bool UpdateCountryAndSiteForGivenPK(
PartnerEditTDS MainDS,
long partnerKey,
ref string countryCode /* default could be "" */,
ref Int64 siteKey /* default could be -1 */
)
{
bool retval = false;
// Find partnerLocation for given partner_key
DataView partnerLocationView = MainDS.PPartnerLocation.DefaultView;
partnerLocationView.RowFilter = PPartnerLocationTable.GetPartnerKeyDBName() + " = " + partnerKey.ToString();
if (partnerLocationView.Count > 0)
{
// partnerLocation: links one partner to possibly several Locations
// Just get the first one for now (and disregard the others).
// TODO: could determine the best address and use that
PPartnerLocationRow partnerLocationRow = (PPartnerLocationRow)partnerLocationView[0].Row;
DataView locationView = MainDS.PLocation.DefaultView;
locationView.RowFilter =
PLocationTable.GetSiteKeyDBName() + "=" + partnerLocationRow.SiteKey.ToString() + " AND " +
PLocationTable.GetLocationKeyDBName() + "=" + partnerLocationRow.LocationKey.ToString();
if (locationView.Count > 0)
{
PLocationRow locationRow = (PLocationRow)locationView[0].Row;
countryCode = locationRow.CountryCode;
siteKey = locationRow.SiteKey;
retval = true;
}
else // if there is a partner_location, there has _got_ to be the corresponding location
{
throw new Exception("Error in application: I can't find the Location with LocationKey " +
partnerLocationRow.LocationKey.ToString() +
" (Sitekey " + partnerLocationRow.SiteKey.ToString() + ")"
);
}
}
return(retval);
}
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 = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
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 = DBAccess.GDBAccessObj.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);
}
/// <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>
/// Creates a list of locations for a given Partner.
/// </summary>
/// <remarks>Corresponds in parts with with Progress 4GL Method
/// 'GetPartnerLocations' in partner/maillib.p.</remarks>
/// <param name="APartnerKey">PartnerKey of the Partner being processed.</param>
/// <param name="AMailingAddressesOnly">If true: only include addresses with mailing flag set.</param>
/// <param name="AIncludeCurrentAddresses">If true: include current addresses.</param>
/// <param name="AIncludeFutureAddresses">If true: include future addresses.</param>
/// <param name="AIncludeExpiredAddresses">If true: include expired addresses.</param>
/// <param name="APartnerLocations">The Locations of the Partner being processed.</param>
/// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null
/// gets passed then the Method executes DB commands with the 'globally available'
/// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the instance that gets passed in with this
/// Argument!</param>
/// <returns>False if an invalid PartnerKey was passed in or if Petra Security
/// denied access to the Partner, otherwise true.</returns>
public static bool GetPartnerLocations(Int64 APartnerKey,
bool AMailingAddressesOnly,
bool AIncludeCurrentAddresses,
bool AIncludeFutureAddresses,
bool AIncludeExpiredAddresses,
out PPartnerLocationTable APartnerLocations,
TDataBase ADataBase = null)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
String SelectSQL;
DataSet FillDataSet;
OdbcParameter param;
// Initialise out Argument
APartnerLocations = null;
if (APartnerKey > 0)
{
TLogging.LogAtLevel(8, "TMailing.GetPartnerLocations: Checking access to Partner.");
if (TSecurity.CanAccessPartnerByKey(APartnerKey, false, ADataBase) ==
TPartnerAccessLevelEnum.palGranted)
{
ReadTransaction = DBAccess.GetDBAccessObj(ADataBase).GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// Load Partner Locations, taking passed in restrictions into account.
try
{
SelectSQL =
"SELECT *" +
" FROM PUB_" + PPartnerLocationTable.GetTableDBName() +
" WHERE " + PPartnerLocationTable.GetPartnerKeyDBName() + " = ?" +
" AND (NOT ? = true OR (? = true AND " + PPartnerLocationTable.GetSendMailDBName() + " = true))" +
" AND ((? = true AND ((" + PPartnerLocationTable.GetDateEffectiveDBName() + " <= ?" +
" OR " + PPartnerLocationTable.GetDateEffectiveDBName() + " IS NULL)" +
" AND (" + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= ?" +
" OR " + PPartnerLocationTable.GetDateGoodUntilDBName() + " IS NULL)))" +
" OR (? = true AND " + PPartnerLocationTable.GetDateEffectiveDBName() + " > ?)" +
" OR (? = true AND " + PPartnerLocationTable.GetDateGoodUntilDBName() + " < ?))";
List <OdbcParameter> parameters = new List <OdbcParameter>();
param = new OdbcParameter("PartnerKey", OdbcType.Decimal, 10);
param.Value = APartnerKey;
parameters.Add(param);
param = new OdbcParameter("MailingAddressOnly1", OdbcType.Bit);
param.Value = AMailingAddressesOnly;
parameters.Add(param);
param = new OdbcParameter("MailingAddressOnly2", OdbcType.Bit);
param.Value = AMailingAddressesOnly;
parameters.Add(param);
param = new OdbcParameter("IncludeCurrentAddresses", OdbcType.Bit);
param.Value = AIncludeCurrentAddresses;
parameters.Add(param);
param = new OdbcParameter("TodaysDate1", OdbcType.Date);
param.Value = DateTime.Now;
parameters.Add(param);
param = new OdbcParameter("TodaysDate2", OdbcType.Date);
param.Value = DateTime.Now;
parameters.Add(param);
param = new OdbcParameter("IncludeFutureAddresses", OdbcType.Bit);
param.Value = AIncludeFutureAddresses;
parameters.Add(param);
param = new OdbcParameter("TodaysDate3", OdbcType.Date);
param.Value = DateTime.Now;
parameters.Add(param);
param = new OdbcParameter("IncludeExpiredAddresses", OdbcType.Bit);
param.Value = AIncludeExpiredAddresses;
parameters.Add(param);
param = new OdbcParameter("TodaysDate4", OdbcType.Date);
param.Value = DateTime.Now;
parameters.Add(param);
/*
* Our out Argument 'APartnerLocations' is a Typed DataTable, but SelectDT
* returns an untyped DataTable, therefore we need to create a Typed DataTable
* that contains the data of the returned untyped DataTable!
*/
FillDataSet = new DataSet();
APartnerLocations = new PPartnerLocationTable(PPartnerLocationTable.GetTableDBName());
FillDataSet.Tables.Add(APartnerLocations);
DBAccess.GetDBAccessObj(ADataBase).Select(FillDataSet, SelectSQL,
PPartnerLocationTable.GetTableDBName(),
ReadTransaction,
parameters.ToArray());
// TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: FillDataSet.Tables.Count: " + FillDataSet.Tables.Count.ToString());
FillDataSet.Tables.Remove(APartnerLocations);
if (APartnerLocations.Rows.Count > 0)
{
// TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: Found " + APartnerLocations.Rows.Count.ToString() + " PartnerLocations found for Partner " + APartnerKey.ToString() + ".");
}
else
{
/*
* /* No Rows returned = no PartnerLocations for Partner.
* That shouldn't happen with existing Partners, but if it does (eg. non-existing
* PartnerKey passed in) we return an empty Typed DataTable.
*/
// TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: No PartnerLocations found for Partner " + APartnerKey.ToString() + "!");
APartnerLocations = new PPartnerLocationTable();
}
}
finally
{
if (NewTransaction)
{
DBAccess.GetDBAccessObj(ADataBase).CommitTransaction();
TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: committed own transaction.");
}
}
return(true);
}
else
{
TLogging.LogAtLevel(8, "TMailing.GetPartnerLocations: Access to Partner DENIED!");
// Petra Security prevents us from accessing this Partner -> return false;
return(false);
}
}
else
{
// Invalid PartnerKey -> return false;
return(false);
}
}
/// <summary>
/// extend an extract from a list of best addresses
/// </summary>
/// <param name="AExtractId">Extract Id of the Extract to extend</param>
/// <param name="APartnerKeysTable"></param>
/// <param name="APartnerKeyColumn">number of the column that contains the partner keys</param>
/// <param name="ASiteKeyColumn">number of the column that contains the site keys</param>
/// <param name="ALocationKeyColumn">number of the column that contains the location keys</param>
/// <param name="AIgnoreDuplicates">true if duplicates should be looked out for. Can be set to false if called only once and not several times per extract.</param>
/// <param name="ACommitTransaction">true if transaction should be committed at end of method</param>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
bool AIgnoreDuplicates,
bool ACommitTransaction)
{
Boolean NewTransaction;
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
MExtractTable ExtractTable = new MExtractTable();
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, WriteTransaction);
// Location Keys need to be determined as extracts do not only need partner keys but
// also Location Keys.
DetermineBestLocationKeys(APartnerKeysTable, APartnerKeyColumn, ASiteKeyColumn,
ALocationKeyColumn, out PartnerLocationKeysTable,
WriteTransaction);
// use the returned table which contains partner and location keys to build the extract
foreach (PPartnerLocationRow PartnerLocationRow in PartnerLocationKeysTable.Rows)
{
PartnerKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
if (PartnerKey > 0)
{
RecordCounter += 1;
TLogging.Log("Preparing Partner " + PartnerKey.ToString() + " (Record Number " + RecordCounter.ToString() + ")");
// add row for partner to extract and fill with contents
MExtractRow NewRow = ExtractTable.NewRowTyped();
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = PartnerKey;
NewRow.SiteKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetSiteKeyDBName()]);
NewRow.LocationKey = Convert.ToInt32(PartnerLocationRow[PPartnerLocationTable.GetLocationKeyDBName()]);
// only add row if it does not already exist for this partner
if (AIgnoreDuplicates || !ExtractTable.Rows.Contains(new object[] { NewRow.ExtractId, NewRow.PartnerKey, NewRow.SiteKey }))
{
ExtractTable.Rows.Add(NewRow);
}
}
}
if (ExtractTable.Rows.Count > 0)
{
// update field in extract master for quick access to number of partners in extract
MExtractMasterTable ExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(AExtractId, WriteTransaction);
ExtractMaster[0].KeyCount = ExtractTable.Rows.Count;
ExtractTable.ThrowAwayAfterSubmitChanges = true; // no need to keep data as this increases speed significantly
MExtractAccess.SubmitChanges(ExtractTable, WriteTransaction);
MExtractMasterAccess.SubmitChanges(ExtractMaster, WriteTransaction);
}
if (ACommitTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured while extending an Extract from a list of Partner Keys:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
/// <summary>
/// extend an extract from a list of best addresses
/// </summary>
/// <param name="AExtractId">Extract Id of the Extract to extend</param>
/// <param name="APartnerKeysTable"></param>
/// <param name="APartnerKeyColumn">number of the column that contains the partner keys</param>
/// <param name="ASiteKeyColumn">number of the column that contains the site keys</param>
/// <param name="ALocationKeyColumn">number of the column that contains the location keys</param>
/// <param name="AIgnoreDuplicates">true if duplicates should be looked out for. Can be set to false if called only once and not several times per extract.</param>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
bool AIgnoreDuplicates)
{
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
TDBTransaction Transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
MExtractTable ExtractTable = new MExtractTable();
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, Transaction);
// Location Keys need to be determined as extracts do not only need partner keys but
// also Location Keys.
DetermineBestLocationKeys(APartnerKeysTable, APartnerKeyColumn, ASiteKeyColumn,
ALocationKeyColumn, out PartnerLocationKeysTable,
Transaction);
// use the returned table which contains partner and location keys to build the extract
foreach (PPartnerLocationRow PartnerLocationRow in PartnerLocationKeysTable.Rows)
{
PartnerKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
if (PartnerKey > 0)
{
RecordCounter += 1;
TLogging.LogAtLevel(1, "Preparing Partner " + PartnerKey + " (Record Number " + RecordCounter + ")");
// add row for partner to extract and fill with contents
MExtractRow NewRow = ExtractTable.NewRowTyped();
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = PartnerKey;
NewRow.SiteKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetSiteKeyDBName()]);
NewRow.LocationKey = Convert.ToInt32(PartnerLocationRow[PPartnerLocationTable.GetLocationKeyDBName()]);
// only add row if it does not already exist for this partner
if (AIgnoreDuplicates || !ExtractTable.Rows.Contains(new object[] { NewRow.ExtractId, NewRow.PartnerKey, NewRow.SiteKey }))
{
ExtractTable.Rows.Add(NewRow);
}
}
}
if (ExtractTable.Rows.Count > 0)
{
// update field in extract master for quick access to number of partners in extract
MExtractMasterTable ExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(AExtractId, Transaction);
ExtractMaster[0].KeyCount = ExtractTable.Rows.Count;
ExtractTable.ThrowAwayAfterSubmitChanges = true; // no need to keep data as this increases speed significantly
MExtractAccess.SubmitChanges(ExtractTable, Transaction);
MExtractMasterAccess.SubmitChanges(ExtractMaster, Transaction);
}
});
}
/// <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);
TLogging.Log("after saving donors");
}
/// Upgrade to version 2015-01
public static bool UpgradeDatabase201412_201501()
{
// There are no new tables and fields
TDBTransaction SubmitChangesTransaction = null;
TSubmitChangesResult SubmissionResult = TSubmitChangesResult.scrError;
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref SubmitChangesTransaction,
ref SubmissionResult,
delegate
{
PPartnerAttributeTable partnerattributes = new PPartnerAttributeTable();
PPartnerLocationTable partnerlocations = PPartnerLocationAccess.LoadAll(SubmitChangesTransaction);
// this update only works for very simple databases, only one partner location record per partner...
if (partnerlocations.Count > 1000)
{
throw new Exception("the upgrade has not been optimized for huge databases");
}
List <Int64> PartnerKeys = new List <Int64>();
foreach (PPartnerLocationRow partnerlocation in partnerlocations.Rows)
{
if (!PartnerKeys.Contains(partnerlocation.PartnerKey))
{
PartnerKeys.Add(partnerlocation.PartnerKey);
}
else
{
TLogging.Log("several locations for partner " + partnerlocation.PartnerKey.ToString());
}
}
// Number for the p_sequence_i Column. Gets increased with every p_partner_attribute record that gets produced!
int SequenceNumber = 0;
foreach (Int64 partnerkey in PartnerKeys)
{
// Get that Partner's p_partner_location records from PPartnerLocationRecords
DataRow[] CurrentRows = partnerlocations.Select(PPartnerLocationTable.GetPartnerKeyDBName() + " = " + partnerkey.ToString());
if (CurrentRows.Length == 0)
{
continue;
}
DataTable PPartnersLocationsDT = GetNewPPartnerLocationTableInstance();
foreach (DataRow r in CurrentRows)
{
PPartnersLocationsDT.Rows.Add(r.ItemArray);
}
TLocationPK bestAddress = Calculations.DetermineBestAddress(PPartnersLocationsDT);
int IndexPhone = 0;
int IndexEmail = 0;
int IndexFax = 0;
int IndexUrl = 0;
int IndexMobile = 0;
List <string> AvoidDuplicates = new List <string>();
string AttributeConcatenated;
foreach (PPartnerLocationRow partnerlocation in PPartnersLocationsDT.Rows)
{
bool primaryAddress =
(bestAddress.LocationKey == partnerlocation.LocationKey && bestAddress.SiteKey == partnerlocation.SiteKey);
bool currentAddress = (((int)partnerlocation[PARTNERLOCATION_ICON_COLUMN]) == 1);
bool businessAddress = (partnerlocation.LocationType == "BUSINESS" || partnerlocation.LocationType == "FIELD");
// TODO: avoid duplicate entries with the same type
if (!partnerlocation.IsEmailAddressNull())
{
PPartnerAttributeRow partnerattribute = partnerattributes.NewRowTyped();
partnerattribute.Sequence = SequenceNumber++;
partnerattribute.PartnerKey = partnerlocation.PartnerKey;
partnerattribute.Value = partnerlocation.EmailAddress;
partnerattribute.AttributeType = MPartnerConstants.ATTR_TYPE_EMAIL;
partnerattribute.Current = currentAddress;
partnerattribute.Primary = primaryAddress;
partnerattribute.Index = IndexEmail++;
partnerattribute.Specialised = businessAddress;
partnerattribute.NoLongerCurrentFrom = partnerlocation.DateGoodUntil;
AttributeConcatenated = ConcatPartnerAttributes(partnerattribute);
if (!AvoidDuplicates.Contains(AttributeConcatenated))
{
partnerattributes.Rows.Add(partnerattribute);
AvoidDuplicates.Add(AttributeConcatenated);
}
else
{
TLogging.Log("dropping duplicate " + AttributeConcatenated);
}
partnerlocation.SetEmailAddressNull();
}
if (!partnerlocation.IsTelephoneNumberNull())
{
if (!partnerlocation.IsExtensionNull())
{
partnerlocation.TelephoneNumber += "-" + partnerlocation.Extension;
}
PPartnerAttributeRow partnerattribute = partnerattributes.NewRowTyped();
partnerattribute.Sequence = SequenceNumber++;
partnerattribute.PartnerKey = partnerlocation.PartnerKey;
partnerattribute.Value = partnerlocation.TelephoneNumber;
partnerattribute.AttributeType = MPartnerConstants.ATTR_TYPE_PHONE;
partnerattribute.Current = currentAddress;
partnerattribute.Primary = primaryAddress;
partnerattribute.Index = IndexPhone++;
partnerattribute.Specialised = businessAddress;
partnerattribute.NoLongerCurrentFrom = partnerlocation.DateGoodUntil;
AttributeConcatenated = ConcatPartnerAttributes(partnerattribute);
if (!AvoidDuplicates.Contains(AttributeConcatenated))
{
partnerattributes.Rows.Add(partnerattribute);
AvoidDuplicates.Add(AttributeConcatenated);
}
else
{
TLogging.Log("dropping duplicate " + AttributeConcatenated);
}
partnerlocation.SetTelephoneNumberNull();
}
if (!partnerlocation.IsFaxNumberNull())
{
if (!partnerlocation.IsFaxExtensionNull())
{
partnerlocation.FaxNumber += "-" + partnerlocation.FaxExtension;
}
PPartnerAttributeRow partnerattribute = partnerattributes.NewRowTyped();
partnerattribute.Sequence = SequenceNumber++;
partnerattribute.PartnerKey = partnerlocation.PartnerKey;
partnerattribute.Value = partnerlocation.FaxNumber;
partnerattribute.AttributeType = MPartnerConstants.ATTR_TYPE_FAX;
partnerattribute.Current = currentAddress;
partnerattribute.Primary = primaryAddress;
partnerattribute.Index = IndexFax++;
partnerattribute.Specialised = businessAddress;
partnerattribute.NoLongerCurrentFrom = partnerlocation.DateGoodUntil;
AttributeConcatenated = ConcatPartnerAttributes(partnerattribute);
if (!AvoidDuplicates.Contains(AttributeConcatenated))
{
partnerattributes.Rows.Add(partnerattribute);
AvoidDuplicates.Add(AttributeConcatenated);
}
else
{
TLogging.Log("dropping duplicate " + AttributeConcatenated);
}
partnerlocation.SetFaxNumberNull();
}
if (!partnerlocation.IsAlternateTelephoneNull())
{
PPartnerAttributeRow partnerattribute = partnerattributes.NewRowTyped();
partnerattribute.Sequence = SequenceNumber++;
partnerattribute.PartnerKey = partnerlocation.PartnerKey;
partnerattribute.Value = partnerlocation.AlternateTelephone;
partnerattribute.AttributeType = MPartnerConstants.ATTR_TYPE_PHONE;
partnerattribute.Current = currentAddress;
partnerattribute.Primary = primaryAddress;
partnerattribute.Index = IndexPhone++;
partnerattribute.Specialised = businessAddress;
partnerattribute.NoLongerCurrentFrom = partnerlocation.DateGoodUntil;
AttributeConcatenated = ConcatPartnerAttributes(partnerattribute);
if (!AvoidDuplicates.Contains(AttributeConcatenated))
{
partnerattributes.Rows.Add(partnerattribute);
AvoidDuplicates.Add(AttributeConcatenated);
}
else
{
TLogging.Log("dropping duplicate " + AttributeConcatenated);
}
partnerlocation.SetAlternateTelephoneNull();
}
if (!partnerlocation.IsMobileNumberNull())
{
PPartnerAttributeRow partnerattribute = partnerattributes.NewRowTyped();
partnerattribute.Sequence = SequenceNumber++;
partnerattribute.PartnerKey = partnerlocation.PartnerKey;
partnerattribute.Value = partnerlocation.MobileNumber;
partnerattribute.AttributeType = MPartnerConstants.ATTR_TYPE_MOBILE_PHONE;
partnerattribute.Current = currentAddress;
partnerattribute.Primary = primaryAddress;
partnerattribute.Index = IndexMobile++;
partnerattribute.Specialised = businessAddress;
partnerattribute.NoLongerCurrentFrom = partnerlocation.DateGoodUntil;
AttributeConcatenated = ConcatPartnerAttributes(partnerattribute);
if (!AvoidDuplicates.Contains(AttributeConcatenated))
{
partnerattributes.Rows.Add(partnerattribute);
AvoidDuplicates.Add(AttributeConcatenated);
}
else
{
TLogging.Log("dropping duplicate " + AttributeConcatenated);
}
partnerlocation.SetMobileNumberNull();
}
if (!partnerlocation.IsUrlNull())
{
PPartnerAttributeRow partnerattribute = partnerattributes.NewRowTyped();
partnerattribute.Sequence = SequenceNumber++;
partnerattribute.PartnerKey = partnerlocation.PartnerKey;
partnerattribute.Value = partnerlocation.Url;
partnerattribute.AttributeType = MPartnerConstants.ATTR_TYPE_WEBSITE;
partnerattribute.Current = currentAddress;
partnerattribute.Primary = primaryAddress;
partnerattribute.Index = IndexUrl++;
partnerattribute.Specialised = businessAddress;
partnerattribute.NoLongerCurrentFrom = partnerlocation.DateGoodUntil;
AttributeConcatenated = ConcatPartnerAttributes(partnerattribute);
if (!AvoidDuplicates.Contains(AttributeConcatenated))
{
partnerattributes.Rows.Add(partnerattribute);
AvoidDuplicates.Add(AttributeConcatenated);
}
else
{
TLogging.Log("dropping duplicate " + AttributeConcatenated);
}
partnerlocation.SetUrlNull();
}
}
}
PPartnerLocationAccess.SubmitChanges(partnerlocations, SubmitChangesTransaction);
PPartnerAttributeAccess.SubmitChanges(partnerattributes, SubmitChangesTransaction);
SubmissionResult = TSubmitChangesResult.scrOK;
});
return(true);
}
public static DataTable GetEventUnits(bool AIncludeConferenceUnits, bool AIncludeOutreachUnits,
string AEventName, bool AIncludeLocationData, bool ACurrentAndFutureEventsOnly)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
DataColumn[] Key = new DataColumn[1];
DataTable Events = new DataTable();
if (AEventName == "*")
{
AEventName = "";
}
else if (AEventName.EndsWith("*"))
{
AEventName = AEventName.Substring(0, AEventName.Length - 1);
}
if (TLogging.DL >= 9)
{
Console.WriteLine("GetEventUnits called!");
}
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
string SqlStmt =
"SELECT DISTINCT " +
PPartnerTable.GetPartnerShortNameDBName() +
", " + PPartnerTable.GetPartnerClassDBName() +
", " + PUnitTable.GetOutreachCodeDBName();
if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
{
SqlStmt = SqlStmt +
", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName();
}
SqlStmt = SqlStmt +
", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
", " + PUnitTable.GetUnitTypeCodeDBName() +
" FROM pub_" + PPartnerTable.GetTableDBName() +
", pub_" + PUnitTable.GetTableDBName();
if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
{
SqlStmt = SqlStmt +
", pub_" + PLocationTable.GetTableDBName() +
", pub_" + PPartnerLocationTable.GetTableDBName() +
", pub_" + PCountryTable.GetTableDBName();
}
SqlStmt = SqlStmt +
" WHERE " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND ";
if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
{
SqlStmt = SqlStmt +
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 ";
}
SqlStmt = SqlStmt +
PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " +
PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' ";
// add criteria for conference and/or outreach
String ConferenceWhereClause = "(" +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')";
String OutreachWhereClause = PUnitTable.GetOutreachCodeDBName() + " IS NOT NULL AND " +
PUnitTable.GetOutreachCodeDBName() + " <> '' AND (" +
PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONF%' AND " +
PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONG%')";
if (AIncludeConferenceUnits &&
AIncludeOutreachUnits)
{
SqlStmt = SqlStmt + " AND ((" + ConferenceWhereClause + ") OR (" + OutreachWhereClause + "))";
}
else if (AIncludeConferenceUnits)
{
SqlStmt = SqlStmt + " AND (" + ConferenceWhereClause + ")";
}
else if (AIncludeOutreachUnits)
{
SqlStmt = SqlStmt + " AND (" + OutreachWhereClause + ")";
}
// add criteria for event name filter
if (AEventName.Length > 0)
{
// in case there is a filter set for the event name
AEventName = AEventName.Replace('*', '%') + "%";
SqlStmt = SqlStmt + " AND " + PUnitTable.GetUnitNameDBName() +
" LIKE '" + AEventName + "'";
}
if (ACurrentAndFutureEventsOnly)
{
SqlStmt = SqlStmt + " AND " + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= ?";
SqlParameterList.Add(new OdbcParameter("param_date", OdbcType.Date)
{
Value = DateTime.Today.Date
});
}
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName();
Events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "events",
ReadTransaction, SqlParameterList.ToArray());
Key[0] = Events.Columns[PPartnerTable.GetPartnerKeyDBName()];
Events.PrimaryKey = Key;
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
if (TLogging.DL >= 7)
{
Console.WriteLine("GetEventUnits: committed own transaction.");
}
}
}
return(Events);
}
/// <summary>
/// return an XmlDocument with all partner info;
/// the partners are grouped by class, country, status, and sitekey
/// </summary>
/// <returns></returns>
public static string ExportPartners()
{
PartnerEditTDS MainDS = new PartnerEditTDS();
LoadDataFromDB(ref MainDS);
// Group partners into categories.
//
// A partner's category is defined by his: class, country, status, and sitekey
// It is stored as a string e.g. "FAMILY,DE,ACTIVE,0".
//
SortedList <string, List <long> > PartnerCategories = GroupPartnersIntoCategories(MainDS);
// create XML structure for each category
XmlDocument PartnerData = TYml2Xml.CreateXmlDocument();
XmlNode rootNode = PartnerData.FirstChild.NextSibling;
Int32 groupCounter = 0;
foreach (string category in PartnerCategories.Keys)
{
// get category data
groupCounter++;
XmlElement groupNode = PartnerData.CreateElement("PartnerGroup" + groupCounter.ToString());
rootNode.AppendChild(groupNode);
Int32 partnerCounter = 0;
string[] categoryDetails = category.Split(new char[] { ',' });
// may want to skip the categories with sitekey = -1
// right now, we still export them and ignore the partners 0 and 1000000 later
groupNode.SetAttribute("class", categoryDetails[0]);
groupNode.SetAttribute("Country", categoryDetails[1]);
groupNode.SetAttribute("status", categoryDetails[2]);
groupNode.SetAttribute("SiteKey", categoryDetails[3]);
List <long> partnerKeys = PartnerCategories[category];
foreach (long partnerKey in partnerKeys)
{
if ((partnerKey != 0) && (partnerKey != 1000000)) // skip organization root and the 0 when exporting
{
MainDS.PPartner.DefaultView.RowFilter = PPartnerTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
PPartnerRow partnerRow = (PPartnerRow)MainDS.PPartner.DefaultView[0].Row;
PFamilyRow familyRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_FAMILY)
{
MainDS.PFamily.DefaultView.RowFilter = PFamilyTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
familyRow = (PFamilyRow)MainDS.PFamily.DefaultView[0].Row;
}
PPersonRow personRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_PERSON)
{
MainDS.PPerson.DefaultView.RowFilter = PPersonTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
personRow = (PPersonRow)MainDS.PPerson.DefaultView[0].Row;
}
POrganisationRow organisationRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_ORGANISATION)
{
MainDS.POrganisation.DefaultView.RowFilter = POrganisationTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
organisationRow = (POrganisationRow)MainDS.POrganisation.DefaultView[0].Row;
}
PUnitRow unitRow = null;
UmUnitStructureRow unitStructureRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_UNIT)
{
MainDS.PUnit.DefaultView.RowFilter = PUnitTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
unitRow = (PUnitRow)MainDS.PUnit.DefaultView[0].Row;
MainDS.UmUnitStructure.DefaultView.RowFilter = UmUnitStructureTable.GetChildUnitKeyDBName() + " = " + partnerKey.ToString();
long numParents = MainDS.UmUnitStructure.DefaultView.Count;
if (numParents == 1)
{
unitStructureRow = (UmUnitStructureRow)MainDS.UmUnitStructure.DefaultView[0].Row;
}
else
{
throw new Exception(
"Units must have exactly one ParentUnit. " +
"The unit with partnerKey " + partnerKey.ToString() + " has " +
numParents.ToString() + ".");
}
}
PBankRow BankRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_BANK)
{
MainDS.PBank.DefaultView.RowFilter = PBankTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
BankRow = (PBankRow)MainDS.PBank.DefaultView[0].Row;
}
partnerCounter++;
XmlElement partnerNode = PartnerData.CreateElement("Partner" + partnerCounter.ToString());
groupNode.AppendChild(partnerNode);
partnerNode.SetAttribute("PartnerKey", partnerRow.PartnerKey.ToString());
//groupNode.SetAttribute("ShortName", partnerRow.PartnerShortName.ToString());
if (personRow != null)
{
partnerNode.SetAttribute("FirstName", personRow.FirstName.ToString());
partnerNode.SetAttribute("LastName", personRow.FamilyName.ToString());
partnerNode.SetAttribute("Title", personRow.Title.ToString());
}
else if (familyRow != null)
{
partnerNode.SetAttribute("FirstName", familyRow.FirstName.ToString());
partnerNode.SetAttribute("LastName", familyRow.FamilyName.ToString());
partnerNode.SetAttribute("Title", familyRow.Title.ToString());
}
else if (organisationRow != null)
{
partnerNode.SetAttribute("Name", organisationRow.OrganisationName.ToString());
}
else if (unitRow != null)
{
partnerNode.SetAttribute("Name", unitRow.UnitName.ToString());
partnerNode.SetAttribute("UnitTypeCode", unitRow.UnitTypeCode.ToString());
if (unitStructureRow != null)
{
partnerNode.SetAttribute("ParentUnitKey", unitStructureRow.ParentUnitKey.ToString());
}
}
if (BankRow != null)
{
partnerNode.SetAttribute("BranchName", BankRow.BranchName);
partnerNode.SetAttribute("BranchCode", BankRow.BranchCode);
partnerNode.SetAttribute("BranchBic", BankRow.Bic);
partnerNode.SetAttribute("EpFormatFile", BankRow.EpFormatFile);
}
partnerNode.SetAttribute("CreatedAt", partnerRow.DateCreated.Value.ToString("yyyy-MM-dd HH:mm:ss"));
// special types
string specialTypes = "";
MainDS.PPartnerType.DefaultView.RowFilter = PPartnerTypeTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
foreach (DataRowView rv in MainDS.PPartnerType.DefaultView)
{
if (specialTypes.Length > 0)
{
specialTypes += ", ";
}
specialTypes += ((PPartnerTypeRow)rv.Row).TypeCode;
}
if (specialTypes.Length > 0)
{
partnerNode.SetAttribute("SpecialTypes", specialTypes);
}
// addresses
DataView partnerLocationView = MainDS.PPartnerLocation.DefaultView;
partnerLocationView.RowFilter =
PPartnerLocationTable.GetPartnerKeyDBName() + " = " + partnerRow.PartnerKey.ToString() +
"AND " + PPartnerLocationTable.GetLocationKeyDBName() + " <> 0 "; // ignore invalid addresses
Int32 addressCounter = 0;
foreach (DataRowView rv in partnerLocationView)
{
XmlElement addressNode = PartnerData.CreateElement("Address" + (addressCounter > 0 ? addressCounter.ToString() : ""));
addressCounter++;
partnerNode.AppendChild(addressNode);
PPartnerLocationRow partnerLocationRow = (PPartnerLocationRow)rv.Row;
DataView locationView = MainDS.PLocation.DefaultView;
locationView.RowFilter =
PLocationTable.GetSiteKeyDBName() + "=" + partnerLocationRow.SiteKey.ToString() + " AND " +
PLocationTable.GetLocationKeyDBName() + "=" + partnerLocationRow.LocationKey.ToString();
if (locationView.Count > 0)
{
PLocationRow locationRow = (PLocationRow)locationView[0].Row;
addressNode.SetAttribute("Street", locationRow.StreetName);
addressNode.SetAttribute("City", locationRow.City);
addressNode.SetAttribute("PostCode", locationRow.PostalCode);
}
addressNode.SetAttribute("Email", partnerLocationRow.EmailAddress);
addressNode.SetAttribute("Phone", partnerLocationRow.TelephoneNumber);
addressNode.SetAttribute("MobilePhone", partnerLocationRow.MobileNumber);
}
// TODO: notes
// TODO: This doesn't export as much data as it should?
}
}
}
return(TXMLParser.XmlToString(PartnerData));
}
