|
|
Imports C1.Win.C1TrueDBGrid
|
|
|
Imports System
|
|
|
Imports System.Data
|
|
|
Imports System.Data.SqlTypes
|
|
|
Imports System.Data.SqlClient
|
|
|
Namespace TKB.VV.Vertrag
|
|
|
|
|
|
Public Class clsVertragspartner
|
|
|
Inherits TKB.VV.DB.clsVertragspartner
|
|
|
|
|
|
#Region "Deklarationen"
|
|
|
Public Kontaktpersonen As TKB.VV.Sysadmin.DomainTable
|
|
|
Public Kontaktperson As New TKB.VV.Vertrag.clsPersonen
|
|
|
Public Neuer_Datensatz As Boolean = False
|
|
|
Public Vertragspartnerliste As New DataTable
|
|
|
|
|
|
Public fnt_Strikeout As Font
|
|
|
Public fnt_normal As Font
|
|
|
|
|
|
#End Region
|
|
|
''' <summary>
|
|
|
''' Destructor Dispose
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Overloads Sub dispose()
|
|
|
Kontaktpersonen = Nothing
|
|
|
Kontaktperson.Dispose()
|
|
|
MyBase.Dispose()
|
|
|
End Sub
|
|
|
''' <summary>
|
|
|
''' Vertragspartener auslesen
|
|
|
''' </summary>
|
|
|
''' <param name="Nr">Nr des Vertragspartners</param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Get_Partner(ByVal Nr As Integer)
|
|
|
Me.cpMainConnectionProvider = Globals.conn
|
|
|
Me.iVertragspartnerNr = New SqlInt32(CType(Nr, Int32))
|
|
|
Globals.conn.OpenConnection()
|
|
|
Me.SelectOne()
|
|
|
Globals.conn.CloseConnection(True)
|
|
|
End Function
|
|
|
''' <summary>
|
|
|
''' Vertragspartner speichern
|
|
|
''' </summary>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Save_Data() As Integer
|
|
|
Me.cpMainConnectionProvider = Globals.conn
|
|
|
Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
|
|
|
Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr.Value, Int32))
|
|
|
Globals.conn.OpenConnection()
|
|
|
Me.Update()
|
|
|
Globals.conn.CloseConnection(True)
|
|
|
Me.Neuer_Datensatz = False
|
|
|
Return Me.iVertragspartnerNr.Value
|
|
|
End Function
|
|
|
|
|
|
''' <summary>
|
|
|
''' Kontaktpersonen zu einem Vertragspartner auslesen
|
|
|
''' </summary>
|
|
|
''' <param name="c1data">Datagrid, welches die Kontaktpersonen anzeigt</param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Get_Kontaktpersonen(ByRef c1data As C1TrueDBGrid)
|
|
|
Try
|
|
|
Kontaktpersonen.dispose()
|
|
|
Kontaktpersonen = Nothing
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
Kontaktpersonen = New TKB.VV.Sysadmin.DomainTable("Person", Me.iVertragspartnerNr.ToString, Globals.clsmitarbeiter.iMitarbeiternr.Value)
|
|
|
c1data.DataSource = Kontaktpersonen.Tabledata
|
|
|
c1data.DataMember = Kontaktpersonen.Tabledata.Tables(0).TableName
|
|
|
End Function
|
|
|
|
|
|
''' <summary>
|
|
|
''' Kontaktpersonen zu einem Vertragspartner auslesen
|
|
|
''' </summary>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Get_Kontaktpersonen() As DataSet
|
|
|
Try
|
|
|
Kontaktpersonen.dispose()
|
|
|
Kontaktpersonen = Nothing
|
|
|
Catch ex As Exception
|
|
|
End Try
|
|
|
Kontaktpersonen = New TKB.VV.Sysadmin.DomainTable("Person", Me.iVertragspartnerNr.ToString, Globals.clsmitarbeiter.iMitarbeiternr.Value)
|
|
|
Return Kontaktpersonen.Tabledata
|
|
|
End Function
|
|
|
''' <summary>
|
|
|
''' Kopie eines Vertragspartners erstellen
|
|
|
''' </summary>
|
|
|
''' <param name="Basenr"></param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Create_Copy(Optional ByVal Basenr As Integer = 0) As Integer
|
|
|
If Basenr <> 0 Then
|
|
|
Get_Partner(Basenr)
|
|
|
End If
|
|
|
Dim db As New TKB.VV.DB.clsMyKey_Tabelle
|
|
|
db.cpMainConnectionProvider = Globals.conn
|
|
|
Dim newkey = db.get_dbkey("Vertragspartner")
|
|
|
db.Dispose()
|
|
|
|
|
|
Me.cpMainConnectionProvider = Globals.conn
|
|
|
Me.iVertragspartnerNr = New SqlInt32(CType(newkey, Int32))
|
|
|
Me.daErstellt_am = New SqlDateTime(CType(Now, DateTime))
|
|
|
Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
|
|
|
Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr.Value, Int32))
|
|
|
Globals.conn.OpenConnection()
|
|
|
Me.Insert()
|
|
|
Globals.conn.CloseConnection(True)
|
|
|
Me.Neuer_Datensatz = True
|
|
|
Return newkey
|
|
|
End Function
|
|
|
|
|
|
''' <summary>
|
|
|
''' Vertragspartner inaktivieren
|
|
|
''' </summary>
|
|
|
''' <param name="Basenr"></param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Delete_Partner(Optional ByVal Basenr As Integer = 0) As Integer
|
|
|
If Basenr <> 0 Then
|
|
|
Me.Get_Partner(Basenr)
|
|
|
End If
|
|
|
Me.cpMainConnectionProvider = Globals.conn
|
|
|
Me.bAktiv = New SqlBoolean(CType(False, Boolean))
|
|
|
Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
|
|
|
Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr.Value, Int32))
|
|
|
Globals.conn.OpenConnection()
|
|
|
Me.Update()
|
|
|
Globals.conn.CloseConnection(True)
|
|
|
Delete_Kontaktpersonen()
|
|
|
Delete_Vertragselemente(Me.iVertragspartnerNr.Value)
|
|
|
Me.Neuer_Datensatz = False
|
|
|
End Function
|
|
|
|
|
|
Public Function Delete_Vertragselemente(ByVal partnernr As Integer)
|
|
|
Dim dt As New DataTable
|
|
|
Dim scmCmdToExecute As SqlCommand = New SqlCommand()
|
|
|
Dim dtToReturn As DataTable = New DataTable()
|
|
|
Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
|
|
|
scmCmdToExecute.CommandText = "dbo.[sp_vertragselement_get_childs]"
|
|
|
scmCmdToExecute.CommandType = CommandType.StoredProcedure
|
|
|
scmCmdToExecute.Connection = conn.scoDBConnection
|
|
|
Try
|
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@vertragselementnr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, 0))
|
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@mitarbeiternr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.clsmitarbeiter.iMitarbeiternr.Value))
|
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@partnernr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, partnernr))
|
|
|
sdaAdapter.Fill(dtToReturn)
|
|
|
Dim Ve As New TKB.VV.DB.clsVertragselement
|
|
|
Dim dtve As New DataTable
|
|
|
ve.cpMainConnectionProvider = Globals.conn
|
|
|
For Each dr As DataRow In dtToReturn.Rows
|
|
|
Ve.iVertragselementnr = New SqlInt32(CType(dr.Item("Vertragselementnr"), Int32))
|
|
|
dtve.Rows.Clear()
|
|
|
dtve = Ve.SelectOne
|
|
|
If dtve.Rows.Count > 0 Then
|
|
|
Ve.bAktiv = New SqlBoolean(CType(False, Boolean))
|
|
|
Ve.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr.Value, Int32))
|
|
|
Ve.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
|
|
|
Globals.conn.OpenConnection()
|
|
|
Ve.Update()
|
|
|
Globals.conn.CloseConnection(True)
|
|
|
End If
|
|
|
|
|
|
Next
|
|
|
Ve.Dispose()
|
|
|
dtve.Dispose()
|
|
|
dtToReturn.Dispose()
|
|
|
Catch ex As Exception
|
|
|
Throw New Exception("Fehler beim L<>schen der Vertragselemente::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
|
|
|
Finally
|
|
|
scmCmdToExecute.Dispose()
|
|
|
sdaAdapter.Dispose()
|
|
|
dtToReturn.Dispose()
|
|
|
End Try
|
|
|
End Function
|
|
|
''' <summary>
|
|
|
''' Inaktivieren aller aktiven Kontaktpersonen zu einem Vertragspartner
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub Delete_Kontaktpersonen()
|
|
|
Dim i As Integer
|
|
|
For i = 0 To Me.Kontaktpersonen.Tabledata.Tables(0).Rows.Count - 1
|
|
|
If Me.Kontaktpersonen.Tabledata.Tables(0).Rows(i).Item("Aktiv") = True Then
|
|
|
Me.Kontaktperson.Delete_Kontakt(Me.Kontaktpersonen.Tabledata.Tables(0).Rows(i).Item("PersonNr"))
|
|
|
End If
|
|
|
Next
|
|
|
End Sub
|
|
|
''' <summary>
|
|
|
''' Vertragspartner physisch l<>schen
|
|
|
''' </summary>
|
|
|
''' <param name="Basenr"></param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Overloads Function Delete(Optional ByVal Basenr As Integer = 0) As Integer
|
|
|
If Basenr <> 0 Then
|
|
|
Me.Get_Partner(Basenr)
|
|
|
End If
|
|
|
Me.cpMainConnectionProvider = Globals.conn
|
|
|
Globals.conn.OpenConnection()
|
|
|
MyBase.Delete()
|
|
|
Globals.conn.CloseConnection(True)
|
|
|
Me.Neuer_Datensatz = False
|
|
|
End Function
|
|
|
''' <summary>
|
|
|
''' Neuer Vertragspartner
|
|
|
''' </summary>
|
|
|
''' <returns>Key des neuen Vertragspartners</returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Add_New() As Integer
|
|
|
Dim db As New TKB.VV.DB.clsMyKey_Tabelle
|
|
|
db.cpMainConnectionProvider = Globals.conn
|
|
|
Dim newkey = db.get_dbkey("Vertragspartner")
|
|
|
db.Dispose()
|
|
|
Me.iVertragspartnerNr = New SqlInt32(CType(newkey, Int32))
|
|
|
Me.sNameZ1 = New SqlString(CType("", String))
|
|
|
Me.sNameZ2 = New SqlString(CType("", String))
|
|
|
Me.sStrasse = New SqlString(CType("", String))
|
|
|
Me.sPostfach = New SqlString(CType("", String))
|
|
|
Me.sPLZ = New SqlString(CType("", String))
|
|
|
Me.sOrt = New SqlString(CType("", String))
|
|
|
Me.sTelefon = New SqlString(CType("", String))
|
|
|
Me.sTelefax = New SqlString(CType("", String))
|
|
|
Me.sEMail = New SqlString(CType("", String))
|
|
|
Me.sInternet = New SqlString(CType("", String))
|
|
|
Me.sBemerkung = New SqlString(CType("", String))
|
|
|
Me.bAktiv = New SqlBoolean(CType(True, Boolean))
|
|
|
Me.daErstellt_am = New SqlDateTime(CType(Now, DateTime))
|
|
|
Me.daMutiert_am = New SqlDateTime(CType(Now, DateTime))
|
|
|
Me.iMutierer = New SqlInt32(CType(Globals.clsmitarbeiter.iMitarbeiternr.Value, Int32))
|
|
|
Me.iSecurityLevelNr = New SqlInt32(CType(0, Int32))
|
|
|
Me.sSuchbegriffe = New SqlString(CType("", String))
|
|
|
Me.cpMainConnectionProvider = Globals.conn
|
|
|
Globals.conn.OpenConnection()
|
|
|
Me.Insert()
|
|
|
Globals.conn.CloseConnection(True)
|
|
|
Me.Neuer_Datensatz = True
|
|
|
Return newkey
|
|
|
End Function
|
|
|
|
|
|
''' <summary>
|
|
|
''' Standard-Vertragspartnername auslesen
|
|
|
''' </summary>
|
|
|
''' <param name="Vertragspartnernr">Nummer des Vertragspartners</param>
|
|
|
''' <returns>Name des Vertragspartners</returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Get_Vertragspartnername(ByVal Vertragspartnernr As Integer) As String
|
|
|
Get_Partner(Vertragspartnernr)
|
|
|
Try
|
|
|
Return Me.sNameZ1 + " " + Me.sNameZ2 + ", " + Me.sOrt
|
|
|
Catch ex As Exception
|
|
|
Return ""
|
|
|
End Try
|
|
|
End Function
|
|
|
''' <summary>
|
|
|
''' Vertragspartner suchen
|
|
|
''' </summary>
|
|
|
''' <param name="Vertragspartnernr"></param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Public Function Vertragspartner_Suchen(ByRef Vertragspartnernr As Integer) As Boolean
|
|
|
Dim vpsuche As New frmVertragspartnerSuche
|
|
|
vpsuche.ShowDialog()
|
|
|
Try
|
|
|
If vpsuche.DialogResult = DialogResult.OK Then
|
|
|
Vertragspartnernr = vpsuche.Vertragspartnernr
|
|
|
Return True
|
|
|
Else
|
|
|
Vertragspartnernr = -1
|
|
|
Return False
|
|
|
End If
|
|
|
Catch ex As Exception
|
|
|
Return False
|
|
|
Finally
|
|
|
vpsuche.Dispose()
|
|
|
End Try
|
|
|
End Function
|
|
|
|
|
|
Public Function Get_Vertragspartnerliste(ByVal Suchstring As String, ByRef tree As TreeView)
|
|
|
Dim scmCmdToExecute As SqlCommand = New SqlCommand()
|
|
|
Dim dtToReturn As DataTable = New DataTable()
|
|
|
Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
|
|
|
scmCmdToExecute.CommandText = "dbo.sp_get_vertragspartner"
|
|
|
scmCmdToExecute.CommandType = CommandType.StoredProcedure
|
|
|
scmCmdToExecute.Connection = conn.scoDBConnection
|
|
|
Try
|
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@mitarbeiternr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.clsmitarbeiter.iMitarbeiternr.Value))
|
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@SuchString", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Suchstring))
|
|
|
sdaAdapter.Fill(dtToReturn)
|
|
|
Load_Tree(tree, dtToReturn)
|
|
|
Me.Vertragspartnerliste = dtToReturn
|
|
|
Return dtToReturn
|
|
|
Catch ex As Exception
|
|
|
Throw New Exception("clsVertragsuebersicht::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
|
|
|
Finally
|
|
|
scmCmdToExecute.Dispose()
|
|
|
sdaAdapter.Dispose()
|
|
|
End Try
|
|
|
|
|
|
End Function
|
|
|
|
|
|
Public Function Get_Vertragspartnerliste(ByVal Suchstring As String) As DataTable
|
|
|
Dim scmCmdToExecute As SqlCommand = New SqlCommand()
|
|
|
Dim dtToReturn As DataTable = New DataTable()
|
|
|
Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
|
|
|
scmCmdToExecute.CommandText = "dbo.sp_get_vertragspartner"
|
|
|
scmCmdToExecute.CommandType = CommandType.StoredProcedure
|
|
|
scmCmdToExecute.Connection = conn.scoDBConnection
|
|
|
Try
|
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@mitarbeiternr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.clsmitarbeiter.iMitarbeiternr.Value))
|
|
|
scmCmdToExecute.Parameters.Add(New SqlParameter("@SuchString", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Suchstring))
|
|
|
sdaAdapter.Fill(dtToReturn)
|
|
|
Return dtToReturn
|
|
|
Catch ex As Exception
|
|
|
Throw New Exception("clsVertragsuebersicht::" & scmCmdToExecute.CommandText & "::Error occured." & ex.Message, ex)
|
|
|
Finally
|
|
|
scmCmdToExecute.Dispose()
|
|
|
sdaAdapter.Dispose()
|
|
|
End Try
|
|
|
|
|
|
End Function
|
|
|
|
|
|
''' <summary>
|
|
|
''' Treeview laden und je eintrag einen "Dummy-Eintrag" erstellen
|
|
|
''' </summary>
|
|
|
''' <param name="Tree"></param>
|
|
|
''' <returns></returns>
|
|
|
''' <remarks></remarks>
|
|
|
Private Function Load_Tree(ByRef Tree As TreeView, ByVal dt As DataTable)
|
|
|
fnt_Strikeout = New System.Drawing.Font(Tree.Font.FontFamily, Tree.Font.Size, FontStyle.Strikeout)
|
|
|
fnt_normal = New System.Drawing.Font(Tree.Font.FontFamily, Tree.Font.Size, FontStyle.Regular)
|
|
|
|
|
|
Tree.Enabled = False
|
|
|
Tree.Nodes.Clear()
|
|
|
For Each dr As DataRow In dt.Rows
|
|
|
Dim tn As New TreeNode
|
|
|
tn.Text = dr.Item("Description")
|
|
|
tn.Tag = dr.Item("Vertragspartnernr")
|
|
|
If dr.Item("aktiv") = True Then
|
|
|
tn.ImageIndex = 0
|
|
|
tn.SelectedImageIndex = 0
|
|
|
tn.StateImageIndex = 0
|
|
|
Else
|
|
|
tn.ImageIndex = 6
|
|
|
tn.SelectedImageIndex = 6
|
|
|
tn.StateImageIndex = 6
|
|
|
End If
|
|
|
Tree.Nodes.Add(tn)
|
|
|
Next
|
|
|
Tree.Enabled = True
|
|
|
End Function
|
|
|
|
|
|
End Class
|
|
|
|
|
|
End Namespace
|
|
|
|
