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 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 PUnitTable GetLedgerUnits() { PUnitTable UnitTable = new PUnitTable(); PUnitRow UnitRow; TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetLedgerUnits 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() + ", " + PPartnerTypeTable.GetTableDBName() + " WHERE pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " = pub_" + PPartnerTypeTable.GetTableDBName() + "." + PPartnerTypeTable.GetPartnerKeyDBName() + " AND " + PPartnerTypeTable.GetTypeCodeDBName() + " = '" + MPartnerConstants.PARTNERTYPE_LEDGER + "'"; // sort rows according to name SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName(); DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "ledgers", 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; } }
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 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 GetLedgerUnits(string ALedgerName) { PUnitTable UnitTable = new PUnitTable(); PUnitRow UnitRow; TDBTransaction ReadTransaction; Boolean NewTransaction = false; if (ALedgerName == "*") { ALedgerName = ""; } else if (ALedgerName.EndsWith("*")) { ALedgerName = ALedgerName.Substring(0, ALedgerName.Length - 1); } TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetLedgerUnits 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() + ", " + PPartnerTypeTable.GetTableDBName() + " WHERE pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " = pub_" + PPartnerTypeTable.GetTableDBName() + "." + PPartnerTypeTable.GetPartnerKeyDBName() + " AND " + PPartnerTypeTable.GetTypeCodeDBName() + " = '" + MPartnerConstants.PARTNERTYPE_LEDGER + "'"; if (ALedgerName.Length > 0) { // in case there is a filter set for the event name ALedgerName = ALedgerName.Replace('*', '%') + "%"; SqlStmt = SqlStmt + " AND " + PUnitTable.GetUnitNameDBName() + " LIKE '" + ALedgerName + "'"; } // sort rows according to name SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName(); DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "ledgers", 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.GetLedgerUnits: committed own transaction."); } } return(UnitTable); }