/// <summary> /// Retrieves the Partner ShortName, the PartnerClass and PartnerStatus. /// </summary> /// <param name="APartnerKey">PartnerKey to identify the Partner.</param> /// <param name="APartnerShortName">Returns the ShortName.</param> /// <param name="APartnerClass">Returns the PartnerClass (FAMILY, ORGANISATION, etc).</param> /// <param name="APartnerStatus">Returns the PartnerStatus (eg. ACTIVE, DIED).</param> /// <param name="ATransaction">Open DB Transaction.</param> /// <returns>True if partner was found, otherwise false.</returns> public static Boolean RetrievePartnerShortName(Int64 APartnerKey, out String APartnerShortName, out TPartnerClass APartnerClass, out TStdPartnerStatusCode APartnerStatus, TDBTransaction ATransaction) { Boolean ReturnValue; StringCollection RequiredColumns; PPartnerTable PartnerTable; // initialise out Arguments APartnerShortName = ""; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here. APartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here. APartnerStatus = TStdPartnerStatusCode.spscINACTIVE; if (APartnerKey != 0) { // only some fields are needed RequiredColumns = new StringCollection(); RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName()); RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName()); RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName()); PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ATransaction, null, 0, 0); if (PartnerTable.Rows.Count == 0) { ReturnValue = false; } else { // since we loaded by primary key there must just be one partner row APartnerShortName = PartnerTable[0].PartnerShortName; APartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass); APartnerStatus = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode); ReturnValue = true; } } else { // Return result as valid if Partner Key is 0. ReturnValue = true; } return(ReturnValue); }
private DataTable GetConferenceListTable(TDBTransaction AReadTransaction, string ATableName) { DataTable Table; DataColumn[] Key = new DataColumn[1]; // Used eg. Select Event Dialog Table = DBAccess.GetDBAccessObj(AReadTransaction).SelectDT( "SELECT DISTINCT " + PPartnerTable.GetPartnerShortNameDBName() + ", " + PPartnerTable.GetPartnerClassDBName() + ", " + PUnitTable.GetOutreachCodeDBName() + ", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() + ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() + ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() + ", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + ", " + PUnitTable.GetUnitTypeCodeDBName() + " FROM PUB." + PPartnerTable.GetTableDBName() + ", PUB." + PUnitTable.GetTableDBName() + ", PUB." + PLocationTable.GetTableDBName() + ", PUB." + PPartnerLocationTable.GetTableDBName() + ", PUB." + PCountryTable.GetTableDBName() + " WHERE " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' AND " + PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' AND (" + PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " + PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')" , ATableName, AReadTransaction); Key[0] = Table.Columns[PPartnerTable.GetPartnerKeyDBName()]; Table.PrimaryKey = Key; return(Table); }
/// <summary> /// todoComment /// </summary> /// <returns></returns> public String DetermineCurrentPartnerClass() { DataRow CurrentDR = this.CurrentDataRow; String PartnerClass; if (CurrentDR != null) { // get PartnerClass of current DataRow PartnerClass = Convert.ToString(CurrentDR[PPartnerTable.GetPartnerClassDBName()]); } else { PartnerClass = ""; } // MessageBox.Show(PartnerClass); return(PartnerClass); }
/// <summary> /// The screen has been shown /// </summary> private void RunOnceOnActivationManual() { // enable autofind in list for first character (so the user can press character to find list entry) this.clbAddress.AutoFindColumn = ((Int16)(1)); this.clbAddress.AutoFindMode = Ict.Common.Controls.TAutoFindModeEnum.FirstCharacter; clbAddress.SpecialKeys = ((SourceGrid.GridSpecialKeys)((((((SourceGrid.GridSpecialKeys.Arrows | SourceGrid.GridSpecialKeys.PageDownUp) | SourceGrid.GridSpecialKeys.Enter) | SourceGrid.GridSpecialKeys.Escape) | SourceGrid.GridSpecialKeys.Control) | SourceGrid.GridSpecialKeys.Shift))); string CheckedColumn = "CHECKED"; string ShortNameColumn = PPartnerTable.GetPartnerShortNameDBName(); string PartnerKeyColumn = PPartnerLocationTable.GetPartnerKeyDBName(); string PartnerClassColumn = PPartnerTable.GetPartnerClassDBName(); string TelephoneColumn = PPartnerLocationTable.GetTelephoneNumberDBName(); string LocationTypeColumn = PPartnerLocationTable.GetLocationTypeDBName(); DataTable NewTable = FPartnerSharingLocationDV.ToTable(true, new string[] { ShortNameColumn, PartnerKeyColumn, PartnerClassColumn, TelephoneColumn, LocationTypeColumn }); NewTable.Columns.Add(new DataColumn(CheckedColumn, typeof(bool))); clbAddress.Columns.Clear(); clbAddress.AddCheckBoxColumn("", NewTable.Columns[CheckedColumn], 17, false); clbAddress.AddTextColumn(Catalog.GetString("Name"), NewTable.Columns[ShortNameColumn], 240); clbAddress.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), NewTable.Columns[PartnerKeyColumn], 90); clbAddress.AddTextColumn(Catalog.GetString("Partner Class"), NewTable.Columns[PartnerClassColumn], 90); clbAddress.AddTextColumn(Catalog.GetString("Telephone"), NewTable.Columns[TelephoneColumn], 130); clbAddress.AddTextColumn(Catalog.GetString("Location Type"), NewTable.Columns[LocationTypeColumn], 90); clbAddress.DataBindGrid(NewTable, ShortNameColumn, CheckedColumn, PartnerKeyColumn, false, true, false); // initialize list of checked items clbAddress.SetCheckedStringList(""); // this is just to avoid compiler warning from FMainDS being initialized but not used if (FMainDS.IsInitialized) { } }
/// <summary> /// Loads all costcentres that are linked to a partner, with the partner key and partner class /// </summary> public static DataTable GetData(String ATableName, Int32 ALedgerNumber, TDBTransaction AReadTransaction) { return(DBAccess.GDBAccessObj.SelectDT( String.Format( "SELECT {0}, {3}.{1}, {3}.{5}, {2}, {8} FROM pub_{3} " + "LEFT OUTER JOIN PUB_{7} ON PUB_{7}.{1} = PUB_{3}.{1}, pub_{4} " + "WHERE pub_{3}.{1} = pub_{4}.{1} AND pub_{3}.{5} = {6}", AValidLedgerNumberTable.GetCostCentreCodeDBName(), AValidLedgerNumberTable.GetPartnerKeyDBName(), PPartnerTable.GetPartnerClassDBName(), AValidLedgerNumberTable.GetTableDBName(), PPartnerTable.GetTableDBName(), AValidLedgerNumberTable.GetLedgerNumberDBName(), ALedgerNumber.ToString(), PUnitTable.GetTableDBName(), PUnitTable.GetUnitTypeCodeDBName()), ATableName, AReadTransaction)); }
public static DataTable GetEventUnits() { List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); DataColumn[] Key = new DataColumn[3]; DataTable Events = new DataTable(); if (TLogging.DL >= 9) { Console.WriteLine("GetEventUnits called!"); } TDBTransaction Transaction = null; DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { string SqlStmt = "SELECT DISTINCT " + PPartnerTable.GetPartnerShortNameDBName() + ", " + PPartnerTable.GetPartnerClassDBName() + ", " + PUnitTable.GetOutreachCodeDBName() + ", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() + ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() + ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() + ", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + ", " + PUnitTable.GetUnitTypeCodeDBName() + ", " + PUnitTable.GetUnitNameDBName() + " FROM pub_" + PPartnerTable.GetTableDBName() + ", pub_" + PUnitTable.GetTableDBName() + ", pub_" + PLocationTable.GetTableDBName() + ", pub_" + PPartnerLocationTable.GetTableDBName() + ", pub_" + PCountryTable.GetTableDBName() + " WHERE " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " + PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' "; // sort rows according to name SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName(); Events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "events", Transaction, SqlParameterList.ToArray()); Key[0] = Events.Columns[PPartnerTable.GetPartnerKeyDBName()]; Key[1] = Events.Columns[PPartnerLocationTable.GetSiteKeyDBName()]; Key[2] = Events.Columns[PPartnerLocationTable.GetLocationKeyDBName()]; Events.PrimaryKey = Key; }); return(Events); }
/// <summary> /// This report considers gifts given between the two specified dates, and can include all gifts or, if /// selected, those to a particular motivation, motivation detail or recipient. For the defined set of gifts /// and its total value, the donors are sorted into a list, starting with those who gave most, and showing /// the percentage that their gifts contributed to the total received (for this motivation or recipient, if /// specified) and the cumulative percentage, moving down the list starting with the top donor. /// </summary> /// <param name="ATotalAmount">Pre calculated value of the total gifts given with these parameters</param> /// <param name="ATopXPercent">Upper limit of the percentage to show in the report</param> /// <param name="ABottomXPercent">Lower limit of the percentage to show in the report</param> /// <param name="AExtract">true to use only partners from an extract</param> /// <param name="AExtractName">extract name</param> /// <param name="AStartDate">Start date of the gifts given</param> /// <param name="AEndDate">End date of the gifts given</param> /// <param name="ARecipientKey">Partner key of a specific recipient. If 0 then use all recipients</param> /// <param name="AMotivationGroup">Limit gifts to this motivation group. If % use all motivation groups</param> /// <param name="AMotivationDetail">Limit gifts to this motivation detail. If % use all motivation details</param> /// <returns></returns> private bool MakeTopDonor(decimal ATotalAmount, decimal ATopXPercent, decimal ABottomXPercent, bool AExtract, String AExtractName, DateTime AStartDate, DateTime AEndDate, Int64 ARecipientKey, String AMotivationGroup, String AMotivationDetail) { Int64 LedgerNumber = situation.GetParameters().Get("param_ledger_number_i").ToInt64(); String CurrencyType = situation.GetParameters().Get("param_currency").ToString(); StringBuilder SqlString = new StringBuilder(); SqlString.Append("SELECT DISTINCT "); SqlString.Append("gift.p_donor_key_n AS DonorKey, "); SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerShortNameDBName() + " AS ShortName, "); SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerClassDBName() + " AS PartnerClass, "); if (CurrencyType == "Base") { SqlString.Append("SUM(detail." + AGiftDetailTable.GetGiftAmountDBName() + ") AS Amount "); } else { SqlString.Append("SUM(detail." + AGiftDetailTable.GetGiftAmountIntlDBName() + ") AS Amount "); } SqlString.Append( " FROM " + AGiftTable.GetTableDBName() + " as gift, " + AGiftDetailTable.GetTableDBName() + " as detail, " + PPartnerTable.GetTableDBName() + ", " + AGiftBatchTable.GetTableDBName() + " "); if (AExtract) { SqlString.Append(", " + MExtractTable.GetTableDBName() + ", " + MExtractMasterTable.GetTableDBName()); SqlString.Append( " WHERE gift." + AGiftTable.GetDonorKeyDBName() + " = " + MExtractTable.GetTableDBName() + "." + MExtractTable.GetPartnerKeyDBName()); SqlString.Append( " AND " + MExtractTable.GetTableDBName() + "." + MExtractTable.GetExtractIdDBName() + " = " + MExtractMasterTable.GetTableDBName() + "." + MExtractMasterTable.GetExtractIdDBName()); SqlString.Append(" AND " + MExtractMasterTable.GetTableDBName() + "." + MExtractMasterTable.GetExtractNameDBName() + " = '"); SqlString.Append(AExtractName); SqlString.Append("' AND "); } else { SqlString.Append(" WHERE "); } SqlString.Append(" detail." + AGiftDetailTable.GetLedgerNumberDBName() + " = gift." + AGiftTable.GetLedgerNumberDBName()); SqlString.Append(" AND detail." + AGiftDetailTable.GetBatchNumberDBName() + " = gift." + AGiftTable.GetBatchNumberDBName()); SqlString.Append( " AND detail." + AGiftDetailTable.GetGiftTransactionNumberDBName() + " = gift." + AGiftTable.GetGiftTransactionNumberDBName()); SqlString.Append(" AND gift." + AGiftTable.GetDateEnteredDBName() + " BETWEEN '"); SqlString.Append(AStartDate.ToString("yyyy-MM-dd")); SqlString.Append("' AND '"); SqlString.Append(AEndDate.ToString("yyyy-MM-dd")); SqlString.Append("' AND gift." + AGiftTable.GetLedgerNumberDBName() + " = "); SqlString.Append(LedgerNumber.ToString()); SqlString.Append(" AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetLedgerNumberDBName() + " = "); SqlString.Append(LedgerNumber.ToString()); SqlString.Append( " AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchNumberDBName() + " = gift." + AGiftTable.GetBatchNumberDBName()); SqlString.Append(" AND ( " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'Posted' OR "); SqlString.Append(AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'posted' ) "); SqlString.Append( " AND " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = gift." + AGiftTable.GetDonorKeyDBName()); if (ARecipientKey != 0) { SqlString.Append(" AND detail." + AGiftDetailTable.GetRecipientKeyDBName() + " = "); SqlString.Append(ARecipientKey.ToString()); } if (AMotivationGroup != "%") { SqlString.Append(" AND detail." + AGiftDetailTable.GetMotivationGroupCodeDBName() + " LIKE '"); SqlString.Append(AMotivationGroup); SqlString.Append("' "); } if (AMotivationDetail != "%") { SqlString.Append(" AND detail." + AGiftDetailTable.GetMotivationDetailCodeDBName() + " LIKE '"); SqlString.Append(AMotivationDetail); SqlString.Append("' "); } SqlString.Append(" GROUP BY gift." + AGiftTable.GetDonorKeyDBName() + ", "); SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerShortNameDBName() + ", "); SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerClassDBName()); SqlString.Append(" ORDER BY Amount DESC"); DataTable Table = situation.GetDatabaseConnection().SelectDT(SqlString.ToString(), "table", situation.GetDatabaseConnection().Transaction, new OdbcParameter[] { }); decimal CummulativeAmount = 0; decimal TopAmount = ATotalAmount * ATopXPercent / 100; decimal BottomAmount = ATotalAmount * ABottomXPercent / 100; int NumColumns = 7; int ChildRow = 1; situation.GetResults().Clear(); for (int Counter = 0; Counter < Table.Rows.Count; ++Counter) { decimal CurrentAmount = Convert.ToDecimal(Table.Rows[Counter]["Amount"]); if (CurrentAmount < 0) { continue; } if ((CummulativeAmount <= TopAmount) && (CummulativeAmount >= BottomAmount)) { Int64 DonorKey = Convert.ToInt64(Table.Rows[Counter]["DonorKey"]); String ShortName = (String)Table.Rows[Counter]["ShortName"]; String PartnerClass = (String)Table.Rows[Counter]["PartnerClass"]; CummulativeAmount += CurrentAmount; // Transfer to results TVariant[] Header = new TVariant[NumColumns]; TVariant[] Description = { new TVariant(), new TVariant() }; TVariant[] Columns = new TVariant[NumColumns]; for (int Counter2 = 0; Counter2 < NumColumns; ++Counter2) { Header[Counter2] = new TVariant(); Columns[Counter2] = new TVariant(); } StringBuilder PartnerAddress = new StringBuilder(); PPartnerLocationRow AddressRow; if (Ict.Petra.Server.MReporting.MPartner.TRptUserFunctionsPartner.GetPartnerBestAddressRow(DonorKey, situation, out AddressRow)) { PLocationTable LocationTable = PLocationAccess.LoadByPrimaryKey(AddressRow.SiteKey, AddressRow.LocationKey, situation.GetDatabaseConnection().Transaction); if (LocationTable.Rows.Count > 0) { PLocationRow LocationRow = (PLocationRow)LocationTable.Rows[0]; PartnerAddress.Append(LocationRow.Locality); if (LocationRow.Locality.Length > 0) { PartnerAddress.Append(", "); } PartnerAddress.Append(LocationRow.StreetName); if (PartnerAddress.Length > 0) { PartnerAddress.Append(", "); } PartnerAddress.Append(LocationRow.Address3); if (PartnerAddress.Length > 0) { PartnerAddress.Append(", "); } PartnerAddress.Append(LocationRow.PostalCode); PartnerAddress.Append(" "); PartnerAddress.Append(LocationRow.City); if (LocationRow.County.Length > 0) { PartnerAddress.Append(", "); PartnerAddress.Append(LocationRow.County); } PartnerAddress.Append(", "); PartnerAddress.Append(LocationRow.CountryCode); } } Columns[0] = new TVariant(DonorKey.ToString("0000000000")); Columns[1] = new TVariant(PartnerClass); Columns[2] = new TVariant(ShortName); Columns[3] = new TVariant(CurrentAmount, "-#,##0.00;#,##0.00"); Columns[4] = new TVariant((CurrentAmount * 100 / ATotalAmount), "-#,##0.00;#,##0.00"); Columns[5] = new TVariant((CummulativeAmount * 100 / ATotalAmount), "-#,##0.00;#,##0.00"); Columns[6] = new TVariant(PartnerAddress.ToString()); situation.GetResults().AddRow(0, ChildRow++, true, 2, "", "", false, Header, Description, Columns); } else { CummulativeAmount += CurrentAmount; } } return(true); }
public static Boolean MergedPartnerDetails(Int64 AMergedPartnerPartnerKey, out String AMergedPartnerPartnerShortName, out TPartnerClass AMergedPartnerPartnerClass, out Int64 AMergedIntoPartnerKey, out String AMergedIntoPartnerShortName, out TPartnerClass AMergedIntoPartnerClass, out String AMergedBy, out DateTime AMergeDate) { TDBTransaction ReadTransaction; Boolean NewTransaction; StringCollection RequiredColumns; PPartnerTable MergedPartnerTable; PPartnerTable PartnerMergedIntoTable; PPartnerMergeTable PartnerMergeTable; Boolean ReturnValue = false; // Initialise out Arguments AMergedPartnerPartnerShortName = ""; AMergedPartnerPartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here. AMergedIntoPartnerKey = -1; AMergedIntoPartnerShortName = ""; AMergedIntoPartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here. AMergedBy = ""; AMergeDate = DateTime.MinValue; if (AMergedPartnerPartnerKey != 0) { /* * First we look up the Partner that was Merged to get some details of it. */ // only some fields are needed RequiredColumns = new StringCollection(); RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName()); RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName()); RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName()); ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); try { MergedPartnerTable = PPartnerAccess.LoadByPrimaryKey( AMergedPartnerPartnerKey, RequiredColumns, ReadTransaction, null, 0, 0); if (MergedPartnerTable.Rows.Count == 0) { ReturnValue = false; } else { if (SharedTypes.StdPartnerStatusCodeStringToEnum( MergedPartnerTable[0].StatusCode) != TStdPartnerStatusCode.spscMERGED) { ReturnValue = false; } else { AMergedPartnerPartnerShortName = MergedPartnerTable[0].PartnerShortName; AMergedPartnerPartnerClass = SharedTypes.PartnerClassStringToEnum(MergedPartnerTable[0].PartnerClass); /* * Now we look up the Partner that was Merged in the PartnerMerge DB Table * to get the information about the Merged-Into Partner. */ PartnerMergeTable = PPartnerMergeAccess.LoadByPrimaryKey(AMergedPartnerPartnerKey, ReadTransaction); if (PartnerMergeTable.Rows.Count == 0) { /* * Although we didn't find the Merged Partner in the PartnerMerge * DB Table it is still a valid, Merged Partner, so we return true * in this case. */ ReturnValue = true; } else { /* * Now we look up the Merged-Into Partner to get some details of it. */ PartnerMergedIntoTable = PPartnerAccess.LoadByPrimaryKey( PartnerMergeTable[0].MergeTo, RequiredColumns, ReadTransaction, null, 0, 0); if (PartnerMergedIntoTable.Rows.Count == 0) { ReturnValue = false; } else { AMergedIntoPartnerKey = PartnerMergeTable[0].MergeTo; AMergedBy = PartnerMergeTable[0].MergedBy; AMergeDate = PartnerMergeTable[0].MergeDate.Value; AMergedIntoPartnerShortName = PartnerMergedIntoTable[0].PartnerShortName; AMergedIntoPartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerMergedIntoTable[0].PartnerClass); ReturnValue = true; } } } } } finally { if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); TLogging.LogAtLevel(7, "TPartnerServerLookups.MergedPartnerDetails: committed own transaction."); } } } return(ReturnValue); }
public static Boolean VerifyPartnerAndGetDetails(Int64 APartnerKey, out String APartnerShortName, out TPartnerClass APartnerClass, out Boolean AIsMergedPartner, out Boolean AUserCanAccessPartner) { APartnerShortName = ""; APartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here. AIsMergedPartner = false; AUserCanAccessPartner = false; TDBTransaction ReadTransaction; Boolean NewTransaction; StringCollection RequiredColumns; PPartnerTable PartnerTable; Boolean ReturnValue = true; TStdPartnerStatusCode PartnerStatus = TStdPartnerStatusCode.spscACTIVE; // initialise outout Arguments if (APartnerKey != 0) { // only some fields are needed RequiredColumns = new StringCollection(); RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName()); RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName()); RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName()); RequiredColumns.Add(PPartnerTable.GetRestrictedDBName()); RequiredColumns.Add(PPartnerTable.GetUserIdDBName()); RequiredColumns.Add(PPartnerTable.GetGroupIdDBName()); ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); try { PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ReadTransaction, null, 0, 0); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); TLogging.LogAtLevel(7, "TPartnerServerLookups.VerifyPartner: committed own transaction."); } } if (PartnerTable.Rows.Count == 0) { ReturnValue = false; } else { // since we loaded by primary key there must just be one partner row APartnerShortName = PartnerTable[0].PartnerShortName; APartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass); PartnerStatus = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode); // check if user can access partner if (Ict.Petra.Server.MPartner.Common.TSecurity.CanAccessPartner(PartnerTable[0]) == TPartnerAccessLevelEnum.palGranted) { AUserCanAccessPartner = true; } // check if partner is merged if (PartnerStatus == TStdPartnerStatusCode.spscMERGED) { AIsMergedPartner = true; } else { AIsMergedPartner = false; } ReturnValue = true; } } else { // Return result as valid if Partner Key is 0. ReturnValue = true; } return(ReturnValue); }
/// <summary> /// generate the partners from a text file that was generated with Benerator /// </summary> /// <param name="AInputBeneratorFile"></param> public static void GenerateFamilyPartners(string AInputBeneratorFile) { TDataBase db = DBAccess.Connect("GenerateFamilyPartners"); TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted); // get a list of banks (all class BANK) string sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank"; DataTable BankKeys = db.SelectDT(sqlGetBankPartnerKeys, "keys", Transaction); Transaction.Rollback(); PartnerEditTDS MainDS = new PartnerEditTDS(); // AlanP: May 2016 - We may no longer need the UTF8 because the method now automatically discovers the encoding even with no BOM XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",", Encoding.UTF8); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; while (RecordNode != null) { string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation"); PFamilyRow familyRecord = null; PPartnerRow FamilyPartnerRow = null; if (familySituation == "singleMan") { familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Male", MainDS); SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS); } else if (familySituation == "singleWoman") { familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Female", MainDS); SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS); } else if (familySituation == "family") { familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Male", MainDS); SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS); SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS); int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren")); if (NumberOfChildren > 0) { FamilyPartnerRow = (PPartnerRow)MainDS.PPartner.Rows.Find(familyRecord.PartnerKey); FamilyPartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_FAMILY; } } FamilyPartnerRow = (PPartnerRow)MainDS.PPartner.Rows.Find(familyRecord.PartnerKey); FamilyPartnerRow.ReceiptEachGift = false; FamilyPartnerRow.ReceiptLetterFrequency = "Annual"; SampleDataWorkers.GenerateAddressForFamily(RecordNode, familyRecord, MainDS); SampleDataWorkers.GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys); if (MainDS.PFamily.Rows.Count % 100 == 0) { TLogging.Log("created donor " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName); } RecordNode = RecordNode.NextSibling; } // we do not save person records for normal family partners MainDS.PPerson.Clear(); // need to clear all partner records of the PERSON partners as well DataView PersonPartners = new DataView(MainDS.PPartner); PersonPartners.RowFilter = string.Format("{0} = '{1}'", PPartnerTable.GetPartnerClassDBName(), MPartnerConstants.PARTNERCLASS_PERSON); DataView PartnerLocations = new DataView(MainDS.PPartnerLocation); foreach (DataRowView rv in PersonPartners) { PPartnerRow partnerRow = (PPartnerRow)rv.Row; PartnerLocations.RowFilter = string.Format("{0} = {1}", PPartnerLocationTable.GetPartnerKeyDBName(), partnerRow.PartnerKey); PartnerLocations[0].Row.Delete(); partnerRow.Delete(); } MainDS.ThrowAwayAfterSubmitChanges = true; PartnerEditTDSAccess.SubmitChanges(MainDS); TLogging.Log("after saving donors"); }
public static DataTable GetEventUnits(bool AIncludeConferenceUnits, bool AIncludeOutreachUnits, string AEventName, bool AIncludeLocationData, bool ACurrentAndFutureEventsOnly) { TDBTransaction ReadTransaction; Boolean NewTransaction = false; List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); DataColumn[] Key = new DataColumn[1]; DataTable Events = new DataTable(); if (AEventName == "*") { AEventName = ""; } else if (AEventName.EndsWith("*")) { AEventName = AEventName.Substring(0, AEventName.Length - 1); } if (TLogging.DL >= 9) { Console.WriteLine("GetEventUnits called!"); } ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead, TEnforceIsolationLevel.eilMinimum, out NewTransaction); try { string SqlStmt = "SELECT DISTINCT " + PPartnerTable.GetPartnerShortNameDBName() + ", " + PPartnerTable.GetPartnerClassDBName() + ", " + PUnitTable.GetOutreachCodeDBName(); if (AIncludeLocationData || ACurrentAndFutureEventsOnly) { SqlStmt = SqlStmt + ", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() + ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() + ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName(); } SqlStmt = SqlStmt + ", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + ", " + PUnitTable.GetUnitTypeCodeDBName() + " FROM pub_" + PPartnerTable.GetTableDBName() + ", pub_" + PUnitTable.GetTableDBName(); if (AIncludeLocationData || ACurrentAndFutureEventsOnly) { SqlStmt = SqlStmt + ", pub_" + PLocationTable.GetTableDBName() + ", pub_" + PPartnerLocationTable.GetTableDBName() + ", pub_" + PCountryTable.GetTableDBName(); } SqlStmt = SqlStmt + " WHERE " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND "; if (AIncludeLocationData || ACurrentAndFutureEventsOnly) { SqlStmt = SqlStmt + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " + PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND "; } SqlStmt = SqlStmt + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " + PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' "; // add criteria for conference and/or outreach String ConferenceWhereClause = "(" + PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " + PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')"; String OutreachWhereClause = PUnitTable.GetOutreachCodeDBName() + " IS NOT NULL AND " + PUnitTable.GetOutreachCodeDBName() + " <> '' AND (" + PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONF%' AND " + PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONG%')"; if (AIncludeConferenceUnits && AIncludeOutreachUnits) { SqlStmt = SqlStmt + " AND ((" + ConferenceWhereClause + ") OR (" + OutreachWhereClause + "))"; } else if (AIncludeConferenceUnits) { SqlStmt = SqlStmt + " AND (" + ConferenceWhereClause + ")"; } else if (AIncludeOutreachUnits) { SqlStmt = SqlStmt + " AND (" + OutreachWhereClause + ")"; } // add criteria for event name filter if (AEventName.Length > 0) { // in case there is a filter set for the event name AEventName = AEventName.Replace('*', '%') + "%"; SqlStmt = SqlStmt + " AND " + PUnitTable.GetUnitNameDBName() + " LIKE '" + AEventName + "'"; } if (ACurrentAndFutureEventsOnly) { SqlStmt = SqlStmt + " AND " + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= ?"; SqlParameterList.Add(new OdbcParameter("param_date", OdbcType.Date) { Value = DateTime.Today.Date }); } // sort rows according to name SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName(); Events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "events", ReadTransaction, SqlParameterList.ToArray()); Key[0] = Events.Columns[PPartnerTable.GetPartnerKeyDBName()]; Events.PrimaryKey = Key; } catch (Exception e) { TLogging.Log(e.ToString()); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); if (TLogging.DL >= 7) { Console.WriteLine("GetEventUnits: committed own transaction."); } } } return(Events); }