public DataTable searchById(string OrganisationsNr)
{
string query = "select OrganisationsNr, Name, CreditorSchemeID, BIC, IBAN from dbo.Organisationen where OrganisationsNr = " + "'" + OrganisationsNr + "'";
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("OrganisationsNr", SqlDbType.VarChar);
sqlParameters[0].Value = Convert.ToString(OrganisationsNr);
return(conn.executeSelectQuery(query, sqlParameters));
}
public DataTable searchById(string OrganisationsNr, string DatumAbrechnung)
{
string query = "select distinct PaymentInformationID, PaymentMethod, BatchBooking, NumberOfTransactionsPaymentInfo, ControlSumPaymentInfo, ServiceLevelCode, LocalInstrumentCode, SequenceType, RequestedCollectionDate, CreditorName, CreditorIdentification, CreditorAgentBIC, CreditorAccountIBAN, ChargeBearer from dbo.Lastschriften where OrganisationsNr = " + "'" + OrganisationsNr + "' and DatumAbrechnung = " + "'" + DatumAbrechnung + "'";
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("OrganisationsNr", SqlDbType.VarChar);
sqlParameters[0].Value = Convert.ToString(OrganisationsNr);
sqlParameters[1] = new SqlParameter("DatumAbrechnung", SqlDbType.VarChar);
sqlParameters[1].Value = Convert.ToString(DatumAbrechnung);
return(conn.executeSelectQuery(query, sqlParameters));
}
public DataTable readDTA()
{
string query = @"SELECT Bankleitzahl,
Kontonummer,
InterneKundennummer,
GegenkontoBankleitzahl,
GegenkontoNr,
BetragInEuro,
Valutadatum,
Kontoinhaber,
GegenkontoInhaber,
Verwendungszweck
FROM DELL.Mitgliederverwaltung.dbo.DTA";
return(conn.executeSelectQuery(query));
}
public DataTable searchById(string _sOrganisationsNr, string _sSequenceType, string _sDatumAbrechnung = "NULL")
{
if (_sDatumAbrechnung == "NULL")
{
query = @"SELECT OrganisationsNr,
MessageID,
CreationDateTime,
NumberOfTransactions,
ControlSum,
InitiatingPartyname,
PaymentInformationID,
PaymentMethod,
BatchBooking,
NumberOfTransactionsPaymentInfo,
ControlSumPaymentInfo,
ServiceLevelCode,
LocalInstrumentCode,
SequenceType,
CategoryPurpose,
RequestedCollectionDate,
CreditorName,
CreditorPostalAddressCountry,
CreditorPostalAddressAddressLine,
CreditorAccountIBAN,
CreditorAccountCurrency,
CreditorAgentBIC,
ChargeBearer,
CreditorIdentification,
InstructionID,
EndToEndID,
MandateID,
DateOfSignature,
AmendmentIndicator,
OriginalMandateID,
OriginalCreditorName,
OriginalCreditorIdentification,
OriginalDebtorAccountIBAN,
OriginalDebtorAgentBIC,
CreditorIdentificationTransactionInfo,
DebtorName as 'Teilnehmer',
DebtorAgentBIC as 'BIC',
RTRIM(DebtorAccountIBAN) as 'IBAN',
InstructedAmount as 'Betrag',
DebtorPostalAddressCountry,
DebtorPostalAddressAddressLine,
PurposeCode,
UnstructuredRemittanceInfo as 'Verwendungszweck',
LastschriftStatus,
DatumImport,
DatumAbrechnung
FROM dbo.Lastschriften
WHERE OrganisationsNr = " + "'" + _sOrganisationsNr + "' " + "and SequenceType = " + "'" + _sSequenceType + "' " + "and DatumAbrechnung is NULL " + "order by DebtorName";
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("OrganisationsNr", SqlDbType.VarChar);
sqlParameters[0].Value = Convert.ToString(_sOrganisationsNr);
sqlParameters[1] = new SqlParameter("SequenceType", SqlDbType.VarChar);
sqlParameters[1].Value = Convert.ToString(_sSequenceType);
return(conn.executeSelectQuery(query, sqlParameters));
}
else
{
query = @"SELECT OrganisationsNr,
MessageID,
CreationDateTime,
NumberOfTransactions,
ControlSum,
InitiatingPartyname,
PaymentInformationID,
PaymentMethod,
BatchBooking,
NumberOfTransactionsPaymentInfo,
ControlSumPaymentInfo,
ServiceLevelCode,
LocalInstrumentCode,
SequenceType,
CategoryPurpose,
RequestedCollectionDate,
CreditorName,
CreditorPostalAddressCountry,
CreditorPostalAddressAddressLine,
CreditorAccountIBAN,
CreditorAccountCurrency,
CreditorAgentBIC,
ChargeBearer,
CreditorIdentification,
InstructionID,
EndToEndID,
MandateID,
DateOfSignature,
AmendmentIndicator,
OriginalMandateID,
OriginalCreditorName,
OriginalCreditorIdentification,
OriginalDebtorAccountIBAN,
OriginalDebtorAgentBIC,
CreditorIdentificationTransactionInfo,
DebtorName as 'Teilnehmer',
DebtorAgentBIC as 'BIC',
RTRIM(DebtorAccountIBAN) as 'IBAN',
InstructedAmount as 'Betrag',
DebtorPostalAddressCountry,
DebtorPostalAddressAddressLine,
PurposeCode,
UnstructuredRemittanceInfo as 'Verwendungszweck',
LastschriftStatus,
DatumImport,
DatumAbrechnung
FROM dbo.Lastschriften
WHERE OrganisationsNr = " + "'" + _sOrganisationsNr + "' " + "and SequenceType = " + "'" + _sSequenceType + "' " + "and DatumAbrechnung = " + "'" + _sDatumAbrechnung + "' " + "order by DebtorName";
SqlParameter[] sqlParameters = new SqlParameter[3];
sqlParameters[0] = new SqlParameter("OrganisationsNr", SqlDbType.VarChar);
sqlParameters[0].Value = Convert.ToString(_sOrganisationsNr);
sqlParameters[1] = new SqlParameter("SequenceType", SqlDbType.VarChar);
sqlParameters[1].Value = Convert.ToString(_sSequenceType);
sqlParameters[2] = new SqlParameter("DatumAbrechnung", SqlDbType.VarChar);
sqlParameters[2].Value = Convert.ToString(_sDatumAbrechnung);
return(conn.executeSelectQuery(query, sqlParameters));
}
}
