/// <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 bool GetAllReceivingFields(long AConferenceKey, ref DataTable AFieldsTable) { TDBTransaction ReadTransaction; Boolean NewTransaction = false; TLogging.LogAtLevel(9, "TConferenceOptions.GetAllReceivingFields called!"); TDataBase db = DBAccess.Connect("GetAllReceivingFields"); ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead, out NewTransaction); try { OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[0]; DataSet TmpDS = ReadTransaction.DataBaseObj.Select( "SELECT PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() + ", PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() + " FROM PUB_" + PUnitTable.GetTableDBName() + ", PUB_" + PPartnerTable.GetTableDBName() + ", " + "PUB_" + PPartnerTypeTable.GetTableDBName() + " WHERE PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() + " = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() + " AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetPartnerKeyDBName() + " = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() + " AND PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetStatusCodeDBName() + " = \"ACTIVE\"" + " AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetTypeCodeDBName() + " = \"LEDGER\"" + " ORDER BY PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() + " ASC", "TempTable", ReadTransaction, ParametersArray); DataTable ResultTale = TmpDS.Tables[0]; for (int Counter = 0; Counter < ResultTale.Rows.Count; ++Counter) { DataRow NewRow = AFieldsTable.NewRow(); NewRow[SELECTION] = false; NewRow[UNIT_KEY] = ResultTale.Rows[Counter][PUnitTable.GetPartnerKeyDBName()]; NewRow[UNIT_NAME] = ResultTale.Rows[Counter][PUnitTable.GetUnitNameDBName()]; NewRow[USED_IN_CONFERENCE] = true; AFieldsTable.Rows.Add(NewRow); } } finally { if (NewTransaction) { ReadTransaction.Commit(); TLogging.LogAtLevel(7, "TConferenceOptions.GetReceivingFields: committed own transaction."); } } return(true); }
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> /// Loads all available Ledgers and their names into a DataTable /// /// </summary> /// <returns>void</returns> public static DataTable GetData(String ATableName, TDBTransaction AReadTransaction) { return(DBAccess.GDBAccessObj.SelectDT( String.Format( "SELECT {0} AS LedgerNumber, {1} AS LedgerName FROM pub_{2}, pub_{3} " + "WHERE pub_{2}.{4} = pub_{3}.{4} " + "AND pub_{2}.{5} = 1", ALedgerTable.GetLedgerNumberDBName(), PPartnerTable.GetPartnerShortNameDBName(), ALedgerTable.GetTableDBName(), PPartnerTable.GetTableDBName(), PPartnerTable.GetPartnerKeyDBName(), ALedgerTable.GetLedgerStatusDBName()), ATableName, AReadTransaction)); }
/// <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 PUnitTable GetActiveFieldUnits() { PUnitTable UnitTable = new PUnitTable(); PUnitRow UnitRow; TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetActiveFieldUnits called!"); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { // Load data string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + ", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() + " FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() + " WHERE ((" + PUnitTable.GetOutreachCodeDBName() + " IS NULL)" + " OR(" + PUnitTable.GetOutreachCodeDBName() + " = ''))" + " AND " + PUnitTable.GetUnitTypeCodeDBName() + " <> 'KEY-MIN'" + " AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'"; // sort rows according to name SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName(); DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "fields", Transaction); foreach (DataRow eventRow in events.Rows) { UnitRow = (PUnitRow)UnitTable.NewRow(); UnitRow.PartnerKey = Convert.ToInt64(eventRow[0]); UnitRow.UnitName = Convert.ToString(eventRow[1]); UnitTable.Rows.Add(UnitRow); } }); return(UnitTable); }
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); }
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> /// 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); }
/// <summary> /// export all posted invoices of other suppliers 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, "accountspayable.csv")); Console.WriteLine("Writing file: " + filename); StringBuilder sb = new StringBuilder(); TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); // get all posted or paid ap_documents by their date issued string sql = String.Format( "SELECT * FROM PUB_{0} " + "WHERE {1} = {2} AND " + "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " + "{7} >= ? AND {7} <= ?", AApDocumentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName()); List <OdbcParameter> Parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("startdate", OdbcType.DateTime); param.Value = TAccountingPeriodsWebConnector.GetPeriodStartDate(ALedgerNumber, AFinancialYear, 0, 1); Parameters.Add(param); param = new OdbcParameter("enddate", OdbcType.DateTime); param.Value = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber, AFinancialYear, 0, 12); Parameters.Add(param); AApDocumentTable apDocuments = new AApDocumentTable(); DBAccess.GDBAccessObj.SelectDT(apDocuments, sql, Transaction, Parameters.ToArray(), 0, 0); // get all ap details sql = String.Format( "SELECT Detail.* FROM PUB_{0} AS Doc, PUB_{8} AS Detail " + "WHERE Doc.{1} = {2} AND " + "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " + "{7} >= ? AND {7} <= ? AND " + "Detail.{1} = Doc.{1} AND Detail.{9} = Doc.{9} AND " + "Detail.{10} IN ({11})", AApDocumentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName(), AApDocumentDetailTable.GetTableDBName(), AApDocumentTable.GetApDocumentIdDBName(), AApDocumentDetailTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'"); AApDocumentDetailTable apDetails = new AApDocumentDetailTable(); DBAccess.GDBAccessObj.SelectDT(apDetails, sql, Transaction, Parameters.ToArray(), 0, 0); apDetails.DefaultView.Sort = AApDocumentDetailTable.GetApDocumentIdDBName(); // get all ap payments sql = String.Format( "SELECT DP.{0}, DP.{1}, P.{2} AS {3}, DP.{7}, DP.{15} FROM PUB_{4} AS Doc, PUB_{5} AS DP, PUB_{6} AS P " + "WHERE Doc.{7} = {8} AND " + "({9} = '{10}' OR {9} = '{11}' OR {9} = '{12}') AND " + "{13} >= ? AND {13} <= ? AND " + "DP.{7} = Doc.{7} AND DP.{14} = Doc.{14} AND " + "P.{7} = Doc.{7} AND P.{15} = DP.{15}", AApDocumentPaymentTable.GetApDocumentIdDBName(), AApDocumentPaymentTable.GetAmountDBName(), AApPaymentTable.GetPaymentDateDBName(), AApDocumentPaymentTable.GetDateCreatedDBName(), AApDocumentTable.GetTableDBName(), AApDocumentPaymentTable.GetTableDBName(), AApPaymentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName(), AApDocumentTable.GetApDocumentIdDBName(), AApPaymentTable.GetPaymentNumberDBName()); AApDocumentPaymentTable apPayments = new AApDocumentPaymentTable(); DBAccess.GDBAccessObj.SelectDT(apPayments, sql, Transaction, Parameters.ToArray(), 0, 0); apPayments.DefaultView.Sort = AApDocumentPaymentTable.GetApDocumentIdDBName(); // get the analysis attributes for the taxes sql = String.Format( "SELECT Attrib.* FROM PUB_{0} AS Doc, PUB_{8} AS Attrib " + "WHERE Doc.{1} = {2} AND " + "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " + "{7} >= ? AND {7} <= ? AND " + "Attrib.{1} = Doc.{1} AND Attrib.{9} = Doc.{9}", AApDocumentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName(), AApAnalAttribTable.GetTableDBName(), AApDocumentTable.GetApDocumentIdDBName()); AApAnalAttribTable apAttrib = new AApAnalAttribTable(); DBAccess.GDBAccessObj.SelectDT(apAttrib, sql, Transaction, Parameters.ToArray(), 0, 0); apAttrib.DefaultView.Sort = AApAnalAttribTable.GetApDocumentIdDBName() + "," + AApAnalAttribTable.GetDetailNumberDBName(); // get the supplier name sql = String.Format( "SELECT DISTINCT p.* FROM PUB_{0} AS Doc, PUB_{8} AS p " + "WHERE Doc.{1} = {2} AND " + "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " + "{7} >= ? AND {7} <= ? AND " + "p.{9} = Doc.{10}", AApDocumentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName(), PPartnerTable.GetTableDBName(), PPartnerTable.GetPartnerKeyDBName(), AApDocumentTable.GetPartnerKeyDBName()); PPartnerTable suppliers = new PPartnerTable(); DBAccess.GDBAccessObj.SelectDT(suppliers, sql, Transaction, Parameters.ToArray(), 0, 0); foreach (AApDocumentRow doc in apDocuments.Rows) { PPartnerRow supplier = (PPartnerRow)suppliers.Rows.Find(doc.PartnerKey); DataRowView[] detailsRV = apDetails.DefaultView.FindRows(doc.ApDocumentId); foreach (DataRowView rv in detailsRV) { AApDocumentDetailRow detail = (AApDocumentDetailRow)rv.Row; if (doc.CreditNoteFlag) { detail.Amount *= -1.0m; } DataRowView[] payments = apPayments.DefaultView.FindRows(doc.ApDocumentId); string DatePaid = string.Empty; if (payments.Length > 1) { DatePaid = "Several Payments: "; foreach (DataRowView rvPayment in payments) { AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)rvPayment.Row); DatePaid += payment.DateCreated.Value.ToString("yyyyMMdd") + " "; DatePaid += payment.PaymentNumber.ToString() + "; "; } // for gdpdu, only write dates to this column DatePaid = string.Empty; } else if (payments.Length == 1) { AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)payments[0].Row); DatePaid = payment.DateCreated.Value.ToString("yyyyMMdd"); } sb.Append(StringHelper.StrMerge( new string[] { doc.ApNumber.ToString(), detail.DetailNumber.ToString(), doc.DateIssued.ToString("yyyyMMdd"), DatePaid, doc.PartnerKey.ToString(), supplier.PartnerShortName, detail.CostCentreCode, detail.AccountCode, String.Format("{0:N}", detail.Amount), detail.Narrative }, ACSVSeparator)); sb.Append(ANewLine); } } DBAccess.GDBAccessObj.RollbackTransaction(); StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }
private void ApplySecurity() { if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PPartnerTable.GetTableDBName())) { // need to disable all Fields that are DataBound to p_partner // MessageBox.Show('Disabling p_partner fields...'); CustomEnablingDisabling.DisableControl(pnlNotes, txtPartnerComment); } }
public static DataTable GetAvailableSites() { TDBTransaction ReadTransaction = new TDBTransaction(); DataTable SitesTable = new DataTable(); DataTable UnusedSitesTable = new DataTable(); DataTable UsedSitesTable = new DataTable(); DataRow SitesRow; string IsPartnerLedger = SharedConstants.SYSMAN_AVAILABLE_SITES_COLUMN_IS_PARTNER_LEDGER; string SiteKey = PUnitTable.GetPartnerKeyDBName(); string SiteShortName = PUnitTable.GetUnitNameDBName(); Int64 PartnerKey; SitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool))); SitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64))); SitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string))); UnusedSitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool))); UnusedSitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64))); UnusedSitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string))); UsedSitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool))); UsedSitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64))); UsedSitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string))); DBAccess.ReadTransaction( ref ReadTransaction, delegate { try { // Load data string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + ", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() + " FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() + " WHERE ((" + PUnitTable.GetUnitTypeCodeDBName() + " = 'F')" + " OR(" + PUnitTable.GetUnitTypeCodeDBName() + " = 'A'))" + " AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'"; // sort rows according to name SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName(); DataTable sites = ReadTransaction.DataBaseObj.SelectDT(SqlStmt, "fields", ReadTransaction); foreach (DataRow tempSiteRow in sites.Rows) { PartnerKey = Convert.ToInt64(tempSiteRow[0]); // check if a site is already used in table p_partner_ledger if (PPartnerLedgerAccess.CountViaPUnit(PartnerKey, ReadTransaction) > 0) { SitesRow = UsedSitesTable.NewRow(); SitesRow[IsPartnerLedger] = true; SitesRow[SiteKey] = PartnerKey; SitesRow[SiteShortName] = Convert.ToString(tempSiteRow[1]); UsedSitesTable.Rows.Add(SitesRow); } else { SitesRow = UnusedSitesTable.NewRow(); SitesRow[IsPartnerLedger] = false; SitesRow[SiteKey] = PartnerKey; SitesRow[SiteShortName] = Convert.ToString(tempSiteRow[1]); UnusedSitesTable.Rows.Add(SitesRow); } } // first add used sites to table foreach (DataRow tempSiteRow in UsedSitesTable.Rows) { SitesRow = SitesTable.NewRow(); SitesRow[IsPartnerLedger] = tempSiteRow[IsPartnerLedger]; SitesRow[SiteKey] = tempSiteRow[SiteKey]; SitesRow[SiteShortName] = tempSiteRow[SiteShortName]; SitesTable.Rows.Add(SitesRow); } // and now add unused sites to table foreach (DataRow tempSiteRow in UnusedSitesTable.Rows) { SitesRow = SitesTable.NewRow(); SitesRow[IsPartnerLedger] = tempSiteRow[IsPartnerLedger]; SitesRow[SiteKey] = tempSiteRow[SiteKey]; SitesRow[SiteShortName] = tempSiteRow[SiteShortName]; SitesTable.Rows.Add(SitesRow); } } catch (Exception e) { TLogging.Log(e.ToString()); } }); return(SitesTable); }
public static bool SaveSiteKeys(List <Int64> ASiteKeysSetUpForUse, List <Int64> ASiteKeysToRemove) { TDBTransaction Transaction = new TDBTransaction(); bool SubmissionOK = true; PPartnerLedgerTable PartnerLedgerTable = new PPartnerLedgerTable(); PPartnerLedgerRow PartnerLedgerRow; // save site keys that can be used in p_partner_ledger DBAccess.WriteTransaction( ref Transaction, ref SubmissionOK, delegate { // create new records in p_partner_ledger if not there yet foreach (Int64 SiteKey in ASiteKeysSetUpForUse) { if (PPartnerLedgerAccess.CountViaPUnit(SiteKey, Transaction) == 0) { PartnerLedgerRow = PartnerLedgerTable.NewRowTyped(); PartnerLedgerRow.PartnerKey = SiteKey; // calculate last partner id, from older uses of this ledger number object MaxExistingPartnerKeyObj = Transaction.DataBaseObj.ExecuteScalar( String.Format("SELECT MAX(" + PPartnerTable.GetPartnerKeyDBName() + ") FROM " + PPartnerTable.GetTableDBName() + " WHERE " + PPartnerTable.GetPartnerKeyDBName() + " > {0} AND " + PPartnerTable.GetPartnerKeyDBName() + " < {1}", SiteKey, SiteKey + 500000), Transaction); if (MaxExistingPartnerKeyObj.GetType() != typeof(DBNull)) { // found a partner key for this site already: set it to last used value PartnerLedgerRow.LastPartnerId = Convert.ToInt32(Convert.ToInt64(MaxExistingPartnerKeyObj) - SiteKey); } else { // in this case there was no partner key for this site yet PartnerLedgerRow.LastPartnerId = 0; } PartnerLedgerTable.Rows.Add(PartnerLedgerRow); } } // delete records from p_partner_ledger that are no longer needed foreach (Int64 SiteKey in ASiteKeysToRemove) { PPartnerLedgerAccess.DeleteByPrimaryKey(SiteKey, Transaction); } PPartnerLedgerAccess.SubmitChanges(PartnerLedgerTable, Transaction); }); // make sure SitesList will be refreshed when called next time TPartnerCacheableWebConnector.RefreshCacheableTable(TCacheablePartnerTablesEnum.InstalledSitesList); return(SubmissionOK); }
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); }
public static PUnitTable GetActiveFieldUnits(string AFieldName) { PUnitTable UnitTable = new PUnitTable(); PUnitRow UnitRow; TDBTransaction ReadTransaction; Boolean NewTransaction = false; if (AFieldName == "*") { AFieldName = ""; } else if (AFieldName.EndsWith("*")) { AFieldName = AFieldName.Substring(0, AFieldName.Length - 1); } TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetActiveFieldUnits called!"); ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead, TEnforceIsolationLevel.eilMinimum, out NewTransaction); try { // Load data string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + ", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() + " FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() + " WHERE ((" + PUnitTable.GetOutreachCodeDBName() + " IS NULL)" + " OR(" + PUnitTable.GetOutreachCodeDBName() + " = ''))" + " AND " + PUnitTable.GetUnitTypeCodeDBName() + " <> 'KEY-MIN'" + " AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'"; if (AFieldName.Length > 0) { // in case there is a filter set for the event name AFieldName = AFieldName.Replace('*', '%') + "%"; SqlStmt = SqlStmt + " AND " + PUnitTable.GetUnitNameDBName() + " LIKE '" + AFieldName + "'"; } // sort rows according to name SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName(); DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "fields", ReadTransaction); foreach (DataRow eventRow in events.Rows) { UnitRow = (PUnitRow)UnitTable.NewRow(); UnitRow.PartnerKey = Convert.ToInt64(eventRow[0]); UnitRow.UnitName = Convert.ToString(eventRow[1]); UnitTable.Rows.Add(UnitRow); } } catch (Exception e) { TLogging.Log(e.ToString()); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); TLogging.LogAtLevel(7, "TPartnerDataReaderWebConnector.GetActiveFieldUnits: committed own transaction."); } } return(UnitTable); }