public static int CanFamilyMergeIntoDifferentClass(long APartnerKey)
{
int ReturnValue = 0;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
ref Transaction,
delegate
{
PPersonTable PersonTable = PPersonAccess.LoadViaPFamily(APartnerKey, Transaction);
if (PersonTable.Rows.Count > 0)
{
ReturnValue = 1;
}
AGiftDetailTable GiftDetailTable = AGiftDetailAccess.LoadViaPPartnerRecipientKey(APartnerKey, Transaction);
if (GiftDetailTable.Rows.Count > 0)
{
ReturnValue = 2;
}
PBankingDetailsTable BankingDetailsTable = PBankingDetailsAccess.LoadViaPPartner(APartnerKey, Transaction);
if (BankingDetailsTable.Rows.Count > 0)
{
ReturnValue = 3;
}
});
return(ReturnValue);
}
/// <summary>
/// return a list of all applicants for a given event
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="AEventCode"></param>
/// <param name="ARegisteringOffice"></param>
/// <param name="AApplicationStatus"></param>
/// <param name="ARole"></param>
/// <param name="AClearJSONData"></param>
/// <param name="ATransaction"></param>
/// <returns></returns>
private static ConferenceApplicationTDS GetApplications(
ref ConferenceApplicationTDS AMainDS,
string AEventCode,
Int64 ARegisteringOffice,
string AApplicationStatus,
string ARole,
bool AClearJSONData,
TDBTransaction ATransaction)
{
LoadApplicationsFromDB(ref AMainDS,
AEventCode, ARegisteringOffice,
ARole, new Nullable <Int64>(),
ATransaction);
DataView PersonView = AMainDS.PPerson.DefaultView;
PersonView.Sort = PPersonTable.GetPartnerKeyDBName();
DataView GenAppView = AMainDS.PmGeneralApplication.DefaultView;
GenAppView.Sort = PmGeneralApplicationTable.GetPartnerKeyDBName() + "," +
PmGeneralApplicationTable.GetApplicationKeyDBName() + "," + PmGeneralApplicationTable.GetRegistrationOfficeDBName();
if (AMainDS.HasChanges())
{
AMainDS.EnforceConstraints = false;
AMainDS.AcceptChanges();
}
return(AMainDS);
}
/// <summary>
/// Returns the Family Members of a Family.
/// </summary>
/// <param name="AFamilyPartnerKey">PartnerKey of the FAMILY.</param>
/// <param name="AReadTransaction">Open DB Transaction.</param>
/// <returns>An Instance of <see cref="PartnerInfoTDSFamilyMembersTable" />.
/// If there were Family Members, there will be one DataRow for each Family Member.</returns>
private static PartnerInfoTDSFamilyMembersTable GetFamilyMembers(Int64 AFamilyPartnerKey,
TDBTransaction AReadTransaction)
{
OdbcParameter[] ParametersArray;
DataSet TmpDS;
PPersonTable FamilyPersonsDT;
PartnerInfoTDSFamilyMembersRow NewRow;
PartnerInfoTDSFamilyMembersTable FamilyMembersDT;
FamilyMembersDT = new PartnerInfoTDSFamilyMembersTable();
ParametersArray = new OdbcParameter[1];
ParametersArray[0] = new OdbcParameter("", OdbcType.Decimal, 10);
ParametersArray[0].Value = (System.Object)AFamilyPartnerKey;
TmpDS = new DataSet();
FamilyPersonsDT = new PPersonTable();
TmpDS.Tables.Add(FamilyPersonsDT);
AReadTransaction.DataBaseObj.Select(TmpDS,
"SELECT " + "PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ", " +
PPersonTable.GetFamilyNameDBName() + ", " +
PPersonTable.GetTitleDBName() + ", " +
PPersonTable.GetFirstNameDBName() + ", " +
PPersonTable.GetMiddleName1DBName() + ", " +
PPersonTable.GetFamilyIdDBName() + ' ' +
"FROM PUB_" + PPersonTable.GetTableDBName() +
" INNER JOIN " + "PUB_" + PPartnerTable.GetTableDBName() + " ON " +
"PUB_" + PPersonTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + " = " +
"PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ' ' +
"WHERE " + PPersonTable.GetFamilyKeyDBName() + " = ? " +
"AND " + PPartnerTable.GetStatusCodeDBName() + " <> '" +
SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscMERGED) + "' " + // Make sure we don't load MERGED Partners (shouldn't have a p_family_key_n, but just in case.)
"ORDER BY " + PPersonTable.GetFamilyIdDBName() + " ASC",
PPersonTable.GetTableName(), AReadTransaction, ParametersArray, 0, 0);
// Add Persons to Table
for (Int32 Counter = 0; Counter <= FamilyPersonsDT.Rows.Count - 1; Counter += 1)
{
NewRow = FamilyMembersDT.NewRowTyped(false);
NewRow.PartnerKey = FamilyPersonsDT[Counter].PartnerKey;
NewRow.PartnerShortName =
Calculations.DeterminePartnerShortName(TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFamilyName,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnTitle,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFirstName,
FamilyPersonsDT[Counter]),
TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnMiddleName1, FamilyPersonsDT[Counter]));
NewRow.FamilyId = FamilyPersonsDT[Counter].FamilyId;
FamilyMembersDT.Rows.Add(NewRow);
}
return(FamilyMembersDT);
}
private static PartnerDetails GetRecipient(Int64 APartnerKey)
{
if (RecipientList.ContainsKey(APartnerKey))
{
return(RecipientList[APartnerKey]);
}
PartnerDetails Ret = new PartnerDetails();
PPartnerTable PartnerTbl = PPartnerAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (PartnerTbl.Rows.Count > 0)
{
PPartnerRow PartnerRow = PartnerTbl[0];
Ret.LastName = PartnerRow.PartnerShortName;
if (PartnerRow.PartnerClass == "PERSON")
{
PPersonTable PersonTbl = PPersonAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (PersonTbl.Rows.Count > 0)
{
PPersonRow PersonRow = PersonTbl[0];
Ret.FirstName = PersonRow.FirstName;
Ret.LastName = PersonRow.FamilyName;
Ret.Class = "PERSON";
}
}
if (PartnerRow.PartnerClass == "FAMILY")
{
PFamilyTable FamilyTbl = PFamilyAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (FamilyTbl.Rows.Count > 0)
{
PFamilyRow FamilyRow = FamilyTbl[0];
Ret.FirstName = FamilyRow.FirstName;
Ret.LastName = FamilyRow.FamilyName;
Ret.Class = "FAMILY";
}
}
PPartnerLocationRow LocationRow;
TLocationPK LocationKey = ServerCalculations.DetermineBestAddress(APartnerKey, out LocationRow);
if (LocationKey.LocationKey != -1)
{
Ret.Email = LocationRow.EmailAddress;
}
}
RecipientList.Add(APartnerKey, Ret);
return(Ret);
}
public static int CheckPartnerCommitments(long AFromPartnerKey, long AToPartnerKey, TPartnerClass APartnerClass)
{
PPersonTable FromPersonTable = null;
PPersonTable ToPersonTable = null;
int ReturnValue = 0;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
if (APartnerClass == TPartnerClass.FAMILY)
{
FromPersonTable = PPersonAccess.LoadViaPFamily(AFromPartnerKey, Transaction);
ToPersonTable = PPersonAccess.LoadViaPFamily(AToPartnerKey, Transaction);
}
else if (APartnerClass == TPartnerClass.PERSON)
{
FromPersonTable = PPersonAccess.LoadViaPPartner(AFromPartnerKey, Transaction);
ToPersonTable = PPersonAccess.LoadViaPPartner(AToPartnerKey, Transaction);
}
// check if from partner has commitments
if (PersonHasCommitments(FromPersonTable, Transaction))
{
ReturnValue = 1;
// check if two persons are in same family
if ((APartnerClass == TPartnerClass.PERSON) &&
(((PPersonRow)FromPersonTable.Rows[0]).FamilyKey == ((PPersonRow)ToPersonTable.Rows[0]).FamilyKey))
{
ReturnValue = 3;
}
// check if to partner also has commitments
else if (PersonHasCommitments(ToPersonTable, Transaction))
{
ReturnValue = 2;
}
}
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
TLogging.LogAtLevel(7, "TMergePartnersWebConnector.CheckPartnerCommitments: rollback own transaction.");
}
return(ReturnValue);
}
// 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);
}
public static int CheckPartnerCommitments(long AFromPartnerKey, long AToPartnerKey, TPartnerClass APartnerClass)
{
PPersonTable FromPersonTable = null;
PPersonTable ToPersonTable = null;
int ReturnValue = 0;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
ref Transaction,
delegate
{
if (APartnerClass == TPartnerClass.FAMILY)
{
FromPersonTable = PPersonAccess.LoadViaPFamily(AFromPartnerKey, Transaction);
ToPersonTable = PPersonAccess.LoadViaPFamily(AToPartnerKey, Transaction);
}
else if (APartnerClass == TPartnerClass.PERSON)
{
FromPersonTable = PPersonAccess.LoadViaPPartner(AFromPartnerKey, Transaction);
ToPersonTable = PPersonAccess.LoadViaPPartner(AToPartnerKey, Transaction);
}
// check if from partner has commitments
if (PersonHasCommitments(FromPersonTable, Transaction))
{
ReturnValue = 1;
// check if two persons are in same family
if ((APartnerClass == TPartnerClass.PERSON) &&
(((PPersonRow)FromPersonTable.Rows[0]).FamilyKey == ((PPersonRow)ToPersonTable.Rows[0]).FamilyKey))
{
ReturnValue = 3;
}
// check if to partner also has commitments
else if (PersonHasCommitments(ToPersonTable, Transaction))
{
ReturnValue = 2;
}
}
});
return(ReturnValue);
}
public static int CanFamilyMergeIntoDifferentClass(long APartnerKey)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
PPersonTable PersonTable = PPersonAccess.LoadViaPFamily(APartnerKey, Transaction);
if (PersonTable.Rows.Count > 0)
{
return(1);
}
AGiftDetailTable GiftDetailTable = AGiftDetailAccess.LoadViaPPartnerRecipientKey(APartnerKey, Transaction);
if (GiftDetailTable.Rows.Count > 0)
{
return(2);
}
PBankingDetailsTable BankingDetailsTable = PBankingDetailsAccess.LoadViaPPartner(APartnerKey, Transaction);
if (BankingDetailsTable.Rows.Count > 0)
{
return(3);
}
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
TLogging.LogAtLevel(7, "TMergePartnersWebConnector.CanFamilyMergeIntoDifferentClass: rollback own transaction.");
}
return(0);
}
public static Int64 GetFamilyKeyForPerson(Int64 APersonKey)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
Int64 ReturnValue = 0;
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
PPersonTable PersonDT = new PPersonTable();
try
{
PersonDT = PPersonAccess.LoadByPrimaryKey(APersonKey, ReadTransaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
if (PersonDT.Rows.Count == 1)
{
ReturnValue = ((PPersonRow)PersonDT.Rows[0]).FamilyKey;
}
else
{
// we don't have a valid partner key
throw new EOPAppException(
"TPartnerServerLookups.GetFamilyKeyForPerson: The partner key is not valid!");
}
return(ReturnValue);
}
/// <summary>
/// generate a person record and update the associated family partner
/// </summary>
/// <param name="ACurrentNode"></param>
/// <param name="AFamilyRow"></param>
/// <param name="APrefix"></param>
/// <param name="AMainDS"></param>
public static void GeneratePersonRecord(XmlNode ACurrentNode, PFamilyRow AFamilyRow, string APrefix, PartnerEditTDS AMainDS)
{
PPersonRow PersonRow = AMainDS.PPerson.NewRowTyped();
PPartnerRow PartnerRow = AMainDS.PPartner.NewRowTyped();
if (NumberOfPartnerKeysReserved == 0)
{
NumberOfPartnerKeysReserved = 100;
NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved);
}
long PartnerKey = NextPartnerKey;
NextPartnerKey++;
NumberOfPartnerKeysReserved--;
PartnerRow.PartnerKey = PartnerKey;
PersonRow.PartnerKey = PartnerRow.PartnerKey;
PersonRow.FamilyKey = AFamilyRow.PartnerKey;
// create family id: count existing family members
DataView FamilyView = new DataView(AMainDS.PPerson);
FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + AFamilyRow.PartnerKey.ToString();
FamilyView.Sort = PPersonTable.GetFamilyIdDBName();
PersonRow.FamilyId = FamilyView.Count;
PersonRow.FirstName = TXMLParser.GetAttribute(ACurrentNode, APrefix + "FirstName");
PersonRow.MiddleName1 = TXMLParser.GetAttribute(ACurrentNode, APrefix + "MiddleName");
PersonRow.FamilyName = AFamilyRow.FamilyName;
if (FamilyView.Count == 1)
{
PartnerRow.PreviousName = TXMLParser.GetAttribute(ACurrentNode, APrefix + "FamilyName");
}
PersonRow.Title = TXMLParser.GetAttribute(ACurrentNode, APrefix + "Title");
PartnerRow.PartnerShortName = Calculations.DeterminePartnerShortName(
PersonRow.FamilyName,
PersonRow.Title,
PersonRow.FirstName);
PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_PERSON;
PartnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE;
PersonRow.DateOfBirth = Convert.ToDateTime(TXMLParser.GetAttribute(ACurrentNode, APrefix + "DateOfBirth"));
string gender = TXMLParser.GetAttribute(ACurrentNode, APrefix + "Gender");
if ((APrefix == "Male") || (gender == "MALE"))
{
PersonRow.Gender = MPartnerConstants.GENDER_MALE;
PartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_MALE;
}
else if ((APrefix == "Female") || (gender == "FEMALE"))
{
PersonRow.Gender = MPartnerConstants.GENDER_FEMALE;
PartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_FEMALE;
}
PPartnerRow FamilyPartnerRow = (PPartnerRow)AMainDS.PPartner.Rows.Find(AFamilyRow.PartnerKey);
if (FamilyView.Count == 0)
{
FamilyPartnerRow.AddresseeTypeCode = PartnerRow.AddresseeTypeCode;
}
else if (FamilyView.Count == 1)
{
// this is a couple
PersonRow.MaritalStatus = MPartnerConstants.MARITALSTATUS_MARRIED;
// find first person record as well
PPersonRow Husband = (PPersonRow)FamilyView[0].Row;
Husband.MaritalStatus = MPartnerConstants.MARITALSTATUS_MARRIED;
AFamilyRow.MaritalStatus = MPartnerConstants.MARITALSTATUS_MARRIED;
AFamilyRow.FirstName = Husband.FirstName + " and " + PersonRow.FirstName;
AFamilyRow.Title = Husband.Title + " and " + PersonRow.Title;
// update family shortname
FamilyPartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_COUPLE;
FamilyPartnerRow.PartnerShortName = Calculations.DeterminePartnerShortName(
AFamilyRow.FamilyName,
AFamilyRow.Title,
AFamilyRow.FirstName);
}
else if (FamilyView.Count > 1)
{
FamilyPartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_FAMILY;
}
AMainDS.PPerson.Rows.Add(PersonRow);
AMainDS.PPartner.Rows.Add(PartnerRow);
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="AFamilyPartnerKey"></param>
/// <param name="AReadTransaction"></param>
/// <returns></returns>
private static PPersonTable GetFamilyMemberPartnerKeys(Int64 AFamilyPartnerKey, TDBTransaction AReadTransaction)
{
PPersonTable TemplateDT;
PPersonRow TemplateRow;
StringCollection RequiredColumns;
// TLogging.LogAtLevel(9, "GetFamilyMemberPartnerKeys for Family with PartnerKey " + AFamilyPartnerKey.ToString());
TemplateDT = new PPersonTable();
TemplateRow = TemplateDT.NewRowTyped(false);
TemplateRow.FamilyKey = AFamilyPartnerKey;
//
// Retrieve the PartnerKeys of the Persons of the Family
//
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPersonTable.GetPartnerKeyDBName());
return PPersonAccess.LoadUsingTemplate(TemplateRow, RequiredColumns, AReadTransaction);
}
private static PartnerDetails GetDonor(Int64 APartnerKey)
{
if (DonorList.ContainsKey(APartnerKey))
{
return(DonorList[APartnerKey]);
}
PartnerDetails Ret = new PartnerDetails();
PPartnerTable PartnerTbl = PPartnerAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (PartnerTbl.Rows.Count > 0)
{
PPartnerRow PartnerRow = PartnerTbl[0];
Ret.LastName = PartnerRow.PartnerShortName;
Ret.Anonymous = PartnerRow.AnonymousDonor;
if (PartnerRow.PartnerClass == "PERSON")
{
PPersonTable PersonTbl = PPersonAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (PersonTbl.Rows.Count > 0)
{
PPersonRow PersonRow = PersonTbl[0];
Ret.FirstName = PersonRow.FirstName;
Ret.LastName = PersonRow.FamilyName;
Ret.Class = "PERSON";
}
}
if (PartnerRow.PartnerClass == "FAMILY")
{
PFamilyTable FamilyTbl = PFamilyAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (FamilyTbl.Rows.Count > 0)
{
PFamilyRow FamilyRow = FamilyTbl[0];
Ret.FirstName = FamilyRow.FirstName;
Ret.LastName = FamilyRow.FamilyName;
Ret.Class = "FAMILY";
}
}
PPartnerLocationRow PartnerLocationRow;
TLocationPK LocationKey = ServerCalculations.DetermineBestAddress(APartnerKey, out PartnerLocationRow);
if (LocationKey.LocationKey != -1)
{
Ret.Email = PartnerLocationRow.EmailAddress;
Ret.Telephone = PartnerLocationRow.TelephoneNumber;
PLocationTable LocationTbl = PLocationAccess.LoadByPrimaryKey(PartnerLocationRow.SiteKey,
PartnerLocationRow.LocationKey,
FTransaction);
if (LocationTbl.Rows.Count > 0)
{
PLocationRow LocationRow = LocationTbl[0];
Ret.Address = Calculations.DetermineLocationString(LocationRow, Calculations.TPartnerLocationFormatEnum.plfCommaSeparated);
}
}
}
DonorList.Add(APartnerKey, Ret);
return(Ret);
}
/// <summary>
/// create PPartnerLocation records for the FAMILY partner, and all the PERSON records of this family
/// </summary>
public static void GenerateAddressForFamily(XmlNode ACurrentNode, PFamilyRow AFamilyRow, PartnerEditTDS AMainDS)
{
PLocationRow locationRow = AMainDS.PLocation.NewRowTyped();
locationRow.SiteKey = 0; // DomainManager.GSiteKey;
locationRow.LocationKey = (AMainDS.PLocation.Count + 1) * -1;
locationRow.StreetName = TXMLParser.GetAttribute(ACurrentNode, "Addr2");
locationRow.PostalCode = TXMLParser.GetAttribute(ACurrentNode, "PostCode");
locationRow.City = TXMLParser.GetAttribute(ACurrentNode, "City");
locationRow.County = TXMLParser.GetAttribute(ACurrentNode, "Province");
locationRow.CountryCode = TXMLParser.GetAttribute(ACurrentNode, "CountryCode");
AMainDS.PLocation.Rows.Add(locationRow);
PPartnerLocationRow familyLocationRow = AMainDS.PPartnerLocation.NewRowTyped();
familyLocationRow.PartnerKey = AFamilyRow.PartnerKey;
familyLocationRow.LocationKey = locationRow.LocationKey;
familyLocationRow.SiteKey = locationRow.SiteKey;
familyLocationRow.LocationType = MPartnerConstants.LOCATIONTYPE_HOME;
familyLocationRow.SendMail = true;
AMainDS.PPartnerLocation.Rows.Add(familyLocationRow);
DataView FamilyView = new DataView(AMainDS.PPerson);
FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + AFamilyRow.PartnerKey.ToString();
FamilyView.Sort = PPersonTable.GetFamilyIdDBName();
// for each person, also create a location record
for (int countPerson = 0; countPerson < FamilyView.Count; countPerson++)
{
PPersonRow personRow = (PPersonRow)FamilyView[countPerson].Row;
PPartnerLocationRow personLocationRow = AMainDS.PPartnerLocation.NewRowTyped();
personLocationRow.PartnerKey = personRow.PartnerKey;
personLocationRow.LocationKey = familyLocationRow.LocationKey;
personLocationRow.SiteKey = familyLocationRow.SiteKey;
personLocationRow.LocationType = MPartnerConstants.LOCATIONTYPE_HOME;
personLocationRow.SendMail = true;
AMainDS.PPartnerLocation.Rows.Add(personLocationRow);
string prefix = "Male";
if (countPerson == 1)
{
prefix = "Female";
}
else if (countPerson > 1)
{
prefix = "Child" + (countPerson - 1).ToString();
}
personLocationRow.EmailAddress = TXMLParser.GetAttribute(ACurrentNode, prefix + "Email");
// set email of first person for whole family
if (countPerson == 0)
{
familyLocationRow.EmailAddress = personLocationRow.EmailAddress;
}
}
}
public static Int64 GetFamilyKeyForPerson(Int64 APersonKey)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
Int64 ReturnValue = 0;
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
PPersonTable PersonDT = new PPersonTable();
try
{
PersonDT = PPersonAccess.LoadByPrimaryKey(APersonKey, ReadTransaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
if (PersonDT.Rows.Count == 1)
{
ReturnValue = ((PPersonRow)PersonDT.Rows[0]).FamilyKey;
}
else
{
// we don't have a valid partner key
throw new EOPAppException(
"TPartnerServerLookups.GetFamilyKeyForPerson: The partner key is not valid!");
}
return ReturnValue;
}
/// <summary>
/// export all details of workers in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int64 SiteKey,
Int32 AYear)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "angestellte.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
PmStaffDataTable staffdata = new PmStaffDataTable();
PPartnerTable partners = new PPartnerTable();
partners.Constraints.Clear();
PPersonTable persons = new PPersonTable();
persons.Constraints.Clear();
TDBTransaction Transaction = new TDBTransaction();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
TDataBase db = Transaction.DataBaseObj;
// get all partners with a commitment period for this date range
// ignore non-native workers. field must be home office, or receiving field
string sql =
String.Format(
"SELECT * FROM PUB_pm_staff_data s, PUB_p_partner p, PUB_p_person per " +
"WHERE p.p_partner_key_n = s.p_partner_key_n " +
"AND (s.pm_home_office_n = ? OR pm_receiving_field_office_n = ? OR pm_receiving_field_n = ?) " +
"AND per.p_partner_key_n = s.p_partner_key_n " +
// start of commitment during this year
"AND ((pm_start_of_commitment_d BETWEEN ? AND ?) " +
// start of commitment before this year, end of commitment null or during/after this year
" OR (pm_start_of_commitment_d < ? AND (pm_start_of_commitment_d IS NULL OR pm_start_of_commitment_d >= ?)))");
List <OdbcParameter> Parameters = new List <OdbcParameter>();
OdbcParameter param;
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
param = new OdbcParameter("enddate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 12, 31);
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
db.SelectDT(staffdata, sql.Replace("SELECT *", "SELECT s.*"), Transaction, Parameters.ToArray(), 0, 0);
db.SelectDT(partners, sql.Replace("SELECT *", "SELECT p.*"), Transaction, Parameters.ToArray(), 0, 0);
db.SelectDT(persons, sql.Replace("SELECT *", "SELECT per.*"), Transaction, Parameters.ToArray(), 0, 0);
});
foreach (PmStaffDataRow staff in staffdata.Rows)
{
partners.DefaultView.Sort = "p_partner_key_n";
persons.DefaultView.Sort = "p_partner_key_n";
PPartnerRow partner = (PPartnerRow)partners.DefaultView.FindRows(new object[] { staff.PartnerKey })[0].Row;
PPersonRow person = (PPersonRow)persons.DefaultView.FindRows(new object[] { staff.PartnerKey })[0].Row;
sb.Append(StringHelper.StrMerge(
new string[] {
partner.PartnerKey.ToString(),
partner.PartnerShortName.ToString(),
person.DateOfBirth.HasValue?person.DateOfBirth.Value.ToString("yyyyMMdd"):String.Empty,
partner.PartnerKey.ToString()
}, ACSVSeparator));
sb.Append(ANewLine);
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// generate the partners from a text file that was generated with Benerator
/// </summary>
/// <param name="AInputBeneratorFile"></param>
public static void GenerateWorkers(string AInputBeneratorFile)
{
PartnerEditTDS MainDS = new PartnerEditTDS();
PersonnelTDS PersonnelDS = new PersonnelTDS();
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys = "SELECT p_partner_key_n, p_unit_name_c FROM PUB_p_unit WHERE u_unit_type_code_c = 'F'";
DataTable FieldKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetFieldPartnerKeys, "keys", null);
// get a list of banks (all class BANK)
string sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank";
DataTable BankKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetBankPartnerKeys, "keys", null);
// 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;
if (familySituation == "singleMan")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
}
else if (familySituation == "singleWoman")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Female", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
}
else if (familySituation == "family")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
int AgeDifferenceSpouse = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "AgeDifferenceSpouse"));
DataView FamilyView = new DataView(MainDS.PPerson);
FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + familyRecord.PartnerKey.ToString();
FamilyView.Sort = PPersonTable.GetFamilyIdDBName();
PPersonRow HusbandPersonRow = (PPersonRow)FamilyView[0].Row;
PPersonRow WifePersonRow = (PPersonRow)FamilyView[1].Row;
WifePersonRow.DateOfBirth =
WifePersonRow.DateOfBirth.Value.AddYears(
AgeDifferenceSpouse - (WifePersonRow.DateOfBirth.Value.Year - HusbandPersonRow.DateOfBirth.Value.Year));
if (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year < 19)
{
WifePersonRow.DateOfBirth.Value.AddYears(
19 - (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year));
}
int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren"));
for (int countChild = 0; countChild < NumberOfChildren; countChild++)
{
DateTime DateOfBirthChild = Convert.ToDateTime(
TXMLParser.GetAttribute(RecordNode,
"Child" + (countChild + 1).ToString() + "DateOfBirth"));
// mother must have been 19 when the child was born
if (DateOfBirthChild.Year < WifePersonRow.DateOfBirth.Value.Year + 19)
{
continue;
}
GeneratePersonRecord(RecordNode, familyRecord, "Child" + (countChild + 1).ToString(), MainDS);
}
}
GenerateAddressForFamily(RecordNode, familyRecord, MainDS);
GenerateCommitmentRecord(RecordNode, familyRecord, MainDS, PersonnelDS, FieldKeys);
GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys);
if (MainDS.PFamily.Rows.Count % 100 == 0)
{
TLogging.Log("created worker " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName);
}
RecordNode = RecordNode.NextSibling;
}
MainDS.ThrowAwayAfterSubmitChanges = true;
PartnerEditTDSAccess.SubmitChanges(MainDS);
PersonnelDS.ThrowAwayAfterSubmitChanges = true;
PersonnelTDSAccess.SubmitChanges(PersonnelDS);
TLogging.Log("after saving workers");
}
// 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;
}
private void ApplySecurity()
{
if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PPartnerTable.GetTableDBName()))
{
// need to disable all Fields that are DataBound to p_partner. This continues in the switch statments!
// timop: I have disabled all controls. usually you have p_partner modify permissions, or none
CustomEnablingDisabling.DisableControlGroup(pnlContent);
}
switch (SharedTypes.PartnerClassStringToEnum(FPartnerClass))
{
case TPartnerClass.PERSON:
if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PPersonTable.GetTableDBName()))
{
// need to disable all Fields that are DataBound to p_partner
CustomEnablingDisabling.DisableControl(pnlPerson, cmbPersonAddresseeTypeCode);
CustomEnablingDisabling.DisableControl(pnlPerson, chkPersonNoSolicitations);
// need to disable all Fields that are DataBound to p_person
CustomEnablingDisabling.DisableControlGroup(pnlPerson);
cmbPersonAddresseeTypeCode.Focus();
}
break;
case TPartnerClass.FAMILY:
if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PFamilyTable.GetTableDBName()))
{
// need to disable all Fields that are DataBound to p_partner
CustomEnablingDisabling.DisableControl(pnlFamily, cmbFamilyAddresseeTypeCode);
CustomEnablingDisabling.DisableControl(pnlFamily, chkFamilyNoSolicitations);
// need to disable all Fields that are DataBound to p_family
CustomEnablingDisabling.DisableControlGroup(pnlFamily);
cmbFamilyAddresseeTypeCode.Focus();
}
break;
case TPartnerClass.CHURCH:
if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PChurchTable.GetTableDBName()))
{
// need to disable all Fields that are DataBound to p_partner
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);
// need to disable all Fields that are DataBound to p_church
CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);
cmbOtherAddresseeTypeCode.Focus();
}
break;
case TPartnerClass.ORGANISATION:
if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, POrganisationTable.GetTableDBName()))
{
// need to disable all Fields that are DataBound to p_partner
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);
// need to disable all Fields that are DataBound to p_organisation
CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);
cmbOtherAddresseeTypeCode.Focus();
}
break;
case TPartnerClass.UNIT:
if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PUnitTable.GetTableDBName()))
{
// need to disable all Fields that are DataBound to p_partner
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);
// need to disable all Fields that are DataBound to p_unit
CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);
cmbOtherAddresseeTypeCode.Focus();
}
break;
case TPartnerClass.BANK:
if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PBankTable.GetTableDBName()))
{
// need to disable all Fields that are DataBound to p_partner
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);
// need to disable all Fields that are DataBound to p_bank
CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);
cmbOtherAddresseeTypeCode.Focus();
}
break;
case TPartnerClass.VENUE:
if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PVenueTable.GetTableDBName()))
{
// need to disable all Fields that are DataBound to p_partner
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);
// need to disable all Fields that are DataBound to p_venue
CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);
cmbOtherAddresseeTypeCode.Focus();
}
break;
default:
MessageBox.Show(String.Format(Catalog.GetString("Unrecognised Partner Class '{0}'!"), FPartnerClass));
break;
}
}
/// <summary>
/// export all details of workers in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int64 SiteKey,
Int32 AYear)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "angestellte.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
PmStaffDataTable staffdata = new PmStaffDataTable();
PPartnerTable partners = new PPartnerTable();
partners.Constraints.Clear();
PPersonTable persons = new PPersonTable();
persons.Constraints.Clear();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction,
delegate
{
// get all partners with a commitment period for this date range
// ignore non-native workers. field must be home office, or receiving field
string sql =
String.Format(
"SELECT * FROM PUB_pm_staff_data s, PUB_p_partner p, PUB_p_person per " +
"WHERE p.p_partner_key_n = s.p_partner_key_n " +
"AND (s.pm_home_office_n = ? OR pm_receiving_field_office_n = ? OR pm_receiving_field_n = ?) " +
"AND per.p_partner_key_n = s.p_partner_key_n " +
// start of commitment during this year
"AND ((pm_start_of_commitment_d BETWEEN ? AND ?) " +
// start of commitment before this year, end of commitment null or during/after this year
" OR (pm_start_of_commitment_d < ? AND (pm_start_of_commitment_d IS NULL OR pm_start_of_commitment_d >= ?)))");
List <OdbcParameter>Parameters = new List <OdbcParameter>();
OdbcParameter param;
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
param = new OdbcParameter("enddate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 12, 31);
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
DBAccess.GDBAccessObj.SelectDT(staffdata, sql.Replace("SELECT *", "SELECT s.*"), Transaction, Parameters.ToArray(), 0, 0);
DBAccess.GDBAccessObj.SelectDT(partners, sql.Replace("SELECT *", "SELECT p.*"), Transaction, Parameters.ToArray(), 0, 0);
DBAccess.GDBAccessObj.SelectDT(persons, sql.Replace("SELECT *", "SELECT per.*"), Transaction, Parameters.ToArray(), 0, 0);
});
foreach (PmStaffDataRow staff in staffdata.Rows)
{
partners.DefaultView.Sort = "p_partner_key_n";
persons.DefaultView.Sort = "p_partner_key_n";
PPartnerRow partner = (PPartnerRow)partners.DefaultView.FindRows(new object[] { staff.PartnerKey })[0].Row;
PPersonRow person = (PPersonRow)persons.DefaultView.FindRows(new object[] { staff.PartnerKey })[0].Row;
sb.Append(StringHelper.StrMerge(
new string[] {
partner.PartnerKey.ToString(),
partner.PartnerShortName.ToString(),
person.DateOfBirth.HasValue?person.DateOfBirth.Value.ToString("yyyyMMdd"):String.Empty,
partner.PartnerKey.ToString()
}, ACSVSeparator));
sb.Append(ANewLine);
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// export all posted invoices for conference and seminar participants in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "participants.csv"));
Console.WriteLine("Writing file: " + filename);
TDBTransaction Transaction = new TDBTransaction();
AGiftDetailTable giftdetails = new AGiftDetailTable();
AGiftTable gifts = new AGiftTable();
AGiftBatchTable batches = new AGiftBatchTable();
PPersonTable persons = new PPersonTable();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
TDataBase db = Transaction.DataBaseObj;
// all gift details towards a costcentre that needs to be exported
string sql =
String.Format("SELECT DISTINCT D.* " +
"FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D " +
"WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " +
"AND G.{3} = B.{3} AND G.{9} = B.{9} " +
"AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " +
"AND D.{11} IN ({12}) " +
"AND NOT D.{13} = '{14}'",
AGiftBatchTable.GetTableDBName(),
AGiftTable.GetTableDBName(),
AGiftDetailTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
AGiftBatchTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
AGiftDetailTable.GetMotivationGroupCodeDBName(),
"GIFT");
db.SelectDT(giftdetails, sql, Transaction, null, 0, 0);
sql = sql.Replace("SELECT DISTINCT D.*", "SELECT DISTINCT G.*");
db.SelectDT(gifts, sql, Transaction, null, 0, 0);
gifts.DefaultView.Sort =
AGiftTable.GetBatchNumberDBName() + "," +
AGiftTable.GetGiftTransactionNumberDBName();
sql = sql.Replace("SELECT DISTINCT G.*", "SELECT DISTINCT B.*");
db.SelectDT(batches, sql, Transaction, null, 0, 0);
batches.DefaultView.Sort = AGiftTable.GetBatchNumberDBName();
sql =
String.Format("SELECT DISTINCT P.* " +
"FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D, PUB.{15} AS P " +
"WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " +
"AND G.{3} = B.{3} AND G.{9} = B.{9} " +
"AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " +
"AND D.{11} IN ({12}) " +
"AND NOT D.{13} = '{14}' " +
"AND P.{16} = G.{17}",
AGiftBatchTable.GetTableDBName(),
AGiftTable.GetTableDBName(),
AGiftDetailTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
AGiftBatchTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
AGiftDetailTable.GetMotivationGroupCodeDBName(),
"GIFT",
PPersonTable.GetTableDBName(),
PPersonTable.GetPartnerKeyDBName(),
AGiftTable.GetDonorKeyDBName());
db.SelectDT(persons, sql, Transaction, null, 0, 0);
persons.DefaultView.Sort = PPersonTable.GetPartnerKeyDBName();
});
StringBuilder sb = new StringBuilder();
foreach (AGiftDetailRow detail in giftdetails.Rows)
{
AGiftRow gift = (AGiftRow)gifts.DefaultView.FindRows(new object[] { detail.BatchNumber, detail.GiftTransactionNumber })[0].Row;
AGiftBatchRow batch = (AGiftBatchRow)batches.DefaultView.FindRows(detail.BatchNumber)[0].Row;
DataRowView[] personList = persons.DefaultView.FindRows(gift.DonorKey);
PPersonRow person = (personList.Length > 0 ? (PPersonRow)personList[0].Row : null);
sb.Append(StringHelper.StrMerge(
new string[] {
"GB" + detail.BatchNumber.ToString() + "_G" + detail.GiftTransactionNumber.ToString() +
"_D" + detail.DetailNumber.ToString(),
String.Format("{0:N}", detail.GiftTransactionAmount),
batch.GlEffectiveDate.ToString("yyyyMMdd"),
gift.DonorKey.ToString(),
person !=
null ? (person.DateOfBirth.HasValue ? person.DateOfBirth.Value.ToString("yyyyMMdd") : string.Empty) : string.Empty,
detail.CostCentreCode,
batch.BatchDescription,
detail.GiftCommentOne,
detail.GiftCommentTwo
}, ACSVSeparator));
sb.Append(ANewLine);
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// create PPartnerLocation records for the FAMILY partner, and all the PERSON records of this family
/// </summary>
public static void GenerateAddressForFamily(XmlNode ACurrentNode, PFamilyRow AFamilyRow, PartnerEditTDS AMainDS)
{
PLocationRow locationRow = AMainDS.PLocation.NewRowTyped();
locationRow.SiteKey = 0; // DomainManager.GSiteKey;
locationRow.LocationKey = (AMainDS.PLocation.Count + 1) * -1;
locationRow.StreetName = TXMLParser.GetAttribute(ACurrentNode, "Addr2");
locationRow.PostalCode = TXMLParser.GetAttribute(ACurrentNode, "PostCode");
locationRow.City = TXMLParser.GetAttribute(ACurrentNode, "City");
locationRow.County = TXMLParser.GetAttribute(ACurrentNode, "Province");
locationRow.CountryCode = TXMLParser.GetAttribute(ACurrentNode, "CountryCode");
AMainDS.PLocation.Rows.Add(locationRow);
PPartnerLocationRow familyLocationRow = AMainDS.PPartnerLocation.NewRowTyped();
familyLocationRow.PartnerKey = AFamilyRow.PartnerKey;
familyLocationRow.LocationKey = locationRow.LocationKey;
familyLocationRow.SiteKey = locationRow.SiteKey;
familyLocationRow.LocationType = MPartnerConstants.LOCATIONTYPE_HOME;
familyLocationRow.SendMail = true;
string gender = TXMLParser.GetAttribute(ACurrentNode, "familySituation");
Random r = new Random();
string email = TXMLParser.GetAttribute(ACurrentNode, "FemaleEmail");
if ((gender == "singleMan") || ((gender == "family") && (r.Next(2) == 0)))
{
email = TXMLParser.GetAttribute(ACurrentNode, "MaleEmail");
}
if (email.Length > 0)
{
PPartnerAttributeRow partnerAttributeRow = AMainDS.PPartnerAttribute.NewRowTyped();
partnerAttributeRow.PartnerKey = AFamilyRow.PartnerKey;
partnerAttributeRow.AttributeType = MPartnerConstants.ATTR_TYPE_EMAIL;
partnerAttributeRow.Index = 0;
partnerAttributeRow.Primary = true;
partnerAttributeRow.Value = email;
AMainDS.PPartnerAttribute.Rows.Add(partnerAttributeRow);
partnerAttributeRow = AMainDS.PPartnerAttribute.NewRowTyped();
partnerAttributeRow.PartnerKey = AFamilyRow.PartnerKey;
partnerAttributeRow.AttributeType = MPartnerConstants.ATTR_TYPE_PARTNERS_PRIMARY_CONTACT_METHOD;
partnerAttributeRow.Index = 9999;
partnerAttributeRow.Primary = false;
partnerAttributeRow.Value = MPartnerConstants.ATTR_TYPE_EMAIL;
AMainDS.PPartnerAttribute.Rows.Add(partnerAttributeRow);
}
AMainDS.PPartnerLocation.Rows.Add(familyLocationRow);
DataView FamilyView = new DataView(AMainDS.PPerson);
FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + AFamilyRow.PartnerKey.ToString();
FamilyView.Sort = PPersonTable.GetFamilyIdDBName();
// for each person, also create a location record
for (int countPerson = 0; countPerson < FamilyView.Count; countPerson++)
{
PPersonRow personRow = (PPersonRow)FamilyView[countPerson].Row;
PPartnerLocationRow personLocationRow = AMainDS.PPartnerLocation.NewRowTyped();
personLocationRow.PartnerKey = personRow.PartnerKey;
personLocationRow.LocationKey = familyLocationRow.LocationKey;
personLocationRow.SiteKey = familyLocationRow.SiteKey;
personLocationRow.LocationType = MPartnerConstants.LOCATIONTYPE_HOME;
personLocationRow.SendMail = false;
AMainDS.PPartnerLocation.Rows.Add(personLocationRow);
}
}
/// <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));
}
private static void GenerateCommitmentRecord(
XmlNode ACurrentNode,
PFamilyRow AFamilyRow,
PartnerEditTDS AMainDS,
PersonnelTDS APersonnelDS,
DataTable AFieldKeys)
{
DataView FamilyView = new DataView(AMainDS.PPerson);
FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + AFamilyRow.PartnerKey.ToString();
FamilyView.Sort = PPersonTable.GetFamilyIdDBName();
PPersonRow firstPerson = (PPersonRow)FamilyView[0].Row;
int FieldID =
Convert.ToInt32(TXMLParser.GetAttribute(ACurrentNode, "fieldCommitment")) % AFieldKeys.Rows.Count;
long FieldPartnerKey = Convert.ToInt64(AFieldKeys.Rows[FieldID].ItemArray[0]);
PPartnerGiftDestinationRow giftDestination = AMainDS.PPartnerGiftDestination.NewRowTyped();
if (NextKeyForGiftDestination == -1)
{
NextKeyForGiftDestination = TPartnerDataReaderWebConnector.GetNewKeyForPartnerGiftDestination();
}
else
{
NextKeyForGiftDestination++;
}
giftDestination.Key = NextKeyForGiftDestination;
giftDestination.FieldKey = FieldPartnerKey;
giftDestination.PartnerKey = AFamilyRow.PartnerKey;
giftDestination.PartnerClass = MPartnerConstants.PARTNERCLASS_FAMILY;
giftDestination.DateEffective = Convert.ToDateTime(TXMLParser.GetAttribute(ACurrentNode, "startDateCommitment"));
PmStaffDataRow staffData = APersonnelDS.PmStaffData.NewRowTyped();
staffData.SiteKey = DomainManager.GSiteKey;
staffData.Key = (APersonnelDS.PmStaffData.Count + 1) * -1;
staffData.PartnerKey = firstPerson.PartnerKey;
staffData.ReceivingField = FieldPartnerKey;
// TODO: could add foreign nationals
staffData.HomeOffice = DomainManager.GSiteKey;
staffData.OfficeRecruitedBy = DomainManager.GSiteKey;
staffData.StartOfCommitment = Convert.ToDateTime(TXMLParser.GetAttribute(ACurrentNode, "startDateCommitment"));
int LengthCommitment = Convert.ToInt32(TXMLParser.GetAttribute(ACurrentNode, "lengthCommitment"));
staffData.StatusCode = "LONG-TERMER";
if (LengthCommitment > 0)
{
string LengthCommitmentUnit = TXMLParser.GetAttribute(ACurrentNode, "lengthCommitmentUnit");
if (LengthCommitmentUnit == "week")
{
staffData.StatusCode = "SHORT-TERMER";
staffData.EndOfCommitment = staffData.StartOfCommitment.AddDays(7 * LengthCommitment);
}
else if (LengthCommitmentUnit == "month")
{
staffData.StatusCode = "SHORT-TERMER";
staffData.EndOfCommitment = staffData.StartOfCommitment.AddMonths(LengthCommitment);
}
else if (LengthCommitmentUnit == "year")
{
if (LengthCommitment < 3)
{
staffData.StatusCode = "WORKER";
}
staffData.EndOfCommitment = staffData.StartOfCommitment.AddYears(LengthCommitment);
}
}
APersonnelDS.PmStaffData.Rows.Add(staffData);
giftDestination.DateExpires = staffData.EndOfCommitment;
if (AMainDS.PPartnerGiftDestination == null)
{
AMainDS.PPartnerGiftDestination.Merge(new PPartnerGiftDestinationTable());
}
AMainDS.PPartnerGiftDestination.Rows.Add(giftDestination);
// TODO depending on start and end date of commitment, set EX-WORKER or no special type yet at all
string SpecialType = MPartnerConstants.PARTNERTYPE_WORKER;
if (!staffData.IsEndOfCommitmentNull() && (staffData.EndOfCommitment < DateTime.Today))
{
SpecialType = MPartnerConstants.PARTNERTYPE_EX_WORKER;
}
if (SpecialType != string.Empty)
{
// create special type for family partner
PPartnerTypeRow PartnerTypeRow = AMainDS.PPartnerType.NewRowTyped();
PartnerTypeRow.PartnerKey = AFamilyRow.PartnerKey;
PartnerTypeRow.TypeCode = SpecialType;
AMainDS.PPartnerType.Rows.Add(PartnerTypeRow);
// set special type WORKER for the parents
for (int countPerson = 0; countPerson < FamilyView.Count && countPerson < 2; countPerson++)
{
PPersonRow personRow = (PPersonRow)FamilyView[countPerson].Row;
// create special type for the person partners
PartnerTypeRow = AMainDS.PPartnerType.NewRowTyped();
PartnerTypeRow.PartnerKey = personRow.PartnerKey;
PartnerTypeRow.TypeCode = SpecialType;
AMainDS.PPartnerType.Rows.Add(PartnerTypeRow);
}
}
}
/// <summary>
/// Returns the Family Members of a Family.
/// </summary>
/// <param name="AFamilyPartnerKey">PartnerKey of the FAMILY.</param>
/// <param name="AReadTransaction">Open DB Transaction.</param>
/// <returns>An Instance of <see cref="PartnerInfoTDSFamilyMembersTable" />.
/// If there were Family Members, there will be one DataRow for each Family Member.</returns>
private static PartnerInfoTDSFamilyMembersTable GetFamilyMembers(Int64 AFamilyPartnerKey,
TDBTransaction AReadTransaction)
{
OdbcParameter[] ParametersArray;
DataSet TmpDS;
PPersonTable FamilyPersonsDT;
PartnerInfoTDSFamilyMembersRow NewRow;
PartnerInfoTDSFamilyMembersTable FamilyMembersDT;
FamilyMembersDT = new PartnerInfoTDSFamilyMembersTable();
ParametersArray = new OdbcParameter[1];
ParametersArray[0] = new OdbcParameter("", OdbcType.Decimal, 10);
ParametersArray[0].Value = (System.Object)AFamilyPartnerKey;
TmpDS = new DataSet();
FamilyPersonsDT = new PPersonTable();
TmpDS.Tables.Add(FamilyPersonsDT);
DBAccess.GDBAccessObj.Select(TmpDS,
"SELECT " + "PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ", " +
PPersonTable.GetFamilyNameDBName() + ", " +
PPersonTable.GetTitleDBName() + ", " +
PPersonTable.GetFirstNameDBName() + ", " +
PPersonTable.GetMiddleName1DBName() + ", " +
PPersonTable.GetFamilyIdDBName() + ' ' +
"FROM PUB_" + PPersonTable.GetTableDBName() +
" INNER JOIN " + "PUB_" + PPartnerTable.GetTableDBName() + " ON " +
"PUB_" + PPersonTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + " = " +
"PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ' ' +
"WHERE " + PPersonTable.GetFamilyKeyDBName() + " = ? " +
"AND " + PPartnerTable.GetStatusCodeDBName() + " <> '" +
SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscMERGED) + "' " + // Make sure we don't load MERGED Partners (shouldn't have a p_family_key_n, but just in case.)
"ORDER BY " + PPersonTable.GetFamilyIdDBName() + " ASC",
PPersonTable.GetTableName(), AReadTransaction, ParametersArray, 0, 0);
// Add Persons to Table
for (Int32 Counter = 0; Counter <= FamilyPersonsDT.Rows.Count - 1; Counter += 1)
{
NewRow = FamilyMembersDT.NewRowTyped(false);
NewRow.PartnerKey = FamilyPersonsDT[Counter].PartnerKey;
NewRow.PartnerShortName =
Calculations.DeterminePartnerShortName(TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFamilyName,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnTitle,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFirstName,
FamilyPersonsDT[Counter]),
TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnMiddleName1, FamilyPersonsDT[Counter]));
NewRow.FamilyId = FamilyPersonsDT[Counter].FamilyId;
FamilyMembersDT.Rows.Add(NewRow);
}
return FamilyMembersDT;
}
/// arrange the panels and controls according to the partner class
public void InitialiseUserControl()
{
FIgnorePartnerStatusChange = false;
// Set up ToolTip
this.components = new System.ComponentModel.Container();
FTipMain = new System.Windows.Forms.ToolTip(this.components);
FTipMain.AutoPopDelay = 4000;
FTipMain.InitialDelay = 500;
FTipMain.ReshowDelay = 100;
BuildValidationControlsDict();
// Ensure that the Worker Field Panel (and thus the Button on it) always comes last in the Tab Order
pnlWorkerField.TabIndex = 999;
#region Show fields according to Partner Class
txtPartnerKey.PartnerClass = FPartnerClass;
switch (SharedTypes.PartnerClassStringToEnum(FPartnerClass))
{
case TPartnerClass.PERSON:
pnlPerson.Visible = true;
pnlWorkerField.Visible = true;
pnlPerson2ndLine.Visible = true;
// Set ToolTips in addition to StatusBar texts for fields to make it clearer what to fill in there...
FTipMain.SetToolTip(this.txtPersonTitle, PPersonTable.GetTitleHelp());
FTipMain.SetToolTip(this.txtPersonFirstName, PPersonTable.GetFirstNameHelp());
FTipMain.SetToolTip(this.txtPersonMiddleName, PPersonTable.GetMiddleName1Help());
FTipMain.SetToolTip(this.txtPersonFamilyName, PPersonTable.GetFamilyNameHelp());
txtPersonTitle.TextChanged += new EventHandler(OnAnyDataColumnChanging);
txtPersonFirstName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
txtPersonMiddleName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
txtPersonFamilyName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
this.cmbPersonGender.SelectedValueChanged += new System.EventHandler(this.CmbPersonGender_SelectedValueChanged);
txtPartnerClass.BackColor = TCommonControlsHelper.PartnerClassPERSONColour;
// Ensure that the Text in the first TextBox isn't all selected when the Form is brought up
txtPersonTitle.SelectionStart = 0;
break;
case TPartnerClass.FAMILY:
pnlFamily.Visible = true;
pnlWorkerField.Visible = true;
pnlFamily2ndLine.Visible = true;
// Set ToolTips in addition to StatusBar texts for fields to make it clearer what to fill in there...
FTipMain.SetToolTip(this.txtFamilyTitle, PFamilyTable.GetTitleHelp());
FTipMain.SetToolTip(this.txtFamilyFirstName, PFamilyTable.GetFirstNameHelp());
FTipMain.SetToolTip(this.txtFamilyFamilyName, PFamilyTable.GetFamilyNameHelp());
txtFamilyTitle.TextChanged += new EventHandler(OnAnyDataColumnChanging);
txtFamilyFirstName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
txtFamilyFamilyName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
// Ensure that the Text in the first TextBox isn't all selected when the Form is brought up
txtFamilyTitle.SelectionStart = 0;
break;
case TPartnerClass.CHURCH:
pnlChurch.Visible = true;
pnlOther2ndLine.Visible = true;
txtChurchName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
// Ensure that the Text in the first TextBox isn't all selected when the Form is brought up
txtChurchName.SelectionStart = 0;
break;
case TPartnerClass.ORGANISATION:
pnlOrganisation.Visible = true;
pnlOther2ndLine.Visible = true;
txtOrganisationName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
// Ensure that the Text in the first TextBox isn't all selected when the Form is brought up
txtOrganisationName.SelectionStart = 0;
break;
case TPartnerClass.UNIT:
pnlUnit.Visible = true;
pnlOther2ndLine.Visible = true;
txtUnitName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
FMainDS.PUnit.ColumnChanging += new DataColumnChangeEventHandler(OnUnitDataColumnChanging);
// Ensure that the Text in the first TextBox isn't all selected when the Form is brought up
txtUnitName.SelectionStart = 0;
break;
case TPartnerClass.BANK:
pnlBank.Visible = true;
pnlOther2ndLine.Visible = true;
txtBranchName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
// Ensure that the Text in the first TextBox isn't all selected when the Form is brought up
txtBranchName.SelectionStart = 0;
break;
case TPartnerClass.VENUE:
pnlVenue.Visible = true;
pnlOther2ndLine.Visible = true;
txtVenueName.TextChanged += new EventHandler(OnAnyDataColumnChanging);
// Ensure that the Text in the first TextBox isn't all selected when the Form is brought up
txtVenueName.SelectionStart = 0;
break;
default:
MessageBox.Show(String.Format(Catalog.GetString("Unrecognised Partner Class '{0}'!"), FPartnerClass));
break;
}
#endregion
}
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;
PPartnerLocationTable PartnerLocation;
string CountryName;
string EmailAddress;
if (TAddressTools.GetBestAddress(ADonorKey, out Location, out PartnerLocation, out CountryName, out EmailAddress, 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));
}
private Boolean PartnerStatusCodeChangePromotion(string ANewPartnerStatusCode)
{
Boolean ReturnValue;
String FamilyMembersText;
PartnerEditTDSFamilyMembersTable FamilyMembersDT;
Int32 Counter;
Int32 Counter2;
PartnerEditTDSFamilyMembersRow FamilyMembersDR;
PartnerEditTDSFamilyMembersInfoForStatusChangeRow FamilyMembersInfoDR;
DialogResult FamilyMembersResult;
DataView FamilyMembersDV;
ReturnValue = true;
FamilyMembersText = "";
/* Retrieve Family Members from the PetraServer */
FamilyMembersDT = FPartnerEditUIConnector.GetDataFamilyMembers(FMainDS.PPartner[0].PartnerKey, "");
FamilyMembersDV = new DataView(FamilyMembersDT, "", PPersonTable.GetFamilyIdDBName() + " ASC", DataViewRowState.CurrentRows);
/* Build a formatted String of Family Members' PartnerKeys and ShortNames */
for (Counter = 0; Counter <= FamilyMembersDV.Count - 1; Counter += 1)
{
FamilyMembersDR = (PartnerEditTDSFamilyMembersRow)FamilyMembersDV[Counter].Row;
FamilyMembersText = FamilyMembersText + " " + StringHelper.FormatStrToPartnerKeyString(FamilyMembersDR.PartnerKey.ToString()) +
" " + FamilyMembersDR.PartnerShortName + Environment.NewLine;
}
/* If there are Family Members, ... */
if (FamilyMembersText != "")
{
/* show MessageBox with Family Members to the user, asking whether to promote. */
FamilyMembersResult =
MessageBox.Show(
String.Format(
Catalog.GetString("Partner Status change to '{0}': \r\n" +
"Should OpenPetra apply this change to all Family Members of this Family?"),
ANewPartnerStatusCode) + Environment.NewLine + Environment.NewLine +
Catalog.GetString("The Family has the following Family Members:") + Environment.NewLine +
FamilyMembersText + Environment.NewLine +
Catalog.GetString("(Choose 'Cancel' to cancel the change of the Partner Status\r\n" +
"for this Partner)."),
Catalog.GetString("Promote Partner Status Change to All Family Members?"),
MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
/* Check User's response */
switch (FamilyMembersResult)
{
case System.Windows.Forms.DialogResult.Yes:
/*
* User wants to promote the Partner StatusCode change to Family
* Members: add new DataTable for that purpose if it doesn't exist yet.
*/
if (FMainDS.FamilyMembersInfoForStatusChange == null)
{
FMainDS.Tables.Add(new PartnerEditTDSFamilyMembersInfoForStatusChangeTable());
FMainDS.InitVars();
}
/*
* Remove any existing DataRows so we start from a 'clean slate'
* (the user could change the Partner StatusCode more than once...)
*/
FMainDS.FamilyMembersInfoForStatusChange.Rows.Clear();
/*
* Add the PartnerKeys of the Family Members that we have just displayed
* to the user to the DataTable.
*
* Note: This DataTable will be sent to the PetraServer when the user
* saves the Partner. The UIConnector will pick it up and process it.
*/
for (Counter2 = 0; Counter2 <= FamilyMembersDV.Count - 1; Counter2 += 1)
{
FamilyMembersDR = (PartnerEditTDSFamilyMembersRow)FamilyMembersDV[Counter2].Row;
FamilyMembersInfoDR = FMainDS.FamilyMembersInfoForStatusChange.NewRowTyped(false);
FamilyMembersInfoDR.PartnerKey = FamilyMembersDR.PartnerKey;
FMainDS.FamilyMembersInfoForStatusChange.Rows.Add(FamilyMembersInfoDR);
}
break;
case System.Windows.Forms.DialogResult.No:
/* no promotion wanted > nothing to do */
/* (StatusCode will be changed only for the Family) */
break;
case System.Windows.Forms.DialogResult.Cancel:
ReturnValue = false;
break;
}
}
else
{
}
/* no promotion needed since there are no Family Members */
/* (StatusCode will be changed only for the Family) */
return(ReturnValue);
}
private PartnerEditTDSFamilyMembersTable GetFamilyMembersInternal(Int64 AFamilyPartnerKey,
String AWorkWithSpecialType,
out Int32 ACount,
Boolean ACountOnly)
{
TDBTransaction ReadTransaction;
OdbcParameter[] ParametersArray;
Boolean NewTransaction = false;
PartnerEditTDSFamilyMembersTable PartnerTypeFamilyMembersDT;
PPersonTable FamilyPersonsDT;
PPartnerTypeTable PartnerTypesDT;
DataRow[] PartnerTypesFoundRows;
PartnerEditTDSFamilyMembersRow NewRow;
StringCollection RequiredColumns;
int Counter;
Boolean TypeCodePresent;
DataSet TmpDS;
PartnerTypeFamilyMembersDT = new PartnerEditTDSFamilyMembersTable();
try
{
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
ParametersArray = new OdbcParameter[1];
ParametersArray[0] = new OdbcParameter("", OdbcType.Decimal, 10);
ParametersArray[0].Value = (System.Object)AFamilyPartnerKey;
if (ACountOnly)
{
ACount = Convert.ToInt32(DBAccess.GDBAccessObj.ExecuteScalar(
"SELECT COUNT(*) " +
"FROM PUB_" + PPersonTable.GetTableDBName() +
" INNER JOIN " + "PUB_" + PPartnerTable.GetTableDBName() +
" ON " + "PUB_" + PPersonTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + " = " +
"PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ' ' +
"WHERE " + PPersonTable.GetFamilyKeyDBName() + " = ? " +
"AND " + PPartnerTable.GetStatusCodeDBName() + " <> '" +
SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscMERGED) + "'", ReadTransaction,
ParametersArray));
// Make sure we don't count MERGED Partners (shouldn't have a p_family_key_n, but just in case.)
}
else
{
// Find all Persons that belong to the Family
// TLogging.LogAtLevel(7, "TPartnerEditUIConnector.GetFamilyMembersInternal: loading Persons for Family " + AFamilyPartnerKey.ToString() + "...");
TmpDS = new DataSet();
FamilyPersonsDT = new PPersonTable();
TmpDS.Tables.Add(FamilyPersonsDT);
DBAccess.GDBAccessObj.Select(TmpDS,
"SELECT " + "PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ", " +
PPersonTable.GetFamilyNameDBName() + ", " +
PPersonTable.GetTitleDBName() + ", " +
PPersonTable.GetFirstNameDBName() + ", " +
PPersonTable.GetMiddleName1DBName() + ", " +
PPersonTable.GetFamilyIdDBName() + ", " +
PPersonTable.GetGenderDBName() + ", " +
PPersonTable.GetDateOfBirthDBName() + ' ' +
"FROM PUB_" + PPersonTable.GetTableDBName() +
" INNER JOIN " + "PUB_" + PPartnerTable.GetTableDBName() + " ON " +
"PUB_" + PPersonTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + " = " +
"PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ' ' +
"WHERE " + PPersonTable.GetFamilyKeyDBName() + " = ? " +
"AND " + PPartnerTable.GetStatusCodeDBName() + " <> '" +
SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscMERGED) + "'", // Make sure we don't load MERGED Partners (shouldn't have a p_family_key_n, but just in case.)
PPersonTable.GetTableName(), ReadTransaction, ParametersArray, 0, 0);
ACount = FamilyPersonsDT.Rows.Count;
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTypeTable.GetTypeCodeDBName());
TypeCodePresent = false;
// Add Persons to Table
for (Counter = 0; Counter <= FamilyPersonsDT.Rows.Count - 1; Counter += 1)
{
// Load a Person's SpecialTypes if requested
if (AWorkWithSpecialType != "")
{
PartnerTypesDT = PPartnerTypeAccess.LoadViaPPartner(
FamilyPersonsDT[Counter].PartnerKey,
RequiredColumns,
ReadTransaction,
null,
0,
0);
if (PartnerTypesDT.Rows.Count != 0)
{
// check if the searched for Special Type is present
PartnerTypesFoundRows = PartnerTypesDT.Select(
PPartnerTypeTable.GetTypeCodeDBName() + " = '" + AWorkWithSpecialType + "'");
if ((PartnerTypesFoundRows != null) && (PartnerTypesFoundRows.Length > 0))
{
TypeCodePresent = true;
}
else
{
TypeCodePresent = false;
}
}
else
{
TypeCodePresent = false;
}
}
NewRow = PartnerTypeFamilyMembersDT.NewRowTyped(false);
NewRow.PartnerKey = FamilyPersonsDT[Counter].PartnerKey;
NewRow.PartnerShortName =
Calculations.DeterminePartnerShortName(TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFamilyName,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnTitle,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFirstName,
FamilyPersonsDT[Counter]),
TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnMiddleName1, FamilyPersonsDT[Counter]));
NewRow.FamilyId = FamilyPersonsDT[Counter].FamilyId;
if (AWorkWithSpecialType != "")
{
NewRow.TypeCodePresent = TypeCodePresent;
NewRow.TypeCodeModify = false;
// TODO 2 oChristianK cSpecial Types / Family Members : Add all other Special Types of each Person
NewRow.OtherTypeCodes = "#NOT YET RETRIEVED FROM DB!#";
}
NewRow.Gender = FamilyPersonsDT[Counter].Gender;
try
{
NewRow.DateOfBirth = FamilyPersonsDT[Counter].DateOfBirth;
}
catch (System.Data.StrongTypingException)
{
NewRow.DateOfBirth = System.DateTime.MinValue;
}
PartnerTypeFamilyMembersDT.Rows.Add(NewRow);
}
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerEditUIConnector.GetFamilyMembersInternal: committed own transaction.");
}
}
return PartnerTypeFamilyMembersDT;
}
/// <summary>
/// export all posted invoices for conference and seminar participants in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "participants.csv"));
Console.WriteLine("Writing file: " + filename);
TDBTransaction Transaction = null;
AGiftDetailTable giftdetails = new AGiftDetailTable();
AGiftTable gifts = new AGiftTable();
AGiftBatchTable batches = new AGiftBatchTable();
PPersonTable persons = new PPersonTable();
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction,
delegate
{
// all gift details towards a costcentre that needs to be exported
string sql =
String.Format("SELECT DISTINCT D.* " +
"FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D " +
"WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " +
"AND G.{3} = B.{3} AND G.{9} = B.{9} " +
"AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " +
"AND D.{11} IN ({12}) " +
"AND NOT D.{13} = '{14}'",
AGiftBatchTable.GetTableDBName(),
AGiftTable.GetTableDBName(),
AGiftDetailTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
AGiftBatchTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
AGiftDetailTable.GetMotivationGroupCodeDBName(),
"GIFT");
DBAccess.GDBAccessObj.SelectDT(giftdetails, sql, Transaction, null, 0, 0);
sql = sql.Replace("SELECT DISTINCT D.*", "SELECT DISTINCT G.*");
DBAccess.GDBAccessObj.SelectDT(gifts, sql, Transaction, null, 0, 0);
gifts.DefaultView.Sort =
AGiftTable.GetBatchNumberDBName() + "," +
AGiftTable.GetGiftTransactionNumberDBName();
sql = sql.Replace("SELECT DISTINCT G.*", "SELECT DISTINCT B.*");
DBAccess.GDBAccessObj.SelectDT(batches, sql, Transaction, null, 0, 0);
batches.DefaultView.Sort = AGiftTable.GetBatchNumberDBName();
sql =
String.Format("SELECT DISTINCT P.* " +
"FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D, PUB.{15} AS P " +
"WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " +
"AND G.{3} = B.{3} AND G.{9} = B.{9} " +
"AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " +
"AND D.{11} IN ({12}) " +
"AND NOT D.{13} = '{14}' " +
"AND P.{16} = G.{17}",
AGiftBatchTable.GetTableDBName(),
AGiftTable.GetTableDBName(),
AGiftDetailTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
AGiftBatchTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
AGiftDetailTable.GetMotivationGroupCodeDBName(),
"GIFT",
PPersonTable.GetTableDBName(),
PPersonTable.GetPartnerKeyDBName(),
AGiftTable.GetDonorKeyDBName());
DBAccess.GDBAccessObj.SelectDT(persons, sql, Transaction, null, 0, 0);
persons.DefaultView.Sort = PPersonTable.GetPartnerKeyDBName();
});
StringBuilder sb = new StringBuilder();
foreach (AGiftDetailRow detail in giftdetails.Rows)
{
AGiftRow gift = (AGiftRow)gifts.DefaultView.FindRows(new object[] { detail.BatchNumber, detail.GiftTransactionNumber })[0].Row;
AGiftBatchRow batch = (AGiftBatchRow)batches.DefaultView.FindRows(detail.BatchNumber)[0].Row;
DataRowView[] personList = persons.DefaultView.FindRows(gift.DonorKey);
PPersonRow person = (personList.Length > 0 ? (PPersonRow)personList[0].Row : null);
sb.Append(StringHelper.StrMerge(
new string[] {
"GB" + detail.BatchNumber.ToString() + "_G" + detail.GiftTransactionNumber.ToString() +
"_D" + detail.DetailNumber.ToString(),
String.Format("{0:N}", detail.GiftTransactionAmount),
batch.GlEffectiveDate.ToString("yyyyMMdd"),
gift.DonorKey.ToString(),
person !=
null ? (person.DateOfBirth.HasValue ? person.DateOfBirth.Value.ToString("yyyyMMdd") : string.Empty) : string.Empty,
detail.CostCentreCode,
batch.BatchDescription,
detail.GiftCommentOne,
detail.GiftCommentTwo
}, ACSVSeparator));
sb.Append(ANewLine);
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
private static void ParsePartners(ref PartnerImportExportTDS AMainDS,
XmlNode ACurNode,
TDBTransaction ATransaction,
ref TVerificationResultCollection AVerificationResult)
{
XmlNode LocalNode = ACurNode;
while (LocalNode != null)
{
if (LocalNode.Name.StartsWith("PartnerGroup"))
{
ParsePartners(ref AMainDS, LocalNode.FirstChild, ATransaction, ref AVerificationResult);
}
else if (LocalNode.Name.StartsWith("Partner"))
{
PPartnerRow PartnerRow = AMainDS.PPartner.NewRowTyped();
Boolean IsExistingPartner = false;
if (!TYml2Xml.HasAttributeRecursive(LocalNode, "SiteKey"))
{
throw new Exception(Catalog.GetString("Missing SiteKey Attribute"));
}
if (!TYml2Xml.HasAttributeRecursive(LocalNode, "status"))
{
throw new Exception(Catalog.GetString("Missing status Attribute"));
}
// get a new partner key
if (TYml2Xml.HasAttribute(LocalNode, "PartnerKey"))
{
PartnerRow.PartnerKey = Convert.ToInt64(TYml2Xml.GetAttribute(LocalNode, "PartnerKey"));
if (PPartnerAccess.Exists(PartnerRow.PartnerKey, ATransaction))
{
AMainDS.Merge(PPartnerAccess.LoadByPrimaryKey(PartnerRow.PartnerKey, ATransaction));
AMainDS.PPartner.DefaultView.RowFilter = String.Format("{0} = '{1}'",
PPartnerTable.GetPartnerKeyDBName(),
PartnerRow.PartnerKey);
PartnerRow = (PPartnerRow)AMainDS.PPartner.DefaultView[0].Row;
IsExistingPartner = true;
}
else
{
AMainDS.PPartner.Rows.Add(PartnerRow);
}
}
else
{
PartnerRow.PartnerKey = TImportExportYml.NewPartnerKey;
TImportExportYml.NewPartnerKey--;
}
String PartnerClass = TYml2Xml.GetAttributeRecursive(LocalNode, "class");
TLogging.LogAtLevel(TLogging.DEBUGLEVEL_TRACE,
PartnerClass + " " +
LocalNode.Name + " " +
"PartnerKey=" + PartnerRow.PartnerKey
);
if (IsExistingPartner && (PartnerClass != PartnerRow.PartnerClass))
{
throw new Exception(String.Format("Error: Yml contains Existing Partner {0} with a different partner class {1}!",
PartnerRow.PartnerKey, PartnerClass));
}
PartnerRow.PartnerClass = PartnerClass;
if (PartnerClass == MPartnerConstants.PARTNERCLASS_FAMILY)
{
PFamilyRow FamilyRow;
if (IsExistingPartner)
{
AMainDS.Merge(PFamilyAccess.LoadByPrimaryKey(PartnerRow.PartnerKey, ATransaction));
AMainDS.PFamily.DefaultView.RowFilter = String.Format("{0} = '{1}'",
PFamilyTable.GetPartnerKeyDBName(),
PartnerRow.PartnerKey);
FamilyRow = (PFamilyRow)AMainDS.PFamily.DefaultView[0].Row;
}
else
{
FamilyRow = AMainDS.PFamily.NewRowTyped();
FamilyRow.PartnerKey = PartnerRow.PartnerKey;
PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_FAMILY;
AMainDS.PFamily.Rows.Add(FamilyRow);
}
FamilyRow.FamilyName = TYml2Xml.GetAttributeRecursive(LocalNode, "LastName");
FamilyRow.FirstName = TYml2Xml.GetAttribute(LocalNode, "FirstName");
FamilyRow.Title = TYml2Xml.GetAttribute(LocalNode, "Title");
if (TYml2Xml.HasAttribute(LocalNode, "CreatedAt"))
{
FamilyRow.DateCreated = Convert.ToDateTime(TYml2Xml.GetAttribute(LocalNode, "CreatedAt"));
}
PartnerRow.AddresseeTypeCode = MPartnerConstants.PARTNERCLASS_FAMILY;
PartnerRow.PartnerShortName =
Calculations.DeterminePartnerShortName(FamilyRow.FamilyName, FamilyRow.Title, FamilyRow.FirstName);
}
if (PartnerClass == MPartnerConstants.PARTNERCLASS_PERSON)
{
PPersonRow PersonRow;
if (IsExistingPartner)
{
AMainDS.Merge(PPersonAccess.LoadByPrimaryKey(PartnerRow.PartnerKey, ATransaction));
AMainDS.PPerson.DefaultView.RowFilter = String.Format("{0} = '{1}'",
PPersonTable.GetPartnerKeyDBName(),
PartnerRow.PartnerKey);
PersonRow = (PPersonRow)AMainDS.PPerson.DefaultView[0].Row;
}
else
{
PersonRow = AMainDS.PPerson.NewRowTyped();
PersonRow.PartnerKey = PartnerRow.PartnerKey;
AMainDS.PPerson.Rows.Add(PersonRow);
}
PersonRow.FamilyName = TYml2Xml.GetAttributeRecursive(LocalNode, "LastName");
PersonRow.FirstName = TYml2Xml.GetAttribute(LocalNode, "FirstName");
PersonRow.Title = TYml2Xml.GetAttribute(LocalNode, "Title");
if (TYml2Xml.HasAttribute(LocalNode, "CreatedAt"))
{
PersonRow.DateCreated = Convert.ToDateTime(TYml2Xml.GetAttribute(LocalNode, "CreatedAt"));
}
// PersonRow.Sp
PartnerRow.PartnerShortName =
Calculations.DeterminePartnerShortName(PersonRow.FamilyName, PersonRow.Title, PersonRow.FirstName);
}
else if (PartnerClass == MPartnerConstants.PARTNERCLASS_ORGANISATION)
{
POrganisationRow OrganisationRow;
if (IsExistingPartner)
{
AMainDS.Merge(POrganisationAccess.LoadByPrimaryKey(PartnerRow.PartnerKey, ATransaction));
AMainDS.POrganisation.DefaultView.RowFilter = String.Format("{0} = '{1}'",
POrganisationTable.GetPartnerKeyDBName(),
PartnerRow.PartnerKey);
OrganisationRow = (POrganisationRow)AMainDS.POrganisation.DefaultView[0].Row;
}
else
{
OrganisationRow = AMainDS.POrganisation.NewRowTyped();
OrganisationRow.PartnerKey = PartnerRow.PartnerKey;
AMainDS.POrganisation.Rows.Add(OrganisationRow);
}
OrganisationRow.OrganisationName = TYml2Xml.GetAttributeRecursive(LocalNode, "Name");
PartnerRow.PartnerShortName = OrganisationRow.OrganisationName;
}
else if (PartnerClass == MPartnerConstants.PARTNERCLASS_UNIT)
{
PUnitRow UnitRow;
if (IsExistingPartner)
{
AMainDS.Merge(PUnitAccess.LoadByPrimaryKey(PartnerRow.PartnerKey, ATransaction));
AMainDS.PUnit.DefaultView.RowFilter = String.Format("{0} = '{1}'",
PUnitTable.GetPartnerKeyDBName(),
PartnerRow.PartnerKey);
UnitRow = (PUnitRow)AMainDS.PUnit.DefaultView[0].Row;
}
else
{
UnitRow = AMainDS.PUnit.NewRowTyped();
UnitRow.PartnerKey = PartnerRow.PartnerKey;
AMainDS.PUnit.Rows.Add(UnitRow);
}
UnitRow.UnitTypeCode = TYml2Xml.GetAttributeRecursive(LocalNode, "UnitTypeCode");
UnitRow.UnitName = TYml2Xml.GetAttributeRecursive(LocalNode, "Name");
if (PartnerRow.PartnerKey < -1)
{
throw new Exception("Invalid Partner Key or No Partner Key - and no proper handling implemented");
// from here...
/*
* AVerificationResult.Add(new TVerificationResult(
* String.Format(Catalog.GetString("Importing Unit {0}"), UnitRow.UnitName),
* Catalog.GetString("You need to provide a partner key for the unit"),
* TResultSeverity.Resv_Critical));
*/
// ...to here: throws Exception in case of a illegal import file?
// The above code must have a glitch
}
if (!TYml2Xml.HasAttribute(LocalNode, "ParentUnitKey"))
{
throw new Exception(
"The currently being processed unit (PartnerKey " +
PartnerRow.PartnerKey +
") requires a ParentUnitKey.");
}
Int64 ParentKey = Convert.ToInt64(TYml2Xml.GetAttributeRecursive(LocalNode, "ParentUnitKey"));
UmUnitStructureRow UnitStructureRow = null;
if (IsExistingPartner)
{
AMainDS.Merge(UmUnitStructureAccess.LoadViaPUnitChildUnitKey(PartnerRow.PartnerKey, ATransaction));
AMainDS.UmUnitStructure.DefaultView.RowFilter = String.Format("{0} = '{1}'",
UmUnitStructureTable.GetChildUnitKeyDBName(),
PartnerRow.PartnerKey);
if (AMainDS.UmUnitStructure.DefaultView.Count > 0)
{
UnitStructureRow = (UmUnitStructureRow)AMainDS.UmUnitStructure.DefaultView[0].Row;
}
}
if (UnitStructureRow == null)
{
UnitStructureRow = AMainDS.UmUnitStructure.NewRowTyped();
UnitStructureRow.ParentUnitKey = ParentKey;
UnitStructureRow.ChildUnitKey = PartnerRow.PartnerKey;
AMainDS.UmUnitStructure.Rows.Add(UnitStructureRow);
}
else
{
UnitStructureRow.ParentUnitKey = ParentKey;
UnitStructureRow.ChildUnitKey = PartnerRow.PartnerKey;
}
PartnerRow.PartnerShortName = UnitRow.UnitName;
}
else if (PartnerClass == MPartnerConstants.PARTNERCLASS_BANK)
{
PBankRow BankRow;
if (IsExistingPartner)
{
AMainDS.Merge(PBankAccess.LoadByPrimaryKey(PartnerRow.PartnerKey, ATransaction));
AMainDS.PBank.DefaultView.RowFilter = String.Format("{0} = '{1}'",
PBankTable.GetPartnerKeyDBName(),
PartnerRow.PartnerKey);
BankRow = (PBankRow)AMainDS.PBank.DefaultView[0].Row;
}
else
{
BankRow = AMainDS.PBank.NewRowTyped();
BankRow.PartnerKey = PartnerRow.PartnerKey;
AMainDS.PBank.Rows.Add(BankRow);
}
BankRow.BranchName = TYml2Xml.GetAttribute(LocalNode, "BranchName");
BankRow.BranchCode = TYml2Xml.GetAttribute(LocalNode, "BranchCode");
BankRow.Bic = TYml2Xml.GetAttribute(LocalNode, "BranchBic");
BankRow.EpFormatFile = TYml2Xml.GetAttribute(LocalNode, "EpFormatFile");
if (TYml2Xml.HasAttribute(LocalNode, "CreatedAt"))
{
BankRow.DateCreated = Convert.ToDateTime(TYml2Xml.GetAttribute(LocalNode, "CreatedAt"));
}
}
else
{
/*
* throw new Exception(
* "Unknown Partner Class" +
* TYml2Xml.GetAttributeRecursive(LocalNode, "class"));
*/
// TODO AVerificationResult add failing problem: unknown partner class
}
PartnerRow.StatusCode = TYml2Xml.GetAttributeRecursive(LocalNode, "status");
// import special types
StringCollection SpecialTypes = StringHelper.StrSplit(TYml2Xml.GetAttributeRecursive(LocalNode, "SpecialTypes"), ",");
if (IsExistingPartner)
{
PPartnerTypeAccess.LoadViaPPartner(AMainDS, PartnerRow.PartnerKey, ATransaction);
}
foreach (string SpecialType in SpecialTypes)
{
PPartnerTypeRow PartnerTypeRow = null;
AMainDS.PPartnerType.DefaultView.RowFilter = String.Format("{0}={1} AND {2}='{3}'",
PPartnerTypeTable.GetPartnerKeyDBName(),
PartnerRow.PartnerKey,
PPartnerTypeTable.GetTypeCodeDBName(),
SpecialType
);
if (AMainDS.PPartnerType.DefaultView.Count > 0)
{
PartnerTypeRow = (PPartnerTypeRow)AMainDS.PPartnerType.DefaultView[0].Row;
}
else
{
PartnerTypeRow = AMainDS.PPartnerType.NewRowTyped();
PartnerTypeRow.PartnerKey = PartnerRow.PartnerKey;
PartnerTypeRow.TypeCode = SpecialType.Trim();
AMainDS.PPartnerType.Rows.Add(PartnerTypeRow);
}
// Check Partner type exists, or create it
bool TypeIsKnown = PTypeAccess.Exists(PartnerTypeRow.TypeCode, ATransaction);
if (!TypeIsKnown)
{
Int32 RowIdx = AMainDS.PType.DefaultView.Find(PartnerTypeRow.TypeCode); // I might have created it a second ago..
if (RowIdx < 0)
{
PTypeRow TypeRow = AMainDS.PType.NewRowTyped();
TypeRow.TypeCode = PartnerTypeRow.TypeCode;
TypeRow.TypeDescription = "Created from YAML import";
AMainDS.PType.Rows.Add(TypeRow);
}
}
}
// import subscriptions
StringCollection Subscriptions = StringHelper.StrSplit(TYml2Xml.GetAttributeRecursive(LocalNode, "Subscriptions"), ",");
foreach (string publicationCode in Subscriptions)
{
PSubscriptionRow subscription = AMainDS.PSubscription.NewRowTyped();
subscription.PartnerKey = PartnerRow.PartnerKey;
subscription.PublicationCode = publicationCode.Trim();
subscription.ReasonSubsGivenCode = "FREE";
AMainDS.PSubscription.Rows.Add(subscription);
}
// import address
XmlNode addressNode = TYml2Xml.GetChild(LocalNode, "Address");
if ((addressNode == null) || (TYml2Xml.GetAttributeRecursive(addressNode, "Street").Length == 0))
{
if (!IsExistingPartner)
{
// add the empty location
PPartnerLocationRow partnerlocation = AMainDS.PPartnerLocation.NewRowTyped(true);
partnerlocation.SiteKey = 0;
partnerlocation.PartnerKey = PartnerRow.PartnerKey;
partnerlocation.DateEffective = DateTime.Now;
partnerlocation.LocationType = "HOME";
partnerlocation.SendMail = false;
partnerlocation.EmailAddress = TYml2Xml.GetAttributeRecursive(addressNode, "Email");
partnerlocation.TelephoneNumber = TYml2Xml.GetAttributeRecursive(addressNode, "Phone");
partnerlocation.MobileNumber = TYml2Xml.GetAttributeRecursive(addressNode, "MobilePhone");
AMainDS.PPartnerLocation.Rows.Add(partnerlocation);
}
}
else
{
PLocationRow location = AMainDS.PLocation.NewRowTyped(true);
location.LocationKey = (AMainDS.PLocation.Rows.Count + 1) * -1;
location.SiteKey = 0;
if (!TYml2Xml.HasAttributeRecursive(LocalNode, "Country"))
{
throw new Exception(Catalog.GetString("Missing Country Attribute"));
}
location.CountryCode = TYml2Xml.GetAttributeRecursive(addressNode, "Country");
location.StreetName = TYml2Xml.GetAttributeRecursive(addressNode, "Street");
location.City = TYml2Xml.GetAttributeRecursive(addressNode, "City");
location.PostalCode = TYml2Xml.GetAttributeRecursive(addressNode, "PostCode");
AMainDS.PLocation.Rows.Add(location);
PPartnerLocationRow partnerlocation = AMainDS.PPartnerLocation.NewRowTyped(true);
partnerlocation.SiteKey = 0;
partnerlocation.LocationKey = location.LocationKey;
partnerlocation.PartnerKey = PartnerRow.PartnerKey;
partnerlocation.SendMail = true;
partnerlocation.DateEffective = DateTime.Now;
partnerlocation.LocationType = "HOME";
partnerlocation.EmailAddress = TYml2Xml.GetAttributeRecursive(addressNode, "Email");
partnerlocation.TelephoneNumber = TYml2Xml.GetAttributeRecursive(addressNode, "Phone");
partnerlocation.MobileNumber = TYml2Xml.GetAttributeRecursive(addressNode, "MobilePhone");
AMainDS.PPartnerLocation.Rows.Add(partnerlocation);
}
// import finance details (bank account number)
XmlNode financialDetailsNode = TYml2Xml.GetChild(LocalNode, "FinancialDetails");
ParseFinancialDetails(AMainDS, financialDetailsNode, PartnerRow.PartnerKey, ATransaction);
}
LocalNode = LocalNode.NextSibling;
}
}
