/// <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 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 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();
}